# Direct Postgres

> The same repositories over SQL, with the same results and RLS.

Source: https://bettersupabase.com/docs/auth/postgres

The repository IR compiles to SQL as well as to PostgREST. Any client with
`queryRaw(text, params)` can run it: `ctx.postgres` from `@supabase/server`,
or a pool from `createPostgres()`.

```ts
import { createPostgres, postgresExecutor } from "better-supabase/postgres";

const postgres = createPostgres(); // SUPABASE_DB_URL or DATABASE_URL

const asUser = betterSupabase.connect(
  postgresExecutor(postgres.asUser({ sub: userId, tenant_id: organizationId })),
);
const asAdmin = betterSupabase.connect(postgresExecutor(postgres.admin));
```

Results match PostgREST exactly: same row shapes, nested includes, relation
filters, pagination and error kinds. The test suite runs the same queries
through both and compares them.

## Identities [#identities]

| Client               | Runs as                                                        |
| -------------------- | -------------------------------------------------------------- |
| `postgres.admin`     | the connection-string role (bypasses RLS on Supabase)          |
| `postgres.asUser(c)` | `authenticated`, with `request.jwt.claims` set, so RLS applies |
| `postgres.anon`      | `anon`                                                         |

`postgres.executorFor(claims)` is `postgresExecutor(postgres.asUser(claims))`.
`createServer` calls it for `ctx.sql` and `actingAs()`, so the SQL compiler is
only bundled into apps that import `better-supabase/postgres`. A custom
`BetterPostgres` passed to `createServer` implements it too.

Claims and role are transaction-local (`set_config(…, true)`), so nothing
leaks back into the pool. Each transaction sets them, with its timeouts, in
one query after `begin`. Only `authenticated` and `anon` can be assumed this
way.

`asUser`, `executorFor` and `transaction` also take session options.
`settings` sets more transaction-local settings, such as
`better_supabase.tenant` for a [resolved tenant](/docs/blocks/access#the-active-tenant);
their names need a dot, so they can't replace `role` or the claims.
`readOnly: true` opens every transaction with `begin read only`, so writes fail
with SQLSTATE 25006.

```ts
const reader = postgres.asUser(claims, {
  settings: { "better_supabase.tenant": organizationId },
  readOnly: true,
});
```

## Timeouts [#timeouts]

`set role` doesn't apply a role's own settings, so the `statement_timeout`
Supabase sets on `authenticated` (8 s) and `anon` (3 s) would not limit these
queries. `createPostgres` applies those two values per transaction instead,
and leaves `admin` without a limit for jobs and migrations.

| Option                     | Default                               | Sets                                                                   |
| -------------------------- | ------------------------------------- | ---------------------------------------------------------------------- |
| `statementTimeout`         | `{ authenticated: 8000, anon: 3000 }` | `statement_timeout` per transaction; a number applies to every role    |
| `idleInTransactionTimeout` | unset                                 | `idle_in_transaction_session_timeout`, so a transaction left open ends |
| `connectionTimeout`        | `10000`                               | how long `connect` waits for a free connection                         |
| `idleTimeout`              | `10000`                               | how long an unused connection stays in the pool                        |

```ts
const postgres = createPostgres({
  statementTimeout: { admin: 60_000, authenticated: 5000 },
  idleInTransactionTimeout: 30_000,
});
```

All values are milliseconds. `connectionTimeout` and `idleTimeout` apply to
the pool `createPostgres` opens, not to one you pass as `pool`.

## Which connection string [#which-connection-string]

Supabase offers three ways in, and the right one depends on where the code
runs:

| Connection                  | Host and port                       | Use it for                       |
| --------------------------- | ----------------------------------- | -------------------------------- |
| Direct                      | `db.<ref>.supabase.co:5432` (IPv6)  | long-running servers, migrations |
| Supavisor, session mode     | `<region>.pooler.supabase.com:5432` | long-running servers on IPv4     |
| Supavisor, transaction mode | `<region>.pooler.supabase.com:6543` | serverless and edge functions    |

Serverless functions open a connection per invocation, so use transaction
mode there and keep `max` small (1 to 3 per instance). Transaction mode
doesn't keep session state between transactions, which `createPostgres`
doesn't need: everything it sets is transaction-local. Doctor's
[BS221](/docs/cli/doctor#bs221) flags a direct or session-mode URL in a
serverless app.

## Transactions [#transactions]

```ts
await postgres.transaction(
  async (tx) => {
    const db = betterSupabase.connect(postgresExecutor(tx), { claims });
    const invoice = await db.invoices.create(data).orThrow();
    await db.invoiceLines.createMany(lines(invoice.id)).orThrow();
  },
  { claims },
);
```

A thrown error rolls everything back. Plugins run as usual inside the
transaction.

## Functions [#functions]

`db.$rpc` works over the Postgres executor too, so a request that runs as an
API key or a job over a direct connection calls the same functions as one
over PostgREST:

```ts
const db = betterSupabase.connect(postgres.executorFor(claims), { claims });
const rows = await db
  .$rpc("customers_by_status", { p_status: "active" })
  .orThrow();
```

It runs `select ... from schema.name(arg => $1, ...)` with the named
arguments and returns what PostgREST returns: an array for a set-returning
or `returns table` function, one value or row otherwise, and `null` for
`void`. It reads whether the function returns a set from the generated
metadata, and sends `json` and `jsonb` arguments as JSON; a function the
metadata doesn't list is looked up in `pg_proc` first. Errors map like any
other statement, and a function that doesn't exist fails with
`invalid_request`, as PostgREST's `PGRST202` does.

## An existing pool [#an-existing-pool]

Pass `pool` to run on a pool you already have, such as the app's `pg.Pool`
or a test double. Anything with `connect()` and `end()` works (the `PgPool`
type), and `postgres.end()` ends it.

```ts
import { Pool } from "pg";
import { createPostgres } from "better-supabase/postgres";

const pool = new Pool({ connectionString: process.env.DATABASE_URL, max: 5 });
await using postgres = createPostgres({ pool });
```

## When to use it [#when-to-use-it]

* jobs, scripts and webhook handlers (with `actingAs` for user-scoped work);
* several writes that must commit together;
* when PostgREST is not reachable from the runtime.

For browser-facing request handling, PostgREST remains the default: it pools
connections for you and needs no database credentials.