Skip to content

Supabase CLI Database Setup

  • Run this command to create a migration file for the todos table:
supabase migration new create_todo_table

This will create a new file in supabase/migrations/ with a timestamp and the name like
20250925125133_create_todo_table.sql.

  • Put this SQL migration inside 20250925125133_create_todo_table.sql file to create a todos table:
CREATE TABLE todos
(
    id          bigint generated by default as identity primary key,
    title       text                                                          not null,
    is_complete boolean                  default false,
    inserted_at timestamp with time zone default timezone('utc'::text, now()) not null
);

Apply Migrations

There are multiple ways to apply migrations under supabase/migrations/:

  • Apply all unapplied migrations in order (local or remote):
supabase migration up
  • Push migrations to the local database (from the migrations folder):
supabase db push --local
  • Reset the local database (drop everything and re-apply all migrations + optional seed):
supabase db reset

migration up and db push --local only apply missing migrations,
while db reset destroys and rebuilds the whole database.
These commands apply migrations only to your local database created via supabase start.

Viewing Your Database with SQL Tools

You can inspect your local or cloud Supabase database using any SQL client, like DBeaver, TablePlus, or DataGrip.

Local Database (Docker)

After supabase start, check your status:

supabase status

You will see something like:

  • Database URL: postgresql://postgres:postgres@127.0.0.1:54322/postgres

Connection Details for SQL Client:

Field Value
Host 127.0.0.1
Port 54322
Database postgres
User postgres
Password postgres
SSL disable

JDBC URL Example:

jdbc:postgresql://127.0.0.1:54322/postgres?user=postgres&password=postgres&ssl=false

You can now open your SQL client, connect using these credentials, and see tables, rows, policies, etc.

Cloud Database (Production)

From your Supabase Dashboard → Settings → Database → Connection Pooling, copy the connection string:

  • Format:
postgresql://<db_user>:<db_pass>@<host>:<port>/<database>

Example:

postgresql://postgres:secretpassword@db.abcdefg.supabase.co:5432/postgres

Connection Details for SQL Client:

Field Value
Host db.abcdefg.supabase.co
Port 5432
Database postgres
User postgres
Password your-secret-password
SSL require
  • Use SSL = true for cloud connections.
  • You can now visualize tables, rows, and run queries safely.

💡 Tips:

  • For local development, most clients can import the Database URL directly.
  • For cloud, always use SSL and your anon / service_role credentials carefully.
  • You can combine this with migration and seed files to populate the local DB exactly like production.

Seed Data

You can change seed sql path on config.toml file like this:

sql_paths = ['./seeds/*.sql']

This may be efficient to keep multiple seed file for each different data applying. For example, 000_insert_todos.sql, 001_update_todos.sql, etc.

In supabase/seeds/000_insert_todos.sql file, add some initial data:

INSERT INTO todos (title, is_complete)
VALUES ('Buy groceries', false),
       ('Finish project report', false),
       ('Call Alice', true);

Run manually:

psql "postgresql://postgres:postgres@127.0.0.1:54322/postgres" -f supabase/seed.sql

Or you can use reset command to drop all tables and re-apply all migrations and seeds:

supabase db reset