The Supabase CLI provides the tools you need to manage multiple environments. This guide shows you how to set up your local Supabase development environment that integrates with GitHub Actions to automatically test and release schema changes to staging and production Supabase projects.
To get started:
- Install the Supabase CLI
- Create a Supabase project or use an existing one
- Initialize a local Git repository
Set up a local environment#
The first step is to set up your local repository with the Supabase CLI:
You should see a new
supabase directory. Then you need to link your local repository with your Supabase project:
supabase login supabase link --project-ref $PROJECT_ID
You can get your
$PROJECT_ID from your project's dashboard URL:
If you're using an existing Supabase project, you might have made schema changes through the Dashboard. Run the following command to pull these changes before making local schema changes from the CLI:
1supabase db remote commit
This command creates a new migration in
supabase/migrations/<timestamp>_remote_commit.sql which reflects the schema changes you have made previously.
Now commit your local changes to Git and run the local development setup:
git add . git commit -m "init supabase" supabase start
You are now ready to develop schema changes locally and create your first migration.
Create a new migration#
There are two ways to make schema changes:
- Manual migration: Write DDL statements manually into a migration file
- Auto schema diff: Make changes through Studio UI and auto generate a schema diff
Create a new migration script by running:
supabase migration new new_employee
You should see a new file created:
supabase/migrations/<timestamp>_new_employee.sql. You can then write SQL statements in this script using a text editor:
1create table public.employees ( 2 id integer primary key generated always as identity, 3 name text 4);
Apply the new migration to your local database:
supabase db reset
This command recreates your local database from scratch and applies all migration scripts under
supabase/migrations directory. Now your local database is up to date.
The new migration command also supports stdin as input. This allows you to pipe in an existing script from another file or stdout:
supabase migration new new_employee < create_employees_table.sql
Auto schema diff#
Unlike manual migrations, auto schema diff creates a new migration script from changes already applied to your local database.
employees table under the
public schema using Studio UI, accessible at localhost:54323 by default.
Next, generate a schema diff by running the following command:
supabase db diff -f new_employee
You should see that a new file
supabase/migrations/<timestamp>_new_employee.sql is created. Open the file and verify that the generated DDL statements are the same as below.
1-- This script was generated by the Schema Diff utility in pgAdmin 4 2-- For the circular dependencies, the order in which Schema Diff writes the objects is not very sophisticated 3-- and may require manual changes to the script to ensure changes are applied in the correct order. 4-- Please report an issue for any failure with the reproduction steps. 5 6CREATE TABLE IF NOT EXISTS public.employees 7( 8 id integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ), 9 name text COLLATE pg_catalog."default", 10 CONSTRAINT employees_pkey PRIMARY KEY (id) 11) 12 13TABLESPACE pg_default; 14 15ALTER TABLE IF EXISTS public.employees 16 OWNER to postgres; 17 18GRANT ALL ON TABLE public.employees TO anon; 19 20GRANT ALL ON TABLE public.employees TO authenticated; 21 22GRANT ALL ON TABLE public.employees TO postgres; 23 24GRANT ALL ON TABLE public.employees TO service_role;
You may notice that the auto-generated migration script is more verbose than the manually written one. This is because the default schema diff tool does not account for default privileges added by the initial schema.
Commit the new migration script to git and you are ready to deploy.
Alternatively, you may pass in the
--use-migra experimental flag to generate a more concise migration using migra.
-f file flag, the output is written to stdout by default.
supabase db diff --use-migra
Deploy a migration#
In a production environment, we recommend using a CI/CD pipeline to deploy new migrations with GitHub Actions rather than deploying from your local machine.
This example uses two Supabase projects, one for production and one for staging.
Prepare your environments by:
- Creating separate Supabase projects for staging and production
- Pushing your git repository to GitHub and enabling GitHub Actions
You need a new project for staging. A project which has already been modified to reflect the production project's schema can't be used because the CLI would reapply these changes.
Configure GitHub Actions#
The Supabase CLI requires a few environment variables to run in non-interactive mode.
SUPABASE_ACCESS_TOKENis your personal access token
SUPABASE_DB_PASSWORDis your project specific database password
We recommend adding these as encrypted secrets to your GitHub Actions runners.
Create the following files inside the
1name: CI 2 3on: 4 pull_request: 5 workflow_dispatch: 6 7jobs: 8 test: 9 runs-on: ubuntu-22.04 10 steps: 11 - uses: actions/checkout@v3 12 13 - uses: supabase/setup-cli@v1 14 with: 15 version: 1.0.0 16 17 - name: Start Supabase local development setup 18 run: supabase start 19 20 - name: Verify generated types are up-to-date 21 run: | 22 supabase gen types typescript --local > types.ts 23 if [ "$(git diff --ignore-space-at-eol types.ts | wc -l)" -gt "0" ]; then 24 echo "Detected uncommitted changes after build. See status below:" 25 git diff 26 exit 1 27 fi
The full example code is available in the demo repository.
Commit these files to git and push to your
main branch on GitHub. Update these environment variables to match your Supabase projects:
When configured correctly, your repository will have CI and Release workflows that trigger on new commits pushed to
Open a PR with new migration#
Follow the migration steps to create a
Checkout a new branch
develop , commit the migration file, and push to GitHub.
git checkout -b feat/employee git add supabase/migrations/<timestamp>_new_employee.sql git commit -m "Add employee table" git push --set-upstream origin feat/employee
Open a PR from
feat/employee to the
develop branch to see that the CI workflow has been triggered.
Once the test error is resolved, merge this PR and watch the deployment in action.
Release to production#
After verifying your staging project has successfully migrated, create another PR from
main and merge it to deploy the migration to the production project.
release job applies all new migration scripts merged in
supabase/migrations directory to a linked Supabase project. You can control which project the job links to via
PROJECT_ID environment variable.
Sync production project to staging#
When setting up a new staging project, you might need to sync the initial schema with migrations previously applied to the production project.
One way is to leverage the Release workflow:
- Create a new branch
mainas the branch source
- Push the
developbranch to GitHub
The GitHub Actions runner will deploy your existing migrations to the staging project.
Alternatively, you can also apply migrations through your local CLI to a linked remote database.
1supabase db push
Once pushed, check that the migration version is up to date for both local and remote databases.
1supabase migration list
Permission denied on db remote commit#
If you have been using Supabase hosted projects for a long time, you might encounter the following permission error when executing
db remote commit.
Error: Error running pg_dump on remote database: pg_dump: error: query failed: ERROR: permission denied for table _type pg_dump: error: query was: LOCK TABLE "graphql"."_type" IN ACCESS SHARE MODE
To resolve this error, you need to grant
postgres role permissions to
graphql schema. You can do that by running the following query from Supabase dashboard's SQL Editor.
1grant all on all tables in schema graphql to postgres, anon, authenticated, service_role; 2grant all on all functions in schema graphql to postgres, anon, authenticated, service_role; 3grant all on all sequences in schema graphql to postgres, anon, authenticated, service_role;
Permission denied on db push#
If you created a table through Supabase dashboard, and your new migration script contains
ALTER TABLE statements, you might run into permission error when applying them on staging or production databases.
ERROR: must be owner of table employees (SQLSTATE 42501); while executing migration <timestamp>
This is because tables created through Supabase dashboard are owned by
supabase_admin role while the migration scripts executed through CLI are under
One way to solve this is to reassign the owner of those tables to
postgres role. For example, if your table is named
users in the public schema, you can run the following command to reassign owner.
1ALTER TABLE users OWNER TO postgres;
Rebasing new migrations#
Sometimes your teammate may merge a new migration file to git main branch, and now you need to rebase your local schema changes on top.
We can handle this scenario gracefully by renaming your old migration file with a new timestamp.
git pull supabase migration new dev_A # Assume the new file is: supabase/migrations/<t+2>_dev_A.sql mv <time>_dev_A.sql <t+2>_dev_A.sql supabase db reset
reset fails, you can manually resolve conflicts by editing
Once validated locally, commit your changes to Git and push to GitHub.