prisma-next-supabase

name

prisma-next-supabase

description

Use Prisma Next with a Supabase project via `@prisma-next/extension-supabase` — wire `extensions: [supabasePack]`, declare cross-space FKs to `supabase:auth.AuthUser`, author RLS policies (`policy_select` / `policy_update` / `@@rls`, `auth.uid()` predicates), build `db.ts` with the `supabase()` factory, bind roles per request (`asUser(jwt)` / `asAnon()` / `asServiceRole()`), query `auth.*` / `storage.*` via the `db.asServiceRole().supabase` admin root, and validate JWTs (`jwksUrl` for current projects / `jwtSecret` for legacy HS256). Use for supabase, RLS, row level security, policy, role binding, anon, authenticated, service_role, auth.users, auth.uid(), JWT, JWKS, SUPABASE_JWKS_URL, SUPABASE_JWT_SECRET, SUPABASE.JWT_INVALID, SUPABASE.CONFIG_INVALID, RoleBoundDb, session pooler, supabase:auth.AuthUser, @prisma-next/extension-supabase.

Prisma Next — Supabase

Edit your data contract. Prisma handles the rest.

This skill covers using Prisma Next against a Supabase project end-to-end: composing the Supabase extension pack, referencing Supabase-owned tables from your contract, authoring row-level-security (RLS) policies, and running role-bound queries through the supabase() runtime.

When to Use

• User has a Supabase project (or wants one) and is wiring Prisma Next into it.
• User wants RLS policies on their tables (policy_select, @@rls, auth.uid()).
• User wants per-request role binding (asUser(jwt), asAnon(), asServiceRole()).
• User wants a foreign key into auth.users (cross-space FK).
• User wants to read Supabase-internal tables (auth.*, storage.*) as an admin.
• User mentions: supabase, RLS, row level security, policy, anon, authenticated, service_role, auth.users, auth.uid(), JWT, jwtSecret, jwksUrl, SUPABASE.JWT_INVALID, RoleBoundDb, session pooler.

When Not to Use

• General contract editing (models, fields, relations) → prisma-next-contract.
• Non-Supabase db.ts wiring, middleware, teardown → prisma-next-runtime.
• General query shapes (filtering, includes, aggregates) → prisma-next-queries — everything there applies to a role-bound db too.
• Migration planning / applying → prisma-next-migrations.

Key Concepts

• The pack is an external contract space. @prisma-next/extension-supabase/pack ships a complete, introspection-generated contract of everything Supabase owns — the auth and storage schemas, their native enum types, and the platform roles (anon, authenticated, service_role) — all with control policy external. Composed via extensions, it means: the migration planner emits no DDL for those objects (Supabase manages them), and db verify confirms they exist in the live database. Your own tables stay managed as usual.
• Roles come from the pack; you never declare them. RLS roles = [authenticated] identifiers resolve against the composed contract. Pointing the runtime at a non-Supabase Postgres fails verify with a not-found issue naming the missing role — the common "wrong database" misconfiguration surfaces before queries run.
• The runtime is role-first. supabase() returns a SupabaseDb with no top-level query surface — there is no db.sql / db.orm until you bind a role. await db.asUser(jwt) / db.asAnon() / db.asServiceRole() each return a RoleBoundDb exposing .sql, .orm, .raw, .execute(plan), and .transaction(fn). This is deliberate: in a Supabase app there is no meaningful "no role" execution context, and defaulting to the connection's login role is a silent-RLS-bypass footgun.
• Role binding is below middleware and cannot leak. Each role-bound query runs on a connection that had set_config('role', …) and set_config('request.jwt.claims', …) applied beneath the user-middleware chain, with RESET ALL on release. Postgres-side auth.uid() / auth.jwt() read those session vars — RLS enforcement is Postgres's job; the runtime's job is binding the context.
• RLS is enforced by policies and grants. Policies filter rows; GRANT controls table access. Prisma Next authors and migrates the policies; it does not author grants (see What Prisma Next doesn't do yet). A role with policies but no GRANT gets a permission error, not filtered rows. On Supabase your public tables already carry the platform-role grants via default privileges — the grant that is actually missing out of the box is service_role's on auth.* / storage.* (see Workflow — Grants).
• JWT validation is eager and configurable — current Supabase projects need jwksUrl. asUser(jwt) verifies the token (via jose) before any connection is acquired: signature + expiry against jwksUrl (asymmetric signing keys — the default on current Supabase projects, which sign ES256) xor jwtSecret (the symmetric HS256 secret — legacy projects only). Both or neither → a structured error with code SUPABASE.CONFIG_INVALID. Bad tokens throw a structured error with code SUPABASE.JWT_INVALID and a typed meta.reason — including a mismatch between the token's algorithm and the configured key source (an ES256 token against a jwtSecret client names the problem and tells you to switch to jwksUrl). The Postgres role is derived from the token's role claim (defaults to authenticated). Note: supabase status still prints a JWT_SECRET even on projects that sign ES256 — its presence does not mean your project uses it.
• Admin access to auth.* / storage.* is a secondary root on service_role only — and needs a one-time grant. db.asServiceRole().supabase exposes the pack's own contract (.sql, .orm, .nativeEnums, .execute). The root exists only on service_role by design, but a real Supabase project grants service_role no table privileges on auth.* / storage.* (only schema USAGE; only postgres holds table grants). Before the admin root can read a Supabase-internal table, run the narrow grant once (see Workflow — Grants). asUser / asAnon have no .supabase, and the primary asServiceRole().sql / .orm stay scoped to your contract.

Workflow — Wire the pack into the config

The concept: the pack registers the Supabase contract space so your contract can reference it and the planner/verifier know what Supabase owns. The extension has no /control subpath yet, so it can't go through the target façade's defineConfig({ extensions: [...] }) — it wires into the low-level config's extensions (see What Prisma Next doesn't do yet). The low-level imports below are a deliberate exception to the façade-only import rule, forced by that gap; the block mirrors examples/supabase/prisma-next.config.ts verbatim — copy it rather than composing your own:

// prisma-next.config.ts
import postgresAdapter from '@prisma-next/adapter-postgres/control';
import { defineConfig } from '@prisma-next/cli/config-types';
import postgresDriver from '@prisma-next/driver-postgres/control';
import supabasePack from '@prisma-next/extension-supabase/pack';
import sql from '@prisma-next/family-sql/control';
import { prismaContract } from '@prisma-next/sql-contract-psl/provider';
import postgres from '@prisma-next/target-postgres/control';
import postgresPackRef from '@prisma-next/target-postgres/pack';
import { postgresCreateNamespace } from '@prisma-next/target-postgres/types';

export default defineConfig({
  family: sql,
  target: postgres,
  adapter: postgresAdapter,
  driver: postgresDriver,
  extensions: [supabasePack],
  contract: prismaContract('./src/contract.prisma', {
    output: 'src/contract.json',
    target: postgresPackRef,
    createNamespace: postgresCreateNamespace,
  }),
  migrations: { dir: 'migrations' },
});

Workflow — Contract: FK into auth.users + RLS policies

The concept: your models live in your namespaces (public); Supabase's live in the pack's (auth, storage). A relation field typed supabase:auth.AuthUser is a cross-space FK — the planner emits REFERENCES "auth"."users"("id"), and the target table is verified, never migrated. RLS policies are top-level policy_<operation> blocks in the same namespace as their target model, and the target model must opt in with @@rls. Mirror examples/supabase/src/contract.prisma:

namespace public {
  model Profile {
    id       Uuid   @id @default(uuid())
    username String
    userId   Uuid   @unique
    user     supabase:auth.AuthUser @relation(fields: [userId], references: [id], onDelete: Cascade)
    @@map("profile")
    @@rls
  }

  // authenticated may read only their own profile.
  policy_select profile_owner_read {
    target = Profile
    roles  = [authenticated]
    using  = "\"userId\"::uuid = auth.uid()"
  }

  // anon may read every profile (a public directory listing).
  policy_select profile_public_read {
    target = Profile
    roles  = [anon]
    using  = "true"
  }

  // authenticated may update only their own profile, and may not
  // reassign it to another owner (WITH CHECK).
  policy_update profile_owner_write {
    target    = Profile
    roles     = [authenticated]
    using     = "\"userId\"::uuid = auth.uid()"
    withCheck = "\"userId\"::uuid = auth.uid()"
  }
}

The Uuid constructor selects native UUID storage in type position. The legacy @db.Uuid spelling is removed; rewrite any String @db.Uuid alias or field as Uuid before emitting.

The pieces:

• Per-operation policy blocks: policy_select, policy_insert, policy_update, policy_delete, policy_all. Body is key = value: target (a model in this namespace), roles (resolve against the composed contract — the pack supplies anon / authenticated / service_role), using, and (for write operations) withCheck. Multiple permissive policies per (target, operation) are valid — Postgres ORs them.
• @@rls is required on policy targets. A policy_* block whose target model lacks @@rls fails emit with PSL_EXTENSION_TARGET_MODEL_MISSING_ATTRIBUTE. A model with @@rls and no policies is also meaningful: RLS enabled, deny-all.
• Predicates are verbatim SQL strings. Quote camelCase column names inside them (\"userId\"), and cast where needed — auth.uid() returns uuid. Renames in your contract do not rewrite predicate bodies.
• TS-builder parity exists. @prisma-next/postgres/contract-builder exports policySelect / policyInsert / policyUpdate / policyDelete / policyAll, rlsEnabled(Model), and role('anon') — mirroring the PSL lowering key-for-key (identical emitted wire names). PSL is the canonical path shown here.

Emit + migrate as usual (prisma-next contract emit, then prisma-next-migrations). The plan creates your table, its FK, ENABLE ROW LEVEL SECURITY, and the CREATE POLICY statements — and no DDL for auth.*.

Workflow — db.ts with the supabase() factory

The concept: instead of the stock postgres() factory, a Supabase app builds its client with supabase() from the extension's /runtime subpath. The factory is async (it prepares JWT key material — including the one-time JWKS fetch when jwksUrl is set), and the result is role-first.

// src/prisma/db.ts
import { supabase } from '@prisma-next/extension-supabase/runtime';
import type { Contract } from './contract.d';
import contractJson from './contract.json' with { type: 'json' };

export const db = await supabase<Contract>({
  contractJson,
  url: process.env['DATABASE_URL'],        // direct Postgres connection — see pitfalls
  jwksUrl: process.env['SUPABASE_JWKS_URL'], // https://<project-ref>.supabase.co/auth/v1/.well-known/jwks.json
  // Legacy HS256 projects use jwtSecret: process.env['SUPABASE_JWT_SECRET'] instead — exactly one of the two.
});

Options beyond the basics: middleware (same composition as postgres() — see prisma-next-runtime; middleware never sees the role-binding set_config calls), poolOptions, pg (BYO pg.Pool / pg.Client instead of url). Teardown is await db.close() / await using exactly as in prisma-next-runtime — the same script-hang rules apply.

Workflow — Role-bound queries

The concept: bind the role that should execute the request, then query through the returned RoleBoundDb — every query surface from prisma-next-queries works, RLS-filtered by Postgres.

// A signed-in user: rows are RLS-scoped to the JWT's auth.uid().
const userDb = await db.asUser(jwt); // async — rejects with code SUPABASE.JWT_INVALID on a bad/expired token
const mine = await userDb.orm.public.Profile.select('id', 'username').all();

// The anon role: sees what anon policies permit.
const listing = await db.asAnon().orm.public.Profile.select('id', 'username').all();

// service_role: BYPASSRLS — sees everything in YOUR contract.
const all = await db.asServiceRole().orm.public.Profile.select('id', 'username').all();

// Writes ride the same surfaces; RLS filters them too. An UPDATE against
// another owner's row affects 0 rows; a withCheck violation raises an error.
const updated = await userDb.orm.public.Profile
  .where({ userId: me })
  .updateAndCount({ username: 'new-name' });

Notes: asAnon() / asServiceRole() are sync; only asUser is async. Multi-namespace contracts address models by coordinate (orm.public.Profile, sql.public.profile) — see prisma-next-queries § Namespace-aware accessors. RoleBoundDb.transaction(fn) wraps work in a transaction on the role-bound session.

Workflow — Admin reads of auth.* / storage.*

The concept: Supabase-internal tables are not part of your contract, so they are not on your query surfaces. The service_role binding carries a secondary root — db.asServiceRole().supabase — which is the pack's contract surface:

const admin = db.asServiceRole();

// SQL builder over the pack contract:
const users = await admin.supabase
  .execute(admin.supabase.sql.auth.users.select('id', 'email').build())
  .toArray();

// ORM over the pack contract:
const sessions = await admin.supabase.orm.auth.AuthSession.select('id', 'aal').all();

// Native enum values (e.g. auth.aal_level) come typed:
type AalLevel = (typeof admin.supabase.nativeEnums.auth.AalLevel)['Value'];

The admin root needs a one-time grant. A real Supabase project gives service_role no table privileges on auth.* / storage.* — out of the box, the reads above fail with permission denied for table users (sqlState 42501). Grant exactly what you read, narrowly:

GRANT USAGE ON SCHEMA auth TO service_role;
GRANT SELECT ON TABLE auth.users TO service_role;

Other boundaries to respect: asUser / asAnon have no .supabase; the admin root has no .transaction (it is a separate contract-bound runtime sharing the pool — a transaction spanning both roots is out of scope); and for user management (creating users, password resets) prefer the GoTrue Admin API — Supabase-internal schemas can drift across platform upgrades; direct service_role SQL is for ad-hoc admin reads.

Workflow — Grants

The concept: RLS policies are row filters on top of ordinary table privileges — a role with policies but no GRANT gets permission denied, not filtered rows. On Supabase the two directions are easy to get backwards:

• Your own public tables need nothing. Supabase ships ALTER DEFAULT PRIVILEGES on public, so tables created by prisma-next db init / migrate inherit full grants for anon / authenticated / service_role automatically — the same as dashboard-created tables. RLS policies are what actually protect the rows; do not add per-table grants, and do not narrow the defaults unless you have a reason.
• The one grant you do need is for admin reads of Supabase-internal tables — service_role has no table privileges on auth.* / storage.* (see Admin reads above for the narrow GRANT USAGE / GRANT SELECT pair).

Run grants via the Supabase SQL editor or psql. Symptom of a missing grant: permission denied for table … (sqlState 42501) instead of an empty result.

Workflow — Connecting to a real Supabase project