Skip to main content
Managing PostgreSQL schemas with imperative migrations - writing ALTER TABLE statements by hand, tracking which migrations ran where - doesn’t scale. Declarative schema management flips this: you define the desired state, and the tool figures out how to get there. It’s the Terraform approach for databases. Both Supabase and pgschema offer this declarative workflow for PostgreSQL. Recently, we’ve seen users migrating from Supabase’s declarative schemas to pgschema. This article compares both tools to help you understand when each makes sense.

​
Workflow Comparison

Supabase (migration history in DB)

pgschema (migration history in git)

​
Supabase

Supabase maintains two directories and a database table:
  • supabase/schemas/ - Your desired state (what you edit)
  • supabase/migrations/ - Generated migration files (timestamped SQL)
  • supabase_migrations.schema_migrations - Table tracking which migrations have been applied
When you run supabase db diff, it compares your schema files against the cumulative result of existing migrations and generates a new timestamped migration file. Adding a column: 
-- supabase/schemas/users.sql
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  phone TEXT  -- add this line
);

# Generate migration from schema diff
supabase db diff -f add_phone_column
# Creates: supabase/migrations/20240115120000_add_phone_column.sql

# Apply locally
supabase migration up

# Push to remote
supabase db push
Rolling back: Supabase uses versioned migrations. To rollback, you reset to a previous migration timestamp: 
supabase db reset --version 20240101000000
This replays all migrations up to that version. The migration history table tracks what’s been applied.

​
pgschema

pgschema has a single source of truth:
  • Your schema SQL file(s) - that’s it
  • No migration history table
  • No migrations directory
Each plan command does a fresh comparison: desired state (your SQL files) vs current state (live database). Adding a column: 
-- schema.sql
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  phone TEXT  -- add this line
);

# See the migration plan
pgschema plan --host localhost --db myapp --user postgres --file schema.sql

# Save the plan for review and apply
pgschema plan --host localhost --db myapp --user postgres --file schema.sql --output-json plan.json

# Apply the reviewed plan
pgschema apply --host localhost --db myapp --user postgres --plan plan.json
Rolling back: There’s no migration history. To rollback, edit your schema file to the desired state and apply: 
-- schema.sql - remove the phone column
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

pgschema plan --host localhost --db myapp --user postgres --file schema.sql --output-json plan.json
# Shows: ALTER TABLE users DROP COLUMN phone;

pgschema apply --host localhost --db myapp --user postgres --plan plan.json
Rollback is just another state change. No timestamps, no migration history to manage.

​
Concurrent Change Detection in CI/CD

Both tools decouple migration generation from execution. Both produce migration SQL. But pgschema’s JSON plan format encodes more than just the SQL statements - it includes a fingerprint of the database schema at plan time: 
{
  "source_fingerprint": {
    "hash": "965b1131737c955e24c7f827c55bd78e4cb49a75adfd04229e0ba297376f5085"
  },
  "statements": [...]
}
This matters in CI/CD pipelines. A typical workflow:
  1. PR is opened, CI runs pgschema plan and saves plan.json as an artifact
  2. Team reviews the plan in the PR
  3. PR is merged, CD runs pgschema apply --plan plan.json
But what if another PR merged and applied schema changes between steps 1 and 3? With Supabase, you might apply a stale migration that conflicts with changes made by another PR. With pgschema, the apply fails: 
pgschema apply --host prod.db.com --db myapp --user postgres --plan plan.json
# ❌ Error: schema fingerprint mismatch detected - the database schema has changed since the plan was generated.
The CI pipeline catches the conflict. You regenerate the plan, review again, and apply. No silent schema drift.

​
Database Object Support

Supabase uses migra for schema diffing, which has documented limitations. pgschema also has limitations, but they’re fundamentally different. Supabase/migra cannot track these schema-level objects:
  • Row-level security (RLS) policies
  • Materialized views
  • Comments
  • Partitions
  • Column privileges
  • View ownership and grants
  • Domain statements
For these, Supabase recommends falling back to versioned migrations - defeating the purpose of declarative schemas. pgschema supports all of the above. Its limitations are at cluster and database levels - objects typically provisioned once during setup rather than changed frequently:
  • Cluster level: CREATE DATABASE, CREATE ROLE, CREATE TABLESPACE
  • Database level: CREATE EXTENSION, CREATE SCHEMA, CREATE PUBLICATION
  • RENAME operations (use DROP + CREATE instead)
These objects are typically managed separately through infrastructure tools, cloud consoles, or initial setup scripts.
Object TypeSupabase/migrapgschema
Tables, views, functions✅✅
RLS policies❌✅
Materialized views❌✅
Triggers❌✅
Procedures❌✅
Partitions❌✅
Comments❌✅
Column privileges❌✅
Domain types❌✅
CREATE INDEX CONCURRENTLY❌✅
ADD CONSTRAINT … NOT VALID❌✅
The last two rows matter for production. pgschema automatically rewrites your declarative schema changes into online DDL - you write a normal CREATE INDEX, pgschema generates CREATE INDEX CONCURRENTLY. You add a foreign key constraint, pgschema generates ADD CONSTRAINT ... NOT VALID followed by VALIDATE CONSTRAINT. No manual intervention needed.

​
Organizing Schema Files

Both tools support splitting schemas into multiple files, but with different approaches.

​
Multi-file Structure

Supabase processes files in supabase/schemas/ in lexicographic order. For custom ordering, you configure config.toml: 
[db.migrations]
schema_paths = [
  "./schemas/employees.sql",
  "./schemas/*.sql",
]
pgschema uses PostgreSQL’s native \i (include) directive. You can dump an existing database into a modular structure: 
pgschema dump --host localhost --db myapp --user postgres \
  --multi-file --file schema/main.sql
This generates: 
schema/
├── main.sql              # Entry point with \i directives
├── tables/
│   ├── users.sql
│   └── orders.sql
├── views/
├── functions/
└── indexes/
You can include individual files or entire folders: 
\i tables/users.sql
\i views/
\i functions/
The key difference: pgschema uses PostgreSQL’s native \i directive, so your schema files work directly with psql - no tool-specific configuration needed.

​
Ignoring Objects

Supabase has a --schema flag to include/exclude entire schemas. By default, auth and storage schemas are excluded from diffs since they’re managed by Supabase. You can diff specific schemas with: 
supabase db diff --schema public,custom_schema
However, there’s no way to ignore individual tables or objects within a schema. pgschema supports a .pgschemaignore file with pattern matching: 
[tables]
patterns = ["temp_*", "test_*", "!test_core_*"]

[views]
patterns = ["debug_*", "analytics_*"]

[functions]
patterns = ["fn_test_*"]
This is useful for:
  • Gradual adoption - onboard tables incrementally without managing everything at once
  • Temporary objects - exclude debug views, temp tables, development-only objects
  • Legacy objects - ignore deprecated tables while managing new ones

​
Summary

AspectSupabasepgschema
LicenseApache 2.0Apache 2.0
WorkflowSchema files → migrations → history tableSchema files → plan → apply (git history, fingerprinting)
Database object supportLimited by migra (no RLS, triggers, partitions)Comprehensive schema-level support
Online DDLManualAuto-generates CONCURRENTLY, NOT VALID
Multi-file schemasconfig.toml with glob patternsPostgreSQL-native \i directive
Ignore patternsSchema-level onlyGranular .pgschemaignore with wildcards
Database compatibilitySupabase-hosted PostgreSQLAny PostgreSQL database

​
Conclusion

Both tools embrace declarative schema management - define your desired state, let the tool generate the migration. This approach reduces manual SQL writing and eliminates migration ordering headaches. Choose Supabase if you’re fully invested in the Supabase ecosystem (Auth, Storage, Edge Functions) and your schema uses basic objects. The integrated tooling provides a cohesive experience. Choose pgschema if you need broader PostgreSQL support (RLS, triggers, partitions), online DDL for production safety, or database portability across AWS RDS, Cloud SQL, self-hosted, or Supabase. Both tools are open source. Try them on a test database and see which workflow fits your team. For those who are considering migrating from Supabase declarative schema to pgschema, you can check out the experience/gaps from the community discussion.