Schema management

Each project’s tables live in its own schema in a managed Postgres database. Design them on the console’s Schema page, or in SQL files that you apply with the CLI.

The Schema page

On the Schema page you can create tables and columns, and set types, defaults, unique constraints, foreign keys and indexes. Each change you save is recorded as a migration. The Table Editor shows and edits the rows.

SQL files

Keep your table definitions in SQL files and apply them with vaif db push. The Schema Engine reads each file’s table changes and applies them, then runs the file’s data statements. See Migrations.

A file may create tables and add columns, single-column primary keys, unique constraints, foreign keys (with ON DELETE actions) and plain indexes. It may not drop or change existing columns, or create views, SQL functions, triggers, custom types or composite primary or foreign keys; such a statement is refused with its number and the reason. A CHECK constraint, and an index that is partial, on an expression, or of a method other than the default, is skipped with a warning.

The SQL editor

The console’s SQL Editor, and POST /schema-engine/query/{projectId}, run SQL as your project’s own database login. That login can read and write rows, but it cannot create tables: CREATE TABLE there fails with 42501 (permission denied). Use the Schema page or a SQL file for table changes.

Statements may touch only your project’s own schema. Some statements are refused, such as SET ROLE, SET search_path and CREATE EXTENSION; see Error codes. Extensions are turned on separately: see Extensions.

Column types

Use types the Schema Engine can apply:

Type	Use for
uuid	IDs (DEFAULT gen_random_uuid())
text	Strings
integer, bigint	Whole numbers
numeric	Exact decimals, such as money
float, double	Approximate decimals
boolean	True or false
timestamptz	Dates and times
date	Calendar dates
jsonb	JSON documents
text[], integer[]	Lists of strings or whole numbers

Who can read and write

Limit which rows each user can read and write with row rules. Row rules are enforced by the data API and by realtime; they are not Postgres policies, so do not write CREATE POLICY in your SQL.

Relationships

-- One-to-many: a post has many comments
ALTER TABLE comments
  ADD CONSTRAINT fk_comments_post
  FOREIGN KEY (post_id) REFERENCES posts(id)
  ON DELETE CASCADE;

-- Many-to-many through a join table
CREATE TABLE post_tags (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  post_id uuid REFERENCES posts(id) ON DELETE CASCADE,
  tag_id uuid REFERENCES tags(id) ON DELETE CASCADE,
  UNIQUE (post_id, tag_id)
);

Types for your code

vaif generate writes TypeScript types for your tables. See Installation.