PizzaSQL

**A SQL database built from scratch in Go** SQLite-compatible SQL · PostgreSQL wire protocol · HTTP/JSON API · Built-in storage [![Go Version](https://img.shields.io/badge/Go-1.21+-00ADD8?logo=go)](https://go.dev/) [![Build Status](https://img.shields.io/badge/build-passing-brightgreen.svg)]() [![Test Coverage](https://img.shields.io/badge/coverage-90%25-brightgreen.svg)]() [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

SQL Dialect: PizzaSQL uses SQLite syntax, not PostgreSQL syntax
- Use INTEGER PRIMARY KEY AUTOINCREMENT, not SERIAL
- Use TEXT, not VARCHAR with enforced length
Parameter placeholders: use $1, $2... (PostgreSQL style) or ? (SQLite style). PG Bind rewrites $n only outside quoted regions and line/block comments. Initialization probes such as SELECT version() and SHOW server_version match complete token sequences, never substrings inside SQL literals or unrelated projections.
Extended protocol scope: Parse accepts one command; Bind requests must use text parameters/all-text results. Describe returns ParameterDescription then NoData without executing anything; Execute still sends the real RowDescription. Binary results and nonzero Execute row limits fail with SQLSTATE 0A000 before side effects. Portals are one-shot, not resumable. Unsupported binary parameter OIDs also report 0A000.
No PostgreSQL-specific features: no schemas, roles, ARRAY, JSONB, etc.

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):

?format=sqlite query param
Filename extension: .db, .sqlite, .sqlite3
Content-Type: application/x-sqlite3 or application/octet-stream
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.

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

README.md 18 KB Histórico Raw

PizzaSQL

What is PizzaSQL?

Access Methods

Architecture

Features

Core SQL Operations

Multiple Access Methods

SQLite Compatibility

Performance & Architecture

Quick Start

Installation

Start the Server

Your First Query

Process Management

Benchmarks

Run Benchmarks

PostgreSQL Wire Protocol Support

Connecting with Client Libraries

Command-Line Tools

Important Notes

Architecture

Documentation

CLI Reference

HTTP API

Database Export / Import

SQL export/import

SQLite .db import

SQL Support

Testing

SQLLogicTest

README.md 18 KB

Histórico Raw

SQLite `.db` import