# PostgreSQL

> Connect to PostgreSQL from JavaScript and TypeScript with `elide:postgres`: pooled connections, parameterised queries, transactions, savepoints and batches.

`elide:postgres` connects to a PostgreSQL server through a connection pool. Every call returns a Promise. The same API
drives [MySQL](https://elide.help/docs/api-mysql).

```javascript
// pg.mjs
import { Database } from "elide:postgres";

const db = await Database.connect({
  host: "127.0.0.1",
  port: 5432,
  database: "app",
  user: "app",
  password: process.env.DB_PASSWORD,
  pool: { maxSize: 4 },
});

await db.exec("CREATE TABLE IF NOT EXISTS users (id SERIAL PRIMARY KEY, name TEXT NOT NULL, age INT)");
console.log(await db.exec("INSERT INTO users (name, age) VALUES ($1, $2)", "Ada", 36));

const adults = await db.query("SELECT id, name, age FROM users WHERE age > $1", 18);
console.log(await adults.all());
console.log(await (await db.query("SELECT name FROM users WHERE id = $1", 999)).get());

const [a, b] = await Promise.all([
  db.query("SELECT count(*) AS n FROM users"),
  db.query("SELECT max(age) AS oldest FROM users"),
]);
console.log(await a.get(), await b.get());
console.log(db.poolStats.maxConnections);

await db.close();
```

With `DB_PASSWORD` set in the environment:

```bash
elide run pg.mjs
```

```text
{affectedRows: 1, insertId: 1, insertIds: [1]}
[{id: 1, name: "Ada", age: 36}]
null
{n: 1} {oldest: 36}
4
```

## Connect

`Database.connect()` takes an options object or a `postgresql://` URL with user, password, host, port and database.
It resolves to a database backed by a connection pool.

| Option | Default |
| --- | --- |
| `database`, `user`, `password` | Required; a missing one fails with `password is required` (or that field's name) |
| `host` | `"localhost"` |
| `port` | `5432` |
| `pool.minSize`, `pool.maxSize` | `2`, `10` |
| `pool.connectionTimeout`, `pool.idleTimeout`, `pool.maxLifetime` | `5000`, `30000`, `1800000` ms |
| `pool.leakDetectionThreshold` | `0` (off), ms |
| `ssl.enabled`, `ssl.rejectUnauthorized` | `false`, `true` |
| `ssl.ca`, `ssl.cert`, `ssl.key` | Certificate file paths |
| `properties` | Extra driver properties, as strings |

A refused connection or a wrong password rejects with `Failed to connect to PostgreSQL: …`.

## Queries and rows

| Member | Resolves to |
| --- | --- |
| `query(sql, ...params)` | A result; `await` its `all()` (rows as objects) or `get()` (first row or `null`) |
| `exec(sql, ...params)` | `{ affectedRows, insertId, insertIds }` |
| `prepare(sql)` | A statement with `query()`, `exec()`, `executeBatch(paramLists)` and `close()` |
| `batch([[sql, params], …])` | One `exec` result per statement |
| `transaction(async (tx) => …)` | The callback's return value |
| `close()` | Closes the pool; `connected` becomes `false` and later queries reject with `Database connection is closed` |

Parameters are `$1`, `$2`, … or `?`. Numbers, strings, booleans and `null` map to their SQL types; timestamps read as
strings. A SQL error rejects when you read the result (`all()`, `get()`), not when `query()` resolves.

`poolStats` reports `activeConnections`, `idleConnections`, `totalConnections`, `pendingRequests`, `maxConnections` and
`minConnections`.

## Transactions

```javascript
// tx.mjs
import { Database } from "elide:postgres";

const db = await Database.connect(process.env.DATABASE_URL);

const age = await db.transaction(async (tx) => {
  await tx.exec("UPDATE users SET age = age + 1 WHERE name = $1", "Ada");
  await tx.savepoint("before_cleanup");
  await tx.exec("DELETE FROM users");
  await tx.rollbackTo("before_cleanup");
  return (await tx.query("SELECT age FROM users WHERE name = $1", "Ada")).get();
});
console.log(age);

try {
  await db.transaction(async (tx) => {
    await tx.exec("DELETE FROM users");
    throw new Error("abort");
  });
} catch (e) {
  console.log(e.message);
}
console.log(await (await db.query("SELECT count(*) AS n FROM users")).get());

const update = await db.prepare("UPDATE users SET age = $1 WHERE name = $2");
console.log(await update.executeBatch([[37, "Ada"], [42, "Alan"]]));
update.close();

console.log(await db.batch([
  ["INSERT INTO users (name, age) VALUES ($1, $2)", ["Grace", 85]],
  ["DELETE FROM users WHERE name = $1", ["Grace"]],
]));
await db.close();
```

With `DATABASE_URL` set to the database's `postgresql://` URL:

```bash
elide run tx.mjs
```

```text
{age: 37}
Error: abort
{n: 1}
(2)[{affectedRows: 1, insertId: null, insertIds: []}, {affectedRows: 0, insertId: null, insertIds: []}]
(2)[{affectedRows: 1, insertId: 2, insertIds: [2]}, {affectedRows: 1, insertId: null, insertIds: []}]
```

The transaction commits when the callback resolves and rolls back when it throws or rejects. `tx` has `query`, `exec`,
`commit()`, `rollback()`, `savepoint(name)`, `rollbackTo(name)`, `active`, and `isolation` (default
`"READ_COMMITTED"`).

## Samples

- [PostgreSQL queries](https://elide.help/docs/code-samples#postgresql-queries)
- [Databases](https://elide.help/docs/api-database): choosing a driver.