Supabase CLI Tour

If you are new to the Supabase CLI, or migration tools in general, it may not be easy to grasp why it's so useful. Well, what better way to introduce it than a guided tour? Now, without further ado...

Prerequisites

This tour assumes you have the following installed in your environment:

• Git
• Node.js (>=14)
• Docker (make sure the daemon is up and running)
• A Postgres client (can be psql, SQL IDEs, etc.)
• Supabase CLI (follow the instructions here to install)

You also need a new Supabase project. Take note of the DB connection string in Settings > Database > Connection string. Once you have it set up, clone this repository and cd to examples/tour.

1. Initialize the project

The first thing we need to do is to initialize the Supabase CLI on the current project:

supabase init

This will create a supabase directory which is managed by the CLI. Then we need to link the current project with the remote database. Use the connection string from your Supabase project here.

supabase db remote set 'postgresql://postgres:<your_password>@db.<your_project_ref>.supabase.co:5432/postgres'

After that, run:

supabase db remote commit

Why do we need to do this? Because you may have modified your remote database's schema (e.g. using the Table Editor) after the project is set up. In that case we want to record these changes as applied migrations (i.e. they are not run again on the remote database). This is useful if you want to start using the CLI after playing a bit with the Supabase project.

You'll notice that supabase/migrations is now populated with 2 migrations: ..._init.sql and ..._remote_commit.sql. ..._init.sql is automatically generated by the CLI, and ..._remote_commit.sql performs the change needed so that your migrations reflect the schema of your Supabase project.

Now we can start the local development server by running:

supabase start

Keep supabase start running while we do the following steps. If this is your first time running the command, this might take a while. Take note of the API URL and anon key and replace the placeholders in index.js with them.

With that, we can do... absolutely nothing, because we haven't set up your schema yet!

2. Changing schema

To change the schema for the local database, simply run some SQL against the DB URL shown by supabase start. For the tour, we'll run these changes using a Postgres client of your choice:

CREATE TABLE boarders(
	id int4 PRIMARY KEY,
	name text
);

Now we have the boarders table in the local database, but how do we incorporate this into migrations? If you've used other migration tools, you might be used to writing the migrations manually, but here we just need to run:

supabase db commit add_boarders

This will create a new migration named <timestamp>_add_boarders.sql that represents any changes we've done to the local database since supabase start.

It won't be interesting without sample data in the table, but instead of inserting it directly, we use the seed script in supabase/seed.sql. This way we don't have to run it manually every time we run supabase start.

-- in supabase/seed.sql
INSERT INTO boarders(id, name) VALUES (1, 'Arthur Dent'), (2, 'Ford Prefect');

Now run the following to rerun the migration scripts and the seed script:

supabase db reset

Then run the following:

npm install
npm run start

You should now see the contents of boarders. Hooray!

3. Resetting schema changes

What if we ran some nasty SQL on the local database and want to wipe it all clean?

-- run on local database
ALTER TABLE boarders ADD occupation text DEFAULT 'Hitchhiker';

For that we run:

supabase db reset

And the local database will be reset.

4. Deploying migrations

Finally, we need to deploy all these local changes to the remote database, i.e. the Supabase project DB. Once you're happy with your schema changes, run:

supabase db push

Now you can check the table editor, and you'll see the changes are now live!

---

That concludes the tour... for now. This will get updated as we add new features to the CLI.