Database

Zeno uses Supabase Postgres for the database and Drizzle for type-safe schema definitions, migrations, and server-side queries. With the @zeno-lib/db package, an app defines tables in TypeScript, generates SQL migrations from them, and runs user-scoped queries with the caller's role and JWT claims set, so Supabase Row Level Security (RLS) policies apply.

To read this guide, you do not need to have used Drizzle, RLS, or @zeno-lib/db before.

ConceptsCopy Anchor Link

Supabase PostgresCopy Anchor Link

Every Supabase project includes a Postgres database. You can query it directly from trusted server code, and Supabase can also expose it through APIs such as PostgREST and Supabase client libraries.

Every user-scoped table needs Row Level Security. RLS is a Postgres feature that attaches policies to tables. A policy is an authorization rule that Postgres checks whenever a role reads, inserts, updates, or deletes rows.

Postgres requests run as database roles. The roles you meet most often are:

• anon: an unauthenticated request. Use this for data that can be read or written without a signed-in user, if your app allows that.
• authenticated: a request with a valid signed-in Supabase user. Use this for data that depends on who the user is.
• service_role: a trusted role that bypasses RLS, for code paths that must skip user-scoped policies.
• postgres: the role the database client connects as by default. It also bypasses RLS.

Zeno connects to Postgres directly rather than through Supabase's API, so it chooses the role itself. RLS-scoped queries run as anon or authenticated. Trusted admin queries run as the postgres connection role. A dedicated client runs as service_role for trusted work that should bypass RLS through Supabase's BYPASSRLS grant. A user token only ever resolves to anon or authenticated; service_role is reachable only through that explicit server-side client, never from a JWT.

Inside RLS policies, Supabase helpers expose information about the current request:

• auth.uid() returns the current user's id.
• auth.jwt() returns the current user's JWT claims. Use trusted app metadata for authorization data, not user-editable metadata.

DrizzleCopy Anchor Link

Drizzle schema files let you declare Postgres tables in TypeScript. Those declarations become the types you use in application queries.

Drizzle Kit migrations read those schema files, generate SQL migration files, and apply them to the database. In Zeno, the normal workflow is codebase-first: update the TypeScript schema, generate migrations, review the generated SQL, and apply it.

If you already have a database, the direction reverses, and your schema files describe what Postgres already has. Drizzle's Supabase with an existing database guide covers drizzle-kit pull, and Existing schema covers what Zeno adds. It explains which helpers match what Supabase creates, what must stay out of your schema barrel, and how to check that the description still matches.

Drizzle also supports RLS policies, including Supabase's built-in roles and helpers.

Why Drizzle for data queries?Copy Anchor Link

Supabase also ships a JavaScript client whose data API (supabase.from(...).select(...)) reaches Postgres through PostgREST. It is convenient to start with, but for application data Zeno queries Postgres directly with Drizzle.

• SQL, not a query meta-language. The Supabase data API builds queries through PostgREST's filter and resource-embedding syntax, a meta-language layered over SQL. It works for simple lookups, but you soon hit its limits with joins beyond embedded resources, richer aggregations, conditional logic, and other advanced SQL features. Drizzle can express all of these, because its query builder mirrors SQL and adds TypeScript types.
• No forced workarounds. When the meta-language cannot express a query, you either split it into several round-trips or push it into an RPC function. Both tend to be slower than the single statement you would have written in SQL.
• Relational writes. PostgREST can embed related rows when reading, but it has no relational insert or update, so nested writes turn into several calls you sequence yourself. With Drizzle you write each insert or update as a SQL statement and group them in a real transaction.
• Fewer layers between app and database. The JavaScript client sends HTTP to PostgREST, which parses the request, plans it, and serializes rows to JSON. Drizzle uses a direct postgres-js connection to Postgres and skips the PostgREST and HTTP hop, which lowers latency and serialization overhead.
• Multi-statement transactions. PostgREST runs each request as its own transaction, so you cannot group several calls into one atomic unit. Drizzle runs over a direct connection and lets you decide how to manage transactions, so related writes can commit or roll back together.
• One TypeScript schema, generated migrations. You define tables once in TypeScript; Drizzle Kit diffs that schema and generates SQL migrations that you commit to git. You don't change the schema in the dashboard and then regenerate client types to match.
• End-to-end types from the source of truth. Drizzle infers query result types from the same table objects Drizzle Kit generates migrations from, so renaming or removing a column is a type error at every call site that uses it. There is no separate generated-types file to regenerate and keep in sync.
• No runaway type inference. The Supabase client derives result types from a generated types file, and on larger schemas or nested embeds TypeScript can fail with Type instantiation is excessively deep and possibly infinite (supabase-js #808). Drizzle infers types straight from your table objects, so it avoids that error.
• Seeding in TypeScript too. Seed scripts run through the same Drizzle schema and client as the rest of the app, so you write seed data in TypeScript with full types instead of maintaining separate SQL or dashboard steps. When the schema changes, the type checker flags seed data that no longer fits.
• Less locked in to Supabase. Zeno relies on Supabase for authentication, Realtime, and Storage, but data access goes through Drizzle and standard Postgres, so the data layer itself is not tied to Supabase. You can move or change the database without rewriting every query.
• No drift from Postgres. Drizzle queries follow standard SQL, so your queries and your team's SQL knowledge carry over to other Postgres databases instead of depending on a Supabase-specific query dialect.

InstallationCopy Anchor Link

Install the database and Supabase packages in the package or app that owns your schema:

pnpm add @zeno-lib/db @zeno-lib/supabase drizzle-orm@1.0.0-rc.3 postgres
pnpm add -D drizzle-kit@1.0.0-rc.3 dotenv

Package scriptsCopy Anchor Link

Add these scripts to the same package.json. They drive local Supabase, migrations, and seeding:

{
  "scripts": {
    "dev": "pnpm exec supabase start",
    "stop": "pnpm exec supabase stop",
    "reset": "pnpm exec supabase db reset && pnpm db:seed",
    "db:generate": "drizzle-kit generate",
    "db:seed": "node src/seed.ts"
  }
}

What each script does:

• dev starts your local Supabase stack, including local Postgres.
• stop stops the local Supabase stack.
• reset rebuilds the local database from migration files, then runs your seed script.
• db:generate reads your TypeScript schema and writes a SQL migration under supabase/migrations.
• db:seed runs your project-owned seed script.

EnvironmentCopy Anchor Link

Both the runtime clients and the Drizzle Kit preset read SUPABASE_DATABASE_URL from the environment. To override it, pass connectionString to a runtime client, or dbCredentials to the preset.

For local Supabase:

SUPABASE_DATABASE_URL=postgresql://postgres:postgres@127.0.0.1:54322/postgres

Anatomy of the connection URLCopy Anchor Link

SUPABASE_DATABASE_URL is a standard PostgreSQL connection URI:

postgresql://[user[:password]@][host][:port][/dbname][?param=value&...]

Find a deployed project's string in the Supabase dashboard via the Connect button. The direct connection uses port 5432; the transaction pooler uses 6543.

For deployed Supabase projects, prefer the Supabase connection pooler. The runtime client sets prepare: false so the transaction-mode pooler works; see Querying for details.

To make a deployed app fail fast on the wrong URL, pass requirePooler: true to the factories. It rejects any port other than 6543 and any local host, without echoing the URL (which carries the password):

createServiceClient({
  requirePooler: process.env.VERCEL_ENV === "production",
})

resolveDatabaseUrl({ connectionString?, requirePooler? }) from @zeno-lib/db resolves and checks the URL the same way, without creating a client.

Next stepsCopy Anchor Link

Work through the package in the order you would build with it:

• Tables: declare RLS-ready Postgres tables in TypeScript with Zeno's Drizzle helpers.
• Migrations: configure Drizzle Kit, then generate, apply, and seed SQL migrations.
• Querying: create clients and run RLS-scoped, admin, and lower-level queries.
• Existing schema: describe a database you already have, and verify the description matches it.