Home

Database Migrations

How to manage schema migrations for your Supabase project.

Database migrations are SQL statements that create, update, or delete your existing database schemas. They are a common way of tracking changes to your database over time.

Schema migrations

For this guide, we'll create a table called employees and see how we can make changes to it.

You will need to install the Supabase CLI and start the local development stack.

1

Create your first migration file

To get started, generate a new migration to store the SQL needed to create our employees table.

Terminal

_10
supabase migration new create_employees_table

2

Add the SQL to your migration file

This creates a new migration file in supabase/migrations directory.

To that file, add the SQL to create this employees table.

supabase/migrations/<timestamp>_create_employees_table.sql

_10
create table if not exists employees (
_10
id bigint primary key generated always as identity,
_10
name text not null,
_10
email text,
_10
created_at timestamptz default now()
_10
);

3

Apply your first migration

Run this migration to create the employees table.

Now you can visit your new employees table in the local Dashboard.

Terminal

_10
supabase migration up

4

Modify your employees table

Next, modify your employees table by adding a column for department.

Terminal

_10
supabase migration new add_department_column

5

Add a new column to your table

To that new migration file, add the SQL to create a new department column.

supabase/migrations/<timestamp>_add_department_column.sql

_10
alter table if exists public.employees
_10
add department text default 'Hooli';

6

Apply your second migration

Run this migration to update your existing employees table.

Terminal

_10
supabase migration up

Finally, you should see the department column added to your employees table in the local Dashboard.

Seeding data

Now that you are managing your database with migrations scripts, it would be great have some seed data to use every time you reset the database.

1

Populate your table

Create a seed script in supabase/seed.sql.

To that file, add the SQL to insert data into your employees table.

supabase/seed.sql

_10
insert into public.employees
_10
(name)
_10
values
_10
('Erlich Bachman'),
_10
('Richard Hendricks'),
_10
('Monica Hall');

2

Reset your database

Reset your database to reapply migrations and populate with seed data.

Terminal

_10
supabase db reset

You should now see the employees table, along with your seed data in the Dashboard! All of your database changes are captured in code, and you can reset to a known state at any time, complete with seed data.

Diffing changes

This workflow is great if you know SQL and are comfortable creating tables and columns. If not, you can still use the Dashboard to create tables and columns, and then use the CLI to diff your changes and create migrations.

1

Create your table from the Dashboard

Create a new table called cities, with columns id, name and population.

Then generate a schema diff.

Terminal

_10
supabase db diff -f create_cities_table

2

Add schema diff as a migration

A new migration file is created for you.

Alternately, you can copy the table definitions directly from the Table Editor.

supabase/migrations/<timestamp>_create_cities_table.sql

_10
create table "public"."cities" (
_10
"id" bigint primary key generated always as identity,
_10
"name" text,
_10
"population" bigint
_10
);

3

Test your migration

Test your new migration file by resetting your local database.

Terminal

_10
supabase db reset

The last step is deploying these changes to a live Supabase project.

Deploy your project

You've been developing your project locally, making changes to your tables via migrations. It's time to deploy your project to the Supabase Platform and start scaling up to millions of users!

Head over to Supabase and create a new project to deploy to.

1

Log in to the Supabase CLI

Login to the Supabase CLI using an auto-generated Personal Access Token.

Terminal

_10
supabase login

2

Link your project

Link to your remote project by selecting from the on-screen prompt.

Terminal

_10
supabase link

3

Deploy database changes

Push your migrations to the remote database.

Terminal

_10
supabase db push

Visiting your live project on Supabase, you'll see a new employees table, complete with the department column you added in the second migration above.