The hang: what you see
You spin up a fresh Supabase project, define a simple Prisma model,
and run your first migration:
npx prisma migrate dev --name init
Instead of a confirmation, the process prints three lines and then
sits there — no error, no progress, no timeout:
Environment variables loaded from .env
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "postgres", schema "public" at "localhost:54322"
A developer on Stack Overflow reported this exact symptom
with a local Dockerised Supabase, but the same freeze also appears
on hosted Supabase projects. The command never finishes until you
break it. If you’re staring at that output right now, the migration
is hung — and the fix is a one-line connection change.
Why PgBouncer’s transaction mode stalls prisma migrate dev
Under the hood, prisma migrate dev acquires a PostgreSQL
advisory lock to prevent simultaneous migrations, runs
introspection queries, applies DDL, and stores the result in a
migrations table — all within long-lived database sessions. These
sessions rely on state that persists across multiple statements.
Supabase, like many managed Postgres offerings, places
PgBouncer in front of the database to
manage connection pools. The connection pooler acts as an
intermediary that multiplexes client connections, which improves
efficiency for typical query workloads. By default, Supabase’s
pooler runs in transaction mode: after each transaction
commits, PgBouncer resets the session, discarding any locks,
prepared statements, and session-level settings.
Transaction mode vs session mode
Transaction mode resets the session after every transaction,
destroying session‑scoped state like advisory locks. Session mode
keeps the session alive across transactions, preserving locks and
temporary objects. Supabase provides two pooler ports for this
reason:
- 6543 — transaction mode (default pooler)
- 5432 — session mode (keeps the session alive across transactions)
When prisma migrate dev obtains an advisory lock inside one
transaction and then tries to reuse the session in a later call,
the transaction‑mode pooler has already recycled the connection —
the lock is gone, the client waits for a response that never
arrives, and the CLI hangs indefinitely. This is not a Prisma bug;
it is a documented limitation of transaction pooling.
The hang surfaces because the connection string you used points to
the transaction‑mode pooler. On the hosted platform that’s port
6543; in local Docker it may appear as port 54322 if the pooler is
mapped there.
The fix: bypass the transaction pooler
The fix is to give prisma migrate dev a direct connection to
PostgreSQL — either through the session‑mode pooler (port 5432) or
by reaching the database container directly. The Prisma CLI
supports a separate directUrl datasource property exactly for
this scenario.
For hosted Supabase (supabase.com)
Open your project’s Settings → Database page and note the
connection string labelled “Session mode” (port 5432). Then update
your Prisma schema to use two URLs:
```prisma title="prisma/schema.prisma"
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
directUrl = env("DIRECT_URL")
}
```bash title=".env"
DATABASE_URL="postgresql://postgres.[PROJECT-REF]:[PASSWORD]@aws-0-[REGION].pooler.supabase.com:6543/postgres"
DIRECT_URL="postgresql://postgres.[PROJECT-REF]:[PASSWORD]@aws-0-[REGION].pooler.supabase.com:5432/postgres"
-
urlstays on the transaction pooler (port 6543) for normal query traffic — safe for typical CRUD operations. -
directUrlpoints to the session‑mode pooler (port 5432), which preserves session state. Prisma usesdirectUrlfor migrations, shadow database operations, and any commands that needpg_advisory_lock.
Restart the migration and it will complete in seconds.
For local Supabase Docker
The standard supabase start command outputs a database URL on
port 54322. Even though that port is labelled as the “database”
URL, it may route through the pooler container in transaction
mode, causing the same hang. Fix it by exposing the bare
PostgreSQL port directly.
Add a port mapping to the db service in the Supabase
docker-compose.yml (or in a docker-compose.override.yml):
```yaml title="docker-compose.override.yml"
services:
db:
ports:
- "5432:5432"
Then point `DIRECT_URL` to the newly exposed port:
```bash title=".env"
DIRECT_URL="postgresql://postgres:postgres@localhost:5432/postgres"
Make sure no other process is using host port 5432, then run
npx prisma migrate dev --name init again.
-
Symptom:
prisma migrate devprints three lines and hangs. - Root cause: PgBouncer transaction mode drops session state, breaking advisory locks.
-
Fix: Set
directUrlto a session-mode (port 5432) or direct connection in Prisma. - Verification: Migration runs to completion and prints “Your database is now in sync”.
When directUrl isn’t enough: the shadow database (P3014)
After switching to a direct connection you may hit a new error:
P3014: Prisma Migrate could not create the shadow database.
Please make sure the database user has permission to create databases.
Original error: ERROR: permission denied to create database
Prisma Migrate uses a shadow database — a temporary clone that
validates migrations before touching production. Many managed
services (including Heroku Postgres and some Supabase free‑tier
plans) do not grant the CREATEDB privilege to the default user.
The CREATEDB privilege is the PostgreSQL permission that allows a
role to create new databases; Prisma Migrate requires it for
spinning up the shadow database.
The solution is to pre‑create a shadow database and point Prisma to
it with shadowDatabaseUrl. Create the database manually (through
the Supabase Dashboard or psql) and then add:
```prisma title="prisma/schema.prisma"
datasource db {
provider = "postgresql"
url = env("DATABASE_URL")
directUrl = env("DIRECT_URL")
shadowDatabaseUrl = env("SHADOW_DATABASE_URL")
}
```bash title=".env"
SHADOW_DATABASE_URL="postgresql://postgres.[PROJECT-REF]:[PASSWORD]@aws-0-[REGION].pooler.supabase.com:5432/postgres_shadow"
Now migrations run without the CREATEDB requirement. (I cover
the full P3014 workflow in
Prisma Migrate P3014: permission denied to create database.)
Why not just use prisma db push?
While debugging, you might try prisma db push as a quick
workaround. prisma db push syncs your database schema directly
from the Prisma schema, skipping migration files and the advisory
locks needed by migrate dev. It can succeed even through a
transaction‑mode pooler, which makes the hang disappear. However,
db push does not generate migration history, so you lose the
audit trail, rollback capability, and seed‑driven workflows that
migrate dev provides. For production, stick with directUrl and
migrations.
TLS “UnsupportedCertVersion” errors (Deno + Prisma)
If you are using Prisma through Deno (for instance inside a Supabase
Edge Function via Deno Deploy), you might see a log entry like:
TLS connection failed with message: invalid peer certificate contents:
invalid peer certificate: UnsupportedCertVersion
This occurs because the Deno runtime’s TLS stack rejects the
certificate provided by the Supabase pooler. The connection may
still fall back to non‑encrypted mode, which is a security risk in
production.
A quick workaround for local development is to run deno with the
unsafe flag:
deno run --unsafely-ignore-certificate-errors your-script.ts
For production, upgrade to the latest Deno version (which ships an
updated TLS library) or use a direct PostgreSQL connection over a
secure tunnel. A similar class of connection errors — for instance
invalid port parsing — is detailed in
Prisma Can’t Connect to PostgreSQL: Fix invalid port.
Verify the migration runs
After applying the fix, re‑run the migration:
npx prisma migrate dev --name init
If everything is wired correctly you’ll see:
Applying migration `20260925000000_init`
The following migration(s) have been created and applied from new
migration files:
migrations/
└─ 20260925000000_init/
└── migration.sql
Your database is now in sync with your schema.
You can also check that the migration engine can connect without
stall by running:
npx prisma migrate status
It should print which migrations have been applied and the current
database state — no hang.
If you later run prisma migrate dev again and see this error
instead of a hang, you’ve moved past the pooler problem but hit a
different known issue:
prepared statement "s0" already exists
That error is triggered by the same transaction‑mode pooler after a
migration retry. I walk through the fix in
prepared statement s0 already exists: Fix for Prisma + PgBouncer.
FAQ
Why does prisma migrate dev hang with no error instead of failing fast?
Prisma expects an advisory lock response from PostgreSQL. When
PgBouncer (transaction mode) drops the session, the lock call
hangs because the pooler never forwards the expected answer — so
Prisma never gets an error, just an open socket. Changing to a
session‑mode or direct connection resolves the stall.
Can I skip directUrl and just change the main DATABASE_URL to port 5432?
Yes, you can — but your application queries will then run through
the session pooler, which holds connections longer and can exhaust
the pool sooner. Keeping the transaction pooler for normal traffic
and using directUrl only for migration operations gives you the
best of both worlds.
Related
- Prisma Migrate P3014: permission denied to create database
- prepared statement s0 already exists: Fix for Prisma + PgBouncer
- Prisma Can’t Connect to PostgreSQL: Fix invalid port
- Prisma: Can’t reach database server at database:5432 on M1
Originally published at https://www.iloveblogs.blog
