PostgreSQL

elide:postgres connects to a PostgreSQL server through a connection pool. Every call returns a Promise. The same API drives MySQL.

// 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:

elide run pg.mjs

{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

// 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:

elide run tx.mjs

{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
Databases: choosing a driver.