Skip to content
Active Record
Esc
↑↓navigate↵open⌘Jpreview
On this page

Moving to Postgres

Move a D1 app to Postgres, with its Models and code unchanged.

Planning the move

The examples move the app from Getting started.

What changes How
Packages Add pg, and PGlite for reading your schema.
active-record.jsonc It names your Postgres database in place of the D1 one.
migrations/ Rewritten in Postgres’s types, with the same file names.
src/db/schema.ts Regenerated, so it names the PostgreSQL adapter.
Your Wrangler config A Hyperdrive binding in place of the D1 one.
Your data Copied from D1 to Postgres.

You’ll need sqlite3 and psql on your machine to copy the data.

Installing pg

The PostgreSQL adapter comes with @forestfuture/active-record. Install pg, which it connects with, and PGlite, which active-record schema reads your schema with:

npm install pg
npm install --save-dev @electric-sql/pglite

Declaring the database

Replace the D1 database in your active-record.jsonc with your Postgres one:

{
  "$schema": "./node_modules/@forestfuture/active-record/config-schema.json",
  "databases": {
    "HYPERDRIVE": {
      "adapter": "postgresql",
      "url": "postgres://user:password@localhost:5432/my_app",
      "remoteUrl": "${PRODUCTION_DATABASE_URL}",
    },
  },
}

HYPERDRIVE is the binding your Worker will reach Postgres through. url is your local database, and remoteUrl your real one, read here from the environment. Every active-record command now works on Postgres. See Declaring your databases.

Rewriting the migrations

Rewrite each migration in Postgres’s types, and keep its file name. Here are the app’s users:

CREATE TABLE users (
  id UUID PRIMARY KEY,
  email TEXT NOT NULL,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMPTZ NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL
);
CREATE UNIQUE INDEX index_users_on_email ON users (email);

And its posts:

CREATE TABLE posts (
  id UUID PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES users (id),
  title TEXT NOT NULL,
  published_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL,
  updated_at TIMESTAMPTZ NOT NULL
);
CREATE INDEX index_posts_on_user_id ON posts (user_id);

These are the files g model writes on Postgres for the same commands. Change each type this way:

On D1 On Postgres
id TEXT PRIMARY KEY, made in code id UUID PRIMARY KEY
id INTEGER PRIMARY KEY id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
INTEGER BIGINT
REAL DOUBLE PRECISION
DATETIME TIMESTAMPTZ
BOOLEAN, TEXT The same
TEXT holding JSON JSONB

If your generateId makes keys that aren’t UUIDs, keep those keys TEXT, and their foreign keys too.

A datetime kept as a Unix time has a storage override. It can stay BIGINT, and keep the override. To store it as TIMESTAMPTZ instead, add a migration that converts it after you copy the data. Then remove the override from your Model:

ALTER TABLE payments ALTER COLUMN paid_at TYPE TIMESTAMPTZ USING to_timestamp(paid_at);

For milliseconds, convert to_timestamp(paid_at / 1000.0).

Then create the tables in your local database:

npx active-record migrate

Regenerating the schema

Regenerate your schema from the new migrations:

npx active-record schema

Its attributes come out the same as on D1. Only its adapter changes:

import { defineSchema } from "@forestfuture/active-record";
import { postgresql } from "@forestfuture/active-record/postgresql";

export const schema = defineSchema({
  adapter: postgresql({ binding: "HYPERDRIVE" }),
  // …
});

Copying the data

Copy your rows with this script. It exports your D1 database through Wrangler, writes each table as CSV with sqlite3, and loads them into Postgres with psql, all in one transaction:

#!/bin/sh
# Copies rows from the D1 database bound as DB to the Postgres database DATABASE_URL names.
#
#   DATABASE_URL=postgres://… sh copy-data.sh --remote users posts
#
# Name each table after the tables its foreign keys point at. Use --local for your local D1.
set -eu

fail() {
  echo "copy-data.sh: $1" >&2
  exit 1
}

d1=$1 # --remote or --local
shift
# Names go into SQL, so each must be letters, digits and underscores.
for table in "$@"; do
  case $table in
    "" | [0-9]* | *[!A-Za-z0-9_]*) fail "can't copy a table named '$table'" ;;
  esac
done

work=$(mktemp -d)
trap 'rm -rf "$work"' EXIT
# dash runs no EXIT trap on Ctrl-C, so exit first, and the copies of your data go too.
trap 'exit 1' INT TERM HUP

# D1's tables and rows, as SQL, loaded into a SQLite file of your own.
npx wrangler d1 export DB "$d1" --output "$work/d1.sql"
sqlite3 "$work/d1.db" < "$work/d1.sql"

# Each table as CSV. sqlite3 quotes empty text and leaves NULL unquoted, as Postgres reads them.
for table in "$@"; do
  info="FROM pragma_table_info('$table')"
  odd=$(sqlite3 "$work/d1.db" "SELECT count(*) $info WHERE name GLOB '[0-9]*' OR name GLOB '*[^A-Za-z0-9_]*'")
  [ "$odd" = 0 ] || fail "$table has a column whose name isn't letters, digits and underscores"
  columns=$(sqlite3 "$work/d1.db" "SELECT group_concat('\"' || name || '\"', ', ') $info")
  [ -n "$columns" ] || fail "D1 has no table named $table"
  sqlite3 -csv -header "$work/d1.db" "SELECT $columns FROM \"$table\"" > "$work/$table.csv"
  # Postgres folds an unquoted migration's names to lowercase, so they're named that way here.
  pg_table=$(printf '%s' "$table" | tr '[:upper:]' '[:lower:]')
  pg_columns=$(printf '%s' "$columns" | tr '[:upper:]' '[:lower:]')
  printf '%s\n' "\\copy \"$pg_table\" ($pg_columns) FROM '$work/$table.csv' WITH (FORMAT csv, HEADER true)" >> "$work/copy.sql"
done

# Every table in one transaction, so a failure copies nothing.
psql "$DATABASE_URL" --single-transaction -v ON_ERROR_STOP=1 --quiet --file "$work/copy.sql"

Copy your local data to your local database:

DATABASE_URL=postgres://user:password@localhost:5432/my_app sh copy-data.sh --local users posts

Name each table after the tables it points at, so users comes before posts.

Switching the binding to Hyperdrive

Create a Hyperdrive config for your real database:

npx wrangler hyperdrive create my-app --connection-string="$PRODUCTION_DATABASE_URL"

Then bind it in your Wrangler config, with the nodejs_compat flag pg needs. Keep your D1 binding there too:

{
  "name": "my-app",
  "main": "src/index.ts",
  "compatibility_date": "2026-09-01",
  "compatibility_flags": ["nodejs_compat"],
  "hyperdrive": [
    {
      "binding": "HYPERDRIVE",
      "id": "<the id it printed>",
      "localConnectionString": "postgres://user:password@localhost:5432/my_app",
    },
  ],
  // copy-data.sh exports D1 through this binding. Remove it once you've copied your real data.
  "d1_databases": [{ "binding": "DB", "database_name": "my-app-db", "database_id": "…" }],
}

npx wrangler dev now runs your app on your local Postgres.

Going live

Then create your tables on the real database, copy the data, and deploy:

npx active-record migrate --remote
DATABASE_URL="$PRODUCTION_DATABASE_URL" sh copy-data.sh --remote users posts
npx wrangler deploy

Removing the D1 database

Once your app runs on Postgres, remove the d1_databases entry from your Wrangler config.

Last updated on