Prisma Migrations Hanging on Supabase Connection Poolers: Root Cause & Fix

Introduction

If you’re building with Prisma and Supabase, you’ve likely enjoyed the seamless “out-of-the-box” PostgreSQL experience. But push prisma migrate dev into a production-like Supabase project, and you may encounter a silent killer: the command hangs indefinitely, printing no error, no SQL, just a cursor blinking on a line like Waiting for migration to be applied....

This isn’t a Prisma bug, nor is it purely a Supabase misconfiguration. It’s a collision between Prisma’s migration architecture and Supabase’s default connection pooler behavior. In this article, we’ll dissect the exact symptom, trace the root cause to its source, and provide a working fix with real code examples you can apply today.

---

1. The Exact Symptom

The symptom is deceptively simple:

$ prisma migrate dev --name init

Output:

Prisma schema loaded from prisma/schema.prisma (v5.18.0)
Relay configuration applied

Applying schema migration to database...

The migration was successfully applied to the database.

Applied 1 new migration: init

Or, more commonly, the command never exits. It may sit for minutes, outputting nothing after “Applying schema migration to database...”, or fail with a timeout error from your CI runner. Crucially, no Prisma error code is returned, and prisma studio remains unaffected—the issue is isolated to the migration binary’s database interaction.

What’s happening underneath: Prisma is waiting on a PostgreSQL advisory lock that never resolves, or it’s retrying a CREATE DATABASE command that silently fails under transaction-mode pooling.

---

2. The Root Cause: Advisory Locks + Transaction-Mode Pooling

To understand the fix, we have to understand the mechanics Prisma relies on.

Prisma’s Migration Locking Strategy

Prisma uses PostgreSQL advisory locks to serialize migrations. Inside every migration transaction, you’ll find an implicit SELECT pg_advisory_xact_lock(12345) call. This lock is session-scoped and tied to the transaction. It ensures that only one prisma migrate process can modify the schema at a time. When the migration transaction commits, the advisory lock is released automatically.

Supabase’s Default Pooling Mode

Supabase sits on top of PostgreSQL and uses PgBouncer as a connection pooler. By default, Supabase deploys PgBouncer in transaction mode. In this mode:

• A client connection is borrowed from the pool for the duration of a single transaction.
• Once the transaction commits or rolls back, the connection is returned to the pool.
• Critically, all session-level state is destroyed. This includes temporary tables, SET commands, and—most importantly—advisory locks.

The Collision

When Prisma opens a connection through the Supabase pooler to run a migration:

1. Prisma begins a transaction and acquires an advisory lock via pg_advisory_xact_lock.
2. The migration SQL runs (CREATE TABLE, ALTER COLUMN, etc.).
3. If the transaction completes, Prisma attempts to commit.
4. The problem: In transaction-mode pooling, the connection may be released between individual statements if Prisma’s internal logic doesn’t keep the transaction boundary intact, or if the pooler decides to recycle the connection. More importantly, any advisory lock acquired on that connection is lost when the session resets.
5. Prisma’s migration engine, unaware the lock was released, continues waiting for the lock to be released by another concurrent process—or worse, it assumes the lock is still held and enters a retry loop, or hangs waiting for a CREATE DATABASE command that now fails because the session that started it no longer exists.

The result is a migration that appears to “succeed” from the application side but never completes from the database side, or one that hangs indefinitely.

A Note on CREATE DATABASE

Prisma creates a shadow database for each migration using CREATE DATABASE "prisma_migration_name". This command requires CREATEDB privileges and is auto-committed (it cannot run inside a transaction). In Supabase’s multi-tenant architecture, CREATE DATABASE is restricted to the postgres role, and PgBouncer transaction mode can cause the statement to be executed on a different backend than the one the connection was originally borrowed from, leading to permission errors or silent failures.

---

3. The Fix: directUrl Pattern with Code Example

The most reliable fix is to bypass the transaction-mode pooler entirely for migration operations. Prisma supports a directUrl field in its datasource configuration. When provided, Prisma connects directly to the PostgreSQL instance, ignoring PgBouncer altogether.

Adding directUrl to schema.prisma

Add a directUrl to your datasource block. This URL should point to the direct PostgreSQL endpoint supplied by Supabase, not the pooled endpoint.

// prisma/schema.prisma
datasource db {
  provider = "postgresql"

  // url is used by Prisma Client, operations, etc. — goes through the pooler
  url      = env("DATABASE_URL")

  // directUrl bypasses the pooler for operations that require session continuity
  // (migrations, db push, raw SQL, etc.)
  directUrl = env("DIRECT_URL")
}

The DIRECT_URL Environment Variable

Obtain the direct connection string from the Supabase Dashboard:

1. Go to Settings → Database.
2. Click Connection string.
3. Select “Direct connection” (not the pooler one).
4. Copy the string. It will look something like:

postgresql://postgres:[e-mail]:5432/postgres?sslmode=require

Add it to your .env file (or your CI/CD environment):

# .env
DATABASE_URL="postgresql://postgres:[e-mail]:5432/prisma?sslmode=require"
DIRECT_URL="postgresql://postgres:[e-mail]:5432/postgres?sslmode=require"

Note: The DATABASE_URL can remain pointing to your existing pooler-managed database (e.g., prisma database). The directUrl only needs access to the postgres database, which Supabase creates automatically for each project and which holds the schema metadata Prisma needs for migrations.

Why This Works

When prisma migrate dev runs, it opens a TCP connection directly to the PostgreSQL backend. Advisory locks are preserved across the entire transaction because there’s no pooler resetting session state between statements. CREATE DATABASE executes on the correct backend with the correct permissions. The migration completes in a single, uninterrupted session.

Minimal schema.prisma Example

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"

  url      = env("DATABASE_URL")
  directUrl = env("DIRECT_URL")
}

model User {
  id    String   @id @default(uuid())
  email String   @unique
  name  String?
}

Run the migration:

prisma migrate dev --name init

You should see the migration apply immediately, without hanging.

---

4. The Shadow Database CREATEDB Gotcha

Even with directUrl, you may encounter a secondary issue: Prisma attempts to create a shadow database for every migration using CREATE DATABASE "mig_name". In a standard PostgreSQL setup, this works if the connecting user has CREATEDB. In Supabase, the situation is nuanced.

The postgres Role Supabase Provides

Supabase creates a postgres superuser role for each project. This role does have CREATEDB privilege by default. However, Supabase also enforces a multi-tenant constraint: database names must be unique across the project, and the postgres role’s CREATE DATABASE command is routed through PgBouncer.

The Gotcha

If your directUrl points to postgres database but the connection is still going through a pooler that resets session state, or if the postgres role’s password has been rotated/changed in the dashboard without updating your .env, the CREATE DATABASE statement will fail with:

error: must be superuser or a member of the role CREATEDB

Or, more subtly, the statement may succeed on one connection, but Prisma’s migration tracker table (_prisma_migrations) is inspecting a different database because the shadow DB was created on a different backend instance.

The Workaround

Ensure your directUrl connects to the exact postgres database hosted by Supabase, and that the password matches the one displayed in the Supabase Dashboard > Connection string page. Additionally, you can pre-create a dedicated migration database and point Prisma at it, but the simplest path is:

1. Use directUrl as described above.
2. Ensure the postgres role password in .env matches Supabase’s current dashboard password.
3. On first run, Prisma will create the shadow DB automatically. If it fails, run psql directly via the direct URL and execute CREATE DATABASE prisma_mig_name; once, then re-run prisma migrate dev.

---

5. Decision Matrix: Which URL for Which Prisma Command

Not every Prisma command needs directUrl. Here’s a practical matrix to decide which connection string to use for each command:

Prisma Command	Use DATABASE_URL (pooler)	Use directUrl (direct)	Reasoning
prisma migrate dev	❌	✅	Needs persistent session, advisory locks, CREATE DATABASE.
prisma migrate reset	❌	✅	Same as above; resets schema and re-applies all migrations.
prisma migrate deploy	❌	✅	Deploys pending migrations to prod; requires stable session.
prisma migrate save	✅	❌	Only writes migration files to disk; no DB session required.
prisma generate	✅	❌	Generates the JS client; uses DATABASE_URL for introspection.
prisma studio	✅	❌	UI tool; connection pooling is fine for read-only ops.
prisma db push	⚠️ (see note)	✅ Recommended	Can work with pooler, but directUrl avoids shadow-DB issues.
prisma introspect	✅	❌	Discovers schema; pooler sufficient.
prisma format	✅	❌	Lints schema files; no DB interaction.
prisma watch	✅	❌	Auto-reloads schema; pooler OK.

Key Takeaway: Any command that initiates a transaction, acquires locks, or runs CREATE DATABASE should use directUrl. All others are fine with the pooled DATABASE_URL.

---

6. How to Verify the Fix Works

After applying the directUrl configuration, verify the fix with the following steps. Run these in a local terminal or your CI environment.

Step 1: Confirm the Environment Variables Are Loaded

echo $DIRECT_URL
# Should output the full postgresql connection string

Step 2: Run a Migration and Watch the Timeline

prisma migrate dev --name initial

Expected output:

Prisma schema loaded from prisma/schema.prisma (v5.18.0)
Relay configuration applied

Applying schema migration to database...

The migration was successfully applied to the database.

Applied 1 new migration: initial

The command should complete in under 30 seconds. If it hangs, proceed to Step 3.

Step 3: Inspect Supabase Logs for Advisory Lock Activity

In the Supabase Dashboard, go to Logs and filter for postgres. You should see entries like:

LOG: statement: SELECT pg_advisory_xact_lock(12345);
LOG: statement: CREATE TABLE "users" (...);
LOG: statement: CREATE DATABASE "prisma_initial";

If you see pg_advisory_xact_lock followed immediately by CREATE DATABASE on the same connection, the fix is working. If the lock appears on one connection and the CREATE DATABASE on another (different PID), the pooler is still interfering, and you may need to ensure directUrl is the only URL Prisma uses for migration-related operations.

Step 4: Verify No Hangs on Repeated Runs

Run the migration again, this time with a second migration:

prisma migrate dev --name add_email_confirmation

Repeat Step 2. Both migrations should apply sequentially without hanging. If the second migration hangs, check that no other process is holding an advisory lock on the postgres database.

Step 5: Confirm Pooler Is Bypassed for Migration-Only Traffic

You can verify that prisma generate still works through the pooler (confirming you didn’t break anything):

prisma generate

If this succeeds, your DATABASE_URL pooler config is intact, and only migration traffic is routed through directUrl.

Step 6: Edge Case — Reset and Re-apply

To ensure the fix is robust, run a full reset:

prisma migrate reset --yes

This drops the migration table and re-applies all migrations from scratch. If this completes without hanging, your directUrl configuration is fully compatible with your Supabase setup.

---

Conclusion

Prisma migrations hanging on Supabase is a classic “connection pooling meets session state” bug. The root cause is PgBouncer’s default transaction mode destroying advisory locks and session state between statements, combined with Prisma’s reliance on session-scoped locks and CREATE DATABASE for shadow databases. The fix is deliberate: introduce a directUrl that points Supabase migrations straight to the PostgreSQL backend, bypassing the pooler entirely. Coupled with a decision matrix for which Prisma commands use which URL, and verification steps to confirm the fix, you can eliminate the hanging migrations forever and regain full control over your schema evolution.

---

Author Bio

Cenk Kurtoglu is a Supabase RLS security auditor and full-stack engineer specializing in PostgreSQL optimizations, multi-tenant architecture, and authentication infrastructure. He regularly writes about suppressing Row-Level Security edge cases, connection pooling pitfalls, and Prisma-integrated workflows. Visit his technical blog at rls.cenkkurtoglu.com for deep-dives on Supabase RLS, database security, and migration strategies.