# Supabase gives you three connection strings: pick the right one or production falls over

> Direct on 5432, session pooler on 5432, transaction pooler on 6543. Which one serverless needs, why prepared statements break, and what to run migrations on.

Open Project Settings → Database in the Supabase dashboard and you are offered
three connection strings that differ by a hostname and a port number:

```
# Direct connection
postgresql://postgres:[password]@db.<ref>.supabase.co:5432/postgres

# Session pooler (Supavisor, session mode)
postgresql://postgres.<ref>:[password]@aws-0-<region>.pooler.supabase.com:5432/postgres

# Transaction pooler (Supavisor, transaction mode)
postgresql://postgres.<ref>:[password]@aws-0-<region>.pooler.supabase.com:6543/postgres
```

Choosing wrong does not fail immediately. It fails the first time you get real
concurrent traffic, which is the worst possible moment to learn the difference.

## What each one is

**Direct (`db.<ref>.supabase.co:5432`)** is a real Postgres connection to your
instance. One client connection equals one Postgres backend. Your project's
`max_connections` is small (on the smaller compute sizes, tens, not hundreds)
and Postgres reserves some for itself. It is also **IPv6-only** on the free tier
now, which is the cause of the mystifying `ENETUNREACH` when a build machine or a
serverless runtime has no IPv6 route.

**Session pooler (`...pooler.supabase.com:5432`)** is Supavisor holding the
connection open for the whole client session. It behaves like a direct
connection (prepared statements, `SET`, `LISTEN`, advisory locks all work) but
gives you an IPv4 address and shields Postgres from connection storms a little.
One client connection still occupies one backend for as long as it is open.

**Transaction pooler (`...pooler.supabase.com:6543`)** is Supavisor in
transaction mode. A backend is assigned for the duration of a transaction and
then handed to the next client. Thousands of clients, a few dozen backends.

## The rule

- **Serverless runtime (Vercel functions, edge, Lambda): transaction pooler,
  6543.**
- **Long-lived server (a container, a VM, a worker you control): session pooler
  or direct.**
- **Migrations, `db diff`, introspection, `pg_dump`: direct, or session pooler
  on 5432.** Never 6543.

In this project that is `DATABASE_URL` (6543, what `src/db/client.ts` opens) and
`DIRECT_URL` (5432, what the Supabase CLI and migrations use).

## Why serverless needs the transaction pooler

A serverless platform does not queue requests behind a pool; it creates
instances, and each instance opens its own connections. Sixty concurrent
invocations with a pool of five each is 300 connections against a project that
allows sixty. You get:

```
FATAL: remaining connection slots are reserved for non-replication superuser connections
FATAL: sorry, too many clients already
```

The transaction pooler exists precisely to absorb that. It is not an
optimisation; on serverless it is the only thing that works.

## Why the transaction pooler breaks prepared statements

Transaction mode gives you a *different backend per transaction*. A named
prepared statement lives on one backend. So a driver that prepares statements by
name will, under concurrency, prepare `s1` on backend A and later try to execute
`s1` on backend B:

```
PostgresError: prepared statement "s1" already exists
PostgresError: prepared statement "s3" does not exist
```

Intermittent, load-dependent, and impossible to reproduce locally. The fix is to
turn prepared statements off when you are on 6543:

```ts
// src/db/client.ts already does this; the predicate lives in src/db/url.ts so
// the /verify check and anything you write next cannot disagree about it.
const pooled = isTransactionPooler(url);   // url.includes(":6543")
postgres(url, {
  max: pooled ? 1 : 10,
  prepare: !pooled,
});
```

For other drivers the same switch has different names: node-postgres (under
Prisma 7's `PrismaPg` adapter) sends unnamed statements unless you name them,
and some clients call it `statement_cache_size=0`. Same idea every time.

Note the `max: 1` as well. Supavisor is the pool now. A client-side pool of ten
per instance multiplies your pooler slots by ten for no benefit: a serverless
instance serves one request at a time.

## Why migrations must not use 6543

Migration tools take a **session-level advisory lock** so two deploys cannot
apply the same migration at once. Session-level state does not survive
transaction pooling: the lock is taken on one backend and looked for on another.
Add to that `create index concurrently` (cannot run inside a transaction block)
and multi-statement DDL (can land on different backends), and the failure modes
range from "hangs forever" to "half-applied schema".

So the CLI and the migration runner read `DIRECT_URL`:

```bash
bunx supabase db push --db-url "$DIRECT_URL"
```

If you only ever set one variable, the day you deploy a migration is the day you
find out.

## The IPv6 problem, concretely

The direct host resolves to IPv6 only. Plenty of build environments and CI
runners have no IPv6 route, so:

```
Error: connect ENETUNREACH 2600:1f16:...:5432
```

This is not a firewall on Supabase's side and not a credential problem. Use the
**session pooler** on 5432 instead: it is IPv4-reachable and behaves like a
direct connection for every purpose migrations care about. That is the single
most useful thing to know when a migration works on your laptop and not in a
deploy.

## Local development

`bun run db:start` runs Postgres in Docker on `localhost:54322` with no pooler
in front of it, so prepared statements and session state all work, and there is
no pooled/direct distinction to get wrong. Point both `DATABASE_URL` and
`DIRECT_URL` at the local URL while you develop. `src/db/client.ts` already
disables TLS for localhost.

## How to check what you have

```sql
-- run against DATABASE_URL, under load
select count(*), state from pg_stat_activity
where datname = current_database() group by state;
```

Flat and small while traffic climbs means the pooler is doing its job. Climbing
with concurrency means you are on a direct or session connection and are counting
down to an outage.

And in code, the cheap assertion `bun run verify` makes: `DATABASE_URL` should
contain `:6543` in every deployed environment, `DIRECT_URL` should not.

## Summary table

| Use | Host | Port | Prepared statements |
|---|---|---|---|
| App on serverless | `...pooler.supabase.com` | 6543 | off |
| App on a long-lived server | `...pooler.supabase.com` | 5432 | on |
| Migrations, `db diff`, `pg_dump` | `db.<ref>.supabase.co` or pooler | 5432 | on |
| Local dev | `localhost` | 54322 | on |

---

Agentic Boilerplate: A Next.js repo your agent already knows. Free during launch, then $99 once.

- Site map for agents: https://agenticboilerplate.com/llms.txt
- Public API: https://agenticboilerplate.com/openapi.json
- Contact: agenticstudio@gmail.com