انتقل إلى المحتوى

لم تُترجم هذه الصفحة بعد، لذا تُعرض بالإنجليزية.

PostgreSQL

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

في هذه الصفحة
Open in ChatGPTOpen in Claude

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.

OptionDefault
database, user, passwordRequired; a missing one fails with password is required (or that field’s name)
host"localhost"
port5432
pool.minSize, pool.maxSize2, 10
pool.connectionTimeout, pool.idleTimeout, pool.maxLifetime5000, 30000, 1800000 ms
pool.leakDetectionThreshold0 (off), ms
ssl.enabled, ssl.rejectUnauthorizedfalse, true
ssl.ca, ssl.cert, ssl.keyCertificate file paths
propertiesExtra driver properties, as strings

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

Queries and rows

MemberResolves 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

هل كانت هذه الصفحة مفيدة؟