# PizzaSQL

<div align="center">

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

</div>

---

## 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**
```bash
# 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**
```bash
# 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**
```bash
# 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:**
```bash
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:**
```bash
make build          # output: ./bin/pizzasql
```

### Start the Server

**Option 1: HTTP API**
```bash
pizzasql -kv -http
# Listening on http://localhost:8080
```

**Option 2: PostgreSQL wire protocol**
```bash
pizzasql -kv -pg
# psql -h localhost -p 5432 -d pizzasql
```

**Option 3: Both at once**
```bash
pizzasql -kv -http -pg
```

**Option 4: Interactive REPL**
```bash
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`](PKBFI_STORAGE_MIGRATION.md).

### Your First Query

**Using psql:**
```sql
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:**
```bash
curl -X POST http://localhost:8080/query \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT * FROM users"}'
```

**Response:**
```json
{
  "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`):
```bash
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

```bash
# 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):**
```python
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):**
```javascript
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):**
```go
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

```bash
# 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. 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; Bind requests must use text parameters/all-text results. Describe, binary results, and nonzero Execute row limits fail with SQLSTATE `0A000` before executing side effects. Portals are one-shot, not resumable. Unsupported binary parameter OIDs also report `0A000`.
4. **No PostgreSQL-specific features**: no schemas, roles, `ARRAY`, `JSONB`, etc.

---

## Architecture

```mermaid
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

```bash
# 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

```bash
# 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:**
```bash
pizzasql -kv -db mydb -i source.db
```

**Keep going on errors** (e.g. duplicate rows or unsupported DDL):
```bash
pizzasql -kv -db mydb -i source.db -ignore-errors
```

**Insert into an existing database** (skip `CREATE TABLE`, only insert rows):
```bash
# Not yet exposed as a CLI flag — use the HTTP API's create_tables=false parameter
```

**HTTP API — multipart upload:**
```bash
curl -X POST http://localhost:8080/import \
  -H "X-Database: mydb" \
  -F "file=@source.db"
```

**HTTP API — raw body** with explicit format:
```bash
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:**
```json
{
  "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:**
```sql
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:**
```sql
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:**
```sql
PRAGMA table_info(users);
PRAGMA table_list;
PRAGMA database_list;
```

---

## Testing

```bash
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

```bash
# 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
```