PostgreSQL
Run your Models on Postgres, through Hyperdrive in a Worker or directly on Node.
Installing
The PostgreSQL adapter comes with @forestfuture/active-record, as its /postgresql entry. Install the library with pg, and PGlite for reading your schema:
npm install @forestfuture/active-record pg
npm install --save-dev @electric-sql/pglite
Then name your database in active-record.jsonc, with npx active-record init --adapter postgresql.
A Worker on Postgres needs "compatibility_flags": ["nodejs_compat"] in its Wrangler config, for pg. To move an app from D1, see Moving to Postgres.
Your Models, queries, and callbacks work the same on Postgres. The sections after Connecting are the places Postgres works differently from D1.
Connecting
connect takes a Hyperdrive binding, a connection string, or a pg Pool. Open the request’s database from your Hyperdrive binding:
import { withDatabase } from "@forestfuture/active-record";
import { connect } from "@forestfuture/active-record/postgresql";
export default {
fetch(request, env) {
const db = connect(env.HYPERDRIVE);
return withDatabase(db, () => app.fetch(request, env));
},
};
The first query inside withDatabase opens a client, and the rest of the request shares it. It closes when withDatabase finishes. Each withDatabase call gets its own client, so a Database your Worker opens once can serve every request. Without withDatabase, each query opens a client of its own and closes it when it’s done. So does work that outlives the request, such as a save in ctx.waitUntil.
Your schema names the binding with adapter: postgresql({ binding: "HYPERDRIVE" }), so User.find(id) works in a Worker with nothing to set up. Declare it in your active-record.jsonc, where the command finds your databases.
connect(env.DATABASE_URL) takes a connection string the same way.
On Node, pass your own Pool, which your app ends when it shuts down:
import pg from "pg";
import { connect } from "@forestfuture/active-record/postgresql";
const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });
const db = connect(pool);
Outside a Worker there’s no binding, so wrap each request in withDatabase(db, …) for your Models’ statics.
Opening a client from a binding or a connection string waits up to 10 seconds for Postgres to answer. After that, the query fails with Error: timeout expired. Each query waiting for that client tries again, and can wait as long. To change the limit, set connect_timeout to a number of seconds:
import { connect } from "@forestfuture/active-record/postgresql";
const db = connect("postgres://app@db.example.com/app?connect_timeout=5");
If you want no limit, set connect_timeout=0.
Batches are transactions
A batch is written whole or not at all, as on D1. On Postgres its writes run between BEGIN and COMMIT, on a client of its own. A query your request sends while the batch runs stays outside the transaction, so a failed batch never rolls it back.
Reads run one after another
On D1, a preload or a page sends all its queries in one round trip. On Postgres they run one after another, on one client. A page’s total comes from its own query, so a write between the two can make it disagree with the page’s records for a moment.
Queries you send at once with Promise.all inside one withDatabase call also run one after another, on that call’s client.
Large integers
A bigint column reads as a number. A value past Number.MAX_SAFE_INTEGER throws a TypeError naming its attribute, rather than reading as a number that’s slightly wrong. For exact numbers of any size, use a numeric column and a decimal attribute, which keeps every digit.
Placeholders
onQuery and toSql() show the SQL Postgres runs, with $1, $2, and so on where D1 shows ?.
Raw SQL stays on its database
An sql fragment is sent as you wrote it. A fragment that uses SQLite’s functions fails on Postgres, and one that uses Postgres’s fails on D1.
Sorting
Postgres sorts NULL after every value: last in ascending order, and first in descending order. D1 sorts it first in ascending order. Place NULLs yourself to sort them the same on both:
User.order({ role: { direction: "asc", nulls: "last" } });
Postgres sorts text by your database’s collation, usually its locale’s, where capitals and lowercase letters interleave. D1 sorts by code point, so every capital comes before every lowercase letter.
Distinct records ordered by another column
Postgres refuses to order distinct() records by a column they don’t select, and throws its own error. Select the column, or order by one you select.
After distinct(), pluck("title") selects only title. So does count("title") with a limit or offset. Order them by title, or leave the order out.
NUL characters
Postgres text, and strings in a jsonb value, can’t hold the NUL character (\u0000). A value with one fails with Postgres’s own error.
Keys written by hand
A key you set yourself doesn’t move a Postgres identity column. The next key Postgres makes can then be one you’ve already used, which fails as a duplicate.
Constraint errors name their table
Postgres’s constraint errors name their table and columns. So a batch’s foreign-key failure has .table and .columns, where D1’s has .table null when the batch writes to several tables.
Migrations are applied by the command
On D1, active-record migrate runs Wrangler, which records each migration by its file name. On Postgres, the command applies them itself. It records each one’s version in a schema_migrations table. A version is the number a file’s name starts with, so every file’s name needs one. Renaming the rest of the name doesn’t run the migration again.
Each migration runs in a transaction. One that fails leaves nothing behind, since Postgres can undo a CREATE TABLE or an ALTER TABLE. A statement that can’t run in a transaction, such as CREATE INDEX CONCURRENTLY, fails.
Migrating the real database needs its connection string and a route to its server.
Reading the schema
active-record schema reads your schema by applying your migrations to PGlite, Postgres in memory, so it needs no server. A migration that creates an extension PGlite doesn’t have, such as PostGIS, is read on the server your schemaUrl names instead.
Postgres’s column types decide the attributes. Unlike on D1, a column’s name never does, so an is_active BIGINT is an integer.
Adding a required column
A generated migration can add a NOT NULL column without a default, which SQLite refuses. Postgres runs it on an empty table, and refuses it on a table with rows.
Migrations in folders
The command reads a Postgres database’s migrations from its folder, not from folders inside it. Migrations a tool writes in folders, such as migrations/0000_init/migration.sql, aren’t read.
jsonb keeps the value
A jsonb column keeps a JSON value, not the text you wrote. A raw query reads it back with Postgres’s own spacing and key order. Your Models read the same value either way.