Migrating a managed database to a different provider

You are an agent (or operator) moving a DeployMill-managed app from one managed-database backend to another (e.g. internal Postgres → Neon, or internal Postgres → Supabase) while keeping the same app and not losing data. (The selectable managed-database backends today are internal Postgres, Neon, and Supabase.)

Done looks like: the app runs on the new backend, row counts match the export, and the old data is either dormant or intentionally released. Read the framing below first, because the shape of the task is not what you'd expect.

This is rare, manual, and partly a rewrite, by design

• DeployMill does not move data between databases for you. There is no "switch the provider and migrate the rows" button. You move the data yourself (this guide shows how).
• **A provider swap is blocked in place. Once an app has a provisioned managed DB, reconcile_project refuses to change database.provider and returns database.action: "conflict-provider-change". This guard is deliberate: silently re-provisioning would point the app at an empty database and strand the old one. Do not try to force it, and do not "delete the app and start fresh". Deleting the app also deletes its volumes**, which is usually where the data you want to keep lives.
• SQLite → Postgres is a one-way door (relevant only where SQLite is enabled, and it's currently disabled as a selectable backend). The SQL dialect and type semantics differ (SQLite's dynamic typing vs Postgres boolean/timestamptz/numeric; AUTOINCREMENT vs IDENTITY/sequences; looser vs stricter constraints). Porting the schema and the app's queries is your work (see deploymill://guides/database/node / deploymill://guides/database/python). Tools like pgloader move bytes, not query semantics.

If you only need the new backend and don't care about the existing rows, the simplest path is a fresh app on the new provider, so skip this guide. Use this guide when you must keep the data on the same app.

The supported, data-preserving sequence is six steps. Do them in order.

1. Export the data first (safety net)

Before touching the database wiring, get the data out of the app and somewhere durable. The cleanest way is a temporary read-only endpoint on the current app that dumps each table as JSON, then fetch it:

// add to the CURRENT app (still on the old backend), deploy, then GET it
app.get("/api/export", (c) =>
  c.json({
    users: db.prepare("SELECT * FROM users ORDER BY id").all(),
    posts: db.prepare("SELECT * FROM posts ORDER BY id").all(),
    comments: db.prepare("SELECT * FROM comments ORDER BY id").all(),
  })
);

curl -s https://<your-app-host>/api/export -o export.json

Do this before any reconcile/prune below. Once you have the export saved off the app, the rest is recoverable even if a later step goes wrong.

2. Retire the old managed-database record (without deleting your volumes)

To get past the conflict-provider-change guard you must first remove the old managed-DB record. The only tool path that removes the record is a pruning reconcile with the database block dropped (database.action: "remove-and-drop"):

• Call reconcile_project with prune: true and a config that omits the database block.
• Re-declare everything you want to keep in that same config: domains, mounts, health, rollback. prune deletes any resource present on the app but absent from the config you pass. Forgetting your mounts here is how you lose the volume (and the SQLite file on it). Dry-run first (dryRun: true) and confirm the plan shows your domain and mounts under keep, and only database.action: "remove-and-drop".

// config passed to reconcile_project (prune:true) — note: NO "database" key,
// but mounts/domains ARE still declared so prune keeps them.
{
  "version": 2, "name": "myapp", "workload": "web", "port": 3000,
  "domains": { "prod": "myapp-<org>.deploymill.app" },
  "mounts": [ { "volumeName": "data", "mountPath": "/data", "sizeGb": 1 } ],
  "health": { "path": "/healthz" }
}

This strips DATABASE_URL and drops the old managed-DB record. The app's running container keeps serving on its old env until you redeploy, so there's no outage during the swap. (For the SQLite backend, the file on the app's persistent volume is not touched by this step (the control plane can't reach the app's volume), so the old data stays put as long as you kept the mount.)

3. Provision the new backend

Call reconcile_project again with the new database block (and the same domain/mounts). Dry-run, confirm database.action: "provision", then apply. This spins up the new managed database and sets DATABASE_URL to the new connection string.

{
  "version": 2, "name": "myapp", "workload": "web", "port": 3000,
  "domains": { "prod": "myapp-<org>.deploymill.app" },
  "mounts": [ { "volumeName": "data", "mountPath": "/data", "sizeGb": 1 } ],
  "health": { "path": "/healthz" },
  "database": { "engine": "postgres", "provider": "neon", "connection": "<your-db-connection-id>" }
}

Pass database.connection explicitly (get the id from get_account → databaseConnections). For Neon, use your own connected account. The shared house connection is deprecated and provision against it fails.

4. Port the app code to the new backend

This is the part no tool does for you. Replace the old driver and dialect with the new one. For SQLite → Postgres, swap node:sqlite (synchronous) for pg (a single pooled, async client), and port the schema to Postgres types (BIGINT … GENERATED BY DEFAULT AS IDENTITY, TIMESTAMPTZ, real FK cascades). Follow deploymill://guides/database/node (or /python) for the pooled-client + on-boot-migration pattern. Add a one-shot import endpoint that bulk-inserts the exported rows with their original ids (ON CONFLICT (id) DO NOTHING so it's idempotent), then advances the IDENTITY sequences past the imported max:

SELECT setval(pg_get_serial_sequence('posts','id'),
              (SELECT COALESCE(MAX(id),1) FROM posts), (SELECT COUNT(*) FROM posts) > 0);

Commit the new code and the updated .deploymill/project.json (now declaring the new provider), then deploy with probePath set to your health route. A green health gate proves the app booted and connected to the new database.

5. Import the data + fix sequences

POST the export.json rows to your import endpoint in FK order (users → posts → comments), chunked (~4–5k rows per call) to keep request bodies small. Then call your finalize step (the setval above) once. Preserve ids so foreign keys line up.

6. Verify, then decide what to do with the old data

• Confirm the new backend's row counts match the export, and re-check any derived/denormalized columns for consistency.
• Leaving the old data in place: if you keep the old volume's mount declared in project.json, the original file (e.g. the SQLite .db) stays on its volume, dormant, and the app no longer opens it. To fully release it, drop the mount from project.json and reconcile with prune: true (destructive, deletes the volume).

What NOT to do

• Don't "delete the app and start fresh" to dodge the guard. It deletes the app's volumes. Use the prune-then-reprovision sequence above.
• Don't run prune without re-declaring your domains and mounts in the passed config. Prune removes anything you omit. Dry-run and read the keep/extras lists first.
• Don't expect data semantics to port automatically. Bytes can be copied. Types, constraints, and queries are a manual port. Test the app against the new backend before cutting over real traffic.
• **Don't forget DATABASE_URL only changes the app on the next deploy.** Steps 2–3 update the stored env, and the container picks it up when you deploy the ported code in step 4.
• Don't put a database connection string in a tool/HTTP response. Report the redacted target (host + database) only. The credential never leaves the server.