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()
);

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