Your first schema

View Markdown

This page walks through building a small schema on the acme/production database from Connect. It assumes you have an API key with read, write, and alter_table scopes.

Throughout, remember the key dialect rule: PizzaSQL uses SQLite-compatible SQL, not PostgreSQL. Use INTEGER PRIMARY KEY instead of SERIAL, and TEXT instead of VARCHAR(n). Full details live in SQL reference and Compatibility.

The model

We’ll track users and their invoices:

users — one row per customer.
invoices — one row per invoice, referencing a user.

Create the tables

CREATE TABLE users (
  id         INTEGER PRIMARY KEY,
  email      TEXT NOT NULL,
  name       TEXT NOT NULL,
  plan       TEXT NOT NULL DEFAULT 'free',
  created_at TEXT NOT NULL
);

CREATE TABLE invoices (
  id          INTEGER PRIMARY KEY,
  user_id     INTEGER NOT NULL,
  amount_cents INTEGER NOT NULL,
  status      TEXT NOT NULL DEFAULT 'open',
  issued_at   TEXT NOT NULL
);

CREATE INDEX idx_invoices_user ON invoices(user_id);

A few things to notice:

INTEGER PRIMARY KEY gives each row an auto-assigned integer id when you omit it.
Dynamic defaults are evaluated when the table is created, not for each row. Supply timestamps in the insert with datetime('now') instead.
The index on invoices(user_id) keeps the per-user join fast. See Indexes.
PizzaSQL does not currently enforce UNIQUE or foreign keys. Check those invariants in your application. See Constraints.

Run these over HTTP, one statement at a time, or paste them all into a single /execute batch. Over the PostgreSQL wire protocol you can send the whole block directly:

PGPASSWORD='pz_live_REPLACE_ME' psql \
  "postgresql://u@db.database.pizza:5432/acme%2Fproduction?sslmode=disable" \
  -f schema.sql

Insert rows

Values are passed with ? placeholders (SQLite style) or $1, $2 (PostgreSQL style). Both are accepted; use whichever your driver prefers.

INSERT INTO users (email, name, plan, created_at) VALUES
  ('ada@acme.example',  'Ada Lovelace', 'pro',  datetime('now')),
  ('grace@acme.example', 'Grace Hopper', 'pro',  datetime('now')),
  ('alan@acme.example',  'Alan Turing',  'free', datetime('now'));

INSERT INTO invoices (user_id, amount_cents, status, issued_at) VALUES
  (1, 12000, 'paid', datetime('now')),
  (1, 4500,  'open', datetime('now')),
  (2, 9900,  'open', datetime('now'));

Query it

SELECT
  u.name,
  COUNT(i.id)                 AS invoice_count,
  COALESCE(SUM(i.amount_cents), 0) AS total_cents
FROM users u
LEFT JOIN invoices i ON i.user_id = u.id
GROUP BY u.id
ORDER BY total_cents DESC;

      name       | invoice_count | total_cents
-----------------+---------------+-------------
 Ada Lovelace    |             2 |       16500
 Grace Hopper    |             1 |        9900
 Alan Turing     |             0 |           0

Joins, aggregation, GROUP BY, ORDER BY, and COALESCE are all part of the dialect — see Statements, Expressions & operators, and Functions.

Evolve the schema

Add a column to track a per-user region:

ALTER TABLE users ADD COLUMN region TEXT NOT NULL DEFAULT 'us';

PizzaSQL supports ADD COLUMN, DROP COLUMN, and RENAME COLUMN on ALTER TABLE. Schema-changing statements require the alter_table scope (or drop_table for DROP TABLE).

Verify it all

List your tables and inspect a table’s shape over HTTP:

curl -s https://db.database.pizza/acme/production/schema/tables \
  -H "Authorization: Bearer pz_live_REPLACE_ME"

curl -s https://db.database.pizza/acme/production/schema/tables/users \
  -H "Authorization: Bearer pz_live_REPLACE_ME"

The dashboard’s Schema tab shows the same information visually.

Next steps

PostgreSQL clients and JavaScript / Python — run this schema from your app.
REST API — expose the users table as CRUD endpoints without SQL.
Import & export — bring in an existing SQLite file.