Postgres — Cow

Connect to a database

This guide shows you how to connect a page to Postgres with cow:postgres, run queries with bound parameters, and group changes in a transaction.

The adapter uses the pg driver. Install it in the folder that holds your site:

npm install pg

Then keep the connection in a private helper. Cow keeps one pool for each worker open between requests.

<?js
import { postgres } from 'cow:postgres'

export default await postgres(process.env.DATABASE_URL)

Run queries

Every method returns a promise, so await it. Write $1, $2, and so on for parameters.

<?js
import db from './_db.cow'

await db.run('INSERT INTO notes (title) VALUES ($1)', ['A little idea'])
const notes = await db.all('SELECT id, title FROM notes ORDER BY id DESC')
?>
<ul>
  <?js for (const note of notes) { ?>
    <li><?= note.title ?></li>
  <?js } ?>
</ul>

all(sql, parameters) returns rows. query() is an alias.
get(sql, parameters) returns a row or undefined.
run(sql, parameters) returns { changes, rows }. Add RETURNING id to read a new row's ID from rows.
exec(sql) runs SQL without parameters.

Timestamp columns arrive as Date objects. bigint and numeric columns arrive as strings, so no digits are lost.

Group changes in a transaction

const id = await db.transaction(async tx => {
  const added = await tx.run('INSERT INTO notes (title) VALUES ($1) RETURNING id', ['First idea'])
  await tx.run('INSERT INTO tags (note_id, name) VALUES ($1, $2)', [added.rows[0].id, 'ideas'])
  return added.rows[0].id
})

Run the queries through tx. It holds one connection until the callback finishes. If the callback throws, the transaction rolls back. A nested tx.transaction() uses a savepoint. To choose an isolation level, pass { isolation: 'serializable' }.

Note: Await the transaction before you end the response. Ending it inside the callback fails the request with COW_POSTGRES_RESPONSE_IN_TRANSACTION and rolls back.

Change the schema with migrations

<?js
import { postgres } from 'cow:postgres'

const db = await postgres(process.env.DATABASE_URL)

await db.migrate([
  'CREATE TABLE notes (id serial PRIMARY KEY, title text NOT NULL)',
  'ALTER TABLE notes ADD COLUMN pinned boolean NOT NULL DEFAULT false'
])

export default db

Each step runs once, in order, and the cow_migrations table records it. Pending steps run in one transaction under an advisory lock, so two servers never migrate at once. When nothing is pending, migrate() reads one row.

Warning: Never edit, reorder, or remove a step that has run. Add new steps to the end of the list.

Connection options

await postgres(process.env.DATABASE_URL, {
  max: 5,                // connections in each worker's pool
  connectTimeout: 10000, // milliseconds
  idleTimeout: 30000,    // milliseconds
  statementTimeout: 0,   // milliseconds, 0 means no limit
  ssl: { rejectUnauthorized: true }
})

A site can open up to workers × max connections. Keep that below the server's max_connections. Sessions from cow:web still need a SQLite file.