pizzasql

PizzaSQL

**A SQL database built from scratch in Go** SQLite-compatible SQL · PostgreSQL wire protocol · HTTP/JSON API · Built-in storage [](https://go.dev/) []() []() [Features](#features) · [Quick Start](#quick-start) · [Documentation](#documentation) · [Benchmarks](#benchmarks)

---

What is PizzaSQL?

PizzaSQL is a SQL database engine built from the ground up in Go. It features a hand-written recursive descent parser, SQLite-compatible SQL syntax, and multiple access methods.

PizzaSQL passes 100% of the SQLite SQLLogicTest suite — over 5 million individual SQL tests covering edge cases, type coercion, complex queries, and SQLite compatibility.

Access Methods

• PostgreSQL Wire Protocol — Connect with psql, any PostgreSQL client library (psycopg2, node-postgres, etc.)
• HTTP/JSON API — Query via REST endpoints from any language and the web
• CLI & REPL — Interactive shell and command-line execution

Architecture

• Hand-Written Lexer & Parser — Pure Go implementation
• PizzaKV Storage — Durable append-only .pkvdb backend implemented in Zig
• PKBFI Transport — Checksummed binary frames over TCP or Unix domain sockets
• Thread-Safe — Concurrent query execution with mutex-based locking

---

Features

Core SQL Operations

• Full CRUD: SELECT, INSERT, UPDATE, DELETE
• DDL: CREATE/DROP/ALTER TABLE, CREATE/DROP INDEX
• Joins: INNER, LEFT, RIGHT, FULL OUTER, CROSS
• Aggregation: COUNT, SUM, AVG, MIN, MAX with GROUP BY/HAVING
• Subqueries: Scalar, IN, EXISTS, and correlated subqueries
• Transactions: BEGIN, COMMIT, ROLLBACK, SAVEPOINT
• Advanced SQL: DISTINCT, ORDER BY, LIMIT/OFFSET, CASE expressions

Multiple Access Methods

PostgreSQL Wire Protocol

# Start server
./pizzasql -kv -pg

# Connect with psql
psql -h localhost -p 5432 -d pizzasql

# Or any PostgreSQL client library
postgresql://localhost:5432/pizzasql

HTTP/JSON API

# Start HTTP server
./pizzasql -kv -http

# Query via REST
curl -X POST http://localhost:8080/query \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT * FROM users WHERE age > ?", "params": [25]}'

CLI/REPL

# Interactive mode with storage
./pizzasql -kv

# Single statement
./pizzasql "SELECT * FROM users LIMIT 10"

SQLite Compatibility

• ROWID Support — Implicit rowid column for all tables
• AUTOINCREMENT — Sequential ID generation
• Type Affinity — SQLite-compatible type system
• PRAGMA Statements — table_info, database_list, table_list, version
• SQLite Functions — printf, hex, random, glob, instr, zeroblob
• Conflict Resolution — INSERT OR REPLACE/IGNORE/FAIL/ABORT

Performance & Architecture

• Hand-Written Parser — 176,000 statements/sec
• Fast Lexer — 227,000 ops/sec tokenization
• Automatic Indexing — 10-100x faster than full table scans
• Unix Socket Transport — Sub-millisecond KV latency
• Thread-Safe — Concurrent query execution with mutex-based locking
• Connection Pooling — Efficient resource management

---

Quick Start

Installation

Prerequisites:

• Go 1.21+
• PizzaKV — Storage backend (auto-launched with -kv flag)

Build and install:

git clone https://github.com/danfragoso/pizzasql.git
cd pizzasql
make install        # installs to /usr/local/bin
# or
make install PREFIX=~/.local   # user install, no sudo

Build only:

make build          # output: ./bin/pizzasql

Start the Server

Option 1: HTTP API

pizzasql -kv -http
# Listening on http://localhost:8080

Option 2: PostgreSQL wire protocol

pizzasql -kv -pg
# psql -h localhost -p 5432 -d pizzasql

Option 3: Both at once

pizzasql -kv -http -pg

Option 4: Interactive REPL

pizzasql -kv

The -kv flag auto-launches a PizzaKV storage process connected via Unix socket (.pizzakv.sock in the working directory). PizzaKV stores data in .pkvdb by default. PizzaSQL writes its runtime state to /tmp/pizzasql/<pid>/runtime.json and cleans up on exit.

PizzaSQL requires a PKBFI-capable PizzaKV. Existing legacy .db files must be migrated once before upgrading. See PKBFI_STORAGE_MIGRATION.md.

Your First Query

Using psql:

psql -h localhost -p 5432 -d pizzasql

CREATE TABLE users (
  id    INTEGER PRIMARY KEY AUTOINCREMENT,
  name  TEXT NOT NULL,
  email TEXT UNIQUE
);

INSERT INTO users (name, email) VALUES
  ('Alice', 'alice@example.com'),
  ('Bob',   'bob@example.com');

SELECT * FROM users;

Using HTTP API:

curl -X POST http://localhost:8080/query \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT * FROM users"}'

Response:

{
  "columns": [
    {"name": "id",    "type": "INTEGER"},
    {"name": "name",  "type": "TEXT"},
    {"name": "email", "type": "TEXT"}
  ],
  "rows": [
    [1, "Alice", "alice@example.com"],
    [2, "Bob",   "bob@example.com"]
  ],
  "rowsAffected": 0,
  "executionTimeMicro": 108
}

---

Process Management

PizzaSQL uses a per-instance runtime directory at /tmp/pizzasql/<pid>/ to track process state. Each directory contains a runtime.json with the PizzaSQL and PizzaKV PIDs and connection info.

/tmp/pizzasql/
  12345/
    runtime.json    ← { "pizzasql": { "pid": 12345, ... }, "pizzakv": { "pid": 12346, ... } }
  67890/
    runtime.json

Multiple instances are supported as long as each runs from a different working directory (each needs its own .pkvdb and .pizzakv.sock file).

Stale entries (from crashed processes) are cleaned up automatically on the next startup.

Startup prompt — if another live pizzasql instance is detected you'll see:

Warning: 1 pizzasql instance(s) already running:
  PID 12345 http=:8080 kv=unix:.pizzakv.sock
Continue anyway? [y/N]

Connecting to an external PizzaKV (without -kv):

pizzasql -kvaddr localhost:8085 -http

---

Benchmarks

Performance on an M2 MacBook Air, 10,000-row table, 200 repetitions.

Workload	SQLite	PostgreSQL	PizzaSQL
Point lookup by PK	0.003 ms	0.092 ms	15.628 ms
Category scan (no index)	0.666 ms	0.604 ms	16.080 ms
Value range (no index)	1.531 ms	1.116 ms	17.909 ms
COUNT(*)	0.004 ms	0.305 ms	15.651 ms
Aggregate by category	2.419 ms	1.070 ms	16.667 ms
Top-10 ORDER BY DESC	0.861 ms	1.020 ms	21.735 ms
Category scan (indexed)	0.537 ms	0.279 ms	0.108 ms
Value range (indexed)	2.456 ms	0.828 ms	17.854 ms

These figures predate the .pkvdb/PKBFI storage integration and are retained as a legacy baseline. Current PizzaSQL uses paginated binary scans and versioned binary row tuples; rerun the suite on the target hardware before using these numbers for capacity planning.

Run Benchmarks

# Requires PizzaSQL running at :8080 and PostgreSQL at :5432
go run ./cmd/bench/

# Include raw KV benchmark (find the KV port in /tmp/pizzasql/<pid>/runtime.json)
go run ./cmd/bench/ -kvaddr localhost:<port>

---

PostgreSQL Wire Protocol Support

PizzaSQL implements the PostgreSQL wire protocol, allowing you to use any PostgreSQL client with SQLite-compatible SQL syntax.

Connecting with Client Libraries

Python (psycopg2):

import psycopg2

conn = psycopg2.connect(host="localhost", port=5432, database="pizzasql")
cur = conn.cursor()
cur.execute("CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)")
cur.execute("INSERT INTO users (name) VALUES (%s)", ("Alice",))
cur.execute("SELECT * FROM users")
print(cur.fetchall())
conn.commit()
conn.close()

Node.js (node-postgres):

const { Client } = require('pg');
const client = new Client({ host: 'localhost', port: 5432, database: 'pizzasql' });
await client.connect();
await client.query("CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)");
await client.query("INSERT INTO users (name) VALUES ($1)", ['Alice']);
const res = await client.query("SELECT * FROM users");
console.log(res.rows);
await client.end();

Go (lib/pq):

db, _ := sql.Open("postgres", "host=localhost port=5432 dbname=pizzasql sslmode=disable")
db.Exec("CREATE TABLE users (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)")
db.Exec("INSERT INTO users (name) VALUES ($1)", "Alice")
rows, _ := db.Query("SELECT * FROM users")

Command-Line Tools

# Interactive
psql -h localhost -p 5432 -d pizzasql

# Single command
psql -h localhost -p 5432 -d pizzasql -c "SELECT * FROM users"

# SQL file
psql -h localhost -p 5432 -d pizzasql -f schema.sql

# pgcli
pgcli postgresql://localhost:5432/pizzasql

DBeaver / DataGrip / pgAdmin: connection type PostgreSQL, host localhost, port 5432, database pizzasql, no credentials.

Important Notes

1. SQL Dialect: PizzaSQL uses SQLite syntax, not PostgreSQL syntax
   • Use INTEGER PRIMARY KEY AUTOINCREMENT, not SERIAL
   • Use TEXT, not VARCHAR with enforced length
2. Parameter placeholders: use $1, $2... (PostgreSQL style) or ? (SQLite style). PG Bind rewrites $n only outside quoted regions and line/block comments. The HTTP /query API substitutes ? textually, so a statement that uses the jsonb ? key-existence operator cannot also use ? positional parameters; use the PostgreSQL protocol or $n-free SQL there. Initialization probes such as SELECT version() and SHOW server_version match complete token sequences, never substrings inside SQL literals or unrelated projections.
3. Extended protocol scope: Parse accepts one command. Describe never executes SQL: statement Describe returns ParameterDescription with the negotiated parameter types (a client Parse OID wins, an OID of 0 is inferred from casts, comparisons, assignment targets, and ANY/ALL operands, and otherwise reports TEXT rather than 0) followed by NoData, while portal Describe returns NoData without a ParameterDescription, as in PostgreSQL; Execute still sends the real RowDescription. A placeholder number above PostgreSQL's 65535-parameter protocol limit fails Parse with 54000. Bind accepts text parameters for every type and binary parameters for booleans, integers, floats, numeric, jsonb, and the supported array OIDs; unsupported binary parameter OIDs report 0A000. Bind result formats are honored per column (0=text, 1=binary) for the OIDs listed by GET /capabilities; a binary request for any other reported OID fails with 0A000. Because Describe returns NoData, the format list is resolved when Execute produces the result, so an invalid list or OID is rejected at Execute after an INSERT/UPDATE/DELETE ... RETURNING statement has already run (no rows are sent and no rollback is attempted). Execute honors a row limit: maxRows > 0 returns at most that many rows and then PortalSuspended, and a later Execute on the same portal resumes at the next row without re-running the statement, while maxRows = 0 returns all remaining rows and CommandComplete. The result is materialized once by the first Execute; there is no scroll or backward fetch and no WITH HOLD. RowDescription is sent by the first Execute only, and per-portal result formats apply to every Execute. A portal lives until Close('P'), the end of its transaction (Sync outside an explicit block, COMMIT/ROLLBACK inside one), or a ROLLBACK TO SAVEPOINT, which also drops portals created before the savepoint (stricter than PostgreSQL), after which Execute fails with 34000 (25P02 while the transaction is aborted); a Bind replaces the unnamed portal but re-binding a named portal that already exists fails with 42P03; a negative row limit is a protocol error.
4. PostgreSQL compatibility limits: no schemas or roles. Arrays are one-dimensional and JSONB is a native canonical-text type; see GET /capabilities for the exact surface.

---

Architecture

graph TD
    Client[Client Applications<br/>CLI · HTTP API · PostgreSQL Protocol]
    Client --> Lexer

    subgraph PizzaSQL Core
        Lexer[Lexer - SQL Tokenizer<br/>227,000 ops/sec]
        Parser[Parser - AST Builder<br/>176,000 statements/sec]
        Analyzer[Analyzer - Semantic Analysis<br/>Type checking · Scope resolution]
        Executor[Executor - Query Engine<br/>Index optimization · Transactions]
        Lexer --> Parser --> Analyzer --> Executor
    end

    Executor --> Storage[PizzaKV Storage<br/>Unix socket · Radix trie · Persistent]

    style Client fill:#e1f5ff,stroke:#0288d1,stroke-width:2px
    style PizzaSQL Core fill:#fff3e0,stroke:#f57c00,stroke-width:2px
    style Storage fill:#f3e5f5,stroke:#7b1fa2,stroke-width:2px

---

Documentation

CLI Reference

# Storage
-kv                  Launch PizzaKV automatically (Unix socket)
-kvaddr string       Connect to existing PizzaKV (e.g. localhost:8085)
-kvflags string      Extra flags forwarded to pizzakv (e.g. "-iwal")
-db string           Database name (default "pizzasql")
-pool int            KV connection pool size (default 5)

# HTTP server
-http                Enable HTTP server
-http-host string    Host (default "localhost")
-http-port int       Port (default 8080)
-http-cors           Enable CORS headers (default true)
-http-compression    Enable gzip compression (default true)
-http-auth           Enable API key authentication
-api-keys string     Comma-separated API keys

# PostgreSQL wire protocol
-pg                  Enable PostgreSQL server
-pg-host string      Host (default "localhost")
-pg-port int         Port (default 5432)

# Export / Import
-o string            Output file (export)
-i string            Input file (import; .db/.sqlite/.sqlite3 auto-imports from SQLite)
-table string        Table name (required for CSV)
-format string       Format: sql, csv, sqlite (auto-detected from extension)
-drop                Include DROP TABLE in SQL export
-create-table        Create table from CSV schema on import
-ignore-errors       Continue import on row/table errors

# Misc
-quiet               Suppress request/query logging

HTTP API

Method	Path	Description
POST	/query	Execute SQL, return rows
POST	/execute	Batch statements
GET	/schema/tables	List all tables
GET	/schema/tables/{name}	Table schema
GET	/health	Health check
GET	/version	Build identity and compatibility strings
GET	/capabilities	Machine-readable capability document
GET	/stats	Runtime statistics
GET	/metrics	Prometheus metrics
POST	/transaction/begin	Begin transaction
POST	/transaction/commit	Commit
POST	/transaction/rollback	Rollback

Multi-database: pass X-Database: <name> header to route queries to a specific database. Databases are created on first access.

Database Export / Import

SQL export/import

# Export full database
pizzasql -db mydb -o backup.sql

# Export with DROP TABLE
pizzasql -db mydb -o backup.sql -drop

# Export single table
pizzasql -db mydb -table users -o users.sql

# Export to CSV
pizzasql -db mydb -table users -o users.csv

# Import SQL
pizzasql -db mydb -i backup.sql

# Import CSV (create table from header)
pizzasql -db mydb -table users -i users.csv -create-table

SQLite .db import

PizzaSQL can import a SQLite database file directly. Tables, indexes, and row data are all imported. Pragmas, views, and triggers are skipped.

CLI — auto-detected from .db / .sqlite / .sqlite3 extension:

pizzasql -kv -db mydb -i source.db

Keep going on errors (e.g. duplicate rows or unsupported DDL):

pizzasql -kv -db mydb -i source.db -ignore-errors

Insert into an existing database (skip CREATE TABLE, only insert rows):

# Not yet exposed as a CLI flag — use the HTTP API's create_tables=false parameter

HTTP API — multipart upload:

curl -X POST http://localhost:8080/import \
  -H "X-Database: mydb" \
  -F "file=@source.db"

HTTP API — raw body with explicit format:

curl -X POST "http://localhost:8080/import?format=sqlite" \
  -H "X-Database: mydb" \
  -H "Content-Type: application/octet-stream" \
  --data-binary @source.db

HTTP API options:

Query param	Default	Description
format	auto	sqlite forces binary SQLite mode
create_tables	true	false skips CREATE TABLE, only inserts rows
ignore_errors	false	Continue past individual row/table errors

Response:

{
  "tablesCreated":  ["users", "albums", "tracks"],
  "tablesImported": ["users", "albums", "tracks"],
  "rowsInserted":   27754,
  "indexesCreated": 61,
  "errors": []
}

What gets imported:

• All tables (schema + data)
• Regular indexes (CREATE INDEX)

What is silently skipped:

• Pragmas
• Views
• Triggers
• Expression indexes (e.g. CREATE INDEX ON t(COALESCE(a, b)))
• FOREIGN KEY / CHECK constraints (schema is imported without them)
• AUTOINCREMENT keyword (not needed — PizzaSQL handles PK generation)

Supported file detection (format auto-selection in order):

1. ?format=sqlite query param
2. Filename extension: .db, .sqlite, .sqlite3
3. Content-Type: application/x-sqlite3 or application/octet-stream
4. Magic bytes: file starts with SQLite format 3

SQL Support

Data Types: INTEGER (INT, BIGINT, BOOLEAN) · REAL (FLOAT, DOUBLE, DECIMAL) · TEXT (VARCHAR, CHAR) · BLOB · NUMERIC

Joins: INNER · LEFT · RIGHT · FULL OUTER · CROSS

Aggregates: COUNT · COUNT(DISTINCT) · SUM · AVG · MIN · MAX

Functions: UPPER, LOWER, LENGTH, SUBSTR, TRIM, REPLACE, CONCAT, ABS, ROUND, CEIL, FLOOR, MOD, COALESCE, NULLIF, IFNULL, printf, hex, random, glob, instr, zeroblob

Transactions:

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
UPDATE accounts SET balance = balance + 100 WHERE name = 'Bob';
COMMIT;

Multi-row UPDATE and DELETE, primary-key moves, and DML with RETURNING are staged before one atomic commit. An evaluation or validation failure leaves no partial statement effects; in an explicit transaction, earlier successful statements remain staged. Mutations exceeding a single storage transaction's limits fail rather than being split into independently committed chunks.

DDL is transactional as well: inside an explicit transaction, CREATE TABLE, DROP TABLE, ALTER TABLE (add/drop/rename column, rename table), and CREATE/DROP INDEX are staged. The issuing session sees them immediately, other sessions keep reading the committed catalog, ROLLBACK restores the prior schema and rows, and COMMIT publishes the whole migration in the same atomic batch as the transaction's rows. A statement that fails leaves the transaction's earlier DDL intact, and DDL that conflicts with a concurrently committed DDL fails with a serialization error. Autocommit DDL is unchanged.

Simple autocommit scans with a literal non-negative LIMIT/OFFSET can stop after enough matching rows. Queries requiring sorting, grouping, DISTINCT, windows, subquery projections, or explicit-transaction overlay validation keep the full execution path. Equality predicates inside AND can use a primary or single-column index lookup; the original predicate is still checked on candidates.

Indexes:

CREATE INDEX idx_users_email ON users(email);
CREATE UNIQUE INDEX idx_users_email ON users(email);
DROP INDEX idx_users_email;

Column-based UNIQUE constraints build a derived ownership cache once from durable rows, then validate and update it under the table's exclusive publication gate. Changes are prepared before commit and published only after durable success. Rollback, compare failure, DDL, restart, and ambiguous transport errors never leave speculative ownership entries. NULL-bearing keys remain exempt; composite keys use the constraint encoder, not the SELECT candidate encoder. Expression UNIQUE constraints retain scan validation until evaluator determinism is proven. Exclusive-gate commits also maintain warm column-based SELECT indexes and exact counts incrementally. Shared-gate optimistic commits still invalidate those read caches to avoid publishing them out of durable CAS order. Expression read indexes are rebuilt after such commits instead of assuming evaluator determinism.

PRAGMA:

PRAGMA table_info(users);
PRAGMA table_list;
PRAGMA database_list;

---

Testing

make test           # all tests
make compat         # pChat-style compatibility suite (PIZZASQL_COMPAT=1)
make test-v         # verbose
make test-cover     # coverage report → coverage.html
make test-race      # race detector
make bench          # benchmarks

# Component tests
go test ./pkg/lexer/...
go test ./pkg/parser/...
go test ./pkg/executor/...

SQLLogicTest

# Run against a live server
make sqllogictest URL=http://localhost:8080

# Quick smoke test
make sqllogictest-basic

# Download full SQLite corpus (~5M tests)
make sqllogictest-download
make sqllogictest