> ## Documentation Index
> Fetch the complete documentation index at: https://www.pgschema.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Supabase Declarative Schema vs pgschema

> Comparing two declarative PostgreSQL schema tools: workflow differences, database object support, online DDL, CI/CD integration, and file organization.

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](https://github.com/pgplex/pgschema/issues/227). This article compares both tools to help you understand when each makes sense.

## Workflow Comparison

<CardGroup cols={2}>
  <Card title="Supabase (migration history in DB)">
    ```mermaid theme={null}
    flowchart TB
        A[Edit schema files] --> B[supabase db diff]
        B --> C[Review migration file]
        C --> D[supabase db push]
        D --> E[Migration history updated]
        E --> F[Database updated]
    ```
  </Card>

  <Card title="pgschema (migration history in git)">
    ```mermaid theme={null}
    flowchart TB
        A[Edit schema files] --> B[pgschema plan]
        B --> C[Review plan]
        C --> D[pgschema apply]
        D --> E[Database updated]
    ```
  </Card>
</CardGroup>

### 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:**

```sql theme={null}
-- supabase/schemas/users.sql
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  phone TEXT  -- add this line
);
```

```bash theme={null}
# 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:

```bash theme={null}
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:**

```sql theme={null}
-- schema.sql
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  phone TEXT  -- add this line
);
```

```bash theme={null}
# 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:

```sql theme={null}
-- schema.sql - remove the phone column
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);
```

```bash theme={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](/workflow/plan-review-apply#detect-concurrent-changes-with-fingerprinting) of the database schema at plan time:

```json theme={null}
{
  "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:

```bash theme={null}
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](https://github.com/djrobstep/migra) for schema diffing, which has [documented limitations](https://supabase.com/docs/guides/local-development/declarative-database-schemas#known-caveats). pgschema also has [limitations](/syntax/unsupported), 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 Type                  | Supabase/migra | pgschema |
| ---------------------------- | -------------- | -------- |
| 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](/workflow/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`:

```toml theme={null}
[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](/workflow/modular-schema-files):

```bash theme={null}
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:

```sql theme={null}
\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:

```bash theme={null}
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`](/cli/ignore) file with pattern matching:

```toml theme={null}
[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

| Aspect                      | Supabase                                        | pgschema                                                  |
| --------------------------- | ----------------------------------------------- | --------------------------------------------------------- |
| **License**                 | Apache 2.0                                      | Apache 2.0                                                |
| **Workflow**                | Schema files → migrations → history table       | Schema files → plan → apply (git history, fingerprinting) |
| **Database object support** | Limited by migra (no RLS, triggers, partitions) | Comprehensive schema-level support                        |
| **Online DDL**              | Manual                                          | Auto-generates CONCURRENTLY, NOT VALID                    |
| **Multi-file schemas**      | config.toml with glob patterns                  | PostgreSQL-native `\i` directive                          |
| **Ignore patterns**         | Schema-level only                               | Granular `.pgschemaignore` with wildcards                 |
| **Database compatibility**  | Supabase-hosted PostgreSQL                      | Any 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](https://github.com/pgplex/pgschema/issues/227).