---
title: PostgreSQL
description: "Run your Models on Postgres, through Hyperdrive in a Worker or directly on Node."
seo:
  description: "Use Active Record on Postgres: install pg, connect through Hyperdrive or a Pool, and what works differently from D1, from transactions to sorting."
---

## Installing

The PostgreSQL adapter comes with `@forestfuture/active-record`, as its `/postgresql` entry. Install the library with `pg`, and PGlite for reading your schema:

```sh
npm install @forestfuture/active-record pg
npm install --save-dev @electric-sql/pglite
```

Then name your database in [`active-record.jsonc`](/command-line#declaring-your-databases), 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](/move-to-postgres).

Your Models, queries, and callbacks work the same on Postgres. The sections after [Connecting](#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:

```ts
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`](/command-line#declaring-your-databases), where the command finds your databases.

`connect(env.DATABASE_URL)` takes a connection string the same way.

:::tip
On Workers, use Hyperdrive. It pools connections, so a new client doesn’t mean a new connection to Postgres.
:::

On Node, pass your own Pool, which your app ends when it shuts down:

```ts
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:

```ts
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`.

:::note
A Hyperdrive binding’s connection string isn’t yours to change, so it always waits up to 10 seconds. A Pool uses its own `connectionTimeoutMillis`.
:::

> **Compared with Rails**
>
> Rails sets no limit, so opening a connection waits as long as it takes unless you set `connect_timeout`. Here the limit is 10 seconds unless you set one.

## Batches are transactions

A [batch](/batches#batching-writes) 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](/preloading) or a [page](/ordering-and-pagination#paginating) sends all its queries [in one round trip](/d1#sending-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-types#storing-exact-numbers-days-and-times) 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](/conditions#writing-sql-fragments) 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:

```ts
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](/command-line#active-record-migrate) 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](https://pglite.dev), 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`](/command-line#mapping-postgress-columns) 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.
