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.
Create your first migration file
To get started, generate a new migration to store the SQL needed to create our employees
table.
1supabase migration new create_employees_table
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.
123456create table if not exists employees ( id bigint primary key generated always as identity, name text not null, email text, created_at timestamptz default now());
Apply your first migration
Run this migration to create the employees
table.
Now you can visit your new employees
table in the local Dashboard.
1supabase migration up
Modify your employees table
Next, modify your employees
table by adding a column for department
.
1supabase migration new add_department_column
Add a new column to your table
To that new migration file, add the SQL to create a new department
column.
12alter table if exists public.employeesadd department text default 'Hooli';
Apply your second migration
Run this migration to update your existing employees
table.
1supabase migration up
Finally, you should see the department
column added to your employees
table in the local Dashboard.
View the complete code for this example on GitHub.
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.
Populate your table
Create a seed script in supabase/seed.sql.
To that file, add the SQL to insert data into your employees
table.
123456insert into public.employees (name)values ('Erlich Bachman'), ('Richard Hendricks'), ('Monica Hall');
Reset your database
Reset your database to reapply migrations and populate with seed data.
1supabase 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.
Create your table from the Dashboard
Create a new table called cities
, with columns id
, name
and population
.
Then generate a schema diff.
1supabase db diff -f create_cities_table
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.
12345create table "public"."cities" ( "id" bigint primary key generated always as identity, "name" text, "population" bigint);
Test your migration
Test your new migration file by resetting your local database.
1supabase 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.
Log in to the Supabase CLI
Login to the Supabase CLI using an auto-generated Personal Access Token.
1supabase login
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.