Docs
Sign in
On this page

Tables and SQL

Your project is a full Postgres database. Create tables in the Studio Table Editor or with SQL in the SQL Editor.

#Create a table with SQL

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

alter table public.profiles enable row level security;

#API access for new tables

Tables you create with SQL in public are reachable through the REST API right away, and they start with Row Level Security off (the Postgres default). Until you run alter table … enable row level security, anyone with your anon key can read and change their rows. Studio flags such tables with a red RLS off badge; see Row Level Security.

To keep a table out of the API, revoke access from the API roles:

SQL
revoke all on public.internal_notes from anon, authenticated, service_role;

#Table Editor

  • New table starts with an id column and has three switches: Enable Row Level Security (on), Data API access (on) and Enable Realtime (off). Turning RLS off shows a warning before the table is created.
  • Column presets for an identity id, a uuid id and a user_id that references auth.users; types include your own enums.
  • Browse rows in pages of up to 500, filter (eq, neq, gt, gte, lt, lte, like, ilike, in, is), sort, and edit cells like a spreadsheet. Editing rows needs a primary key.
  • Delete up to 500 selected rows at once and export up to 10,000 rows as CSV.
  • Tables in public can be edited; the auth, storage, extensions and realtime schemas are read-only in the editor.

#SQL Editor

  • Run a query, only the selected text, or a read-only run (inside a READ ONLY transaction). Shortcut: Ctrl/⌘ + Enter.
  • Queries run as your project's owner role in a transaction, with a timeout of up to 60 seconds. Results show up to 1,000 rows (or 5 MB) per statement and can be exported as CSV or copied as JSON. A query can be up to 200 KB.
  • Scripts with begin/commit, vacuum or create index concurrently run as-is outside the wrapping transaction.
  • Schema changes and large updates are checked first: a dialog warns when a statement would lock a busy table, and offers a safer version. See Safe schema changes.
  • Save queries as snippets, start from templates, and find the last 100 queries in History (which you can clear).
  • Schema changes are picked up by the REST API immediately.
  • The AI assistant can generate, explain and fix SQL from the editor.

#Database pages in Studio

Database lists tables, views, functions, triggers (enable, disable, drop), indexes (create with btree, gin, gist, hash or brin; drop), extensions, publications (which tables send realtime changes) and policies.

#Schemas

Tables in the public schema are exposed through the REST API. User accounts live in the auth schema and files in the storage schema. See Schemas and roles.

#Functions

Postgres functions in public can be called through the API with rpc(). See Call functions from the API.

SQL
create function public.count_open_todos() returns bigint
language sql stable as $$
  select count(*) from public.todos where not is_complete
$$;
Was this helpful?