Using supaschema with Supabase Projects and the CLI

supaschema fits into the Supabase workflow as the migration author: you describe your schema in SQL files, supaschema diffs them and writes the migration, and then the Supabase CLI applies it. supaschema never touches your real database directly — it only generates SQL. The Supabase CLI retains full control over when and how migrations are applied.

The model

┌─────────────────────┐     diff      ┌──────────────────────────┐
│  supabase/schemas/  │ ────────────► │  supabase/migrations/    │
│  (your SQL files)   │               │  (generated SQL files)   │
└─────────────────────┘               └──────────────────────────┘
                                                   │
                                              supabase db push
                                                   │
                                                   ▼
                                        ┌─────────────────────┐
                                        │  PostgreSQL          │
                                        │  (local or remote)  │
                                        └─────────────────────┘

You own the schema files. supaschema owns the migration files. Supabase owns the database.

Folder structure

A typical Supabase project using supaschema looks like this:

my-project/
├── supabase/
│   ├── config.toml          # Supabase project config
│   ├── schemas/             # Your declarative schema SQL files
│   │   └── 001_app.sql
│   └── migrations/          # Generated migrations (by supaschema)
├── supaschema.config.json
├── database.types.ts        # Generated by supaschema types
└── database.zod.ts

Do not edit files in supabase/migrations/ by hand unless you are hand-authoring a migration for a change supaschema cannot automate (such as an enum value removal). Generated files will be overwritten on the next diff run.

Configuration

Create supaschema.config.json at the project root. The supabase-auto adapter is the recommended starting point for Supabase projects — it applies sensible defaults for managed schema exclusions, role handling, and transaction wrapping:

{
  "$schema": "./node_modules/supaschema/config-schema.json",
  "adapter": "supabase-auto",
  "schemaPaths": ["supabase/schemas"],
  "migrationsDir": "supabase/migrations",
  "excludedGrantRoles": [
    "supabase_admin",
    "supabase_auth_admin",
    "supabase_storage_admin",
    "dashboard_user",
    "pgbouncer",
    "authenticator"
  ],
  "transactionMode": "per-migration"
}

Key configuration fields

Field	Description
`adapter`	`supabase-auto` pre-configures managed schema exclusions. Use `generic` for plain PostgreSQL.
`schemaPaths`	Array of directories or glob patterns pointing to your schema SQL files.
`migrationsDir`	Where supaschema writes generated migration files. Should match the Supabase CLI’s migrations directory.
`excludedGrantRoles`	Roles that appear in `GRANT` statements in your schema but should not be created by supaschema (they already exist in a real Supabase project).
`transactionMode`	`per-migration` wraps each migration in a single transaction. Use `none` for migrations that include `CREATE INDEX CONCURRENTLY` or other transaction-unsafe operations.

Managed schemas

The supabase-auto adapter automatically excludes the following schemas from declarative ownership. You cannot accidentally overwrite Supabase’s internal tables by placing SQL in these schemas:

auth
storage
realtime
vault
extensions
cron
net
supabase_functions
graphql
graphql_public

If supaschema encounters a reference to one of these schemas in your schema files, it raises a warning rather than generating a migration that would conflict with Supabase’s managed infrastructure.

Auto-discovery of the local stack

When no databaseUrl is set in your config or environment, supaschema reads supabase/config.toml to discover the local Supabase stack’s connection parameters automatically. This means you can run supaschema diff without any additional configuration after supabase start:

supabase start
npx supaschema diff --from git:HEAD --to dir:supabase/schemas

Local development workflow

Start the local Supabase stack

supabase start

Edit your schema files

Write or update your table definitions, views, functions, and RLS policies in supabase/schemas/:

-- supabase/schemas/001_app.sql
CREATE SCHEMA app;

CREATE TABLE app.accounts (
  id   bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  name text NOT NULL
);

ALTER TABLE app.accounts ENABLE ROW LEVEL SECURITY;

CREATE POLICY accounts_select ON app.accounts
  FOR SELECT
  TO public
  USING (true);

Generate the migration

npx supaschema diff

supaschema writes a timestamped migration file to supabase/migrations/ and refreshes database.types.ts and database.zod.ts.

Check the migration

npx supaschema check supabase/migrations/*.sql

Apply locally

supabase db push

Or, if you are using the local migration stack:

supabase migration up

RLS policies and row-level security

Row-level security policies are fully supported. Write them alongside your table definitions in your schema files and supaschema will include them in the generated migration:

-- supabase/schemas/001_app.sql

-- Enable RLS
ALTER TABLE app.accounts ENABLE ROW LEVEL SECURITY;

-- Users can only read their own account
CREATE POLICY accounts_own_select ON app.accounts
  FOR SELECT TO authenticated
  USING (auth.uid() = owner_id);

-- Only the service role can insert
CREATE POLICY accounts_service_insert ON app.accounts
  FOR INSERT TO service_role
  WITH CHECK (true);

supaschema tracks policies by name. Renaming a policy is treated as a drop and recreate. If you need to rename a policy without dropping it, hand-author that specific ALTER POLICY statement.

Verifying against the local stack

Use verify to prove your migration applies cleanly, is idempotent, and leaves the database matching your declared schema. Pass --ensure-roles to create stub roles (authenticated, anon, service_role) that exist in a real Supabase project but may not exist on a plain Postgres instance:

npx supaschema verify \
  --from git:HEAD \
  --to dir:supabase/schemas \
  --migration /tmp/migration.sql \
  --database-url postgresql://postgres:postgres@localhost:54322/postgres \
  --ensure-roles

The default local Supabase stack listens on port 54322. Confirm this matches your supabase/config.toml if you have a non-default configuration.

The sync command

supaschema sync combines the drift gate and apply step into one command. Use it in environments where you want to verify the database is in sync and apply any pending migrations in a single operation:

npx supaschema sync --local

sync exits non-zero if drift is detected and the migration fails to apply, making it safe to use as a deployment gate in staging pipelines.

Pushing to staging and production

supaschema never applies migrations to remote databases. Use the Supabase CLI for that:

# Push all pending migrations to your linked project
supabase db push

# Or target a specific environment
supabase db push --db-url "$PRODUCTION_DATABASE_URL"

Always run npx supaschema verify against a staging environment before pushing to production. Verify applies the migration to a throwaway database — point it at a disposable staging branch, not at production.

For staging branches on Supabase, set SUPASCHEMA_VERIFY_ALLOW_REMOTE=1 to permit verify to target a remote URL that you treat as intentionally disposable:

SUPASCHEMA_VERIFY_ALLOW_REMOTE=1 npx supaschema verify \
  --from git:HEAD \
  --to dir:supabase/schemas \
  --migration /tmp/migration.sql \
  --database-url "$STAGING_BRANCH_DATABASE_URL" \
  --ensure-roles

End-to-end example

Here is the complete flow from schema change to type-safe client usage:

Declare the schema

-- supabase/schemas/001_app.sql
CREATE TABLE app.posts (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  account_id uuid NOT NULL REFERENCES app.accounts(id) ON DELETE CASCADE,
  title      text NOT NULL,
  body       text,
  published  boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE app.posts ENABLE ROW LEVEL SECURITY;

CREATE POLICY posts_public_select ON app.posts
  FOR SELECT TO public USING (published = true);

CREATE POLICY posts_own_all ON app.posts
  FOR ALL TO authenticated USING (
    account_id IN (
      SELECT id FROM app.accounts WHERE owner_id = auth.uid()
    )
  );

Generate and check

npx supaschema diff
npx supaschema check supabase/migrations/*.sql

Verify locally

npx supaschema verify \
  --from git:HEAD \
  --to dir:supabase/schemas \
  --migration supabase/migrations/20240601_add_posts.sql \
  --database-url postgresql://postgres:postgres@localhost:54322/postgres \
  --ensure-roles

Apply locally and use the generated types

supabase db push

import { createClient } from '@supabase/supabase-js'
import type { Database } from './database.types'

const supabase = createClient<Database>(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_ANON_KEY!
)

const { data: posts } = await supabase
  .from('posts')
  .select('id, title, body, created_at')
  .eq('published', true)
  .order('created_at', { ascending: false })

​The model

​Folder structure

​Configuration

​Key configuration fields

​Managed schemas

​Auto-discovery of the local stack

​Local development workflow

​RLS policies and row-level security

​Verifying against the local stack

​The sync command

​Pushing to staging and production

​End-to-end example

The model

Folder structure

Configuration

Key configuration fields

Managed schemas

Auto-discovery of the local stack

Local development workflow

RLS policies and row-level security

Verifying against the local stack

The sync command

Pushing to staging and production

End-to-end example