Local Development

Declarative database schemas

Manage your database schemas in one place and generate versioned migrations.

Overview

Declarative schemas allow you to reduce code duplications in managing schema migrations. As your database schema evolves over time, declaring it in one place will help you iterate faster by referring to a single source of truth.

Schema migrations

Schema migrations are SQL statements written in Data Definition Language. They are versioned in your supabase/migrations directory to ensure schema consistency between local and remote environments.

Declaring your schema

1

Create your first schema file

Create a SQL file in supabase/schemas directory that defines an employees table.

supabase/schemas/employees.sql

_10
create table "employees" (
_10
"id" integer not null,
_10
"name" text
_10
);

2

Generate a migration file

Generate a migration file by diffing against your declared schema.

Terminal

_10
supabase db diff -f employees_table

3

Start the local database

The new migration file will be used when starting the database locally so you can validate your schema using the local Dashboard.

Terminal

_10
supabase start

Updating your schema

1

Stop the local database

Before updating your schema files, stop the local development environment.

Terminal

_10
supabase stop

2

Add a new column

Edit supabase/schemas/employees.sql file to add a new column to employees table.

supabase/schemas/employees.sql

_10
create table "employees" (
_10
"id" integer not null,
_10
"name" text,
_10
"age" smallint not null
_10
);

3

Generate a new migration

Diff existing migrations against your declared schema.

Terminal

_10
supabase db diff -f add_age

4

Review the generated migration

Verify that the generated migration contain a single incremental change.

supabase/migrations/<timestamp>_add_age.sql

_10
alter table "public"."employees" add column "age" smallint not null;

5

Apply the pending migration

Start the database locally and apply the pending migration.

Terminal

_10
supabase start && supabase migration up

Managing dependencies

As your database schema evolves, you will probably start using more advanced entities like views and functions. These entities are notoriously verbose to manage using plain migrations because the entire body must be recreated whenever there is a change. Using declarative schema, you can now edit them in-place so it’s much easier to review.

supabase/schemas/employees.sql

_16
create table "employees" (
_16
"id" integer not null,
_16
"name" text,
_16
"age" smallint not null
_16
);
_16
_16
create view "profiles" as
_16
select id, name from "employees";
_16
_16
create function "get_age"(employee_id integer) RETURNS smallint
_16
LANGUAGE "sql"
_16
AS $$
_16
select age
_16
from employees
_16
where id = employee_id;
_16
$$;

Your schema files are run in lexicographic order by default. The order is important when you have foreign keys between multiple tables as the parent table must be created first. For example, your supabase directory may end up with the following structure.


_10
.
_10
└── supabase/
_10
├── schemas/
_10
│ ├── employees.sql
_10
│ └── managers.sql
_10
└── migrations/
_10
├── 20241004112233_employees_table.sql
_10
├── 20241005112233_add_employee_age.sql
_10
└── 20241006112233_add_managers_table.sql

For small projects with only a few tables, the default schema order may be sufficient. However, as your project grows, you might need more control over the order in which schemas are applied. To specify a custom order for applying the schemas, you can declare them explicitly in config.toml. Any glob patterns will evaluated, deduplicated, and sorted in lexicographic order. For example, the following pattern ensures employees.sql is always executed first.

supabase/config.toml

_10
[db.migrations]
_10
schema_paths = [
_10
"./schemas/employees.sql",
_10
"./schemas/*.sql",
_10
]

Pulling in your production schema

To set up declarative schemas on a existing project, you can pull in your production schema by running:

Terminal

_10
supabase db dump > supabase/schemas/prod.sql

From there, you can start breaking down your schema into smaller files and generate migrations. You can do this all at once, or incrementally as you make changes to your schema.

Known caveats

The migra diff tool used for generating schema diff is capable of tracking most database changes. However, there are edge cases where it can fail.

If you need to use any of the entities below, remember to add them through versioned migrations instead.

Data manipulation language

  • DML statements such as insert, update, delete, etc., are not captured by schema diff

View ownership

RLS policies

Other entities