Skip to content
Tutorials Center
My learning

Topics

Lesson 12 of 31 ยท Supabase Postgres Database

Supabase and Postgres Fundamentals

Learn what a Supabase project really is: a Postgres database. Create tables, primary and foreign keys, use the SQL editor, and keep schema as code with CLI migrations.

Supabase is built around one big idea: your backend is an ordinary PostgreSQL database, and everything else (the auto-generated API, authentication, storage, realtime) is wired around it. When you create a project, you get a real Postgres instance. You can connect with any Postgres tool, export it with standard dumps, and take your data elsewhere if you outgrow the platform.

That is the opposite of Firestore's model from the previous chapter. Instead of documents shaped around screens, you have tables, rows, types and relationships, and you can ask any question with SQL. If you already know SQL from the SQL tutorial, you can design a Supabase backend today. This lesson gives you the Supabase-specific bits: where SQL runs, which types you use in Postgres, and how to keep your schema in version control.

The real Supabase needs an account (or a local Docker stack), so dashboard steps and CLI commands here are read-only. The runnable examples use SQLite in the browser as a stand-in. SQLite is not Postgres, so each example notes where the real syntax differs.

What a Supabase project contains

One project, one Postgres

Postgres Your tables, rows and SQL.
Auto API Tables become a REST API.
Auth Users live in the auth schema.
Storage File metadata is rows too.

Everything is data in the same database.

1 / 2

The tables you create usually live in the public schema. A schema is a named folder of tables inside a database. Supabase manages other schemas for you, such as auth (users, sessions), storage (buckets, objects) and realtime. You can read those with SQL and reference them with foreign keys, but you should not edit them by hand.

Creating a table

Open the SQL editor in the dashboard and run ordinary Postgres:

SQL
create table public.profiles (
  id uuid primary key references auth.users (id) on delete cascade,
  username text not null unique,
  created_at timestamptz not null default now()
);

create table public.todos (
  id bigint generated always as identity primary key,
  user_id uuid not null references public.profiles (id) on delete cascade,
  title text not null check (length(title) > 0),
  done boolean not null default false,
  due_date date,
  created_at timestamptz not null default now()
);

A few Postgres types appear in almost every Supabase schema:

Type Use it for
uuid Identifiers, and the ID of every auth user
text Any string (there is no need for varchar(255) in Postgres)
bigint / integer Whole numbers; generated always as identity auto-increments
boolean True or false
timestamptz A point in time, stored in UTC; use it instead of plain timestamp
date A calendar day
numeric(10,2) Exact decimals such as money
jsonb Flexible JSON you can index and query

The SQL data types lesson goes deeper. The key habit: choose types deliberately, because the database will enforce them.

Keys and relationships

Every table needs a primary key: a column (or columns) that uniquely identifies each row. In Supabase you typically choose one of two styles:

  • bigint generated always as identity, a counter that the database fills in for you.
  • uuid with default gen_random_uuid(), a random identifier that is safe to expose in URLs and generate on the client.

A foreign key says a column must match a row in another table. In the example, todos.user_id must exist in profiles.id, and on delete cascade removes a user's todos when the profile is removed. The special case in Supabase is profiles.id, which points at auth.users (id). That is how your own data connects to the user accounts Supabase Auth creates. See constraints and foreign keys for the full rules.

Foreign keys matter more here than in Firestore. Firestore references can dangle forever. Postgres rejects the bad row immediately.

Try it: keys and constraints in a stand-in

SQLite lets you try the same ideas. Differences from Postgres: SQLite needs PRAGMA foreign_keys = ON, uses INTEGER PRIMARY KEY instead of identity columns, and stores dates as text. The last statement fails on purpose, because user 99 does not exist.

SQL Primary and foreign keys

Press Run (or Ctrl + Enter) to see the result here.

The join lists three rows. Deleting Ada cascades, so only Linus's todo remains. The final insert stops the run with FOREIGN KEY constraint failed, the same kind of protection Postgres gives you (its message names the constraint).

Schema as code: migrations

Clicking in the dashboard is fine for learning. For real projects, put the schema in version control with migrations: numbered SQL files applied in order. A migration is just SQL, and the CLI tracks which ones each database has already run.

The Supabase CLI workflow, all of it read-only here:

Terminal
# once per repository
supabase init

# start the full stack locally in Docker (Postgres, Auth, Storage, Studio)
supabase start

# create an empty migration file
supabase migration new create_todos

That creates supabase/migrations/<timestamp>_create_todos.sql. You write the SQL in it:

SQL
create table public.todos (
  id bigint generated always as identity primary key,
  title text not null,
  done boolean not null default false
);

Then you apply and ship it:

Terminal
# rebuild the local database from all migrations
supabase db reset

# link to your hosted project and push new migrations
supabase link --project-ref your-project-ref
supabase db push

If you made changes in the local Studio instead of writing SQL, supabase db diff -f add_due_date compares your local database with the migrations and writes the difference into a new file. To bring a remote project's existing schema into files, use supabase db pull.

Rules of thumb:

  • Never edit a migration that has already been applied elsewhere. Add a new one that changes things further.
  • Name migrations by what they do (add_due_date_to_todos).
  • Keep seed data (sample rows for development) in supabase/seed.sql; it runs on supabase db reset.
  • Test migrations against the local stack before pushing to production.

The migrations lesson in the SQL tutorial covers safe schema changes, such as adding a column with a default without locking a big table.

Try it: a mini migration runner

The stand-in below applies numbered migrations in order and records which ones have run, just like the CLI tracks them in a table. Running it twice applies nothing new, which is the whole point.

SQL Applying migrations once, in order

Press Run (or Ctrl + Enter) to see the result here.

The todo inserted after migration 2 has done = 0, and the later migration added due_date as empty (NULL) to it. The last statement fails on the primary key, which models "this migration was already applied". Real tools check the table first and skip those versions.

Where Postgres goes beyond this lesson

Supabase exposes the whole of Postgres, so everything in the SQL tutorial applies: joins, indexes, views and transactions. Chapter 5 adds Row Level Security, which is how you decide who can read which rows when the browser talks to the database directly. For comparing Postgres with other databases, see the PostgreSQL lesson.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is a Supabase project at its core?

    Auth, the API, storage and realtime are layered on a standard Postgres database that you can query with SQL.
  2. 2. Which type should you normally use for timestamps in Postgres?

    timestamptz stores an exact moment (UTC internally) and avoids time zone surprises.
  3. 3. Why does profiles.id reference auth.users (id)?

    The foreign key links each profile to a real auth user, and on delete cascade can clean up when the user is deleted.
  4. 4. You already pushed a migration to production and find a typo. What should you do?

    Applied migrations are history. Changing them leaves databases out of sync, so write a new one.

Summary

  • A Supabase project is a real Postgres database; the API, auth and storage sit on top of it.
  • Your tables usually go in the public schema; auth and storage are managed by Supabase.
  • Use primary keys, foreign keys, check and not null so the database enforces your rules.
  • Prefer text, uuid, timestamptz, numeric and jsonb types.
  • Keep schema as code: supabase migration new, db reset, db push, and never rewrite applied migrations.

Next: Querying with supabase-js.