# Using these docs with AI Canonical URL: https://docs.database.pizza/ai/ These docs are built to be machine-readable as well as human-readable. If you're using an AI assistant, a coding agent, or a CLI tool that fetches context, this page explains how to give it accurate database.pizza knowledge. ## The files ### `/llms.txt` A compact index of the entire documentation set, intended as a first stop for LLM tools. It contains: - A one-line description of the docs. - A list of every page as `[Title](https://docs.database.pizza/raw/.md): description`, so a tool can see the shape of the site and fetch only the pages it needs. - An "Important context" section summarizing the three facts that matter most to any generated SQL. Give this to a tool when it needs an overview, then let it fetch specific pages by their `raw` URLs. ### `/llms-full.txt` The full documentation corpus in a single file: every page's Markdown body, prefixed by its title and canonical URL, separated by `---`. Use it when a tool should hold the whole reference at once (for example, a long-context model doing schema or SQL generation). ### `/raw/…` Every page is served as raw Markdown at a stable URL mirroring its path: ```text /raw/index.md /raw/getting-started/quickstart.md /raw/clients/http-api.md /raw/ai.md ``` These are the same sources behind the rendered pages, with frontmatter intact. Point a tool at a `raw` URL when you want exactly one page, uncluttered by navigation. ## The copy-page action Each page has a **Copy page** button in its title area (next to a **View Markdown** link). Clicking it fetches the page's raw Markdown and copies it to your clipboard, ready to paste into a prompt or a tool's context window. It's the fastest way to hand a single page to an assistant without hunting for the `raw` URL. ## Prompt hygiene The docs use a fictional organization `acme`, database `production`, and the placeholder key `pz_live_REPLACE_ME`. When you prompt an AI tool: - **Never paste a real API key** into a prompt, source file, or log. Keys are secrets; substitute the placeholder and swap in the real value only in your actual runtime. - **Give the tool the context it lacks.** An assistant doesn't know your org/db slugs, your schema, or your scopes. Provide them explicitly. - **Point at the right page.** For SQL, cite the `raw` URL of the relevant reference page rather than paraphrasing from memory. - **Ask for parameterized SQL.** Request `?` or `$1` placeholders instead of string-concatenated values. ## The PizzaSQL vs. PostgreSQL distinction The single most important thing to tell a code-generating tool is this: **PizzaSQL is not PostgreSQL.** It speaks a PostgreSQL-compatible *wire protocol*, but its SQL is SQLite-compatible. Key consequences: - Use SQLite-style types and DDL: `INTEGER PRIMARY KEY`, `TEXT`, not `SERIAL` or `JSONB`. - No schemas, roles, `ARRAY`, `ENUM`, or Postgres-only functions. - Type handling follows SQLite affinity rules. The canonical reference is the [Compatibility](/sql-reference/compatibility/) page. Include this line in your prompts before asking for SQL: ```text PizzaSQL uses SQLite-style type affinity and exposes a PostgreSQL-compatible wire interface. It is not PostgreSQL itself. Prefer the compatibility page (https://docs.database.pizza/sql-reference/compatibility/) before generating production SQL. ``` ## Example prompts **Generate a schema:** ```text Using https://docs.database.pizza/raw/getting-started/first-schema.md and https://docs.database.pizza/raw/sql-reference/compatibility.md as context, write a SQLite-compatible schema for my org/db `acme/production` with tables `users` and `invoices`. Use INTEGER PRIMARY KEY, TEXT, and `?` placeholders. ``` **Explain a concept:** ```text Read https://docs.database.pizza/raw/engine/transactions.md and summarize how transactions behave over the PostgreSQL protocol versus the HTTP API. ``` ## Related - [Compatibility](/sql-reference/compatibility/) — the dialect boundaries to keep in view. - [Quickstart](/getting-started/quickstart/) — the values (`acme`, `production`, `pz_live_REPLACE_ME`) used throughout. --- # API keys & permissions Canonical URL: https://docs.database.pizza/clients/api-keys/ Every programmatic connection to database.pizza authenticates with an API key. The same key serves three roles: - The `Authorization: Bearer` token for the [HTTP query API](/clients/http-api/) and [REST API](/clients/rest-api/). - The **password** for the [PostgreSQL protocol](/clients/postgresql/). Treat it accordingly — it's a password and a bearer token in one. ## Key format Live keys look like this: ```text pz_live_… ``` The `pz_` prefix and the environment segment (`live`) are followed by a long random value. The dashboard shows only a short prefix of an existing key after creation; the full key is displayed **exactly once**, at creation time. Copy it immediately — there's no way to retrieve it later. ## Permission types Keys fall into three permission types: | Type | Purpose | Bound to a database? | | --- | --- | --- | | `DB_ACCESS` | Full SQL access over the query API and Postgres protocol, constrained by scopes | Yes (required) | | `REST_API` | CRUD via the [REST API](/clients/rest-api/), constrained by scopes and per-table method settings | Yes (required) | | `PLATFORM` | Reserved for platform-level operations | No | For day-to-day application access you'll use `DB_ACCESS`. The dedicated `REST_API` configuration flow is still in preview. ## Scopes Scopes constrain what a key can do. A key with no granted scope is denied everything; grant only what a workload needs. ### `DB_ACCESS` scopes | Scope | Allows | | --- | --- | | `read` | `SELECT` | | `write` | `INSERT`, `UPDATE`, `DELETE` | | `alter_table` | `CREATE TABLE`, `ALTER TABLE` | | `drop_table` | `DROP TABLE` | Statements are validated against these scopes on every request, over both HTTP and the Postgres protocol. A `DROP TABLE` requires the explicit `drop_table` scope; schema changes require `alter_table`. ### `REST_API` scopes | Scope | Allows | | --- | --- | | `read` | `GET` on enabled tables | | `write` | `POST`, `PATCH`, `DELETE` on enabled tables | `REST_API` keys are additionally limited by each table's method toggles — even with the `write` scope, a table won't accept `DELETE` unless `DELETE` is enabled for it. `DB_ACCESS` keys bypass the per-table toggles. ## Creating and revoking keys 1. Open a database in the dashboard and go to **API keys**. 2. Choose **Create API key**, name it, and select the permission type and scopes. 3. Copy the key from the confirmation dialog — this is the only time it's shown. Revoking a key takes effect immediately; any application using it loses access on the next request. Revoke keys you no longer need, and rotate them on any suspicion of exposure. ## Security guidance - **Never commit keys.** Put them in environment variables or a secret manager, and reference them from there (e.g. `process.env.PZ_API_KEY`). - **Never ship keys to the browser.** Route browser requests through your own backend, which holds the key server-side. A live key embedded in client code is effectively public. - **Grant the least privilege that works.** Use a `read`-only key for dashboards and reporting, a `write` key for ingestion, and reserve `alter_table`/`drop_table` for migrations you run deliberately. - **Use separate keys per workload.** Distinct keys let you revoke one consumer without disrupting another, and make audit logs more meaningful. - **Keep the key out of logs.** If you build a wrapper, redact the `Authorization` header in error output. - **Use narrow REST access when the preview controls are available.** Per-table method toggles can expose a smaller surface than arbitrary SQL, but a key shipped to a browser is still public. - **Parameterize your queries.** Scopes protect against *which* statements run; placeholders (`?` / `$1`) protect against SQL injection in the values. Do both. ## Key details at a glance - Keys are stored hashed; only the prefix is visible after creation. - `DB_ACCESS` and `REST_API` keys are bound to a database. Reserved platform keys are organization-level. - A key used for the Postgres protocol is sent as a cleartext password to the proxy, and the proxy currently declines TLS — factor that into how you handle the connection (see [Connect](/getting-started/connect/)). ## Next - [Connect](/getting-started/connect/) — put a key to work. - [REST API](/clients/rest-api/) — what `REST_API` keys can reach. - [Limits & quotas](/guides/limits/) — what counts against your plan. --- # HTTP query API Canonical URL: https://docs.database.pizza/clients/http-api/ The HTTP query API lets you run SQL over HTTPS with an API key — no driver, no connection pooling, no long-lived socket. It's ideal for serverless functions and edge workers. Do not call it with a secret key embedded in public browser code; route those requests through a backend you control. > **Managed vs. engine.** This page documents the managed endpoint at `db.database.pizza`. PizzaSQL, the engine, exposes its own raw HTTP API internally (`POST /query` with an `X-Database` header, no auth); that is not for direct customer use. The managed API adds `/` routing, API key authentication, and scope enforcement on top. ## Authentication Every request requires a `DB_ACCESS` API key in the `Authorization` header: ```text Authorization: Bearer pz_live_REPLACE_ME ``` The key must belong to the organization in the path and have access to the database. Statements are then checked against the key's scopes. See [API keys & permissions](/clients/api-keys/). ## Execute a query `POST /{org}/{db}/query` ```bash curl -s https://db.database.pizza/acme/production/query \ -H "Authorization: Bearer pz_live_REPLACE_ME" \ -H "Content-Type: application/json" \ -d '{"sql": "SELECT * FROM users WHERE plan = ?", "params": ["pro"]}' ``` Request body: | Field | Type | Description | | --- | --- | --- | | `sql` | string | The SQL statement (required). | | `params` | array | Positional parameters for `?` placeholders (optional). PostgreSQL-style `$1` placeholders apply only to the wire protocol. | Response: ```json { "columns": [ { "name": "id", "type": "INTEGER" }, { "name": "name", "type": "TEXT" } ], "rows": [ [1, "Ada Lovelace"], [2, "Grace Hopper"] ], "rowsReturned": 2, "executionTimeMicro": 108, "bytesRead": 38 } ``` - `columns` is an array of `{name, type}` objects. - `rows` is an array of arrays, values JSON-encoded. - `rowsAffected` reports affected rows for writes. Zero-valued fields are omitted from the JSON response. - `lastInsertId` exists in the response schema but is currently not populated. If you need a generated ID, prefer assigning it in your application. - `executionTimeMicro` is engine execution time in microseconds; `bytesRead` is the approximate result size. The same endpoint runs `SELECT`, `INSERT`, `UPDATE`, `DELETE`, and DDL. One statement per request; you cannot `BEGIN`/`COMMIT` here (see below). ## Batch execution `POST /{org}/{db}/execute` Run several statements in one request. Each is validated against your scopes individually, and statements run in order. ```bash curl -s https://db.database.pizza/acme/production/execute \ -H "Authorization: Bearer pz_live_REPLACE_ME" \ -H "Content-Type: application/json" \ -d '{ "statements": [ { "sql": "INSERT INTO users (name) VALUES (?)", "params": ["Ada"] }, { "sql": "INSERT INTO users (name) VALUES (?)", "params": ["Grace"] } ] }' ``` The body accepts `statements` (an array of `{sql, params}`). A `transaction` flag exists but is **not supported** — the endpoint rejects it, and it should be left unset or `false`. ```json { "results": [ { "rowsAffected": 1, "executionTimeMicro": 42 }, { "rowsAffected": 1, "executionTimeMicro": 37 } ] } ``` Each item is a full query-response object. Depending on the statement, it can also include `columns`, `rows`, `rowsReturned`, and `bytesRead`; zero-valued fields are omitted. ## Introspection `GET /{org}/{db}/schema/tables` ```bash curl -s https://db.database.pizza/acme/production/schema/tables \ -H "Authorization: Bearer pz_live_REPLACE_ME" ``` ```json { "tables": ["users", "invoices"] } ``` `GET /{org}/{db}/schema/tables/{table}` returns column metadata for one table. Both require the `read` scope. ## Health check `GET /{org}/{db}/health` returns `{"status":"ok"}` and does **not** require authentication. ## Transactions are not supported The HTTP API is stateless. Any request containing `BEGIN`, `COMMIT`, `ROLLBACK`, `SAVEPOINT`, or `RELEASE` is rejected with `501 Not Implemented`. If you need multi-statement atomicity, use the [PostgreSQL protocol](/clients/postgresql/) instead. ## Errors Errors come back as JSON with an `error` string and a non-2xx status: ```json { "error": "permission denied: operation INSERT requires scope 'write' which is not granted to this API key" } ``` Common cases: | Status | Meaning | | --- | --- | | `400` | Malformed JSON body | | `401` | Missing or invalid API key | | `403` | Key lacks the required scope, or doesn't have access to this database | | `404` | Unknown organization or database slug | | `500` | SQL syntax, analysis, or execution error returned by the engine | | `501` | Transaction statement | See [Errors & troubleshooting](/guides/errors/) for the full table and fixes. ## Related - [JavaScript](/clients/javascript/) and [Python](/clients/python/) — idiomatic HTTP examples. - [REST API](/clients/rest-api/) — CRUD endpoints that don't require writing SQL. --- # JavaScript Canonical URL: https://docs.database.pizza/clients/javascript/ You have two ways to reach your database from JavaScript: the **PostgreSQL wire protocol** via the `pg` driver, or the **HTTP query API** via `fetch`. Choose based on where your code runs and whether you need transactions. Set your key in the environment before starting: ```bash export PZ_API_KEY='pz_live_REPLACE_ME' ``` ## With `pg` (Node.js) The `pg` driver (node-postgres) gives you a real connection pool and transaction support. The database name includes the `/`, and TLS is disabled. ```bash npm install pg ``` ```javascript import { Client } from 'pg'; const client = new Client({ host: 'db.database.pizza', port: 5432, user: 'u', // ignored; any value password: process.env.PZ_API_KEY, database: 'acme/production', // discrete field — no %2F encoding ssl: false, // TLS is declined by the proxy }); await client.connect(); await client.query(` CREATE TABLE IF NOT EXISTS users ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE ) `); await client.query( 'INSERT INTO users (name, email) VALUES ($1, $2)', ['Ada Lovelace', 'ada@acme.example'], ); const res = await client.query( 'SELECT * FROM users WHERE name = $1', ['Ada Lovelace'], ); console.log(res.rows); // [{ id: 1, name: 'Ada Lovelace', email: 'ada@acme.example' }] await client.end(); ``` Use `$1, $2` placeholders with `pg`; never interpolate values into the SQL string. ### Connection pool For a long-running server, use a `Pool` instead of a single `Client`: ```javascript import { Pool } from 'pg'; const pool = new Pool({ host: 'db.database.pizza', port: 5432, user: 'u', password: process.env.PZ_API_KEY, database: 'acme/production', ssl: false, max: 10, }); const { rows } = await pool.query('SELECT COUNT(*) AS n FROM users'); ``` ## With `fetch` (any runtime) The HTTP API needs no driver, so it works in the browser, in edge functions, and in serverless runtimes. It does not support transactions. ```javascript const KEY = process.env.PZ_API_KEY; // or import.meta.env for Vite/browser async function query(sql, params = []) { const res = await fetch( 'https://db.database.pizza/acme/production/query', { method: 'POST', headers: { 'Authorization': `Bearer ${KEY}`, 'Content-Type': 'application/json', }, body: JSON.stringify({ sql, params }), }, ); if (!res.ok) { const { error } = await res.json(); throw new Error(`query failed (${res.status}): ${error}`); } return res.json(); } const result = await query( 'SELECT * FROM invoices WHERE status = ?', ['open'], ); console.log(result.columns); // [{ name: 'id', type: 'INTEGER' }, …] console.log(result.rows); // [[1, 1, 4500, 'open', '…'], …] ``` Use `?` placeholders with the HTTP `params` array. `$1` placeholders are supported by PostgreSQL drivers, not by the HTTP parameter substitution path. ## A browser caveat It is technically possible to call the HTTP API from a browser, but that means shipping your API key to end users. **Don't embed a live key in client-side code.** Route browser requests through your own backend, which holds the key server-side. Use a tightly scoped key (for example `read`-only, or a `REST_API` key with per-table limits) as a defense in depth. See [API keys & permissions](/clients/api-keys/). ## ORMs Because the wire protocol is PostgreSQL-compatible, ORMs that speak Postgres generally work — **as long as** you keep the SQL to the SQLite-compatible subset and set the connection as shown above. Drizzle and Prisma's Postgres providers can connect with the URI from [Connect](/getting-started/connect/); be prepared to adjust migrations away from Postgres-specific types (`SERIAL`, `TEXT[]`, `JSONB`). Check [Compatibility](/sql-reference/compatibility/) before generating schema. ## Next - [Python](/clients/python/) — the same two surfaces in Python. - [REST API](/clients/rest-api/) — CRUD without SQL. - [HTTP query API](/clients/http-api/) — full endpoint reference. --- # PostgreSQL clients Canonical URL: https://docs.database.pizza/clients/postgresql/ Because PizzaSQL speaks the PostgreSQL wire protocol, nearly any PostgreSQL client works out of the box. The only adjustments are the ones described in [Connect](/getting-started/connect/): - Host `db.database.pizza`, port `5432`. - Database name `acme/production` (slash included). - Password is your API key; the user field is ignored. - `sslmode=disable` — TLS is currently declined by the proxy. ## Connection URI ```text postgresql://u:pz_live_REPLACE_ME@db.database.pizza:5432/acme%2Fproduction?sslmode=disable ``` Use this everywhere a client accepts a URI. The `/` in the database name is percent-encoded as `%2F`. ## Command-line tools ```bash # Interactive PGPASSWORD='pz_live_REPLACE_ME' psql \ "postgresql://u@db.database.pizza:5432/acme%2Fproduction?sslmode=disable" # Single command PGPASSWORD='pz_live_REPLACE_ME' psql \ "postgresql://u@db.database.pizza:5432/acme%2Fproduction?sslmode=disable" \ -c "SELECT * FROM users LIMIT 5;" # Run a script PGPASSWORD='pz_live_REPLACE_ME' psql \ "postgresql://u@db.database.pizza:5432/acme%2Fproduction?sslmode=disable" \ -f schema.sql ``` `pgcli` works the same way: ```bash pgcli "postgresql://u:pz_live_REPLACE_ME@db.database.pizza:5432/acme%2Fproduction?sslmode=disable" ``` ## GUIs DBeaver, DataGrip, TablePlus, and pgAdmin all connect using the connection type **PostgreSQL**: - **Host**: `db.database.pizza` - **Port**: `5432` - **Database**: `acme/production` - **User**: `u` (any value) - **Password**: your API key - **SSL**: off / disable Most GUIs use discrete fields, so enter `acme/production` literally — no `%2F` encoding needed. ## Drivers ### Node.js — `pg` ```javascript import { Client } from 'pg'; const client = new Client({ host: 'db.database.pizza', port: 5432, user: 'u', password: process.env.PZ_API_KEY, database: 'acme/production', // discrete field, no encoding ssl: false, }); await client.connect(); const res = await client.query('SELECT * FROM users WHERE id = $1', [1]); console.log(res.rows); await client.end(); ``` See [JavaScript](/clients/javascript/) for a fuller walkthrough. ### Python — `psycopg` ```python import os import psycopg conn = psycopg.connect( host="db.database.pizza", port=5432, user="u", password=os.environ["PZ_API_KEY"], dbname="acme/production", sslmode="disable", ) cur = conn.cursor() cur.execute("SELECT * FROM users WHERE id = %s", (1,)) print(cur.fetchall()) conn.close() ``` See [Python](/clients/python/) for more. ### Go — `pgx` / `lib/pq` ```go import ( "github.com/jackc/pgx/v5" ) conn, err := pgx.Connect(ctx, "postgresql://u:pz_live_REPLACE_ME@db.database.pizza:5432/acme%2Fproduction?sslmode=disable") ``` ## Placeholders PizzaSQL accepts both `?` (SQLite style) and `$1`, `$2` (PostgreSQL style) placeholders. Prefer the style your driver parameterizes natively — most Postgres drivers use `$1`, `$2`. ```sql -- PostgreSQL style SELECT * FROM invoices WHERE user_id = $1 AND status = $2; -- SQLite style SELECT * FROM invoices WHERE user_id = ? AND status = ?; ``` ## Dialect notes The SQL you send is **SQLite-compatible**, not PostgreSQL. That means: - `INTEGER PRIMARY KEY`, not `SERIAL`; values are assigned when omitted. - `TEXT`, not `VARCHAR(n)` with enforced length. - No schemas, roles, `ARRAY`, `JSONB`, or `ENUM` types. - No `RETURNING`-heavy Postgres-specific features; check [Compatibility](/sql-reference/compatibility/) first. The [SQL reference](/sql-reference/overview/) is the source of truth for what's supported. ## Transactions The PostgreSQL protocol supports the full transactional surface — `BEGIN`, `COMMIT`, `ROLLBACK`, `SAVEPOINT` — unlike the HTTP API, which rejects transaction statements. Use this surface when you need multi-statement atomicity. See [Transactions](/engine/transactions/). --- # Python Canonical URL: https://docs.database.pizza/clients/python/ Python offers the same two surfaces as every other language: the **PostgreSQL wire protocol** through a driver like `psycopg`, or the **HTTP query API** through `requests` or `httpx`. Use the wire protocol when you need transactions or a connection pool; use HTTP for serverless and scripts. ```bash export PZ_API_KEY='pz_live_REPLACE_ME' ``` ## With `psycopg` ```bash pip install "psycopg[binary]" ``` ```python import os import psycopg with psycopg.connect( host="db.database.pizza", port=5432, user="u", # ignored; any value password=os.environ["PZ_API_KEY"], dbname="acme/production", # slash is part of the name sslmode="disable", # TLS is declined by the proxy ) as conn: with conn.cursor() as cur: cur.execute( """ CREATE TABLE IF NOT EXISTS users ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE ) """ ) cur.execute( "INSERT INTO users (name, email) VALUES (%s, %s)", ("Ada Lovelace", "ada@acme.example"), ) cur.execute("SELECT * FROM users WHERE name = %s", ("Ada Lovelace",)) print(cur.fetchall()) # [(1, 'Ada Lovelace', 'ada@acme.example')] ``` Use `%s` placeholders with `psycopg`; the driver substitutes them safely. ### With SQLAlchemy SQLAlchemy connects through the standard `postgresql+psycopg` dialect using the URI from [Connect](/getting-started/connect/): ```python from sqlalchemy import create_engine engine = create_engine( "postgresql+psycopg://u:pz_live_REPLACE_ME@db.database.pizza:5432/acme%2Fproduction?sslmode=disable" ) ``` Keep your models to the SQLite-compatible subset (`Integer` primary keys with autoincrement, `String`/`Text`, no `ARRAY`/`JSONB`). See [Compatibility](/sql-reference/compatibility/). ## With `requests` (HTTP) ```python import os import requests KEY = os.environ["PZ_API_KEY"] BASE = "https://db.database.pizza/acme/production" def query(sql, params=None): res = requests.post( f"{BASE}/query", headers={"Authorization": f"Bearer {KEY}"}, json={"sql": sql, "params": params or []}, ) if not res.ok: raise RuntimeError(f"query failed ({res.status_code}): {res.json().get('error')}") return res.json() result = query("SELECT * FROM invoices WHERE status = ?", ["open"]) print(result["columns"]) # [{ "name": "id", "type": "INTEGER" }, …] print(result["rows"]) # [[1, 1, 4500, "open", "…"], …] ``` Use `?` placeholders with the HTTP `params` array. `$1` placeholders are supported on the PostgreSQL wire path, not over HTTP. The HTTP API does not support transactions; use `psycopg` when you need them. ## With `httpx` (async) ```python import os import httpx async def main(): async with httpx.AsyncClient(base_url="https://db.database.pizza") as client: res = await client.post( "/acme/production/query", headers={"Authorization": f"Bearer {os.environ['PZ_API_KEY']}"}, json={"sql": "SELECT 1 + 1 AS answer"}, ) print(res.json()) ``` ## Choosing between them | | `psycopg` | `requests` | | --- | --- | --- | | Transactions | Yes | No | | Connection reuse | Yes | Stateless | | Serverless / scripts | Needs a socket | Anywhere | | Placeholder style | `%s` | `?` or `$1` | Both enforce the same API key scopes. ## Next - [JavaScript](/clients/javascript/) — the same surfaces in JS. - [HTTP query API](/clients/http-api/) — full endpoint reference. - [PostgreSQL clients](/clients/postgresql/) — more drivers and GUIs. --- # REST API (preview) Canonical URL: https://docs.database.pizza/clients/rest-api/ The REST surface exposes tables as small CRUD resources, without requiring clients to write SQL. Routes are derived from table names and live under `/api/`. > **Preview:** The endpoints are available, but the console does not yet expose the full `REST_API` key and per-table configuration flow. A `DB_ACCESS` key can use these routes today and is governed by its `read` and `write` scopes. Treat this surface as beta. ```text GET /{org}/{db}/api/{table} list rows POST /{org}/{db}/api/{table} insert a row GET /{org}/{db}/api/{table}/{id} fetch one row PATCH /{org}/{db}/api/{table}/{id} update one row DELETE /{org}/{db}/api/{table}/{id} delete one row ``` For org `acme` and database `production`, the `users` table is at `https://db.database.pizza/acme/production/api/users`. ## Enabling a table Per-table method settings can restrict `REST_API` keys to selected tables and verbs. `DB_ACCESS` keys bypass those method settings because they already grant SQL access. The console configuration flow for dedicated `REST_API` keys is not yet available. Single-row routes (`GET/PATCH/DELETE …/{id}`) look up rows by an `id` column, so a table intended for REST access should define `id INTEGER PRIMARY KEY`. ## Authentication REST requests use a Bearer API key, exactly like the [HTTP query API](/clients/http-api/): ```text Authorization: Bearer pz_live_REPLACE_ME ``` Two key types work here: - **`REST_API`** keys are purpose-built for these endpoints. They are scoped to `read` and/or `write`, bound to one database, and further restricted by each table's method settings. - **`DB_ACCESS`** keys also work and bypass the per-table method check, since they already carry full SQL scopes. See [API keys & permissions](/clients/api-keys/) for how to create each. ## List rows ```bash curl -s "https://db.database.pizza/acme/production/api/users?limit=20&offset=0" \ -H "Authorization: Bearer pz_live_REPLACE_ME" ``` ```json { "data": [ { "id": 1, "name": "Ada Lovelace", "email": "ada@acme.example" } ], "limit": 20, "offset": 0 } ``` Query parameters: | Param | Default | Notes | | --- | --- | --- | | `limit` | `100` | Max `1000` | | `offset` | `0` | — | | `{column}` | — | Any other parameter filters by equality, e.g. `?plan=pro` | Only column names that exist on the table are accepted as filters; unknown parameters are ignored. ## Fetch one row ```bash curl -s "https://db.database.pizza/acme/production/api/users/1" \ -H "Authorization: Bearer pz_live_REPLACE_ME" ``` ```json { "data": { "id": 1, "name": "Ada Lovelace", "email": "ada@acme.example" } } ``` Returns `404` when no row matches. ## Insert a row ```bash curl -s -X POST "https://db.database.pizza/acme/production/api/users" \ -H "Authorization: Bearer pz_live_REPLACE_ME" \ -H "Content-Type: application/json" \ -d '{"name": "Grace Hopper", "email": "grace@acme.example"}' ``` ```json { "last_insert_id": 0, "rows_affected": 1 } ``` Only known columns are accepted; an unknown column is rejected with `400`. Omit the `id` column and it is assigned automatically. `last_insert_id` is currently always `0`, so do not use it to discover the generated value. ## Update a row ```bash curl -s -X PATCH "https://db.database.pizza/acme/production/api/users/2" \ -H "Authorization: Bearer pz_live_REPLACE_ME" \ -H "Content-Type: application/json" \ -d '{"plan": "pro"}' ``` ```json { "rows_affected": 1 } ``` Sends a partial update — only the columns you provide are changed. ## Delete a row ```bash curl -s -X DELETE "https://db.database.pizza/acme/production/api/users/2" \ -H "Authorization: Bearer pz_live_REPLACE_ME" ``` ```json { "rows_affected": 1 } ``` ## Errors | Status | Meaning | | --- | --- | | `401` | Missing or invalid API key | | `403` | Key type doesn't permit REST access, lacks the `read`/`write` scope, or the method isn't enabled for this table | | `404` | Organization, database, table, or row not found | | `400` | Invalid table name, unknown column, or empty body | The error body is `{"error": "…"}`. Full troubleshooting is in [Errors & troubleshooting](/guides/errors/). ## REST vs. the query API Use the REST API when you want predictable, single-table CRUD with minimal attack surface (it only ever touches the tables you enable). Use the [HTTP query API](/clients/http-api/) or [PostgreSQL protocol](/clients/postgresql/) when you need joins, arbitrary SQL, or transactions. --- # Concurrency Canonical URL: https://docs.database.pizza/engine/concurrency/ PizzaSQL is thread-safe, but its concurrency model is coarse. There is no MVCC and no fine-grained row locking. Concurrency is governed by a small number of locks and caches at the per-database-instance level. ## Locking model Each database namespace has a `SchemaManager` that owns two locks: | Lock | Purpose | Scope | | --- | --- | --- | | `mu` | protects the in-memory schema/index caches and version counter | per database | | `txMu` | coordinates transactions with ordinary statements | per database | The transaction lock works as follows: - **`BEGIN`** takes the write lock (`txMu.Lock()`) and holds it until `COMMIT` or `ROLLBACK`. - **Every other statement** (a `SELECT`, `INSERT`, etc. issued outside a transaction) takes the *read* lock (`txMu.RLock()`) for the duration of that single statement. Because a transaction holds the write lock exclusively, an open transaction **blocks all other statements on that database**, including reads. Multiple non-transactional statements may run concurrently (read locks are shared), but each statement is additionally serialized against schema mutations via `mu`. The net effect: - **Cross-connection, non-transactional reads/writes** can interleave statement-by-statement; each statement is atomic with respect to the row cache. - **One open transaction** pauses everything else on the database until it finishes. - **Different databases** have independent locks — traffic on database A never blocks database B. ## Connection and executor topology The locking happens on the shared `SchemaManager`/`TableManager` for a database, not per connection: - **PostgreSQL wire server**: every accepted connection gets its own `Executor`, but they share the same `SchemaManager` and `TableManager` for the target database (via the `DatabaseManager`). The locks above are therefore shared across all connections to a database. - **HTTP server**: one `Executor` (and thus one `SchemaManager`/`TableManager`) is cached per database name and reused across HTTP requests, so concurrent HTTP requests to the same database share the same locks. The shared state is what makes the database-wide transaction lock effective across clients. ## In-memory caches and their invalidation Several caches live in memory per `TableManager`/`SchemaManager` and affect observable behaviour: - **Row cache** (`rowCache` / `rowIDMap`): a table's rows, loaded lazily on first `SELECT`. A `SELECT` reads from storage once, then serves from cache until a write invalidates the table. - **Index entry cache** (`indexCache`): value→rowids mappings, built lazily (see [Indexes](/engine/indexes/)). - **Schema cache**: table/index definitions, invalidated by DDL. Writes (`INSERT`/`UPDATE`/`DELETE`) invalidate the affected table's row cache and update any warm in-memory index entries. A table-level or index-level write is a full invalidation, not a per-row patch. Implications: - The first query against a table in a fresh connection (or after a restart) pays a full read of that table from storage. - Because caches are per `TableManager`, a schema or data change made through one executor is eventually seen by others — the executor re-syncs its analyzer catalog when the schema version changes, and row data is read from the shared storage-backed cache. ## No MVCC — what that means - There is no snapshot isolation: a long `SELECT` does not run against a stable point-in-time snapshot while other statements proceed. Instead, statements serialize against the transaction lock. - There are no row-level locks and no `SELECT ... FOR UPDATE`, `NOWAIT`, or deadlock detection. - Write conflicts are resolved at the primary-key level (duplicate-PK detection on insert), not by version checking. ## Thread safety and the KV layer Below the SQL layer, a **connection pool** to the PizzaKV key-value store fans out individual key operations. Pool connections are checked out per operation and returned afterward; a timeout or short read on a pooled connection closes it and replaces it rather than reusing a possibly-corrupt connection. This is internal detail, but it means key-value I/O is safe to issue from many goroutines (e.g. `INSERT ... SELECT` writes rows concurrently). ## Practical guidance - Avoid holding long transactions on a busy database — they block all other traffic. - For high-concurrency workloads, prefer many short statements over one big transaction. - Assume the first access after a cold start or a fresh connection is slower (cold caches); warm caches are fast. - Do not rely on read-your-writes visibility across databases — each database is a separate lock and cache domain. --- # Indexes Canonical URL: https://docs.database.pizza/engine/indexes/ PizzaSQL has secondary indexes, but they are much simpler than in a typical database. The most important fact first: **index definitions persist, but index entries live only in memory and are rebuilt on demand**. Selection is also limited to a single case: a single-column equality predicate. ## Creating and dropping indexes ```sql CREATE INDEX idx_users_email ON users(email); CREATE UNIQUE INDEX idx_users_email ON users(email); CREATE INDEX IF NOT EXISTS idx ON t(col); DROP INDEX idx_users_email; DROP INDEX IF EXISTS idx_users_email; ``` - `CREATE INDEX` verifies the table and every column exist before creating the index. - The index definition (name, table, columns, `unique` flag, `DESC` flags) is written to the key-value store, so it survives restarts and is visible to other connections. - `CREATE INDEX` also builds in-memory entries for the rows that already exist at creation time. If that build fails, the index definition is rolled back. - `DROP TABLE` drops the table's indexes automatically. - Multi-column indexes (`(a, b)`) and `DESC` ordering are accepted and stored, but they do not affect how the index is *used* (see below). ## What persists vs. what is rebuilt | Aspect | Persisted? | | --- | --- | | Index definition (name, table, columns, unique) | yes, in the KV store | | Index entries (value → rowids) | **no** — held in memory only | In practice this means: - After an engine restart, an index has no entries until a query triggers a lookup through it. At that point the engine scans the table and reconstructs the value→rowids mapping in memory (`ensureIndex`). - Writes (`INSERT`/`UPDATE`/`DELETE`) keep an already-built in-memory index up to date incrementally, so a warm index stays correct without a rebuild. - Because entries are derived from row data, a rebuild is always consistent with the table; the definition is the only durable state. ## When an index is used Index lookup is attempted for a **single-table** `SELECT` whose `WHERE` clause is, exactly, `column = literal` (or `literal = column`). If the engine finds an index whose **sole column** matches (case-insensitively), it resolves the value to a set of rowids and returns those rows directly. That is the entire optimization: - ✅ `WHERE email = 'a@b.c'` with `CREATE INDEX idx ON users(email)` — uses the index. - ✅ `WHERE email = ?` or `WHERE email = $1` — both managed transports bind parameters by rewriting them as literals before execution, so the resulting equality can use the index. - ❌ `WHERE email = lower(x)` or any non-literal right-hand side. - ❌ `WHERE age > 30`, `WHERE age BETWEEN ...`, `WHERE a = 1 AND b = 2` — range and composite conditions are not index-eligible. - ❌ Multi-column indexes are never used by the selection logic, even for a leading-column equality. - ❌ `ORDER BY` never uses an index for sorting. - ❌ `UPDATE`/`DELETE`/`JOIN` predicates do not use the index for row selection. If no index applies, the engine performs a **full table scan** (it reads every row and applies the filter). There is no cost-based planner deciding between scan and index — the decision is a single heuristic check. ## UNIQUE indexes `CREATE UNIQUE INDEX` stores the `unique` flag in the definition (it appears in `pg_indexes` introspection), but **uniqueness is not enforced**. Duplicate values are allowed. See [Constraints](/sql-reference/constraints/). ## Interaction with the row cache Table rows and index entries are cached in the running database manager and shared by executors for that database. Writes invalidate or update the relevant cache. An index lookup avoids re-reading the table once it is warm, but the first lookup after an engine restart still materializes the whole table to build the index. ## Best practices - Create single-column indexes on columns used in equality predicates against literals — that is the only predicate shape that benefits. - Do not bother with composite indexes for query acceleration; they are never used. - Remember that entries are in-memory state. The first indexed query after an engine restart pays a full scan to build the index. - Because there is no planner, adding an index never hurts correctness — but it only helps the one recognized predicate shape. --- # Transactions Canonical URL: https://docs.database.pizza/engine/transactions/ PizzaSQL supports `BEGIN`, `COMMIT`, `ROLLBACK`, `SAVEPOINT`, `RELEASE`, and `ROLLBACK TO SAVEPOINT`. The implementation is simple and worth understanding precisely, because its guarantees are weaker than PostgreSQL's. ## The model: undo log + global lock A transaction is **not** multi-version concurrency control (MVCC). It is two things: 1. A **database-wide exclusive lock** taken at `BEGIN` and held until `COMMIT` or `ROLLBACK`. While a transaction is open, all other statements on that database (including reads) block. 2. An **in-memory undo log**. Each `INSERT`, `UPDATE`, or `DELETE` inside the transaction appends an entry recording the operation, the table, the primary key, and (for `UPDATE`/`DELETE`) the full previous row. `COMMIT` simply discards the undo log and releases the lock — writes have already been applied to storage as the statements ran. `ROLLBACK` replays the undo log in reverse, restoring previous row state, then releases the lock. ## Transaction control ```sql BEGIN; -- or BEGIN TRANSACTION UPDATE accounts SET balance = balance - 100 WHERE name = 'alice'; UPDATE accounts SET balance = balance + 100 WHERE name = 'bob'; COMMIT; ``` ```sql BEGIN; INSERT INTO log VALUES (1); SAVEPOINT sp1; INSERT INTO log VALUES (2); ROLLBACK TO sp1; -- undoes the second insert RELEASE sp1; COMMIT; -- keeps the first insert ``` - `BEGIN` inside an open transaction is an error. - `COMMIT`/`ROLLBACK` with no open transaction is an error. - `SAVEPOINT` outside a transaction implicitly opens one. - `ROLLBACK TO name` undoes operations back to the savepoint and removes savepoints created after it. - `RELEASE name` removes a savepoint; releasing the outer-most savepoint has no effect on the transaction. ## Guarantees and limitations - **Rollback is session-level.** For ordinary tables with a declared primary key, `ROLLBACK` restores row changes while the engine remains running. The undo log is in memory, so a process failure during an open transaction is not crash-atomic; writes already sent to storage can survive without the in-memory log that would undo them. - **Isolation** is enforced by the exclusive lock — no other connection sees intermediate state, because they cannot run while the transaction is open. This is *stronger* than `READ COMMITTED` (readers block entirely) but means transactions are effectively serialized. - **DDL is not transactional.** `CREATE TABLE`, `DROP TABLE`, `ALTER TABLE`, `CREATE INDEX`, and `DROP INDEX` are **not** recorded in the undo log. Rolling back a transaction does **not** undo DDL executed inside it. - **Durability** of individual writes depends on the underlying key-value store's log, not on the transaction layer. This does not make a multi-statement transaction atomic across an engine crash. ## Failed transactions over the wire On the PostgreSQL protocol, the connection tracks transaction state. If a statement inside a transaction block errors: - The connection enters a "failed transaction" state and rejects further commands with SQLSTATE `25P02` until the client sends `ROLLBACK` (or `ROLLBACK TO SAVEPOINT`). - This mirrors PostgreSQL's aborted-transaction behaviour. ## Managed service restrictions - The **managed HTTP query and execute endpoints reject transaction statements** (`BEGIN`, `COMMIT`, `ROLLBACK`, `SAVEPOINT`, `RELEASE`) with `501 Not Implemented`, and the `/execute` endpoint's `transaction: true` flag is rejected too. Use the PostgreSQL wire protocol if you need transactions on the managed service. - The **managed PostgreSQL proxy** forwards transaction statements to the engine, so transactions work normally over `psql`/drivers. - The engine's own raw HTTP API and CLI do support transaction statements, but those surfaces are internal to the platform. ## Practical guidance - Keep transactions short: they hold a database-wide lock and serialize all traffic on the database. - Do not rely on rolling back DDL — migrate schema outside of transactions, or test-and-recover manually. - Prefer the PostgreSQL connection path for anything that needs multi-statement atomicity on the managed service. --- # Connect Canonical URL: https://docs.database.pizza/getting-started/connect/ Everything you connect to lives at the host `db.database.pizza`. Your organization and database slugs form the routing path: for org `acme` and database `production`, that path is `acme/production`. There are two connection surfaces, and they share the same API key. | Surface | Endpoint | Authentication | | --- | --- | --- | | PostgreSQL wire protocol | `db.database.pizza:5432` | API key as password | | HTTP query API | `https://db.database.pizza/acme/production/query` | API key as `Authorization: Bearer` | ## PostgreSQL wire protocol Connect to `db.database.pizza` on port `5432` like any PostgreSQL server. Three things are different from a stock Postgres setup: 1. **The database name is `acme/production`** — organization and database joined by a slash. It's a single name, not two path segments. 2. **The password is your API key.** The user field is ignored; you can use any value (the examples use `u`). 3. **TLS is declined.** The proxy currently answers SSL negotiation with `N`, so connect with `sslmode=disable`. Don't require or verify TLS here — it will fail. ### Connection string ```text postgresql://u:pz_live_REPLACE_ME@db.database.pizza:5432/acme%2Fproduction?sslmode=disable ``` The slash in the database name must be percent-encoded as `%2F` when a client parses the URI (libpq, `pg`, SQLAlchemy, JDBC, and most others do). If your client takes discrete fields instead of a URI — many GUIs do — enter `acme/production` literally in the database field, no encoding. ### With `psql` ```bash PGPASSWORD='pz_live_REPLACE_ME' psql \ "postgresql://u@db.database.pizza:5432/acme%2Fproduction?sslmode=disable" ``` `psql` also accepts the parts as flags: ```bash PGPASSWORD='pz_live_REPLACE_ME' psql \ -h db.database.pizza -p 5432 -U u -d acme/production ``` ## HTTP query API Send a JSON body to the query endpoint with the key in a Bearer header: ```bash curl -s https://db.database.pizza/acme/production/query \ -H "Authorization: Bearer pz_live_REPLACE_ME" \ -H "Content-Type: application/json" \ -d '{"sql": "SELECT version()"}' ``` The HTTP surface also exposes a batch endpoint, schema introspection, and a health check. See [HTTP query API](/clients/http-api/) for the full reference. ## Connection parameters | Parameter | PostgreSQL | HTTP | | --- | --- | --- | | Host | `db.database.pizza` | `https://db.database.pizza` | | Port | `5432` | `443` | | Database | `acme/production` | path segment `/acme/production` | | User | ignored (use `u`) | — | | Password / token | API key | `Authorization: Bearer ` | | SSL | `sslmode=disable` (TLS declined) | HTTPS only | ## Which should I use? - **PostgreSQL wire protocol** is the right default when you already have a Postgres driver, ORM, or GUI. It gives you transactions and the full SQL surface. - **HTTP query API** is best for serverless functions, edge workloads, and environments where a long-lived database connection isn't practical. Transactions are **not** supported over HTTP — use the Postgres protocol when you need them. Do not expose a database API key in browser code. Both surfaces enforce the same API key scopes. See [API keys & permissions](/clients/api-keys/) and [PostgreSQL clients](/clients/postgresql/) for idiomatic client examples. ## Troubleshooting - **`invalid_password` / "authentication failed"** — the key is wrong, revoked, or lacks `DB_ACCESS` permission. Check the key and its scopes. - **`invalid_catalog_name` / "database must be org/db"** — the database name doesn't contain the `/`. Use `acme/production`. - **SSL negotiation fails** — you forced TLS. Use `sslmode=disable`. - **`invalid api key`** — the key isn't a valid live key, or you pasted it with whitespace. More error cases are covered in [Errors & troubleshooting](/guides/errors/). --- # Your first schema Canonical URL: https://docs.database.pizza/getting-started/first-schema/ This page walks through building a small schema on the `acme/production` database from [Connect](/getting-started/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](/sql-reference/overview/) and [Compatibility](/sql-reference/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 ```sql 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](/engine/indexes/). - PizzaSQL does not currently enforce `UNIQUE` or foreign keys. Check those invariants in your application. See [Constraints](/sql-reference/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: ```bash 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. ```sql 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 ```sql 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; ``` ```text 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](/sql-reference/statements/), [Expressions & operators](/sql-reference/expressions/), and [Functions](/sql-reference/functions/). ## Evolve the schema Add a column to track a per-user region: ```sql 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: ```bash curl -s https://db.database.pizza/acme/production/schema/tables \ -H "Authorization: Bearer pz_live_REPLACE_ME" ``` ```bash 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](/clients/postgresql/) and [JavaScript](/clients/javascript/) / [Python](/clients/python/) — run this schema from your app. - [REST API](/clients/rest-api/) — expose the `users` table as CRUD endpoints without SQL. - [Import & export](/guides/import-export/) — bring in an existing SQLite file. --- # Quickstart Canonical URL: https://docs.database.pizza/getting-started/quickstart/ This guide gets you from a fresh account to a working query in about five minutes. It assumes you've signed up at `app.database.pizza`. We'll use a fictional organization **acme** and a database **production**. Replace both with your own names throughout. ## 1. Create an organization Organizations group your databases, members, and API keys. When you create one, its name becomes a **slug** — the URL-safe identifier used in every connection path. 1. Sign in to the dashboard. 2. Create an organization named `acme`. 3. The slug becomes `acme` (lowercased, spaces turned into `-`). Your connection path always starts with the organization slug, then the database slug: `acme/production`. ## 2. Create a database Inside the `acme` organization, create a database named `production`. - The database name also becomes a slug: `production`. - Each database has an isolated PizzaSQL namespace with its own schema and data. - Databases are isolated from one another — a key for one can't touch another unless you grant it access. ## 3. Create an API key Every programmatic connection authenticates with an API key, which doubles as your PostgreSQL password. 1. Open the `production` database and go to **API keys**. 2. Choose **Create API key** and give it a name like `local dev`. 3. Select the scopes you need. For now, enable **Read** and **Write** (and **Alter table** if you'll be creating tables from a client). 4. Copy the key immediately — it's shown only once. A live key looks like `pz_live_…`. The docs use the placeholder `pz_live_REPLACE_ME`; substitute your real key. > **Security**: Treat the key like a password. Don't commit it to source control, and prefer environment variables. See [API keys & permissions](/clients/api-keys/). ## 4. Run your first query You have two equivalent options. Pick whichever fits your workflow. ### Over HTTP ```bash curl -s https://db.database.pizza/acme/production/query \ -H "Authorization: Bearer pz_live_REPLACE_ME" \ -H "Content-Type: application/json" \ -d '{"sql": "SELECT 1 + 1 AS answer"}' ``` ```json { "columns": [{ "name": "answer", "type": "INTEGER" }], "rows": [[2]], "rowsReturned": 1, "executionTimeMicro": 108, "bytesRead": 4 } ``` The HTTP query API is documented in full at [HTTP query API](/clients/http-api/). ### Over PostgreSQL ```bash PGPASSWORD='pz_live_REPLACE_ME' psql \ "postgresql://u@db.database.pizza:5432/acme%2Fproduction?sslmode=disable" \ -c "SELECT 1 + 1 AS answer;" ``` Two details matter here: - The **database name is `acme/production`** — the slash is part of the name. In a connection URI it must be percent-encoded as `acme%2Fproduction`. - **TLS is currently declined** by the proxy, so connect with `sslmode=disable`. Full details are in [Connect](/getting-started/connect/) and [PostgreSQL clients](/clients/postgresql/). ## 5. Create a table and query it Now create a table and read it back: ```bash curl -s https://db.database.pizza/acme/production/query \ -H "Authorization: Bearer pz_live_REPLACE_ME" \ -H "Content-Type: application/json" \ -d '{"sql": "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL)"}' ``` Then insert and select: ```bash curl -s https://db.database.pizza/acme/production/query \ -H "Authorization: Bearer pz_live_REPLACE_ME" \ -H "Content-Type: application/json" \ -d '{"sql": "INSERT INTO users (name, email) VALUES (?, ?)", "params": ["Ada", "ada@acme.example"]}' ``` ```bash curl -s https://db.database.pizza/acme/production/query \ -H "Authorization: Bearer pz_live_REPLACE_ME" \ -H "Content-Type: application/json" \ -d '{"sql": "SELECT * FROM users"}' ``` Note the `?` placeholders with a `params` array — always pass values this way rather than concatenating them into the SQL string. `INTEGER PRIMARY KEY` values are assigned automatically when omitted. PizzaSQL accepts `UNIQUE`, but does not currently enforce it; enforce email uniqueness in your application. See [Constraints](/sql-reference/constraints/) before relying on schema-level validation. ## Next steps - [Connect](/getting-started/connect/) — every connection parameter, in one place. - [Your first schema](/getting-started/first-schema/) — a realistic schema with indexes. - [SQL reference](/sql-reference/overview/) — the full dialect. --- # Errors & troubleshooting Canonical URL: https://docs.database.pizza/guides/errors/ Errors arrive through each transport's native shape. This page catalogs the common cases across the HTTP query API, REST API, and PostgreSQL protocol. ## Error shape ### HTTP and REST Errors return JSON with an `error` string and a non-2xx status: ```json { "error": "permission denied: operation DROP TABLE requires scope 'drop_table' which is not granted to this API key" } ``` ### PostgreSQL protocol The proxy surfaces errors as standard PostgreSQL `ErrorResponse` frames, so your client shows them like any database error: ```text FATAL: invalid_password authentication failed ``` ## HTTP / REST status codes | Status | Meaning | Typical fix | | --- | --- | --- | | `400` | Malformed JSON request body | Check the JSON and `Content-Type` header | | `401` | Missing or invalid API key | Verify the key, its prefix, and that it isn't revoked or expired | | `403` | Key lacks a required scope, or doesn't have access to this database | Check scopes in the dashboard; grant the missing one | | `404` | Organization, database, table, or row not found | Confirm the `org/db` slugs and the table name | | `409` | Conflict — e.g. a database slug already exists | Choose a different name | | `500` | SQL syntax, analysis, or execution error from the engine | Read the `error` string and check the SQL reference | | `501` | Not implemented — a transaction statement over HTTP | Use the PostgreSQL protocol for transactions | | `503` | The database instance is unavailable or paused | Retry; check the dashboard for the instance status | ## Authentication errors | Message | Cause | | --- | --- | | `missing api key` | No `Authorization: Bearer` header | | `invalid api key` | Key doesn't verify — wrong value, revoked, expired, or not a live key | | `api key does not have database access permissions` | The key isn't a `DB_ACCESS` (or `REST_API`) key | | `api key does not belong to this organization` | Key's org doesn't match the path | | `api key does not have access to this database` | The key is scoped to a different database | ## Permission (scope) errors These arrive as `403` with a message explaining the mismatch, for example: ```text operation INSERT requires scope 'write' which is not granted to this API key ``` - `SELECT` requires `read`. - `INSERT` / `UPDATE` / `DELETE` require `write`. - `CREATE TABLE` / `ALTER TABLE` require `alter_table`. - `DROP TABLE` requires `drop_table`. For session-authenticated dashboard queries, the same operations map to member roles (`viewer` can read; `developer` and above can write and manage schema). See [API keys & permissions](/clients/api-keys/). ## PostgreSQL protocol errors | Code | Meaning | | --- | --- | | `invalid_password` | The key (sent as the password) failed validation | | `invalid_catalog_name` | The `database` parameter is missing the `/` — it must be `org/db` | | `connection_failure` | The engine for your instance is unavailable | | `insufficient_privilege` | A query exceeded the key's scopes | ## SQL errors SQL itself can fail with syntax, analysis, or runtime errors. The managed API-key endpoint currently returns these with `500` and a message that includes the engine's reason: ```text query error: near "SELEC": syntax error ``` Common causes: - **`no such table`** — the table doesn't exist yet, or you're connected to the wrong database. - **`syntax error`** — a typo, or Postgres-only syntax. PizzaSQL uses SQLite-compatible SQL; check [Compatibility](/sql-reference/compatibility/). - **`duplicate primary key`** — an insert reused an existing primary-key value. Other `UNIQUE` constraints are not enforced. - **`NOT NULL constraint failed`** — a `NULL` value in a `NOT NULL` column. ## Transactions over HTTP Requests containing `BEGIN`, `COMMIT`, `ROLLBACK`, `SAVEPOINT`, or `RELEASE` are rejected with `501`: ```json { "error": "transactions are not supported by the HTTP query endpoint" } ``` This is by design — the HTTP API is stateless. Use the [PostgreSQL protocol](/clients/postgresql/) when you need transactions. ## Connection troubleshooting - **SSL negotiation fails** — you're forcing TLS. The proxy declines SSL; connect with `sslmode=disable`. - **`database must be "org/db"`** — the database name in your connection string is missing the `/` separator. - **URI parse errors** — remember to percent-encode the database name's slash: `acme%2Fproduction`. See [Connect](/getting-started/connect/) for the exact parameters. ## If you're still stuck - Confirm the org and database slugs in the dashboard — they're the values used in every path and connection string. - Verify the key's permission type and scopes, and that it hasn't been revoked. - Check the instance status in the dashboard; a paused instance returns `503` or `connection_failure`. - Test the health endpoint, which needs no auth: `GET https://db.database.pizza/acme/production/health`. --- # Import & export Canonical URL: https://docs.database.pizza/guides/import-export/ database.pizza supports three ways to move data around: - **SQL dumps** — a SQLite-compatible SQL script with schema and data. - **CSV** — a single table's rows in comma-separated form. - **SQLite files** — native `.db` / `.sqlite` / `.sqlite3` files. Import and export happen through the dashboard (database **Settings → SQLite import and export**). There's no API-key-authenticated import/export endpoint — you either use the dashboard, or script your own using the [query API](/clients/http-api/) and [PostgreSQL protocol](/clients/postgresql/). ## Export a database From the database's **Settings** page, choose **Export SQLite**. You'll download a SQL dump that includes both schema and data, in a form you can replay into any SQLite-compatible tool. The dump is ordinary SQL. You can re-run it against a fresh database with `psql`: ```bash PGPASSWORD='pz_live_REPLACE_ME' psql \ "postgresql://u@db.database.pizza:5432/acme%2Fproduction?sslmode=disable" \ -f production.sql ``` Or replay it through the HTTP API statement by statement. ## Import a database From **Settings**, choose a `.sql`, `.sqlite`, or `.db` file and **Import**. You'll get back a summary of what happened: ```json { "tablesCreated": ["users", "invoices"], "tablesImported": ["users", "invoices"], "rowsInserted": 27754, "indexesCreated": 61 } ``` - **Tables, their rows, and regular indexes** are imported. - **Some constructs are skipped** during import: views, triggers, pragmas, expression indexes (for example `CREATE INDEX ON t(COALESCE(a, b))`), and `FOREIGN KEY` / `CHECK` constraints. Schema comes in without those; add any you need afterward. - **Integer primary-key generation is automatic** — plain `INTEGER PRIMARY KEY` assigns a value when omitted. `AUTOINCREMENT` is accepted but adds no behavior. ### Continue on errors The import dialog has a **Continue on statement errors** toggle. Leave it off to stop at the first problem; turn it on to skip individual failing rows or statements and import everything else, with skipped errors reported in the summary. ## CSV import and export CSV is always **table-scoped**. To export one table to CSV, or to load a CSV into a table, use the API from your own code or tooling. For a table `users`: ```bash # Export one table as CSV (requires a dashboard session, e.g. via the web app) curl -s "https://app.database.pizza/api/v1/databases/{id}/export-csv?table=users" -H "Cookie: session_id=…" ``` Because the CSV endpoints require a dashboard session rather than an API key, most teams find it simpler to generate CSVs themselves from a `SELECT` through the query API: ```bash curl -s https://db.database.pizza/acme/production/query \ -H "Authorization: Bearer pz_live_REPLACE_ME" \ -H "Content-Type: application/json" \ -d '{"sql": "SELECT * FROM users"}' \ | jq -r '.columns | map(.name) | @csv, (.rows[] | @csv)' ``` On import, a CSV's first row is treated as a header unless you create the table yourself first. Give the target table explicitly when loading CSV. ## Bringing in an existing SQLite database If you're migrating from SQLite, the fastest path is the dashboard import of your `.db` file. Everything in the "what gets imported" list above applies, so review the skipped constructs before you cut over. ## Scripting your own exports You don't need a dedicated export feature for simple cases. A dump is just schema plus rows. Use the query API to pull table names, then each table's `CREATE TABLE` and rows, and assemble them into a script: 1. `GET /acme/production/schema/tables` to list tables. 2. `GET /acme/production/schema/tables/{table}` for each table's columns. 3. `SELECT * FROM {table}` for the rows. This gives you full control over the output and works from any environment. ## Related - [HTTP query API](/clients/http-api/) — the endpoints used for DIY dumps. - [Errors & troubleshooting](/guides/errors/) — import/export failure modes. - [Compatibility](/sql-reference/compatibility/) — what SQL constructs survive a round-trip. --- # Limits & quotas Canonical URL: https://docs.database.pizza/guides/limits/ This page covers two kinds of limits: **resource quotas** on the service, and **SQL compatibility** limits inherited from the PizzaSQL engine. > **Beta note.** database.pizza is under active development. The quota defaults below are the current values and may change as plans evolve; treat them as indicative rather than contractual. ## Resource quotas Quotas apply per organization and cover four dimensions: | Dimension | Current default | What counts | | --- | --- | --- | | Databases | 3 | Active databases in the organization | | Storage | 100 MB | Allocated on-disk space for the organization | | Queries | 500,000 / week | Queries across all surfaces | | Connections | 100 concurrent | Open PostgreSQL connections | Queries and bytes-read are counted per organization per week; concurrent connections are measured at any instant. Usage is visible in the dashboard's **Quota** view. When you hit a quota: - Creating another database beyond the database limit is rejected. - Exceeding storage prevents further writes that would grow the data. - The query and connection quotas cap sustained load until the window or connections free up. If you need more, the limits are configurable per organization — reach out through the dashboard rather than working around them. ## API and endpoint limits - **REST list results** default to `100` rows and cap at `1000` per request (`limit` parameter). - **Import uploads** are size-limited: SQL and CSV multipart uploads cap at 32 MB; SQLite imports cap at 128 MB. - **One statement per `/query` request.** Use `/execute` to batch several, or the PostgreSQL protocol for scripts and transactions. - **No transactions over HTTP.** `BEGIN`/`COMMIT` and friends return `501`; use the PostgreSQL protocol instead. ## SQL compatibility limits PizzaSQL is **SQLite-compatible**, not PostgreSQL. The most common surprises when coming from Postgres: - **`SERIAL` and sequences** don't exist — use `INTEGER PRIMARY KEY`. Values are assigned automatically when omitted. - **`VARCHAR(n)`** is accepted but not length-enforced — use `TEXT`. - **No schemas, roles, `ARRAY`, `JSONB`, or `ENUM`** types. - **No `CREATE DATABASE`/`DROP DATABASE`** from a client — databases are managed in the dashboard (and require the platform's `create_database` scope, not a client key). - **Type affinity** is SQLite-style, so column types are hints more than strict constraints. The complete, authoritative list is on the [Compatibility](/sql-reference/compatibility/) page — read it before generating production SQL. ## What isn't imported Imports of existing SQLite databases skip several constructs: views, triggers, pragmas, expression indexes, and `FOREIGN KEY` / `CHECK` constraints. See [Import & export](/guides/import-export/). ## TLS and connectivity The PostgreSQL proxy currently declines SSL, so all wire-protocol connections use `sslmode=disable`. The HTTP surface is HTTPS-only. Plan around this for sensitive data — see the security notes in [API keys & permissions](/clients/api-keys/). ## Related - [Errors & troubleshooting](/guides/errors/) — what a quota or compatibility failure looks like. - [Compatibility](/sql-reference/compatibility/) — the SQL dialect boundaries. - [Indexes](/engine/indexes/) — keep queries fast within your quota. --- # database.pizza Canonical URL: https://docs.database.pizza/ database.pizza is a managed database service. You create an organization, create a database, and then talk to it two ways: over a **PostgreSQL-compatible wire protocol** using the client you already know, or over a simple **HTTP JSON API** from any language. Under the hood every database runs on [PizzaSQL](/internals/architecture/), a SQL engine built from scratch in Go. PizzaSQL speaks **SQLite-compatible SQL** through a PostgreSQL wire interface, so it looks like Postgres to your tools while using SQLite's forgiving type system. It is *not* PostgreSQL itself. ## Three ways in - **New here?** Follow the [Quickstart](/getting-started/quickstart/) — five minutes from empty account to your first query. - **Writing SQL?** Start with [SQL at a glance](/sql-reference/overview/) and keep the [compatibility notes](/sql-reference/compatibility/) close before generating production queries. - **Curious how it works?** The [Engine Internals](/internals/architecture/) section walks through the PizzaSQL architecture, query lifecycle, and storage model. ## What you get Every database gives you three access surfaces plus a dashboard: | Surface | Where | Best for | | --- | --- | --- | | PostgreSQL wire protocol | `db.database.pizza:5432` | Existing `psql`, `pg`, psycopg, ORM, and BI tools | | HTTP query API | `https://db.database.pizza///query` | Any language, serverless, and edge workloads | | Auto-generated REST API (preview) | `https://db.database.pizza///api/` | Single-table CRUD without writing SQL | | Dashboard | `app.database.pizza` | Schema browsing, query editor, API keys, metrics | All programmatic access is authenticated with **API keys**. Keys carry granular scopes — `read`, `write`, `alter_table`, `drop_table` — so you can issue a read-only key to a reporting job and a write key to an ingestion service. See [API keys & permissions](/clients/api-keys/). ## A taste Create a key in the dashboard, then query over HTTP: ```bash curl -s https://db.database.pizza/acme/production/query \ -H "Authorization: Bearer pz_live_REPLACE_ME" \ -H "Content-Type: application/json" \ -d '{"sql": "SELECT 1 + 1 AS answer"}' ``` Or with `psql`, using the key as the password: ```bash PGPASSWORD='pz_live_REPLACE_ME' psql \ "postgresql://u@db.database.pizza:5432/acme%2Fproduction?sslmode=disable" ``` Throughout these docs we use a fictional organization `acme` and database `production`, and the placeholder key `pz_live_REPLACE_ME`. Substitute your own values — and never paste a real key into anything you share. ## Getting started - [Quickstart](/getting-started/quickstart/) — create an org, database, and key; run your first query. - [Connect](/getting-started/connect/) — connection strings and parameters for every client. - [Your first schema](/getting-started/first-schema/) — tables, inserts, indexes, and queries. ## Use your database - [PostgreSQL clients](/clients/postgresql/) — `psql`, GUIs, and driver configuration. - [HTTP query API](/clients/http-api/) — the managed JSON endpoint. - [JavaScript](/clients/javascript/) and [Python](/clients/python/) — idiomatic examples. - [REST API](/clients/rest-api/) — auto-generated CRUD endpoints. - [API keys & permissions](/clients/api-keys/) — scopes, lifecycle, and security. ## Reference and guides - [SQL reference](/sql-reference/overview/) — types, statements, expressions, functions, and constraints. - [Import & export](/guides/import-export/) — move data in and out. - [Errors & troubleshooting](/guides/errors/) — understand failure responses. - [Limits & quotas](/guides/limits/) — what counts against your plan. ## The managed API vs. the engine It's worth drawing one distinction early. PizzaSQL, the engine, exposes its own raw HTTP API (`POST /query`, `GET /schema/tables`, and so on) and its own PostgreSQL listener. Those are **internal** to the platform. As a customer you never touch them directly. Instead, you talk to the **managed proxy** at `db.database.pizza`, which sits in front of your engine instance and adds authentication, per-organization routing (`/`), scope enforcement, quotas, and metrics. The endpoints look similar to the engine's, but they require a Bearer API key and a `/` path. The pages under [Use your database](#use-your-database) document the managed surface. ## Status database.pizza is under active development. Features, quotas, and exact response shapes may change; anything unstable is flagged inline. If something here disagrees with what you observe, trust the observed behavior and [open an issue](https://github.com/database-pizza/app/issues). --- # Architecture Canonical URL: https://docs.database.pizza/internals/architecture/ PizzaSQL is a SQL database engine written from scratch in Go. It is a single process that can expose three front-ends, all backed by the same core. ## High-level view ``` client ──► PostgreSQL wire server ─┐ client ──► HTTP/JSON server ───────┼──► core ──► PizzaKV (key-value store) client ──► CLI / REPL ─────────────┘ ``` The core is a pipeline: ``` lexer ──► parser ──► analyzer ──► executor ──► storage ``` 1. **Lexer** (`pkg/lexer`) tokenizes SQL into tokens (identifiers, numbers, strings, operators, and ~200 keywords). 2. **Parser** (`pkg/parser`) is a hand-written recursive-descent parser producing an abstract syntax tree (AST) of statements and expressions. 3. **Analyzer** (`pkg/analyzer`) performs semantic analysis: resolving table/column references against a catalog, type/affinity inference, and validating aggregate/`GROUP BY` placement. 4. **Executor** (`pkg/executor`) evaluates the AST against storage, producing a `Result` (columns + rows, or an affected-row count). 5. **Storage** (`pkg/storage`) maps SQL tables to key-value operations and speaks to PizzaKV. ## Front-ends - **PostgreSQL wire server** (`pkg/pgserver`) implements the PostgreSQL protocol v3.0 (simple and extended query) so any Postgres client can connect. - **HTTP/JSON server** (`pkg/httpserver`) exposes `POST /query`, batch `/execute`, schema introspection, import/export, health, and metrics endpoints. - **CLI / REPL** (`main.go`) provides interactive and single-statement access, plus export/import modes. All three construct an `Executor` bound to a database and route statements through the same pipeline, so behaviour is identical across access methods (modulo the transport-specific bits like the PG protocol's transaction state). ## The database manager `pkg/storage`'s `DatabaseManager` maps a database name to a `DatabaseInstance`, which pairs a `SchemaManager` (schema/index definitions) with a `TableManager` (row data and index entries). Each database is an isolated namespace of keys in PizzaKV. Databases are created on first access (`autoCreate`). On the managed database.pizza service, the *engine's* front-ends are internal; customers instead reach a proxy layer (an HTTP router and a PostgreSQL wire proxy) that authenticates API keys and routes to the right engine instance/namespace. See the home page's [managed API vs. engine](/). ## PizzaKV PizzaKV is a separate key-value store (written in Zig) that PizzaSQL talks to over a Unix domain socket or TCP. It provides four operations the storage layer relies on: - `write key value` - `read key` - `reads prefix` (all values under a key prefix) - `delete key` PizzaSQL opens a pool of connections to PizzaKV and serializes each SQL-level operation into one or more of these primitive commands. Rows and schema objects are JSON documents under namespaced keys — the layout is described in [Storage model](/internals/storage/). ## Key design choices and their consequences - **JSON rows**: every row is a JSON object, which makes the engine simple and flexible but means full scans deserialize the entire table. The dominant cost of a full scan is JSON decoding, not the key-value read. - **Hand-written parser**: no parser generator; the grammar is explicit and limited (which is why features like CTEs and window functions are simply absent). - **SQLite affinity**: dynamic typing rather than a strict type system (see [Data types](/sql-reference/data-types/)). - **No planner**: index use is a single heuristic, not a cost-based decision (see [Query lifecycle](/internals/query-lifecycle/)). For the front-ends in detail, see [PostgreSQL protocol](/internals/postgres-protocol/). For how a statement flows through the pipeline, see [Query lifecycle](/internals/query-lifecycle/). --- # Catalog & schema Canonical URL: https://docs.database.pizza/internals/catalog/ "Catalog" in PizzaSQL refers to two related but distinct things: the **durable schema** in PizzaKV, and the **in-memory analyzer catalog** used to resolve names during queries. This page explains both and how they synchronize. ## The durable schema Table and index definitions live in PizzaKV under the key scheme described in [Storage model](/internals/storage/): - A table's schema (name, columns, primary key, `NextRowID`, `AutoIncrement`) is a JSON document at `:_schema:
`. - The list of tables is a JSON array at `:_sys:tables`. - Each index's definition is at `:index:`, with the list at `:indexes`. A `Schema` contains, for each column: name, declared type (the raw string), nullability, a `Default`, and a `PrimaryKey` flag. The declared type string is preserved verbatim; affinity is computed from it at analysis time (see [Data types](/sql-reference/data-types/)). ## The in-memory catalog Each `Executor` holds an `analyzer.Catalog`: a map of table name → `TableInfo` (columns, types, primary-key flag, view flag). This is what the [Analyzer](/internals/architecture/) reads during semantic analysis to resolve tables and columns. It is not shared across executors — it's rebuilt per executor. The catalog is populated from the durable schema by `SyncCatalog()`: 1. List tables from storage. 2. For each, read the schema, drop any stale catalog entry, and create a fresh `TableInfo`. 3. Remove catalog entries for tables that no longer exist. Views are also registered in the catalog (marked `IsView`), which makes the analyzer accept `SELECT ... FROM view` even though the view body lives only in the executor's in-memory view registry. ## Synchronization Because each executor keeps its own catalog, changes made through one path must propagate. The synchronization mechanism: - The `SchemaManager` keeps a monotonically increasing **version counter**, bumped on every schema/index change. - Before analysis, the executor checks whether its cached catalog version matches the `SchemaManager`'s version. If not, it calls `SyncCatalog()`. - If analysis fails with a table/column-not-found error, the executor resyncs once and retries, to recover from schema changed through a different executor/API. This means concurrent DDL and DML from different connections eventually converge, but there can be a brief window where a fresh executor hasn't yet observed a schema change. ## What the catalog does *not* store The catalog is deliberately minimal: - It does **not** store `UNIQUE`, `CHECK`, or `FOREIGN KEY` constraints — those are discarded at `CREATE TABLE` (see [Constraints](/sql-reference/constraints/)). - It does not store index definitions (those live in the `SchemaManager`/storage, not the analyzer catalog). - It does not store statistics, histograms, or anything a cost-based planner would use — there is no planner. ## Views are connection-local `CREATE VIEW` registers the view only in the creating executor's in-memory registry and catalog. Views are **not** persisted to PizzaKV and are not visible to other connections. When a query references a view, the executor transparently rewrites the `FROM` reference into the view's stored `SELECT` AST as a derived table. `DROP VIEW` removes it from that executor only. There is no cross-connection view sharing. ## Multi-database and ATTACH The `DatabaseManager` maps database names to `DatabaseInstance`s, each with its own `SchemaManager` and `TableManager`. `ATTACH DATABASE 'name' AS alias` registers another database's namespace under an alias and adds its tables to the catalog with a `.
` prefix, so cross-database references resolve. `DETACH` removes the alias but does not drop the data. The `main` alias is always the primary database and cannot be detached. ## Schema operations `SchemaManager` methods mutate the durable schema and the cache atomically under a lock, and bump the version counter so other executors resync. `CREATE TABLE`, `ALTER TABLE` (add/drop/rename column, rename table), `RENAME COLUMN`, and index operations all funnel through here. Renaming a column updates the schema's `PrimaryKey` reference if it matched, but does **not** rewrite index definitions that referenced the old column name. --- # PostgreSQL protocol Canonical URL: https://docs.database.pizza/internals/postgres-protocol/ PizzaSQL speaks the PostgreSQL wire protocol (v3.0) so that any PostgreSQL client — `psql`, `node-postgres`, `psycopg`, JDBC, ORMs, GUI tools — can connect. This page covers the engine's protocol implementation, and then the managed database.pizza proxy that sits in front of it. ## Engine protocol support ### Startup and auth - Accepts the standard startup message and negotiates the protocol version. - **SSL is not supported**: an SSLRequest is answered with `N` (decline), after which the client should retry without SSL. - Authentication is `AuthenticationOk` (no password required) at the engine level. - The server advertises these parameter statuses: - `server_version` = `14.0 (PizzaSQL)` (so drivers requiring PG 9.x+ are satisfied) - `server_encoding`, `client_encoding` = `UTF8` - `DateStyle` = `ISO, MDY` - `TimeZone` = `UTC` ### Query protocol - **Simple query** (`Q`): a single statement or a semicolon-separated batch. The batch is parsed fully before execution. - **Extended query** (`Parse`/`Bind`/`Describe`/`Execute`/`Sync`/`Close`): prepared statements and portals are supported for the driver conversation, but there is no server-side statement cache or plan — parameters are bound by rewriting the query text client-side in the engine (`$1`, `$2`, … are substituted as literals before parsing). - `Bind` supports text-format parameters for all types and binary-format parameters for booleans (OID 16), `int2` (21), `int4` (23), and `int8` (20); other binary types are rejected. - Parameter OIDs are mapped to a small set: `0` (infer), `16` boolean, `20`/`21`/`23` integers, `26`/`700`/`701`/`1700` numerics; anything else is treated as text. - Portal execution is single-use (a portal may be executed once, then must be re-bound). ### Command completion and errors - Completion tags: `INSERT 0 n`, `UPDATE n`, `DELETE n`, `SELECT n`, `CREATE TABLE`, `ALTER TABLE`, etc. - Errors carry a severity (`ERROR`/`FATAL`), an SQLSTATE code (a subset: `42601` syntax error, `08P01` protocol violation, `0A000` feature not supported, `25P02` transaction aborted, `XX000` internal, `42P07` duplicate table, `42703` undefined column, …), and a message. - Transaction status is tracked in `ReadyForQuery`: idle (`I`), in transaction block (`T`), failed (`E`). In a failed block, only `ROLLBACK` (or `ROLLBACK TO SAVEPOINT`) is accepted; everything else returns `25P02`. ### Catalog emulation Many drivers run introspection queries on connect. PizzaSQL intercepts and emulates a small subset so tools don't fail: - `SELECT version()` → `PostgreSQL 14.0 (PizzaSQL)` - `SELECT current_user` → the connection's `user` parameter - `SHOW server_version` / `server_encoding` / `client_encoding` - `SELECT ... FROM information_schema.tables / columns / table_constraints / key_column_usage` - `SELECT ... FROM pg_tables / pg_indexes` These are generated from the PizzaSQL schema, not a real PostgreSQL catalog. Filtering (`WHERE table_name = 'x'`, `WHERE schemaname = ...`) is recognized for simple equality patterns; other clauses (joins, subqueries against catalog tables) are not supported. The catalog is a **read-only compatibility shim**, not a queryable schema. ### Type mapping on results Result columns are advertised with a small OID mapping: `INTEGER`/`INT` → `int4` (23), `TEXT`/`VARCHAR`/`CHAR` → `text` (25), `REAL`/`FLOAT` → `float4` (700), `DOUBLE` → `float8` (701), `BOOLEAN` → `bool` (16), `BLOB` → `bytea` (17), anything else → `text`. Values are sent in text format. ### Protocol hardening Message frames are size-capped (`16 MiB` for normal messages, `1 MiB` for startup) to bound memory against malformed clients. ## The managed PostgreSQL proxy On database.pizza, customers do **not** reach the engine directly. A proxy listens at `db.database.pizza:5432` and adds authentication and routing: 1. The client connects and the proxy declines SSL, then requests a **cleartext password**. 2. The password is your **API key**. The proxy verifies it, checks it belongs to the organization and (if scoped) the database. 3. The database name in the connection string must be **`org/db`** (e.g. `acme/production`). In a connection URI the slash must be percent-encoded: `acme%2Fproduction`. 4. On success, the proxy opens a TCP tunnel to the engine's PostgreSQL port with the resolved internal **namespace**, and forwards protocol frames in both directions. While forwarding, the proxy: - **Enforces API-key scopes** on every simple query and prepared statement (`read`, `write`, `alter_table`, `drop_table`). A statement the key isn't allowed to run is rejected with `insufficient_privilege`. - **Tracks queries** for metrics and quota billing by correlating client executions with backend completion frames. - **Forwards transactions** unchanged, so `BEGIN`/`COMMIT`/`ROLLBACK` work over the proxy (unlike the HTTP endpoint, which rejects them). Practical connection string (using the key as the password): ```bash PGPASSWORD='pz_live_REPLACE_ME' psql \ "postgresql://u@db.database.pizza:5432/acme%2Fproduction?sslmode=disable" ``` ## What this means for you - Use `sslmode=disable` / `sslmode=disable` equivalents — TLS is not available. - Your PostgreSQL *password* is the API key; the user is ignored. - The database name is `org/db`, not a bare name. - Only the catalog tables listed above are available to ORMs for introspection; anything deeper fails. - SQL dialect is SQLite, not PostgreSQL — the protocol is a transport, not a promise of PostgreSQL semantics (see [Compatibility](/sql-reference/compatibility/)). --- # Query lifecycle Canonical URL: https://docs.database.pizza/internals/query-lifecycle/ This page traces a single statement from bytes to result. It explains *why* certain queries are fast or slow, and where the engine's "planner" ends (early). ## 1. Lex and parse The SQL string is tokenized and parsed into an AST. Both steps are pure and fail fast with a syntax error for anything the grammar doesn't cover (CTEs, window functions, `RETURNING`, etc.). A statement that parses is guaranteed to be one the engine at least *recognizes*, even if it later turns out to be only partially implemented. Over the PostgreSQL protocol, a multi-statement batch is parsed *in full before execution begins*, so an unsupported trailing statement doesn't leave earlier writes half-committed. ## 2. Lock acquisition Before execution, the engine takes a statement lock: - Outside a transaction: a shared (`RLock`) statement lock for the duration of the statement. - `BEGIN` takes the exclusive transaction lock and holds it until commit/rollback. This is where the database-wide serialization described in [Concurrency](/engine/concurrency/) happens — before any data is touched. ## 3. Analysis (semantic) The `Analyzer` walks the AST against an in-memory **catalog** of tables and columns: - Resolves `FROM` tables (including derived tables and joins) and verifies they exist. - Resolves column references and rejects unknown or ambiguous columns. - Infers types using SQLite affinity rules. - Validates aggregate/`GROUP BY`/`HAVING` placement (e.g. aggregates are not allowed in `WHERE`, `SELECT *` is not allowed in an aggregate query without `GROUP BY`). - Checks `INSERT` value counts and type compatibility. The catalog is a per-executor cache. If schema changed through another path, the executor detects a schema-version mismatch, resyncs the catalog from storage, and retries once before returning a not-found error. ## 4. "Planning" There is **no cost-based planner**. For a `SELECT`, the executor makes at most two decisions: 1. **Constant `WHERE`**: if the `WHERE` clause references no columns, it's evaluated once; a constant-false clause short-circuits to an empty result. 2. **Index eligibility**: if the `WHERE` is exactly `column = literal` (single table, no join), and an index exists whose only column matches, the engine does an index lookup. Otherwise it does a full table scan with a row filter. That's it. There is no join-order optimization, no statistics, no range/partial-index support, and no `ORDER BY` via index. `EXPLAIN`/`EXPLAIN QUERY PLAN` output is illustrative, not derived from this decision process. ## 5. Execution The executor evaluates the statement: - **`SELECT`** loads rows (index lookup or full scan), applies the `WHERE` filter, resolves joins, applies `GROUP BY`/aggregation, `HAVING`, `ORDER BY`, `LIMIT`/`OFFSET`, and `DISTINCT`, then projects the select columns. A fast path uses running accumulators for simple `GROUP BY` aggregates; otherwise full per-group rows are collected. - **`INSERT`** evaluates expressions, assigns rowids/auto keys, checks the primary key for duplicates, applies conflict handling (`OR REPLACE`/`IGNORE`, `ON CONFLICT`), and writes rows (concurrently for `INSERT ... SELECT`). - **`UPDATE`/`DELETE`** select matching rows, evaluate `SET` expressions per row (so self-referencing updates work), and write back. - **DDL** mutates the schema/index catalog and, for tables, truncates/creates storage. If inside a transaction, `INSERT`/`UPDATE`/`DELETE` append undo-log entries as they run. ## 6. Result assembly The executor returns a `Result`: a column list, a set of typed row values, an affected-row count, and (for inserts) a last-insert id field (currently always zero — auto-generated ids are not surfaced back; see [Functions](/sql-reference/functions/)). The front-end then serializes this into its transport format: - **PostgreSQL**: `RowDescription` + `DataRow` frames, then a `CommandComplete` tag; column types are mapped to a small set of PostgreSQL OIDs. - **HTTP**: JSON with `columns` (name + inferred type) and `rows`. ## Where latency goes For full-scan queries, the dominant cost is **JSON deserialization of the whole table** into Go rows, not the key-value reads themselves. Indexed single-column equality lookups skip that and resolve value→rowids directly, which is why they are dramatically faster. Cold caches (fresh connection or restart) also pay a full table read on first access. See [Storage model](/internals/storage/) and [Indexes](/engine/indexes/). --- # Storage model Canonical URL: https://docs.database.pizza/internals/storage/ PizzaSQL stores everything in **PizzaKV**, a key-value store, as JSON documents under a namespaced key scheme. There are no B-trees, no heap files, and no page format — just keys and values. ## Namespacing Every key is prefixed by the database name, so each database is an isolated namespace within the same PizzaKV store. Table and index names are lowercased in keys, making them case-insensitive at the storage layer. ## Key layout | Key pattern | Value | | --- | --- | | `:_sys:tables` | JSON array of table names (the catalog) | | `:_schema:
` | JSON table schema (columns, primary key, `NextRowID`, `AutoIncrement`) | | `:_sys:rowid:
` | rowid counter state | | `:_data:
:` | a single row, as a JSON object | | `:indexes` | JSON array of index names | | `:index:` | JSON index definition (name, table, columns, unique) | | `:idx::` | index entry (value → rowids); **only ever cleared, never written** | The table schema is the source of truth for a table's columns and its `NextRowID` counter. Rows are keyed by their primary-key value; the rowid (`_rowid_`) is stored inside the JSON row itself. ## Rows are JSON A row is `map[string]interface{}` serialized as JSON. Consequences: - Numbers round-trip through JSON as floats, so integer columns are normalized back to `int64` after reading (see [Data types](/sql-reference/data-types/)). - `NULL` is JSON `null`. - BLOBs are strings. The storage layer reads a whole table by issuing a `reads :_data:
:` prefix scan and deserializing each value. ## The in-memory layer On top of PizzaKV, each `TableManager`/`SchemaManager` keeps caches: - **Row cache**: a table's rows, loaded on first `SELECT` and invalidated on write. - **Rowid map**: rowid → row, for index lookups. - **Index entry cache**: value → rowids for each warm index (see [Indexes](/engine/indexes/)). - **Schema cache**: table and index definitions. These caches are per database instance and are what make warm reads fast. They are derived state: after a restart they are rebuilt lazily from the durable keys. ## Rowid management The next rowid is tracked in the table schema's `NextRowID` field. On first use after startup, the engine scans the table's rows to recover `max(rowid) + 1` (so rowid state doesn't need its own write-ahead entry). `INTEGER PRIMARY KEY` aliases this rowid; other primary keys get a separate, invisible rowid counter. ## What is and isn't durable - **Durable**: table schemas, the catalog list, index definitions, and row data. - **Not durable** (derived/rebuilt): rowid counter (recovered from data), index entries, and all in-memory caches. This split is why index definitions survive a restart but their entries must be rebuilt, and why the first query after a cold start is slower than subsequent ones. ## Transactions and storage Transactions are handled at the SQL layer (an undo log), not by the key-value store. Each statement's writes go to PizzaKV immediately; `ROLLBACK` replays the undo log to restore prior values. There is no multi-key atomicity at the PizzaKV level — the undo log is what provides it for row writes (DDL is not covered; see [Transactions](/engine/transactions/)). ## The managed service's storage On database.pizza, each database is a PizzaSQL namespace inside a managed PizzaKV instance, provisioned and routed per organization. From your perspective the storage is opaque; the guarantees above still apply to your data, including the durability of schema/index definitions and row data. --- # Compatibility Canonical URL: https://docs.database.pizza/sql-reference/compatibility/ PizzaSQL is SQLite-flavoured SQL behind a PostgreSQL wire protocol. It is **not** PostgreSQL, and it is **not** a drop-in SQLite either. This page is the master gap list. Read it before porting queries. ## Positioning | Dimension | PizzaSQL | | --- | --- | | SQL dialect | SQLite-compatible | | Wire protocol | PostgreSQL v3.0 (simple + extended) | | Type system | SQLite affinity (dynamic, permissive) | | Storage | JSON rows in a key-value store | | Concurrency | Global per-database lock, undo-log transactions (no MVCC) | | Planner | None — heuristic index selection, otherwise full scan | ## Versus PostgreSQL Not implemented (rejected at parse time, not silently mishandled): - `WITH` / CTEs - Window functions (`OVER`, `PARTITION BY`, `ROW_NUMBER`, …) - `RETURNING` - Schemas and roles (`CREATE SCHEMA`, `GRANT`, `REVOKE`, roles) - `SERIAL`, `BIGSERIAL`, `UUID`, `ARRAY`, `ENUM`, and other rich types. `JSONB` is accepted as a type name but has NUMERIC affinity; it is not a native JSON type. - `TRUNCATE`, `ILIKE`, `SIMILAR TO`, `~` regex operators - Sequences (`CREATE SEQUENCE`, `nextval`) - `DISTINCT ON`, `LATERAL` - `COPY` and `\copy` bulk protocol paths Because the wire is Postgres, drivers may issue **introspection queries** (`information_schema`, `pg_catalog`, `SELECT version()`, `SHOW server_version`) on connect. PizzaSQL emulates a small subset of these so tools don't choke on startup — see [PostgreSQL protocol](/internals/postgres-protocol/) — but the underlying engine has no such catalog. ## Versus SQLite Implemented SQLite features: - `INTEGER PRIMARY KEY` rowid aliasing, `rowid`/`oid`/`_rowid_` - Type affinity rules - `INSERT OR REPLACE/IGNORE/FAIL/ABORT` - `PRAGMA table_info`, `table_list`, `database_list`, `version` - Date/time functions with SQLite-style modifiers SQLite features that are **missing**: - Triggers, `WITHOUT ROWID`, `WITH`/CTEs, window functions, `RETURNING` - `GLOB` (keyword exists but `x GLOB p` is a parse error) - `ATTACH`/`DETACH` beyond the basic alias mechanism (see below) - Full `PRAGMA` coverage (only the four above) ## Features that parse but do nothing This is the dangerous category — SQL that *looks* like it works but silently behaves differently: | Feature | What actually happens | | --- | --- | | `RIGHT JOIN`, `FULL [OUTER] JOIN` | returns an **empty result** | | `NATURAL JOIN`, `USING (cols)` | join condition ignored (becomes a cross join) | | `UNIQUE` column/table constraint | parsed, discarded, **not enforced** | | `CHECK (...)` | parsed, discarded, **never evaluated** | | `FOREIGN KEY ... REFERENCES` | parsed, discarded, **not enforced** | | `CREATE UNIQUE INDEX` | definition stored, **uniqueness not enforced** | | `AUTOINCREMENT` | parsed, stored, **no behaviour** | | `LIKE ... ESCAPE 'x'` | `ESCAPE` ignored | | `ORDER BY ... COLLATE` | no collations; `COLLATE` is not recognized | ## Functions that parse but return NULL Several names are in the engine's function catalog but not implemented. They parse cleanly and then return `NULL`: `ltrim`, `rtrim`, `ceil`, `floor`, `mod`, `iif`, `quote`, `total`, `group_concat`, `last_insert_rowid`, `changes`, `total_changes`. See [Functions](/sql-reference/functions/). ## Type and coercion differences - Columns are not strictly typed; `VARCHAR(n)` length is ignored. - `||` does not propagate `NULL` (`NULL || 'x'` → `'x'`). - Integer division truncates toward zero; division by zero yields `NULL`, not an error. - `NULL` ordering/sorting follows the engine's `compare`, which treats `NULL` as smaller than any value. ## Transaction and concurrency differences - No MVCC, no read snapshot isolation. A transaction takes a database-wide lock; other statements on that database block until commit or rollback. See [Transactions](/engine/transactions/) and [Concurrency](/engine/concurrency/). - DDL (`CREATE`/`DROP`/`ALTER`) is **not undo-logged** — rolling back a transaction does not undo DDL performed inside it. - The managed HTTP query endpoint rejects transaction statements; the managed PostgreSQL proxy forwards them. ## Managed service differences The managed proxy layers additional restrictions on top of the engine: - **API keys** with scopes (`read`, `write`, `alter_table`, `drop_table`) gate every statement; a key without the matching scope gets `403`. - **TLS is declined** by the proxy — connect with `sslmode=disable`. - The database name in a connection string is `org/db` (the slash is part of the name and must be percent-encoded in a URI). - Transaction statements are rejected on the HTTP query/execute endpoints. None of these are PizzaSQL engine limitations; they are properties of the managed routing layer described on the home page. ## Advice for porting 1. Rewrite `RIGHT`/`FULL` joins as `LEFT` joins. 2. Replace `USING`/`NATURAL` with explicit `ON` conditions. 3. Move uniqueness and referential integrity into your application layer. 4. Don't use `AUTOINCREMENT` for anything semantic — it's a no-op. 5. Keep transactions short: they hold a database-wide lock. 6. Verify any function you rely on is in the *implemented* list, not just the declared list. --- # Constraints Canonical URL: https://docs.database.pizza/sql-reference/constraints/ Constraints in PizzaSQL fall into two camps: the ones that are actually implemented, and the ones that merely parse. This page is deliberately blunt about the difference, because silently assuming a `UNIQUE` or `FOREIGN KEY` constraint is enforced will corrupt data. ## The enforced constraints ### PRIMARY KEY - A single column can be `PRIMARY KEY`, or a table-level `PRIMARY KEY (col, ...)`. - A table-level composite key uses **only the first named column** as the effective primary key; true composite keys are not supported. - An **`INTEGER PRIMARY KEY`** column becomes an alias for the implicit rowid. Inserting without a value auto-assigns the next rowid; inserting with a value sets the rowid and advances the counter past it. - A **non-integer primary key** (e.g. `TEXT PRIMARY KEY`) is a normal column with a separate hidden rowid counter. - The primary key drives **duplicate detection** on `INSERT`, and therefore `INSERT OR REPLACE`/`OR IGNORE` and `ON CONFLICT DO NOTHING`/`DO UPDATE` — all of which are keyed on the primary key only. - Without any declared primary key, the engine assigns a synthetic `_rowid_` primary key, so duplicate and NULL values are allowed in user columns. ### NOT NULL - `NOT NULL` is enforced on `INSERT`: inserting a row that omits a `NOT NULL` column with no default is rejected with `missing required column`. - It does **not** enforce on `UPDATE` to `NULL` in all paths, and does not validate type. ### DEFAULT - `DEFAULT expr` is evaluated when the table is created, then stored and applied to inserted rows that omit the column. - Because it's evaluated at DDL time, only constant expressions are useful. There is no `DEFAULT (expr)` re-evaluation per row. ### AUTOINCREMENT - `AUTOINCREMENT` **parses but has no behaviour**. It does not prevent rowid reuse, does not maintain a separate sequence, and behaves exactly like a plain `INTEGER PRIMARY KEY`. - It is retained in the schema JSON for compatibility but is never read. ## The non-enforced constraints These are accepted by the parser and then **silently discarded** — the schema does not store them and no code checks them: | Constraint | Parsed? | Stored? | Enforced? | | --- | --- | --- | --- | | `UNIQUE (col)` | yes | no | **no** | | `UNIQUE` column constraint | yes | no | **no** | | `CHECK (expr)` | yes | no | **no** | | `FOREIGN KEY ... REFERENCES ...` | yes | no | **no** | | `CREATE UNIQUE INDEX` | yes | definition stored | **no** | Consequences to internalize: - Duplicate values are allowed in any column except the primary key. If you need uniqueness, enforce it in your application. - Foreign-key relationships are not validated; deleting a parent row does not cascade, restrict, or set null on children. - `CHECK` constraints never run. `CREATE TABLE t (age INTEGER CHECK (age > 0))` happily accepts `-5`. - `CREATE UNIQUE INDEX` records an index marked `unique` in the catalog (it shows up in `pg_indexes`), but the uniqueness flag is cosmetic — the index still only accelerates lookups and does not reject duplicates. ## Why this matters for migrations SQLite-flavoured schemas often lean on `UNIQUE` and `FOREIGN KEY` for integrity. When importing such a schema (SQL dump or SQLite file), those constraints are **dropped silently** rather than erroring. Inspect the imported schema afterwards and add application-level checks for anything that must stay unique or referentially consistent. See [Compatibility](/sql-reference/compatibility/) for the wider gap list, and [Indexes](/engine/indexes/) for how `CREATE INDEX`/`UNIQUE INDEX` actually behave at the storage layer. --- # Data types Canonical URL: https://docs.database.pizza/sql-reference/data-types/ PizzaSQL follows SQLite's **type affinity** model rather than PostgreSQL's strict typing. A column's declared type is a *hint* about how values are converted and compared; it does not constrain what you can store. There is no fixed-size enforcement, no strict typing error on insert, and no `VARCHAR(n)` length limit. Under the hood, every row is serialized as JSON in the key-value store, which shapes a lot of the behaviour below. ## Affinity rules A column's affinity is derived from its declared type name using the SQLite rules: | Declared type contains | Affinity | | --- | --- | | `INT` | INTEGER | | `CHAR`, `CLOB`, or `TEXT` | TEXT | | `BLOB`, or empty type | BLOB | | `REAL`, `FLOA`, or `DOUB` | REAL | | `BOOLEAN` / `BOOL` | BOOLEAN | | anything else (e.g. `NUMERIC`, `DECIMAL`, `DATE`, `DATETIME`, `JSON`) | NUMERIC | This is substring-based and case-insensitive. `VARCHAR(255)`, `TEXT`, `NCHAR`, and `CLOB` are all TEXT affinity; `INT`, `BIGINT`, `TINYINT`, and `SMALLINT` are all INTEGER affinity. `DATE`, `TIME`, `TIMESTAMP`, `DATETIME`, `JSON`, and `JSONB` are recognised as type names but all fall through to NUMERIC affinity. ## The types you can declare | Type name | Affinity | Notes | | --- | --- | --- | | `INTEGER`, `INT`, `BIGINT`, `SMALLINT`, `TINYINT`, `MEDIUMINT` | INTEGER | `INTEGER PRIMARY KEY` becomes a rowid alias | | `REAL`, `FLOAT`, `DOUBLE` | REAL | | | `NUMERIC`, `DECIMAL(p,s)` | NUMERIC | precision/scale parsed but ignored | | `TEXT`, `VARCHAR(n)`, `CHAR(n)`, `CHARACTER`, `CLOB`, `NCHAR`, `NVARCHAR` | TEXT | length ignored | | `BLOB` | BLOB | stored as a string in practice | | `BOOLEAN`, `BOOL` | BOOLEAN | stored as 1/0 | There is no `ARRAY`, `JSONB`-specific, `UUID`, `SERIAL`, or `ENUM` type. There are no schemas or user-defined types. ## How values are actually stored Each row is a JSON document. That means: - **All numbers deserialize as `float64`** on the way out of storage. PizzaSQL normalizes INTEGER-affinity columns back to `int64` after reading, so arithmetic on integer columns behaves as you'd expect. - **`BOOLEAN` is an integer** (`1`/`0`), matching SQLite. `TRUE` and `FALSE` are literal spellings for those integers. - **BLOBs are strings** internally. There is no real binary type on the wire; over PostgreSQL the column is advertised as `BYTEA` (OID 17) but the value travels as text. - `NULL` is represented by a Go `nil` and serializes as JSON `null`. ## Coercion Values are coerced on use, not on insert. The key rules: - **Arithmetic** (`+`, `-`, `*`, `/`, `%`): if both operands are integers the result is an integer (integer division truncates toward zero); otherwise operands are coerced to floats. Strings that look like numbers are parsed. Division or modulo by zero returns `NULL`. - **Comparison** (`=`, `<`, `>` etc.): operands are compared numerically if both can be parsed as numbers, otherwise as strings. - **`||`** concatenation: both sides are rendered as text; `NULL || 'x'` yields `'x'` (unlike PostgreSQL's `NULL` propagation). - **Truthiness** (`toBool`): `0` and `""` and `"0"` and `"false"` (case-insensitive) are false; any other non-empty value is true; `NULL` is false when coerced to a boolean. ## ROWID Every row carries an implicit **`_rowid_`**, even when no `INTEGER PRIMARY KEY` is declared: - `rowid`, `oid`, and `_rowid_` all refer to the same value. - `INTEGER PRIMARY KEY` (exactly an integer type, single column) **aliases the rowid** — the primary-key value *is* the rowid, and inserting without a value auto-assigns the next one. - Any other primary key (e.g. `TEXT PRIMARY KEY`, or a table-level PK) is a normal column; the rowid is a separate, invisible counter maintained in parallel. - `_rowid_` is not included in `SELECT *`; reference it explicitly (`SELECT rowid, * FROM t`). ## Type introspection `typeof(x)` returns one of `"null"`, `"integer"`, `"real"`, `"text"`, or `"blob"` based on the value's Go representation, mirroring SQLite. ## Caveats to keep in mind - Column types are **not enforced**. Inserting a string into an `INTEGER` column succeeds; it's stored as text and coerced back when used. - Because rows are JSON, very large or binary payloads are less efficient than in a native database. Treat `BLOB` as best-effort. - `CAST(x AS type)` is a value conversion at query time, not a storage change. See [Expressions](/sql-reference/expressions/). --- # Expressions & operators Canonical URL: https://docs.database.pizza/sql-reference/expressions/ ## Operator precedence Expressions evaluate with standard precedence, from tightest to loosest: 1. Parentheses and unary `+` / `-` / `NOT` 2. `*`, `/`, `%` 3. `+`, `-`, `||` 4. comparison `=`, `<>`/`!=`, `<`, `<=`, `>`, `>=` 5. `NOT` 6. `AND` 7. `OR` `IS NULL`, `IN`, `BETWEEN`, and `LIKE` are predicates parsed at the comparison level. ## Arithmetic | Operator | Meaning | | --- | --- | | `+ - *` | numeric | | `/` | division — integer when both operands are integers (truncates toward zero) | | `%` | modulo | | `||` | string concatenation | - Arithmetic on a `NULL` operand yields `NULL`. - **Division or modulo by zero returns `NULL`**, not an error. - Integer operands produce integer results; mixing a real promotes to real. Strings that parse as numbers are coerced. - `||` renders both sides as text and never propagates `NULL`: `NULL || 'x'` → `'x'`. ## Comparison `=`, `<>` (or `!=`), `<`, `<=`, `>`, `>=` follow three-valued logic: if either operand is `NULL`, the result is `NULL` (which behaves as false in a `WHERE` filter). Comparison first tries to compare numerically (if both sides parse as numbers) and falls back to lexicographic string comparison otherwise. ## Logical operators `AND` and `OR` implement SQL three-valued logic: | | TRUE | FALSE | NULL | | --- | --- | --- | --- | | `TRUE AND` | TRUE | FALSE | NULL | | `FALSE AND` | FALSE | FALSE | FALSE | | `NULL AND` | NULL | FALSE | NULL | | `TRUE OR` | TRUE | TRUE | TRUE | | `FALSE OR` | TRUE | FALSE | NULL | | `NULL OR` | TRUE | NULL | NULL | `NOT NULL` is `NULL`. ## CASE Both forms are supported: ```sql -- simple CASE SELECT CASE status WHEN 'a' THEN 1 WHEN 'b' THEN 2 ELSE 0 END FROM t; -- searched CASE SELECT CASE WHEN x > 0 THEN 'pos' WHEN x < 0 THEN 'neg' ELSE 'zero' END FROM t; ``` - A simple `CASE` with a `NULL` operand matches nothing. - A searched `CASE` treats a `NULL` condition as false. - No `ELSE` and no match returns `NULL`. ## CAST ```sql CAST(expr AS TYPE) ``` `TYPE` may be any recognized type name. `CAST(NULL AS anything)` is `NULL`. Conversions: - to `INTEGER`-family → truncates toward zero (`int64(value)`). - to `REAL`/`FLOAT`/`DOUBLE`/`NUMERIC`/`DECIMAL` → float. - to `TEXT`/`VARCHAR`/`CHAR` → string rendering. - Any other target returns the value unchanged. ## Predicates ### IS NULL ```sql x IS NULL x IS NOT NULL ``` ### IN ```sql x IN (1, 2, 3) x IN (SELECT ...) -- exactly one column x NOT IN (...) ``` Full three-valued logic: `NULL IN (...)` is `NULL`; an empty list is `FALSE` for `IN` and `TRUE` for `NOT IN`; a list containing `NULL` with no match yields `NULL`. ### BETWEEN ```sql x BETWEEN low AND high x NOT BETWEEN low AND high ``` Equivalent to `x >= low AND x <= high` (respectively `x < low OR x > high`) with three-valued `NULL` handling. ### LIKE ```sql x LIKE 'pattern' x NOT LIKE 'pattern' x LIKE 'pattern' ESCAPE 'char' ``` - `%` matches any sequence, `_` matches a single character. - Matching is **case-insensitive** (both sides are lowercased). - The `ESCAPE` clause is parsed but **ignored** — the escape character is treated literally. ### EXISTS ```sql EXISTS (SELECT ...) ``` Returns `TRUE` if the subquery returns at least one row, including for correlated subqueries. ## Subqueries - **Scalar subqueries** return the first column of the first row, or `NULL` when empty. Unlike strict SQL, a multi-row scalar subquery returns the first value rather than erroring. - **Correlated subqueries** reference outer columns and are re-evaluated per outer row. - Non-correlated `IN (SELECT ...)` results are cached for the duration of the enclosing query. - A small, specialized optimization decorrelates scalar aggregate subqueries of the form `(SELECT COUNT(*)/SUM/AVG/MIN/MAX(col) FROM inner WHERE inner.key = outer.key)`. ## Column references - Unqualified references resolve against the visible tables; ambiguous references across joined tables are an analysis error. - Qualified references use `table.column` or `alias.column`. - `rowid`, `oid`, and `_rowid_` resolve to the implicit rowid. ## Constant folding A `WHERE` clause that references no columns (e.g. `WHERE 1 = 1`) is evaluated once; a constant-false `WHERE` short-circuits to an empty result (or a single aggregated row for aggregate queries). This is an optimization, not a guarantee, and doesn't imply a general planner. --- # Functions Canonical URL: https://docs.database.pizza/sql-reference/functions/ PizzaSQL ships a fixed set of built-in functions. There are no user-defined functions, no `CREATE FUNCTION`, and no extension mechanism. A few functions are *declared* in the engine's function catalog (so they parse without an "unknown function" error) but are **not implemented** in the executor — they return `NULL`. Those are listed separately so you don't mistake their presence in autocomplete for real behaviour. ## Aggregate functions Used in `SELECT` with or without `GROUP BY`, and in `HAVING`. | Function | Description | | --- | --- | | `COUNT(*)` | number of rows | | `COUNT(expr)` | number of non-`NULL` values | | `COUNT(DISTINCT expr)` | number of distinct non-`NULL` values | | `SUM(expr)` | sum; integer result when all inputs are integers, otherwise real; `NULL` when no values | | `SUM(DISTINCT expr)` | distinct sum | | `AVG(expr)` | average (real); `NULL` when no values | | `MIN(expr)` | minimum, ignoring `NULL`s | | `MAX(expr)` | maximum, ignoring `NULL`s | - Aggregates may be nested inside expressions: `SELECT sum(x) / count(x) FROM t`. - On an empty input set, `COUNT` returns `0`; `SUM`, `AVG`, `MIN`, `MAX` return `NULL`. - `MIN`/`MAX` also work as *scalar* functions over multiple arguments: `MIN(a, b, c)`. - `TOTAL` and `GROUP_CONCAT` are declared in the catalog but **not implemented** — do not rely on them. ## Scalar functions ### Strings | Function | Description | | --- | --- | | `upper(s)` | uppercase | | `lower(s)` | lowercase | | `length(s)` | character length | | `substr(s, start[, len])` / `substring(...)` | 1-indexed substring | | `trim(s)` | trims whitespace | | `replace(s, find, repl)` | replace all occurrences | | `instr(s, sub)` | 1-indexed position of `sub`, or `0` | | `printf(format, ...)` | `fmt.Sprintf`-style formatting | | `concat(a, b, ...)` | concatenate all arguments as text | ### Numbers | Function | Description | | --- | --- | | `abs(x)` | absolute value | | `round(x[, n])` | round to `n` decimals (naive implementation; see caveats) | | `min(a, b, ...)` | scalar minimum | | `max(a, b, ...)` | scalar maximum | | `random()` | random 64-bit integer | ### NULL handling | Function | Description | | --- | --- | | `coalesce(a, b, ...)` | first non-`NULL` argument | | `ifnull(a, b)` | `b` when `a` is `NULL`, else `a` | | `nullif(a, b)` | `NULL` when `a` equals `b`, else `a` | ### Type | Function | Description | | --- | --- | | `typeof(x)` | `"null"`, `"integer"`, `"real"`, `"text"`, or `"blob"` | ### Blobs and encoding | Function | Description | | --- | --- | | `hex(x)` | uppercase hex of the value's bytes | | `unhex(x)` | decode hex to a string | | `zeroblob(n)` | a string of `n` NUL bytes (capped at 1 MiB) | ### Date/time | Function | Description | | --- | --- | | `date(...)` | `YYYY-MM-DD` | | `time(...)` | `HH:MM:SS` | | `datetime(...)` | `YYYY-MM-DD HH:MM:SS` | | `julianday(...)` | Julian day number | | `unixepoch(...)` | Unix seconds (or fractional with `subsec`) | | `strftime(format, ...)` | formatted time | | `timediff(a, b)` | `±YYYY-MM-DD HH:MM:SS.SSS` from `b` to `a` | Date/time functions accept SQLite-style time values and modifiers: - Time values: `'now'`, ISO-8601 text (e.g. `'2026-01-02 03:04:05'`), or a numeric Julian day (optionally followed by `unixepoch`, `julianday`, or `auto`). - Modifiers: `NNN days|hours|minutes|seconds|months|years`, `start of day|month|year`, `weekday N`, `utc`, `localtime`, `subsec`/`subsecond`, and `±YYYY-MM-DD HH:MM:SS.SSS`. - Unknown modifiers are silently ignored rather than erroring. ### Version | Function | Description | | --- | --- | | `pizzasql_version()` | engine build version | | `sqlite_version()` | same value, for SQLite compatibility | ## Declared but not implemented These names are recognized by the parser/analyzer but **return `NULL` when called**. Treat them as unsupported: - `ltrim`, `rtrim` - `ceil`, `floor` - `mod` - `iif` - `quote` - `total`, `group_concat` - `last_insert_rowid`, `changes`, `total_changes` - `randomblob` is implemented in the executor but not registered with the analyzer, so calls are currently rejected as an unknown function. The `last_insert_rowid` gap matters in practice: after an `INSERT` with an auto-generated key, there is no built-in function to retrieve the generated id. If you need it, insert an explicit value instead of relying on auto-generation. ## Caveats - `round` uses `int64(v*mult + 0.5)` and only behaves correctly for non-negative decimal counts; treat it as approximate for edge cases. - `random()` is not cryptographically secure — do not use it for secrets. - `zeroblob` is capped at 1 MiB regardless of the requested size. - `printf` uses Go's `fmt.Sprintf`, whose format verbs differ from SQLite's `printf` in places. --- # SQL at a glance Canonical URL: https://docs.database.pizza/sql-reference/overview/ PizzaSQL is a SQL engine with a hand-written lexer, parser, analyzer, and executor written in Go. It speaks a **SQLite-flavoured SQL dialect** but is served through a **PostgreSQL wire protocol**, which is why it "looks like Postgres" to your tools while behaving like SQLite under the hood. This page is the short version. Each area has a dedicated page: - [Data types](/sql-reference/data-types/) — SQLite-style type affinity, not strict column types. - [Statements](/sql-reference/statements/) — the full statement grammar. - [Expressions & operators](/sql-reference/expressions/) — operators, `CASE`, `IN`, `LIKE`, subqueries. - [Functions](/sql-reference/functions/) — scalar and aggregate functions, and which ones are real. - [Constraints](/sql-reference/constraints/) — what `PRIMARY KEY`, `UNIQUE`, and friends actually do here. - [Compatibility](/sql-reference/compatibility/) — the gap list against SQLite and PostgreSQL. ## Dialect, in one sentence SQLite syntax, PostgreSQL wire transport, SQLite's type system, and a storage engine that keeps each row as a JSON document in a key-value store. ## Statement support ```sql -- Query SELECT [DISTINCT] cols FROM table [WHERE ...] [GROUP BY ...] [HAVING ...] [ORDER BY ...] [LIMIT n] [OFFSET n]; -- Write INSERT INTO t (cols) VALUES (...), (...); INSERT INTO t SELECT ...; INSERT OR REPLACE/IGNORE/FAIL/ABORT INTO t ...; INSERT INTO t ... ON CONFLICT (pk) DO NOTHING | DO UPDATE SET c = v, ...; UPDATE t SET c = v, ... WHERE ...; DELETE FROM t WHERE ...; -- Schema CREATE TABLE t (col TYPE constraints, ...); CREATE INDEX idx ON t (col); CREATE UNIQUE INDEX idx ON t (col); CREATE VIEW v AS SELECT ...; DROP TABLE t; DROP INDEX idx; DROP VIEW v; ALTER TABLE t ADD COLUMN c TYPE; ALTER TABLE t DROP COLUMN c; ALTER TABLE t RENAME TO new_name; ALTER TABLE t RENAME COLUMN old TO new; -- Transactions (see the caveats below) BEGIN; COMMIT; ROLLBACK; SAVEPOINT s; RELEASE s; ROLLBACK TO s; -- Introspection PRAGMA table_info(t); PRAGMA table_list; PRAGMA database_list; PRAGMA version; EXPLAIN ...; EXPLAIN QUERY PLAN ...; ``` ## What is *not* supported PizzaSQL is intentionally small. These features are not implemented at all — they will be rejected by the parser, not silently mishandled: - **Common table expressions** — no `WITH ... AS (...)`. - **Window functions** — no `OVER (...)`, `ROW_NUMBER()`, `PARTITION BY`. - **`RETURNING`** — `INSERT`/`UPDATE`/`DELETE` do not return rows. - **`WITHOUT ROWID`** tables. - **Triggers**, **stored procedures**, **prepared SQL in the engine** (the PG driver handles parameters client-side). - **`GLOB`** — the keyword exists but is not wired up as an operator; `x GLOB 'a*'` is a parse error. A second group of features *parses* but has no effect or is only partially implemented. These are the sharp edges: - `RIGHT JOIN` and `FULL [OUTER] JOIN` parse but return an empty result — they are not executed. Use `LEFT JOIN` and reorder. - `NATURAL JOIN` and `USING (cols)` parse but the condition is ignored. - `UNIQUE`, `CHECK`, and `FOREIGN KEY` constraints parse but are **not enforced** (see [Constraints](/sql-reference/constraints/)). - `AUTOINCREMENT` parses but has **no behaviour** beyond ordinary `INTEGER PRIMARY KEY` rowid generation. - `LIKE ... ESCAPE 'x'` — the `ESCAPE` clause is parsed and ignored. For the full, honest list, see [Compatibility](/sql-reference/compatibility/). ## The engine vs. the managed service One distinction matters throughout these docs. **PizzaSQL**, the engine, has raw limits — its parser, executor, and storage. The **managed database.pizza** service wraps that engine in an API-key-authenticated proxy that adds its own rules on top, including scope enforcement and rejecting transaction statements on the HTTP endpoint. Where a behaviour differs between the two, the relevant page calls it out explicitly. In short: | Concern | Raw PizzaSQL | Managed database.pizza | | --- | --- | --- | | Transactions | `BEGIN`/`COMMIT`/`ROLLBACK` work over PG wire and the engine's own HTTP API | PG wire proxy forwards them; the managed HTTP query endpoint rejects transaction statements | | Auth | None (`-http-auth`/API keys optional) | API keys required, scoped per operation | | Schema features | SQLite-style, no schemas/roles | Same, plus per-organization/database isolation | ## Placeholders Pass values as parameters rather than concatenating them into SQL: - Over the **managed HTTP API**, use `?` placeholders with a `params` array: `{"sql": "SELECT * FROM t WHERE id = ?", "params": [42]}`. - Over the **PostgreSQL wire protocol**, use `$1`, `$2`, … (PostgreSQL style) — drivers bind these for you. ## Conventions used in these docs - `INTEGER`, `TEXT`, etc. are written in uppercase for clarity; the parser is case-insensitive for keywords and identifiers. - Tables and databases are named by example: organization `acme`, database `production`. - Anything marked **unsafe** or **not enforced** is a real behavioural gap, not a documentation convenience. --- # Statements Canonical URL: https://docs.database.pizza/sql-reference/statements/ This page lists each statement family with its exact grammar, behaviour, and caveats. For expression details (operators, `CASE`, `IN`, subqueries) see [Expressions & operators](/sql-reference/expressions/). ## SELECT ```sql SELECT [DISTINCT] column [AS alias], ... FROM table [[AS] alias] [JOIN ...] WHERE condition GROUP BY expr, ... HAVING condition ORDER BY expr [ASC|DESC], ... LIMIT n [OFFSET m]; ``` - `SELECT` without `FROM` evaluates an expression (`SELECT 1 + 2`, `SELECT upper('hi')`). - `DISTINCT` de-duplicates the result row set. - `ORDER BY` accepts expressions, column aliases, and 1-based ordinal positions (`ORDER BY 2`). - `LIMIT`/`OFFSET` accept numeric expressions. - **Set operations**: `UNION`, `UNION ALL`, `INTERSECT`, and `EXCEPT` are supported with standard precedence (`INTERSECT` binds tighter than `UNION`/`EXCEPT`). A compound query's `ORDER BY`/`LIMIT`/`OFFSET` apply to the combined result. - **Subqueries** are supported in `FROM` (derived tables require an alias), as scalar expressions, in `IN (SELECT ...)`, and in `EXISTS (SELECT ...)`, including correlated subqueries. ### Joins ```sql FROM a JOIN b ON a.id = b.a_id FROM a INNER JOIN b ON ... FROM a LEFT [OUTER] JOIN b ON ... FROM a CROSS JOIN b FROM a, b -- implicit cross join, filtered by WHERE ``` - `INNER`, `LEFT`, and `CROSS` joins are fully executed. `LEFT JOIN` produces a NULL-padded row for unmatched left rows. - Equality joins (`ON a.x = b.y`) use a hash join; non-equality `ON` conditions fall back to a nested loop. - **`RIGHT [OUTER] JOIN` and `FULL [OUTER] JOIN` parse but are not executed** — they return an empty result. Rewrite with `LEFT JOIN`. - **`NATURAL JOIN` and `USING (cols)` parse but their join condition is ignored**, effectively becoming a cross join. Always use an explicit `ON`. ## INSERT ```sql INSERT INTO table [(col, ...)] VALUES (expr, ...), (expr, ...); INSERT INTO table [(col, ...)] SELECT ...; INSERT OR REPLACE INTO table ...; INSERT OR IGNORE INTO table ...; INSERT OR FAIL INTO table ...; INSERT OR ABORT INTO table ...; INSERT INTO table ... ON CONFLICT [(pk)] DO NOTHING; INSERT INTO table ... ON CONFLICT [(pk)] DO UPDATE SET col = expr, ...; ``` - Omitting the column list targets all columns in declaration order. - `INSERT ... SELECT` bulk-inserts the materialized result. - **Conflict handling** is driven by the primary key only: - `INSERT OR IGNORE` silently skips duplicate-PK rows. - `INSERT OR REPLACE` deletes the conflicting row and inserts the new one. - `INSERT OR FAIL`/`OR ABORT` abort on the first duplicate. - `ON CONFLICT (target) DO NOTHING` / `DO UPDATE SET ...` work only when the conflict is on the primary key; the `(target)` list must name the PK column (or be omitted). - Auto-generated integer primary keys (and the implicit rowid) are assigned when no PK value is supplied. There is **no `RETURNING`** and no way to read back the generated id in the same statement; use `SELECT last_insert_rowid()`-style patterns with caution (see [Functions](/sql-reference/functions/) — the rowid functions are not implemented). ## UPDATE and DELETE ```sql UPDATE table SET col = expr, ... WHERE condition; DELETE FROM table WHERE condition; ``` - Both evaluate the `WHERE` condition per row. `UPDATE` evaluates `SET` expressions against the row's current values, so `SET balance = balance + 100` works. - Omitting `WHERE` affects every row. - No `RETURNING`, no `ORDER BY`/`LIMIT` on `UPDATE`/`DELETE`. ## CREATE TABLE ```sql CREATE TABLE [IF NOT EXISTS] table ( column TYPE [PRIMARY KEY] [NOT NULL] [DEFAULT expr] [AUTOINCREMENT], ..., [PRIMARY KEY (col, ...)], [UNIQUE (col, ...)], [CHECK (expr)], [FOREIGN KEY (col) REFERENCES other (col)] ); ``` - `IF NOT EXISTS` silently succeeds if the table already exists. - Only `PRIMARY KEY`, `NOT NULL`, and `DEFAULT` have an effect. `AUTOINCREMENT` is accepted but adds no behavior; `UNIQUE`, `CHECK`, and `FOREIGN KEY` are parsed and discarded — see [Constraints](/sql-reference/constraints/). - A table-level `PRIMARY KEY (a, b)` uses the first named column as the primary key; composite keys are not truly supported. - `DEFAULT expr` is evaluated when the table is created, so it must be a constant expression. - `AUTOINCREMENT` is accepted but has no extra behaviour over a plain `INTEGER PRIMARY KEY`. ## ALTER TABLE ```sql ALTER TABLE table ADD COLUMN column TYPE [constraints]; ALTER TABLE table DROP COLUMN column; ALTER TABLE table RENAME TO new_name; ALTER TABLE table RENAME COLUMN old TO new; ``` - `ADD COLUMN` appends a nullable column; `ADD COLUMN IF NOT EXISTS column ...` is also accepted. - You cannot drop the primary-key column. - Renaming is schema-only; index definitions referencing renamed columns are **not** updated automatically. ## CREATE / DROP INDEX and VIEW ```sql CREATE [UNIQUE] INDEX [IF NOT EXISTS] name ON table (col [ASC|DESC], ...); DROP INDEX [IF EXISTS] name; CREATE VIEW [IF NOT EXISTS] view AS SELECT ...; DROP VIEW [IF EXISTS] view; ``` - Index definitions persist, but index entries are rebuilt in memory and only used for single-column equality lookups — see [Indexes](/engine/indexes/). - `UNIQUE` indexes are recorded as unique in the catalog but **uniqueness is not enforced**. - Views are **connection-local and in-memory**: they exist only within the connection that created them and are not persisted or shared. See [Catalog & schema](/internals/catalog/). ## Transactions ```sql BEGIN [TRANSACTION]; COMMIT; ROLLBACK; SAVEPOINT name; RELEASE name; ROLLBACK TO name; ``` - Transactions use an undo log with a database-wide lock — not MVCC. See [Transactions](/engine/transactions/). - `SAVEPOINT` outside a transaction implicitly starts one. - On the **managed HTTP endpoint**, transaction statements are rejected with `501`. Over the **PostgreSQL wire proxy**, transactions are forwarded and work normally. ## PRAGMA and EXPLAIN ```sql PRAGMA table_info(t); -- column list for table t PRAGMA table_list; -- all tables PRAGMA database_list; -- attached databases PRAGMA version; -- engine version EXPLAIN stmt; -- simplified opcode listing EXPLAIN QUERY PLAN stmt; -- SCAN/FILTER/SORT/LIMIT description ``` - Only the four `PRAGMA` forms above are implemented; any other pragma name errors. - `EXPLAIN` output is **illustrative only** — it does not reflect the real execution engine (there is no cost-based planner; see [Query lifecycle](/internals/query-lifecycle/)). ## ATTACH / DETACH DATABASE ```sql ATTACH DATABASE 'name' AS alias; DETACH DATABASE alias; ``` - `ATTACH` registers another database namespace (each database is a key prefix in PizzaKV) under an alias. Aliases `main`, `temp`, and `temporary` are reserved. - `DETACH` cannot detach `main`. Detaching does not drop the underlying database data. ## Not supported These are rejected at parse time: `WITH` (CTEs), window functions, `RETURNING`, `WITHOUT ROWID`, `TRUNCATE`, `UPSERT` (beyond the `OR`/`ON CONFLICT` forms), triggers, and `CREATE SCHEMA`/roles.