---
title: Moving to Postgres
description: "Move a D1 app to Postgres, with its Models and code unchanged."
seo:
  description: "Move an Active Record app from Cloudflare D1 to Postgres with Hyperdrive: rewrite the migrations, regenerate the schema, copy the data, and keep your Models."
---

## Planning the move

The examples move the app from [Getting started](/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:

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

```jsonc active-record.jsonc
{
  "$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](/command-line#declaring-your-databases).

:::note
Hyperdrive needs a password in a local connection string, even for a server that asks for none.
:::

## Rewriting the migrations

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

```sql migrations/20261108090503_create_users.sql
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:

```sql migrations/20261108090504_create_posts.sql
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:

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

```sh
npx active-record migrate
```

## Regenerating the schema

Regenerate your schema from the new migrations:

```sh
npx active-record schema
```

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

```ts src/db/schema.ts
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:

```sh copy-data.sh
#!/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:

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

:::note
If a table has integer keys, set its identity past the keys you copied, so the next row Postgres makes doesn’t take one of them:

```sql
SELECT setval(pg_get_serial_sequence('comments', 'id'), (SELECT max(id) FROM comments));
```

:::

## Switching the binding to Hyperdrive

Create a Hyperdrive config for your real database:

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

```jsonc wrangler.jsonc
{
  "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

:::warning[Stop writes before you copy]
A write that reaches D1 after you copy its data never reaches Postgres. Stop writes first, for example with a deploy that answers them with a 503.
:::

:::note
While it copies, `psql` shows its connection string to anyone on your machine who lists its processes. Copy from a machine only you use. To check your server’s certificate, add `sslmode=verify-full&sslrootcert=system` to the URL you copy to (libpq 16 or later).
:::

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

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

:::tip
Your queries, validations, callbacks, and associations work the same on Postgres. A few things work differently, such as how NULLs and text sort, and `sql` fragments that use SQLite’s own functions. [PostgreSQL](/postgresql) lists each one.
:::
