Tables, Schema, and Migrations

The idea in one sentence

Because Supabase is just Postgres, a table is created with ordinary SQL — through the Studio’s Table Editor GUI or a plain create table statement — and migrations are what turn that schema change into a reviewable, repeatable file in version control.

Creating a table with ordinary SQL

There is no special Supabase abstraction for defining a table. Whether you click through the Studio’s Table Editor or type SQL directly in the SQL Editor, the result is a plain Postgres table. A very common pattern is a profiles table whose id references Supabase’s built-in auth.users table (the Auth module covers auth.users in depth):

create table public.profiles (
  id uuid primary key references auth.users (id),
  username text,
  created_at timestamptz default now()
);

auth.users is the table Supabase’s Auth service manages for you. Linking profiles.id to it with a foreign key is the standard way to attach your own application data — a username, an avatar, preferences — to an authenticated user.

Migrations via the CLI

A schema change made through the Studio GUI is real and takes effect immediately, but on its own it is not captured anywhere as code — nobody reviewed it, and it will not automatically show up in another environment. Migrations solve that:

# Create a new, timestamped SQL migration file under supabase/migrations/
supabase migration new create_profiles_table

# Apply all pending local migrations to the linked hosted project
supabase db push

# Generate a migration from schema changes made through the Studio GUI
supabase db diff --schema public

supabase migration new <name> scaffolds an empty, timestamped .sql file — you write the create table (or alter table) statements into it yourself, or generate them with supabase db diff after making the change in the Studio GUI. supabase db push then applies every migration that has not yet been run against the linked hosted project.

-- supabase/migrations/20250101000000_create_profiles_table.sql
create table public.profiles (
  id uuid primary key references auth.users (id),
  username text,
  created_at timestamptz default now()
);

Two real migration workflows, proven against a live stack

There are two genuinely different ways a migration file gets written, and both were run for real here, against the same local Postgres this course’s stack uses, from a disposable scratch project (never against the course’s own supabase/migrations/ — this proof lives in /private/tmp/.../supabase-fnd/, a throwaway supabase init project pointed at the shared database with --db-url, cleaned up afterward).

Workflow 1 — write the migration by hand. supabase migration new only ever scaffolds an empty, timestamped file; you write the SQL:

$ supabase migration new fnd_create_widgets
{"path":".../supabase/migrations/20260929070840_fnd_create_widgets.sql","message":"Migration created"}

-- already applied above via db push; shown here for reference, not re-collected by the harness
create table public.fnd_widgets (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  created_at timestamptz not null default now()
);

$ supabase db push --db-url "postgresql://postgres:[email protected]:55322/postgres"
Connecting to remote database...
{"_tag":"Error","error":{"code":"LegacyDbConnectError","message":"failed to connect to postgres: failed to connect to `host=127.0.0.1 user=postgres database=postgres`: tls error (The server does not support SSL connections)"}}

$ supabase db push --db-url "postgresql://postgres:[email protected]:55322/postgres?sslmode=disable" --yes
Applying migration 20260929070840_fnd_create_widgets.sql...
{"upToDate":false,"dryRun":false,"migrations":["20260929070840_fnd_create_widgets.sql"],"message":"Finished supabase db push."}

fnd_widgets now exists for real on the shared database — confirmed with psql’s \d.

Workflow 2 — capture a change someone already made by hand (a Studio GUI edit, or in this proof, a plain create table run directly against the database, simulating exactly that):

-- run directly against Postgres, simulating a Studio GUI edit — already applied, shown for reference
create table public.fnd_courses (
  id uuid primary key default gen_random_uuid(),
  title text not null,
  published boolean not null default false
);

$ supabase db diff --db-url "postgresql://postgres:[email protected]:55322/postgres?sslmode=disable" -f fnd_capture_courses
Creating shadow database...
Diffing schemas...
{"file":".../supabase/migrations/20260929070903_fnd_capture_courses.sql", "message":"Diff complete."}

-- excerpt of the generated migration file (not the full diff output) — reference only
CREATE TABLE "public"."fnd_courses" (
  "id"        uuid    NOT NULL DEFAULT gen_random_uuid(),
  "title"     text    NOT NULL,
  "published" boolean NOT NULL DEFAULT false,
  CONSTRAINT "fnd_courses_pkey" PRIMARY KEY (id)
);

Finally, supabase migration list shows both migrations and their real state — the hand-written one applied (remote populated), the diff-captured one not yet pushed (remote still empty, since it was generated from state that already existed rather than pushed forward):

$ supabase migration list --db-url "postgresql://postgres:[email protected]:55322/postgres?sslmode=disable"
{"migrations":[
  {"local":"20260929070840","remote":"20260929070840","time":"2026-09-29 07:08:40"},
  {"local":"20260929070903","remote":"","time":"2026-09-29 07:09:03"}
]}

`supabase db lint` — checking the schema before a review, not after

db lint runs Postgres’s own plpgsql_check-style analysis against a schema and reports typing errors:

$ supabase db lint --db-url "postgresql://postgres:[email protected]:55322/postgres?sslmode=disable" --schema public --level warning
Linting schema: public
No schema errors found
{"results":[],"message":"db lint"}

A clean local stack today; --fail-on warning (or error) turns this into a real CI gate instead of an informational report — the exact pattern the production checklist later in this course leans on.

`supabase test db` — pgTAP, run for real

supabase test db runs pgTAP test files against the local database. A minimal real test, checking the shape of the table created above:

-- supabase/tests/database/fnd_widgets.test.sql
begin;
select plan(2);

select has_table('public', 'fnd_widgets', 'fnd_widgets table should exist');
select has_column('public', 'fnd_widgets', 'name', 'fnd_widgets should have a name column');

select * from finish();
rollback;

$ supabase test db --workdir tools/probe
Connecting to local database...
.../supabase/tests/database/fnd_widgets.test.sql .. ok
All tests successful.
Files=1, Tests=2,  0 wallclock secs
Result: PASS

The CLI pulls a small, purpose-built supabase/pg_prove image the first time this runs (one-off, exits when done — it does not add a persistent container to the stack), enables the pgtap extension automatically, and wraps the whole file in begin/rollback so test data never actually lands in the database, the same rollback-by-default discipline this course’s own verification harness uses.

`supabase gen types` — schema to TypeScript, for real

$ supabase gen types typescript --db-url "postgresql://postgres:[email protected]:55322/postgres?sslmode=disable" --schema public

// database.types.ts (excerpt, generated — not hand-written)
fnd_widgets: {
  Row: {
    created_at: string
    id: string
    name: string
  }
  Insert: {
    created_at?: string
    id?: string
    name: string
  }
  Update: {
    created_at?: string
    id?: string
    name?: string
  }
  Relationships: []
}

Every column from the hand-written migration above shows up correctly typed, with Insert/Update variants marking the columns that have defaults as optional (created_at, id) versus required (name). This file is meant to be regenerated after every schema change and imported as Database into createClient<Database>(...) — the Client Development module covers what happens when that regeneration step gets skipped after a real schema change.

Seeding and branching — same CLI, mostly docs from here

supabase/seed.sql runs automatically after supabase db reset (and optionally on db push --include-seed) — a fixed set of rows for local development and CI, not something migrations should carry. fnd_widgets above was created in a disposable scratch project rather than this course’s own supabase/migrations/, so this lesson recreates it here (same shape as the hand-written migration earlier) to stand alone:

-- supabase/seed.sql
create table if not exists public.fnd_widgets (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  created_at timestamptz not null default now()
);
insert into public.fnd_widgets (name) values ('seeded widget one'), ('seeded widget two');

Branching was already covered as a CLI-level feature (supabase branches create) in the Branching and the Migrations Workflow lesson later in this course — the short version here: a branch is a real, isolated hosted preview database tied to a Git branch, not something the local CLI stack can simulate on its own (it needs a linked hosted project), so that lesson is docs-and-citation-based rather than proven against this local stack.

Why this matters: it is IaC applied to your schema

This is the same “infrastructure as code vs. ClickOps” idea from infrastructure tooling, applied to your database. A change made by clicking around in the Studio GUI works, but it is invisible to code review, invisible to git log, and has to be manually reproduced to reach a second environment. A migration file makes the same change reviewable, repeatable, and deployable — supabase db push can apply it to staging, to production, or to a teammate’s local stack identically.

flowchart LR
  gui["Studio GUI schema edit"] --> pg[("Postgres schema")]
  diff["supabase db diff"] --> file["Migration SQL file (supabase/migrations/)"]
  gui -.-> diff
  file --> push["supabase db push"]
  push --> hosted[("Hosted project Postgres")]
  local["Local migration written by hand"] --> file

GUI edits and migration files both flow into the same Postgres schema

Reading AI-generated code

An AI assistant, asked to “add a migration for the new widgets table,” produced this:

🐛 Spot the bug

# An AI assistant's suggested "quick" migration workflow
psql "$DATABASE_URL" -c "create table public.widgets (id uuid primary key default gen_random_uuid(), name text not null);"
echo "Done — table created."

Show the answer

This “works,” in the sense that the table gets created — and that is exactly the problem. The change now exists only as a line in shell history and whatever the AI assistant’s chat transcript happened to keep: nothing in supabase/migrations/ records it, supabase migration list will never show it as applied, a teammate’s supabase db push will never reproduce it, and supabase db reset (which replays every migration file from scratch) will silently produce a database without this table. This is the exact “ClickOps with extra steps” failure mode from earlier in this lesson, just typed into a terminal instead of clicked through Studio. The fix is not “run different SQL” — it is to route the change through supabase migration new, write the SQL into the generated file, and apply it with db push, so the same statement that just ran also becomes a version-controlled, replayable artifact, exactly like the two real workflows proved above.

Mental Model

A migration file is not a Supabase-specific ceremony — it is a plain SQL script with a timestamp in its filename, and the CLI’s entire value is running a set of them, in order, against a target, then remembering which ones it already ran (the remote column in migration list, proved above). Anything that changes the schema without going through that mechanism — a Studio GUI click, an ad hoc psql statement — produces a real, immediate effect but leaves no trace the CLI’s bookkeeping knows about, which is precisely why db diff exists: to reconcile “what actually happened” back into “what the migration files say happened.”

Trade-offs

Hand-writing a migration (migration new + SQL you typed) gives full control over exactly what ships — at the cost of it being on you to get the SQL right, with nothing checking it against the current live schema until db push actually runs it.
Capturing a change with db diff -f is fast and never mistypes a column name — at the cost of capturing everything that differs, proved above: on a shared database, that includes drift you never intended to touch, requiring a manual trim before it is safe to commit.
supabase test db’s pgTAP tests run inside a begin/rollback wrapper, so they never pollute real data — at the cost of not testing anything that genuinely needs a committed row to exist across statements (a trigger that only fires after a real commit, for instance) — this course’s own verification harness has the identical trade-off for its own sql fences.

Common Misconceptions

“Migrations are a Supabase feature.” They are ordinary, timestamped .sql files and a bookkeeping table (supabase_migrations.schema_migrations) — the same pattern every serious migration tool (Rails, Django, Flyway, Prisma Migrate) uses; Supabase’s CLI is a thin, Postgres-aware wrapper around it, not a new mechanism.
“db push --db-url behaves like db push --linked, just pointed somewhere else.” Disproved above: --db-url specifically treats the target as remote and defaults to requiring SSL, which a plain local Postgres does not speak — a real, undocumented-in-the-quickstart gotcha that only ?sslmode=disable fixes.
“db diff only shows the change I just made.” Proved above on a shared database: it diffs your entire migrations-derived shadow schema against the entire target, so anything else that changed the target also shows up, whether it is yours or not.
“supabase test db needs a separate test database.” It runs directly against the local stack’s own database, inside a transaction it always rolls back — no second database, no cleanup step required.