# gen > Generate database types, typed models, relation metadata and validators. Source: https://bettersupabase.com/docs/cli/gen ```bash better-supabase gen [--tasks ] [--check] [--watch] [--snapshot ] [--db-url-stdin | --project-ref ] better-supabase gen --metadata [--emit | --out [--check]] ``` `gen` is the one command to run after a schema or config change. It writes the typed client from the database, then every other generated file the config sets up, so the config holds the options instead of each command line. ## Tasks [#tasks] | Task | Runs by default when | Does what | | ---------- | ----------------------------------------------------------- | -------------------------------------------------------------------------------- | | `types` | always | Writes the files this page describes | | `sql` | `sql.modules` lists a module, or `realtime.policies` is set | [`sql sync`](/docs/blocks/sql): rewrites the SQL module files | | `spec` | the `specs.entry` file exists | [`spec emit`](/docs/cli/spec), with `specs.failOn` and `specs.manifest` | | `seed` | the `seed.entry` file exists | [`seed`](/docs/cli/local#seed): renders the fixtures to SQL | | `scaffold` | `scaffold.api` is set | Rewrites the generated file of [`scaffold api`](/docs/cli/scaffold), never yours | | `env` | never; list it to run it | [`env`](/docs/cli/local#env): writes the local stack's keys to `env.output` | The tasks run in that order, since later ones read what earlier ones write. Pin the list in the config with `gen.tasks`, or pass `--tasks` for one run: ```ts title="better-supabase.config.ts" export default defineConfig({ gen: { tasks: ["types", "sql", "spec"], watchInterval: 1000 }, }); ``` ```bash better-supabase gen --tasks spec,seed ``` A listed task always runs, even when the config doesn't set it up, so a missing entry file fails loudly instead of being skipped. A run that writes stops at the first task that fails. `--check` runs every task and reports everything that is out of date, and leaves `env` out because it writes local keys rather than a committed file. With one task, the output is that task's own; with several, each gets a section headed by its name. `gen --metadata` reads the schema from a document and runs no other task. `gen` introspects your database with [`@supabase/postgrest-typegen`](https://github.com/supabase/postgrest-typegen), the same generator behind `supabase gen types typescript`, and writes: 1. `database.types.ts`, what `supabase gen types` prints for the same schema (CI checks this against the Supabase CLI), plus a `ComputedFields` key on each table and view that names its computed fields, so `createClient()` works as usual; 2. the main module (`output`), with models in your casing, enum and CHECK constants, typed constraint names, and the `schema` object; 3. the runtime metadata next to it (`generated.meta.js`, with `generated.meta.d.ts`): tables, columns, relations with foreign key actions, and functions. Relations follow each foreign key to the table it references; the copies PostgREST lists for views over that table are left out, so adding a view never renames another table's relations. It is plain JavaScript typed as `SchemaMeta`, so TypeScript doesn't check a large object literal in every program that imports the main module. Each table and function is one line of JSON, which keeps the module small to load and a schema change visible per entry in a diff. Commit both files with the main module; 4. one file per configured generator (`zod()`, `valibot()`, `jsonSchema()`, `standardSchema()`); 5. with `readSets` configured, the `read-sets` SQL module file: one function per [read set](/docs/repository/read-sets). `gen` imports those modules after writing the main module, since they import it. It doesn't need Docker or the Supabase CLI: it only needs a way to run SQL. Files are only rewritten when their contents change, so watchers and bundlers don't rebuild for nothing. Line endings don't count as a change: a checkout with `core.autocrlf` keeps its `\r\n` files, and `--check` passes on them. `database.types.ts` is formatted with [oxfmt](https://oxc.rs/docs/guide/usage/formatter), an optional peer. Without it, `gen` writes the file unformatted and says so; install it with `pnpm add -D oxfmt`. better-supabase accepts any oxfmt from 0.66.0 up to, but not including, 1.0. `@supabase/postgrest-typegen` pins exactly 0.66.0 as its peer, the version that formats like `supabase gen types`, so pnpm warns about a newer oxfmt until you allow it in `pnpm-workspace.yaml`: ```yaml title="pnpm-workspace.yaml" peerDependencyRules: allowedVersions: "@supabase/postgrest-typegen>oxfmt": "0.71.0" ``` Names are sorted by code point, not by locale, so every machine writes the same files. ## Introspection cache [#introspection-cache] Before reading a database, `gen` runs one query that hashes the system catalogs (tables, columns, constraints, policies, functions, grants, comments, role memberships and role settings; planner statistics are left out). When the hash, the schemas, the better-supabase version and the pinned typegen version match the last run, it reuses the snapshot cached in `node_modules/.cache/better-supabase` and skips introspection. Deleting that folder clears the cache. `--watch` keeps one connection open and runs that query every `--interval` milliseconds (`gen.watchInterval` in the config, default 2000), so it reads the full schema only after a change. It also checks the config file's modification time, and when the file changes it loads it again and runs every task again. A change to the spec entry, its overlays or the seed entry reruns the tasks after `types`, without reading the schema; a config that fails to load is reported once and kept until the next edit fixes it. Each run imports the `readSets` and `realtime.policies` modules again, with the project files they import (such as the main module the run wrote); packages in `node_modules` load once. Ctrl-C ends the connection, including a query in flight. Connecting gives up after 10 seconds, and each introspection query after 2 minutes. ## Options [#options] | Option | Effect | | ---------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `--tasks ` | The [tasks](#tasks) to run, comma-separated. Defaults to `gen.tasks`, then every task the config sets up. | | `--check` | Writes nothing; exits 1 when a generated file is out of date and prints a diff of each one. Use it in CI. | | `--watch` | Runs again when the schema, the config file or a task's input changes (polls every `--interval` ms). | | `--interval ` | Milliseconds between checks in `--watch`. Defaults to `gen.watchInterval`, then 2000. | | `--snapshot ` | Reads a saved snapshot instead of connecting. | | `--db-url-stdin` | Reads the connection string from stdin; overrides the config, `$DATABASE_URL` and `$SUPABASE_DB_URL`. | | `--project-ref ` | Reads a hosted project through the Management API. | | `--metadata ` | Reads a `GeneratorMetadata` document (`-` for stdin) and prints one file on stdout. See [From a GeneratorMetadata document](#from-a-generatormetadata-document). | | `--emit ` | With `--metadata`, the file to print: `schema` (default), `types`, `zod`, `valibot`, `json-schema` or `standard-schema`. | | `--out ` | With `--metadata`, writes every generated file into this directory instead of printing one. | With `--json`, `gen` prints `{ "tables", "written" }`, and `gen --check` prints `{ "upToDate", "stale" }`. When several tasks run, it prints `{ "tasks": { "": { "code", "data" } } }` instead, with each task's document under its name. A connection string holds the database password, so no command takes one as an argument, where it would land in shell history and the process list. Set `$DATABASE_URL` or `$SUPABASE_DB_URL`, or pipe it in: ```bash printf %s "$PROD_DB_URL" | better-supabase gen --check --db-url-stdin ``` ## Where the schema comes from [#where-the-schema-comes-from] In order: 1. `--snapshot`, `--db-url-stdin` or `--project-ref`; 2. `source.snapshot` in the config, when none of those flags is passed; 3. `source.dbUrl` or `source.projectRef` in the config; 4. a [Supabase Lite](/docs/platform/lite) project's `[db] driver` in `supabase/config.toml` (turn this off with `source.lite: false`); 5. `$DATABASE_URL`; 6. `$SUPABASE_DB_URL`, the name the Supabase CLI and `better-supabase env` use; 7. the local stack, on the `[db] port` from `supabase/config.toml` (54322 by default). On a Lite project, the `postgres` driver reads its `[db] url`. The `pglite` and `sqlite-postgres` drivers replay `supabase/migrations` and then the declarative schema files into an in-memory PGlite with Lite's auth schema, and introspect that, so `gen` needs no running server. The bare `sqlite` driver takes native SQLite DDL, which `gen` can't read: switch to `sqlite-postgres`, or save a snapshot. ### Hosted projects without a database password [#hosted-projects-without-a-database-password] `source.projectRef` (or `--project-ref`) runs the introspection queries through the Management API's read-only SQL endpoint (`POST /v1/projects/{ref}/database/query/read-only`). You need a [personal access token](https://supabase.com/dashboard/account/tokens) in `SUPABASE_ACCESS_TOKEN`, not the database password, and the queries run as a read-only role. Create a scoped token for this: limit it to the project and the read-only query permission the [personal access tokens guide](https://supabase.com/docs/guides/platform/personal-access-tokens) lists for that endpoint. A classic token carries your whole account, on every organization and project, which is more than CI or an agent needs. A scoped token that lacks the permission answers "You do not have permission to perform this action". ```ts title="better-supabase.config.ts" export default defineConfig({ source: { projectRef: "abcdefghijklmnopqrst" }, }); ``` ```bash SUPABASE_ACCESS_TOKEN=sbp_... better-supabase gen --check ``` `SUPABASE_API_URL` points it at another Management API host. ## What the metadata knows [#what-the-metadata-knows] The generated `schema` carries what the runtime needs to stay correct without extra round trips: * **Read-only columns.** Generated columns, `identity always` columns and view columns Postgres marks as not insertable or updatable are left out of the `Insert` and `Update` types, and writes to them fail before a request is sent. * **Unique keys and constraint names.** `findUnique` accepts the primary key or any named unique key, and `UniqueConstraint`, `CheckConstraint` and `ForeignKeyConstraint` types narrow `isConflict(error, 'customers_kvk_key')`. See [unique keys and errors](/docs/repository/unique-and-errors). * **Foreign key actions.** `on delete cascade`, `set null` and `set default` are recorded on relations, so a delete invalidates the tables it changes. See [caching](/docs/concepts/caching). * **Codecs.** With `codecs` in the config, `int8`, `numeric` and `timestamptz` columns are read exactly (as `bigint`, `string` or `Temporal.Instant`; `timestamp` becomes `Temporal.PlainDateTime`), and the generated validators match. See [Temporal](/docs/concepts/temporal). * **Columns a CHECK makes not null.** A nullable column with `check (slug is not null)`, alone or as a term of a top-level `and`, is typed and validated as not null. It is required on insert unless it has a default or the table has a row-level `before insert` trigger, which can fill it before the CHECK runs. A term under `or` or `not`, and a `not valid` constraint, change nothing. Prefer `not null` on the column itself when you can. * **Columns the database fills on insert.** A not-null column without a default is required in `InsertOf` and the insert validators. When a trigger or another database rule fills it, list its database name in `tables..insertOptional` and `gen` makes it optional on insert while the row type stays not null: ```ts title="better-supabase.config.ts" export default defineConfig({ tables: { invoices: { insertOptional: ["number"] } }, }); ``` `gen` fails when a listed name is not a column of the table. * **Function arguments and results.** Every argument in `Functions` accepts `null`, since Postgres passes `null` to any function, and arguments with a default are optional. Functions that return rows of a table or a `returns table (...)` record get a `result` entry, so `db.$rpc` returns them in your [casing](/docs/concepts/casing#function-results). * **Overloaded functions.** A function with several signatures gets a union of `{ Args; Returns }` in `Functions`, one member per overload in signature order, as in `database.types.ts`. PostgREST picks an overload by the argument names, so `db.$rpc` does too: a call type-checks against any overload, returns the type of the overload whose names it passes, and decodes the result with that overload's `result`. Overloads with the same argument names (`pick(value integer)` and `pick(value text)`) return the union of their types, and PostgREST can't choose between them at runtime either. An overload without arguments is typed `Record`, so it never matches a call that passes one. * **Nullable function results.** Postgres can't promise that a function returns a value: a `strict` function returns `null` for a `null` argument, a SQL function returns `null` when its query finds no row, and an aggregate in a `returns table` column is `null` over no rows. So a scalar result, each element of a `setof` scalar, a single table row and each `returns table` column are typed `| null`. Rows of `returns setof` a table are not, and neither is `void` or `Json`, which already includes `null`. When you know a result is never null, say so in the config: ```ts title="better-supabase.config.ts" export default defineConfig({ functions: { open_ticket_count: { notNull: true }, customer_note_counts: { notNull: ["customer_id", "note_count"] }, }, }); ``` `notNull: true` covers the whole result, and a list names the `returns table` columns (database names) that are never null. `gen` fails on a function or column it can't find. ## Documentation in the validators [#documentation-in-the-validators] The `zod()`, `valibot()`, `jsonSchema()` and `standardSchema()` generators carry what the database says about a table into the schemas, so an OpenAPI document or an MCP tool built from them describes each field: * Each table schema gets a title from the table name (`customer_tags` becomes `Customer tags`, then `Customer tags insert` and `Customer tags update`) and a description from `comment on table`. * Each field gets a description from `comment on column`. A comment line that starts with `@example` adds an example: JSON when it parses (`@example 42`, `@example "Acme B.V."`), text otherwise. Examples the column's type rejects are left out. * Simple CHECK constraints become bounds the validators enforce. Comparisons of a numeric column with a constant (`price >= 0`, `rating between 1 and 5`) become `minimum`, `maximum` and their exclusive forms, and comparisons of `length` or `char_length` with a constant become `minLength` and `maxLength`. Terms joined by `or`, other functions and comparisons between columns are left out. ```sql title="supabase/schemas/customers.sql" create table public.customers ( name text not null check (char_length(name) between 1 and 200) -- ... ); comment on table public.customers is 'Companies the organization sells to.'; comment on column public.customers.name is 'Trading name. @example "Acme B.V."'; ``` ```ts title="src/lib/supabase/generated.zod.ts" export const customersInsert = z .object({ name: z .string() .min(1) .max(200) .meta({ description: "Trading name.", examples: ["Acme B.V."] }), // ... }) .meta({ title: "Customers insert", description: "Companies the organization sells to.", }); ``` Valibot gets the same through `v.title`, `v.description`, `v.examples`, `v.minLength` and `v.minValue` in a pipe, and the JSON Schema through `title`, `description`, `examples`, `minLength` and `minimum`. The `standardSchema()` output carries them in each field spec. The generated metadata carries the comments too, so the API documents that [`defineApi`](/docs/specs) and `createOpenApi` render describe each table and column without a validator. A table or column comment becomes `description` in the metadata (without its `@example` lines), and a comment line that starts with `@deprecated` sets `deprecated: true`. In the document, the table's description goes on its schemas and its tag, each column's on its property, and a deprecated table marks its schemas and operations deprecated. A tag description set on the resource wins over the table comment. Examples stay in the validators; pass `examples` to `defineApi` to put rows in the document. ### Coming from supabase-to-zod [#coming-from-supabase-to-zod] `supabase-to-zod` converts the `database.types.ts` file into zod 3 schemas through `ts-to-zod`. The `zod()` generator writes zod 4 schemas from the database catalog instead, so they carry the CHECK bounds, comments and codecs above, and it keeps separate `Row`, `Insert` and `Update` schemas per table. Replace the `supabase-to-zod` script with `generators: [zod()]` and import `
Row` from `generated.zod.ts`. ## Standard Schema without a validation library [#standard-schema-without-a-validation-library] `standardSchema()` writes `.standard.ts`: a schema per table for its Row, Insert and Update shapes that needs no zod or valibot install. Each one implements [Standard Schema](https://standardschema.dev) (`~standard.validate`) and Standard JSON Schema (`~standard.jsonSchema`), so it works anywhere a Standard Schema does: the [validation plugin](/docs/plugins/validation), `validate()`, form libraries, oRPC and MCP tool inputs. ```ts title="better-supabase.config.ts" import { defineConfig, standardSchema } from "better-supabase/config"; export default defineConfig({ output: "src/lib/supabase/generated.ts", generators: [ standardSchema({ json: { "customers.metadata": "./schemas.ts#metadata" } }), ], }); ``` ```ts title="src/lib/supabase/generated.standard.ts" import { type TableSchema, tableSchema } from "better-supabase"; export const customersInsert: TableSchema> = tableSchema({ title: "Customers insert", fields: { name: { kind: "string", minLength: 1, maxLength: 200 }, status: { kind: "enum", values: ["lead", "active", "archived"], optional: true, }, metadata: { kind: "json", nullable: true, optional: true, schema: metadata, }, }, }); export const validators = { customers: { insert: customersInsert, update: customersUpdate }, }; ``` The checks match the `zod()` output: uuid, integer and ISO date formats, enum values, CHECK bounds, `null` only on nullable columns, and Temporal values on `instant` and `plainDateTime` codec columns. A present key never accepts `undefined`, and unknown keys are dropped from the validated value. `json` takes a Standard Schema per typed jsonb column; its issues keep their path under the column, and its JSON Schema is used when it has one. `~standard.jsonSchema.input()` and `output()` return the same JSON Schema the `jsonSchema()` generator writes for that shape, for the `draft-2020-12`, `draft-07` and `openapi-3.0` targets: ```ts const schema = customersInsert["~standard"].jsonSchema.input({ target: "draft-07", }); ``` ## Snapshots [#snapshots] ```bash better-supabase introspect --out supabase/snapshot.json better-supabase gen --snapshot supabase/snapshot.json ``` A committed snapshot lets CI and contributors generate without a database. [`introspect`](/docs/cli/introspect) writes and checks it. The output is the same after `supabase db reset` and on every machine: object ids come from names, and the arguments of each function keep their declaration order. That matters for extension functions with unnamed arguments, such as pgvector's distance functions and citext's casts, whose argument order the catalog query alone does not fix. ## From a GeneratorMetadata document [#from-a-generatormetadata-document] `gen --metadata` generates from the JSON document that `@supabase/postgrest-typegen` defines (`GeneratorMetadata`, versioned by `GENERATOR_METADATA_VERSION`), the same document [`@supabase/typegen`](https://github.com/supabase/sdk/tree/main/packages/typegen) pipes to out-of-process generators. Pass a file, or `-` to read stdin: ```bash better-supabase gen --metadata - < metadata.json > src/lib/supabase/generated.ts ``` It follows the registry's contract for an out-of-process tool: * stdout holds only the generated file. Notices, warnings and the oxfmt notice go to stderr. * It exits 0 on success, 1 when generation fails (for example without `@supabase/postgrest-typegen` installed), 2 on a usage error, and 65 (the code the registry maps to `MetadataRejectedError`, as for Dart's `supabase_typegen`) when it can't read the document, the document is not JSON, its `version` is not the `GENERATOR_METADATA_VERSION` this release reads, or the schema rejects it. The reason is on stderr. * It reads `better-supabase.config.ts` from the working directory when there is one (`casing`, `schemas`, `tables`, `codecs`, generator options), and the defaults otherwise. `--emit` picks the file: | `--emit` | Prints | | ----------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------- | | `schema` | The `defineSchema` module, with the metadata written into it instead of a separate `generated.meta.js`. It imports `Database` from `databaseTypesOutput`. | | `types` | `database.types.ts`, formatted when oxfmt is installed. | | `zod` | The `zod()` generator's file, with the options from the config when it lists `zod()`. | | `valibot` | The `valibot()` generator's file, likewise. | | `json-schema` | The `jsonSchema()` generator's document, likewise. | | `standard-schema` | The `standardSchema()` generator's file, likewise. | `--out ` writes the usual files (`database.types.ts`, the main module, its metadata module and each configured generator's file) into one directory instead, and `--check` compares them there. A generator with its own `output` keeps that path. It leaves the read-set SQL module and the files an earlier `gen` wrote alone. The document carries no more than the Data API types need, so a few things the database connection adds are missing. Relations, primary keys, enums, single-column unique keys and single-column CHECK unions stay typed; unique keys and checks get the names Postgres gives them by default (`
__key`, `
__check`). Multi-column unique keys and checks, foreign key actions, indexes, triggers, policies, grants, buckets and the realtime publication are absent, and a notice on stderr says so. Run `gen` against the database for the full model. `doctor --metadata` reads the same document. Checks that need what it lacks are skipped with an info finding, and the advisors and live checks are skipped as for a saved snapshot. ### As a `@supabase/typegen` language [#as-a-supabasetypegen-language] The registry runs an out-of-process language in the project directory with the sorted document on stdin. An `externalLanguage` entry for better-supabase runs `npx better-supabase gen --metadata -`: ```ts title="packages/typegen/src/languages/better-supabase.ts" import { externalLanguage } from "./external.ts"; export const betterSupabase = externalLanguage( "better-supabase", [ { name: "emit", audience: "user", kind: "choice", choices: [ "schema", "types", "zod", "valibot", "json-schema", "standard-schema", ], default: "schema", help: "The file to generate: the defineSchema module, database.types.ts or a validator file", }, ], { command: "npx", args: (_metadata, options) => [ "--no-install", "better-supabase", "gen", "--metadata", "-", "--emit", String(options.emit), ], installHint: "Install Node.js, then run `npm install -D better-supabase @supabase/postgrest-typegen` in the project.", classify: (result) => { if ( result.stderr.includes("could not determine executable to run") || result.stderr.includes( 'needs the "@supabase/postgrest-typegen" package', ) ) { return { kind: "not-installed", tool: "the better-supabase package", installHint: "Run `npm install -D better-supabase @supabase/postgrest-typegen` in the project that should receive the types, then generate from that directory.", }; } if (result.exitCode === 65) return { kind: "metadata-rejected" }; return undefined; }, }, ); ``` Once the entry is in the registry's `languages`, `user` options become flags of `supabase gen types`, so `supabase gen types --lang better-supabase --emit zod` prints what `better-supabase gen --metadata - --emit zod` prints for the same database. The upstream registry doesn't ship this entry, so without it, pipe the document to `better-supabase gen --metadata -` yourself.