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 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;

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.


_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. If you need to declare foreign keys between multiple tables, remember to create the parent table 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

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