Direct 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().

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.

IdentitiesCopy Anchor Link

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; 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.

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

TimeoutsCopy Anchor Link

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.

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 stringCopy Anchor Link

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

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 flags a direct or session-mode URL in a serverless app.

TransactionsCopy Anchor Link

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.

FunctionsCopy Anchor Link

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:

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 poolCopy Anchor Link

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.

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 itCopy Anchor Link

• 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.