Querying

ClientsCopy Anchor Link

@zeno-lib/db exports five client factories. Each takes an optional Drizzle config ({ relations?, logger?, casing? }) plus an optional connectionString and requirePooler. Without connectionString, it reads SUPABASE_DATABASE_URL from the environment. For local and deployed connection strings, see Environment.

The RLS clients (createAuthClient, createSupabaseClient, createAnonClient) clamp a user token's role to anon or authenticated. A forged or unexpected role claim falls back to anon, and service_role is reachable only through the explicit createServiceClient, never from a JWT.

For deployed Supabase projects, prefer the Supabase connection pooler for serverless or autoscaling environments. The package sets prepare: false on the underlying postgres-js pools so Supabase's transaction-mode pooler works. The package caches pools per (kind, connectionString), and close() is reference-counted, so a handle releases only its share of the shared pool.

Query with RLSCopy Anchor Link

Create the request-scoped Supabase client first, then bind it to createAuthClient(...). You query the returned client directly, and each query runs as the signed-in Supabase user, so auth.uid() resolves to that user and RLS policies apply:

import { createAuthClient } from "@zeno-lib/db"
import { createClient } from "@zeno-lib/supabase/server"
import * as schema from "{project}-db"

const supabase = await createClient()
const db = createAuthClient(supabase)

const myPosts = await db.select().from(schema.posts)

Awaiting a query calls supabase.auth.getClaims(), opens a transaction, installs the verified JWT claims into transaction-local Postgres settings (so auth.uid() and auth.jwt() work inside RLS policies), and runs the query as the claims' role. Each awaited statement is its own transaction unless it runs inside db.transaction (below), and the client re-resolves claims for every transaction, so a single client always reflects the live session.

createAuthClient(...) does not take a schema. The client resolves types from the tables you reference in the query itself. Pass a relations object only when you want the relational query API (see below).

Relational queriesCopy Anchor Link

The relational query API works the same way once you pass a relations object (from Drizzle's defineRelations) to the factory. Build the relations object once at module scope, not per request:

import { createAuthClient } from "@zeno-lib/db"
import { createClient } from "@zeno-lib/supabase/server"
import { defineRelations } from "drizzle-orm"
import * as schema from "{project}-db"

const relations = defineRelations(schema)
const supabase = await createClient()
const db = createAuthClient(supabase, { relations })

const myPosts = await db.query.posts.findMany()

Multiple statements in one transactionCopy Anchor Link

When you need several statements to share one atomic RLS transaction (for example a read followed by a dependent write), use db.transaction(...) and run each statement on its tx argument, which runs under the same claims and role:

const created = await db.transaction(async (tx) => {
  const [post] = await tx
    .insert(schema.posts)
    .values({ title: "Hello" })
    .returning()
  await tx.insert(schema.auditLog).values({ postId: post.id })
  return post
})

Parallel reads work the same way. Pass tx to each query function, and they share one begin, one claims setup and one commit instead of opening a transaction each:

const [post, comments] = await db.transaction((tx) =>
  Promise.all([selectPost(tx, id), selectComments(tx, id)])
)

Reads that cannot take txCopy Anchor Link

Some reads sit behind code that has no way to receive tx, such as a query factory that calls a server action, which builds its own client with getRequestDb() (see below). For those, a statement on db itself, awaited while the callback runs, joins the transaction too:

const db = await getRequestDb()
await db.transaction(() =>
  // The server actions behind these queries reach the transaction through
  // getRequestDb(), not through tx.
  Promise.all([
    queryClient.prefetchQuery(postQueryOptions(id)),
    queryClient.prefetchQuery(commentsQueryOptions(id)),
  ])
)

This goes through the async context, so it covers only the same client, and only while the callback runs: another client keeps its own transactions, and a statement started after the callback ends opens its own. Pass tx whenever you can, because then the code shows which statements share the transaction.

Either way, the statements run one after another on one connection. A db.transaction inside another one is a savepoint of it, so never run two of those in parallel. Postgres aborts a transaction at its first failed statement, so the statements after it fail too, and db.transaction rejects with that first error even when the callback caught it, as prefetchQuery does.

Use db.transaction only when a single statement is not enough; one direct statement is the common case.

Request-scoped client for Next.jsCopy Anchor Link

@zeno-lib/db/next wraps createSupabaseClient for the App Router. Create one module per app (not a "use server" file) that binds it to your Supabase server client and relations:

import { createRequestDb } from "@zeno-lib/db/next"
import { createClient } from "@zeno-lib/supabase/next-server"
import { relations } from "./relations"

export const {
  defineAction,
  defineFormAction,
  getRequestContext,
  getRequestDb,
} = createRequestDb({ relations, supabase: () => createClient() })
export { UnauthenticatedError } from "@zeno-lib/db/next"

getRequestContext() verifies the cookie session with auth.getClaims() once per server render, because React's cache wraps it. It returns { claims, db }, where db runs as the caller with the whole verified claims object installed. It never falls back to anon. Without a verified sub, it throws UnauthenticatedError. getRequestDb() returns just the db.

defineAction(schema, handler) turns each server action into one line. The returned async function parses its argument with schema.parse, then calls handler(db, input, context):

"use server"

import { defineAction } from "{project}-db/server-db"
import { z } from "zod"
import { deletePost, insertPost } from "./queries"

export const deletePostAction = defineAction(z.number().int(), deletePost)

// Stamp authorship from the verified claims, never from an argument.
export const createPostAction = defineAction(
  z.object({ title: z.string().min(1) }),
  (db, input, { claims }) => insertPost(db, { ...input, userId: claims.sub })
)

An action never resolves to undefined. When the handler does (a findFirst that matches no row, for example), the action resolves to null. TanStack Query rejects a query whose function resolves to undefined, so a query function can return a read action's result as it is, typed T | null.

When being signed out is a valid answer (in a permission check, say), catch the named error instead:

const db = await getRequestDb().catch((error: unknown) => {
  if (error instanceof UnauthenticatedError) return null
  throw error
})

Returning validation errors to a formCopy Anchor Link

In production, Next.js replaces a thrown server-action error's message with a generic one, so the ZodError that defineAction throws on bad input reaches the browser without its issues. For an action a form submits, use defineFormAction, which createRequestDb returns next to defineAction. It takes the same arguments and runs in the same order (validate, then resolve the session, then call the handler), but it resolves to an ActionResult instead of throwing on invalid input:

type ActionResult<T> =
  | { ok: true; data: T }
  | {
      ok: false
      error: {
        fieldErrors: Record<string, string[]> // "address.city", "owners[0].share"
        formErrors: string[] // issues with no path
      }
    }

Field keys are the names TanStack Form registers fields under: object keys joined with ., array indices in brackets. Schema issues come back this way, and so does a FieldValidationError thrown from the handler, for a check only the database can make:

"use server"

import { isConstraintViolation } from "@zeno-lib/db/errors"
import { FieldValidationError } from "@zeno-lib/db/next"
import { defineFormAction } from "{project}-db/server-db"
import { z } from "zod"
import { insertTeam } from "./queries"

export const createTeamAction = defineFormAction(
  z.object({ name: z.string().min(2), slug: z.string().min(3) }),
  async (db, input, { claims }) => {
    try {
      return await insertTeam(db, { ...input, userId: claims.sub })
    } catch (error) {
      if (isConstraintViolation(error, ["teams_slug_key"])) {
        throw new FieldValidationError({ slug: "That slug is taken" })
      }
      throw error
    }
  }
)

Everything else still throws: UnauthenticatedError, a database error, a bug. A FieldValidationError thrown inside db.transaction(...) rolls the transaction back before defineFormAction converts it. On the client, submitAction from @zeno-lib/forms writes the result onto the form; see Server actions.

defineAction still throws. Use it for an action whose errors no form renders (a delete button, a toggle).

Other RLS clientsCopy Anchor Link

When you already hold the verified, decoded claims (for example from a prior supabase.auth.getClaims() call), skip the getClaims() call each transaction makes with createSupabaseClient(accessToken, config?). It accepts a decoded SupabaseToken (the role and sub claims), not a raw JWT string, and does not re-verify it:

import { createSupabaseClient } from "@zeno-lib/db"
import * as schema from "{project}-db"

const db = createSupabaseClient({ role: "authenticated", sub: userId })

const myPosts = await db.select().from(schema.posts)

For unauthenticated, RLS-enforced reads, use createAnonClient(), which runs every query as anon:

import { createAnonClient } from "@zeno-lib/db"

const db = createAnonClient()

Prefer createAuthClient when you have a Supabase client. It resolves and verifies claims through auth.getClaims() before every transaction, so it always reflects the live session.

Use admin and service queries carefullyCopy Anchor Link

For trusted server work that should bypass user RLS, use createAdminClient(...). It needs no Supabase client (it connects via SUPABASE_DATABASE_URL), and you query it directly:

import { createAdminClient } from "@zeno-lib/db"
import * as schema from "./schema"

const db = createAdminClient()

await db.select().from(schema.posts)

createAdminClient(...) does not switch roles. It runs as the role the client connects as (the postgres role by default), which is not subject to RLS. That connection role, not service_role, is what makes admin queries bypass policies.

createServiceClient(...) instead runs every statement as service_role, which bypasses RLS through Supabase's BYPASSRLS grant. It is the only path to service_role; use it when trusted work needs that role rather than the postgres connection role:

import { createServiceClient } from "@zeno-lib/db"
import * as schema from "./schema"

const db = createServiceClient()

await db.select().from(schema.posts)

Use either for seed scripts, webhooks, cron jobs, admin tools, and background jobs. Never use them for user-scoped reads or writes.

Query helpersCopy Anchor Link

@zeno-lib/db/query covers the PostgREST conveniences Drizzle has no equivalent for.

one(rows) is the equivalent of .single(). It returns the row and throws unless there is exactly one. maybeOne(rows) is the equivalent of .maybeSingle(). It returns the row, or undefined when there is none, and throws when there are several.

import { maybeOne, one } from "@zeno-lib/db/query"

const post = one(await db.insert(posts).values(input).returning())
const draft = maybeOne(await db.select().from(posts).where(eq(posts.id, id)))

excludedSet(table, values, { target? }) builds the set that makes onConflictDoUpdate behave like .upsert(). It writes every column the caller supplied back from excluded, under its database name.

import { definedValues, excludedSet } from "@zeno-lib/db/query"

const values = definedValues(input)
await db
  .insert(contacts)
  .values(values)
  .onConflictDoUpdate({ target: contacts.id, set: excludedSet(contacts, values) })

It skips a key holding undefined, because Drizzle leaves it out of the insert and excluded.<col> would be the column default, overwriting the stored value. It never writes back the conflict target ("id" unless target names other column keys). When the caller supplied nothing else, the set falls back to id = excluded.id, because Drizzle refuses an empty set while building the statement. A table without that column (a junction keyed by its foreign keys) gets an empty set; use onConflictDoNothing there.

ErrorsCopy Anchor Link

Drizzle wraps every driver error in a DrizzleQueryError whose own code is undefined, so error.code === "23505" is always false. @zeno-lib/db/errors extracts the postgres.js error:

import { isConstraintViolation, SqlState, toPostgresError } from "@zeno-lib/db/errors"

try {
  await db.insert(contacts).values(row)
} catch (error) {
  if (isConstraintViolation(error, ["contacts_email_key"], { code: SqlState.uniqueViolation })) {
    return { error: "That email is already a contact" }
  }
  throw error
}

toPostgresError(error) returns the PostgresError (with code, constraint_name, detail, …) or undefined. isConstraintViolation(error, constraints?, { code? }) returns it only for an integrity-constraint violation (any class 23 code unless code narrows it) and, when you pass constraints, only for one of those. SqlState names the codes an application usually branches on.

PitfallsCopy Anchor Link

• Do not import @zeno-lib/db runtime clients from Client Components or browser code.
• Do not use createAdminClient(...) or createServiceClient(...) for user-scoped reads and writes; they bypass RLS. Use createAuthClient(...), createSupabaseClient(...), or createAnonClient(...) for RLS-enforced work.
• Do not pass a raw access token string to createSupabaseClient(...); it expects a verified, decoded SupabaseToken and does not re-verify it. Prefer createAuthClient(...), which verifies claims itself.
• Do not feed a user-supplied token to createServiceClient(...). It clamps nothing and grants service_role; it is a trusted backend decision.
• Do not put "use server" in the module that calls createRequestDb, and do not export getRequestDb from a "use server" file: every export there is a public endpoint.
• Do not build a fresh relations object literal per request. Pass a stable relations object, or a cached client built without relations may be reused.