Declarative schema

Declarative schema management means you write the schema you want. You do not hand-write every ALTER TABLE, CREATE INDEX, or DROP COLUMN. supaschema reads your SQL files, compares them with the current state, and generates the migration.

You own the schema intent. supaschema owns the diff.

Imperative vs. declarative

Most teams start with imperative migrations. Each file describes a change. Over time, the current schema gets harder to see because it is spread across many files. Declarative schema management inverts this model.

Imperative (traditional)

You write ALTER TABLE orders ADD COLUMN status text. You track every change as a new file. The current schema is implicit — spread across hundreds of migration files.

Declarative (supaschema)

You write CREATE TABLE orders (id bigint, status text). The current schema is explicit — always visible in your source files. supaschema generates the ALTER TABLE for you.

With the declarative model, schema source files are the source of truth. A new engineer can read the schema tree without replaying migration history. Declarative tooling existed before supaschema. The difference is loop cost. Many workflows replay your schema into a Docker shadow database just to read it. Types often cannot regenerate until after apply:

Traditional declarative workflow using a shadow database, schema replay, diff, apply, and type introspection

supaschema ships PostgreSQL’s parser inside the package. The loop can run without a database. Policy bodies are compared structurally, not by name:

supaschema workflow using embedded PostgreSQL parsing, AST comparison, guarded migration rendering, and offline type generation

SQL files on disk

A schema source is any collection of SQL files that supaschema can parse into a complete picture of your database objects. The most common source is a directory of .sql files referenced by the schemaPaths configuration key (or the dir: source prefix on the CLI).

# Generate from the configured source defaults
supaschema diff

Inside that directory, you write ordinary PostgreSQL CREATE statements — one file per table, one file per domain, or however you prefer to organise them. supaschema does not impose a file-naming convention.

-- database/schemas/orders.sql
CREATE TABLE public.orders (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id   uuid        NOT NULL REFERENCES public.users (id),
  status    text        NOT NULL DEFAULT 'pending',
  total     numeric(12, 2) NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX orders_user_id_idx ON public.orders (user_id);

supaschema extracts every recognisable database object from these files, including:

Tables — columns, constraints, defaults, identity columns
Views and materialised views
Functions and procedures
Types — composite types, domains, ranges
Enums
Indexes — including partial and expression indexes
Row-level security policies
Grants and privileges
Triggers
Extensions (within managed schemas)

supaschema uses PostgreSQL’s own parser compiled to WebAssembly (WASM) to tokenise and validate your SQL files. This means your schema is parsed with 100 % PostgreSQL fidelity — no regex shortcuts, no home-grown grammar — and it works without a running database instance.

SchemaModel snapshot

After parsing source files, supaschema builds a SchemaModel. Think of it as a catalog snapshot. Each object has a type, qualified name, normalized definition, and deterministic content hash.

// SchemaModel interface
interface SchemaModel {
  source: string; // the source URI this model was built from
  objects: SchemaObject[]; // every extracted database object
  diagnostics: Diagnostic[]; // parse warnings and errors
  fingerprint: string; // deterministic hash of all objects
  formatVersion?: number; // snapshot schema version
}

Both sides of a diff become SchemaModels:

from is the current state;
to is the desired state.

Comparing the two models produces the migration plan.

Fingerprints and drift

Every SchemaModel has a fingerprint. The fingerprint changes when any modeled object changes, including a column type, function body, or policy expression.

# Inspect the current fingerprint of your schema files
supaschema fingerprint --from dir:database/schemas

{
  "fingerprint": "e7b4a1d3f2c9...",
  "objectCount": 42,
  "schemas": ["public", "app"]
}

Fingerprints make drift visible. If the live database fingerprint differs from your schema files, someone changed the database outside the normal workflow.

Store the fingerprint output of supaschema fingerprint --from database:$DATABASE_URL in your CI pipeline after every successful deployment. A diverging fingerprint on the next run is an early warning that the live database has drifted from your declared schema.

Why idempotency matters

A migration is idempotent when it can run more than once without failing or changing state after the first run. supaschema generates idempotent SQL by default. Idempotency matters because:

Retries are safe. If a deployment fails halfway through and the runner re-applies the migration file, it will not produce duplicate-object errors or corrupt data.
CI verification is straightforward. The verify command applies the migration twice against a throwaway database to confirm idempotency. If the second application changes any state, the verification fails.
Partial rollouts stay consistent. In multi-region setups where the same migration file is applied to several database instances, each application is guaranteed to converge to the same final state regardless of order or repetition.

Not all SQL is idempotent by default. Statements like INSERT INTO or raw ALTER TABLE … ADD COLUMN (without IF NOT EXISTS) will fail on re-application. supaschema’s renderer handles this automatically for the objects it manages; hand-authored migration SQL should still be reviewed with supaschema check and, when a database is available, supaschema verify.

Managed schemas in Supabase

When you deploy on Supabase, several schemas are owned and managed by the platform itself. supaschema blocks declarative ownership of objects in all of these schemas: auth, storage, realtime, vault, extensions, cron, net, supabase_functions, graphql, graphql_public This protection exists because:

The platform migrates these schemas on your behalf during Supabase version upgrades.
Dropping or altering a platform-managed function (such as auth.uid()) can break authentication across your entire project.
Your schema files should express only the objects you own.

-- ✅ Allowed — you own the public schema
CREATE TABLE public.profiles (
  id uuid PRIMARY KEY REFERENCES auth.users (id)
);

-- ❌ Blocked — auth schema is platform-managed
CREATE FUNCTION auth.custom_hook() ...

You can still reference managed schema objects (foreign keys to auth.users, calls to auth.uid() in policies, etc.). supaschema only prevents you from declaring ownership of objects inside those schemas.

Managed schema protection is enforced at the plan stage: if a diff would create or replace an object inside a blocked schema, supaschema emits an error before rendering any SQL. This keeps your declarative model safely scoped to the schemas you actually control.

Start

Core workflow

Configure

Tasks

Agents and CI

Evidence

Reference

Imperative vs. declarative

Imperative (traditional)

Declarative (supaschema)

SQL files on disk

SchemaModel snapshot

Fingerprints and drift

Why idempotency matters

Managed schemas in Supabase

​Imperative vs. declarative

Imperative (traditional)

Declarative (supaschema)

​SQL files on disk

​SchemaModel snapshot

​Fingerprints and drift

​Why idempotency matters

​Managed schemas in Supabase

Imperative vs. declarative

SQL files on disk

SchemaModel snapshot

Fingerprints and drift

Why idempotency matters

Managed schemas in Supabase