Database Migrations
How to manage schema migrations for your Supabase project.
Database schema changes are managed through "migrations". Database migrations 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.
Create your first migration file
To get started, generate a new migration to store the SQL needed to create our employees table.
_10supabase migration new create_employees_table
Add the SQL to your migration file
This creates a new migration: supabase/migrations/<timestamp> _create_employees_table.sql.
To that file, add the SQL to create this employees table
_10create table employees (_10 id bigint primary key generated always as identity,_10 name text,_10 email text,_10 created_at timestamptz default now()_10);
Apply your migration
Now that you have a migration file, you can run this migration and create the employees table.
Use the reset command here to reset the database to the current migrations
_10supabase db reset
Modify your employees table
Now you can visit your new employees table in the Dashboard.
Next, modify your employees table by adding a column for department. Create a new migration file for that.
_10supabase migration new add_department_to_employees_table
Add a new column to your table
This creates a new migration file: supabase/migrations/<timestamp> _add_department_to_employees_table.sql.
To that file, add the SQL to create a new department column
_10alter table if exists public.employees_10add department text default 'Hooli';
Add sample 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.
For this, you can create a seed script in supabase/seed.sql.
Populate your table
Insert data into your employees table with your supabase/seed.sql file.
_10insert into public.employees_10 (name)_10values_10 ('Erlich Bachman'),_10 ('Richard Hendricks'),_10 ('Monica Hall');
Reset your database
Reset your database (apply current migrations), and populate with seed data
_10supabase 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 a new table called cities, with columns id, name and population. To see the corresponding SQL for this, you can use the supabase db diff --schema public command. This will show you the SQL that will be run to create the table and columns. The output of supabase db diff will look something like this:
_10Diffing schemas: public_10Finished supabase db diff on branch main._10_10create table "public"."cities" (_10 "id" bigint primary key generated always as identity,_10 "name" text,_10 "population" bigint_10);
Alternately, you can view your table definitions directly from the Table Editor:
You can then copy this SQL into a new migration file, and run supabase db reset to apply the changes.
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
Link your project
Associate your project with your remote project using supabase link.
_10supabase link --project-ref <project-id>_10# You can get <project-id> from your project's dashboard URL: https://supabase.com/dashboard/project/<project-id>_10_10supabase db pull_10# Capture any changes that you have made to your remote database before you went through the steps above_10# If you have not made any changes to the remote database, skip this step
supabase/migrations is now populated with a migration in <timestamp>_remote_schema.sql.
This migration captures any changes required for your local database to match the schema of your remote Supabase project.
Review the generated migration file and once happy, apply the changes to your local instance:
_10# To apply the new migration to your local database:_10supabase migration up_10_10# To reset your local database completely:_10supabase db reset
There are a few commands required to link your project. We are in the process of consolidating these commands into a single command. Bear with us!
Deploy database changes
Deploy any local database migrations using db push:
_10supabase 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.