pizzasql/README.md

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 - Custom high-performance Zig backend with radix trie indexes
• Thread-Safe - Concurrent query execution with mutex-based locking

PizzaSQL provides SQLite SQL compatibility with PostgreSQL wire protocol support, making it easy to integrate with existing tools and libraries while maintaining full control over the SQL dialect.

---

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 -pg -pg-port 5432

# 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 -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
./pizzasql

# Single statement
./pizzasql -e "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
• 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 from source:

git clone https://github.com/danfragoso/pizzasql.git
cd pizzasql
make build

Start the Server

Option 1: HTTP API (recommended for web apps)

# Auto-launch PizzaKV and start HTTP server
./pizzasql -http -kv

# Server available at http://localhost:8080

Option 2: PostgreSQL Wire Protocol (recommended for existing PostgreSQL tools)

# Auto-launch PizzaKV and start PostgreSQL-compatible server
./pizzasql -pg -kv

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

# Or use any PostgreSQL client library

Option 3: Interactive CLI

# Auto-launch PizzaKV and start REPL
./pizzasql -kv

Your First Query

Using psql (PostgreSQL wire protocol):

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

-- Create table (SQLite syntax!)
CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  email TEXT UNIQUE
);

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

-- Query
SELECT * FROM users;

Using HTTP API:

# Create table
curl -X POST http://localhost:8080/query \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)"
  }'

# Insert with parameters
curl -X POST http://localhost:8080/query \
  -H "Content-Type: application/json" \
  -d '{
    "sql": "INSERT INTO users (name, email) VALUES (?, ?)",
    "params": ["Alice", "alice@example.com"]
  }'

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

Response:

{
  "columns": [
    {"name": "id", "type": "INTEGER"},
    {"name": "name", "type": "TEXT"},
    {"name": "email", "type": "TEXT"}
  ],
  "rows": [
    [1, "Alice", "alice@example.com"]
  ],
  "rowsAffected": 0,
  "lastInsertId": 0,
  "executionTime": "1.234ms"
}

---

Benchmarks

Performance comparison between PizzaSQL, SQLite, and PostgreSQL.

Operation	SQLite	PizzaSQL (HTTP)	PizzaSQL (PG Wire)	PostgreSQL
INSERT (1000 rows)	308 ops/s	138 ops/s	5780 ops/s	12820 ops/s
SELECT (no index)	288 q/s	128 q/s	165 q/s	414 q/s
CREATE INDEX	6 ms	69 ms	63 ms	31 ms
SELECT (indexed)	318 q/s	136 q/s	1562 q/s	2222 q/s
AGGREGATE	313 q/s	137 q/s	1052 q/s	980 q/s

Note: Wire protocol benchmarks (PizzaSQL PG Wire and PostgreSQL) use connection reuse with transactions, which is what client libraries do automatically. PizzaSQL's aggregate queries outperform PostgreSQL in this benchmark.

Run Your Own Benchmarks

./benchmarks/quick_bench.sh

---

PostgreSQL Wire Protocol Support

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

Protocol Features

• Universal compatibility - Works with thousands of PostgreSQL tools and libraries
• SQLite simplicity - No complex types, permissions, or schemas to manage
• Binary protocol - Efficient data transfer

Connecting with Client Libraries

Python (psycopg2):

import psycopg2

# Connect to PizzaSQL
conn = psycopg2.connect(
    host="localhost",
    port=5432,
    database="pizzasql"
)

# Use SQLite-compatible SQL
cur = conn.cursor()
cur.execute("""
    CREATE TABLE users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT
    )
""")

# Parameterized queries work as expected
cur.execute("INSERT INTO users (name, email) VALUES (%s, %s)", 
            ("Alice", "alice@example.com"))

# Query results
cur.execute("SELECT * FROM users WHERE name = %s", ("Alice",))
rows = cur.fetchall()
for row in rows:
    print(row)

conn.commit()
conn.close()

Node.js (node-postgres):

const { Client } = require('pg');

// Connect to PizzaSQL
const client = new Client({
  host: 'localhost',
  port: 5432,
  database: 'pizzasql'
});

await client.connect();

// Use SQLite-compatible SQL
await client.query(`
  CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT
  )
`);

// Parameterized queries
await client.query(
  'INSERT INTO users (name, email) VALUES ($1, $2)',
  ['Alice', 'alice@example.com']
);

// Query results
const res = await client.query(
  'SELECT * FROM users WHERE name = $1',
  ['Alice']
);
console.log(res.rows);

await client.end();

Go (lib/pq):

package main

import (
    "database/sql"
    _ "github.com/lib/pq"
)

func main() {
    // Connect to PizzaSQL
    db, err := sql.Open("postgres", 
        "host=localhost port=5432 dbname=pizzasql sslmode=disable")
    if err != nil {
        panic(err)
    }
    defer db.Close()

    // Use SQLite-compatible SQL
    _, err = db.Exec(`
        CREATE TABLE users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT
        )
    `)

    // Parameterized queries
    _, err = db.Exec(
        "INSERT INTO users (name, email) VALUES ($1, $2)",
        "Alice", "alice@example.com")

    // Query results
    rows, err := db.Query(
        "SELECT * FROM users WHERE name = $1",
        "Alice")
    defer rows.Close()

    for rows.Next() {
        var id int
        var name, email string
        rows.Scan(&id, &name, &email)
        fmt.Printf("%d: %s (%s)\n", id, name, email)
    }
}

Ruby (pg gem):

require 'pg'

# Connect to PizzaSQL
conn = PG.connect(
  host: 'localhost',
  port: 5432,
  dbname: 'pizzasql'
)

# Use SQLite-compatible SQL
conn.exec(<<-SQL)
  CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT
  )
SQL

# Parameterized queries
conn.exec_params(
  'INSERT INTO users (name, email) VALUES ($1, $2)',
  ['Alice', 'alice@example.com']
)

# Query results
result = conn.exec_params(
  'SELECT * FROM users WHERE name = $1',
  ['Alice']
)

result.each do |row|
  puts "#{row['id']}: #{row['name']} (#{row['email']})"
end

conn.close

PHP (PDO):

<?php
// Connect to PizzaSQL
$dsn = "pgsql:host=localhost;port=5432;dbname=pizzasql";
$pdo = new PDO($dsn);

// Use SQLite-compatible SQL
$pdo->exec("
    CREATE TABLE users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT
    )
");

// Parameterized queries
$stmt = $pdo->prepare(
    "INSERT INTO users (name, email) VALUES (?, ?)"
);
$stmt->execute(['Alice', 'alice@example.com']);

// Query results
$stmt = $pdo->prepare(
    "SELECT * FROM users WHERE name = ?"
);
$stmt->execute(['Alice']);
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach ($rows as $row) {
    echo "{$row['id']}: {$row['name']} ({$row['email']})\n";
}
?>

Rust (tokio-postgres):

use tokio_postgres::{NoTls, Error};

#[tokio::main]
async fn main() -> Result<(), Error> {
    // Connect to PizzaSQL
    let (client, connection) = tokio_postgres::connect(
        "host=localhost port=5432 dbname=pizzasql", 
        NoTls
    ).await?;

    tokio::spawn(async move {
        if let Err(e) = connection.await {
            eprintln!("connection error: {}", e);
        }
    });

    // Use SQLite-compatible SQL
    client.execute(
        "CREATE TABLE users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT
        )", &[]
    ).await?;

    // Parameterized queries
    client.execute(
        "INSERT INTO users (name, email) VALUES ($1, $2)",
        &[&"Alice", &"alice@example.com"]
    ).await?;

    // Query results
    let rows = client.query(
        "SELECT * FROM users WHERE name = $1",
        &[&"Alice"]
    ).await?;

    for row in rows {
        let id: i32 = row.get(0);
        let name: &str = row.get(1);
        let email: &str = row.get(2);
        println!("{}: {} ({})", id, name, email);
    }

    Ok(())
}

Command-Line Tools

psql (PostgreSQL CLI):

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

# Execute single command
psql -h localhost -p 5432 -d pizzasql \
  -c "SELECT * FROM users WHERE age > 25"

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

# CSV output
psql -h localhost -p 5432 -d pizzasql \
  -c "SELECT * FROM users" --csv > users.csv

pgcli (Enhanced PostgreSQL CLI):

pgcli postgresql://localhost:5432/pizzasql

DBeaver, pgAdmin, DataGrip:

• Connection type: PostgreSQL
• Host: localhost
• Port: 5432
• Database: pizzasql
• No username/password required

Important Notes

1. SQL Dialect: PizzaSQL uses SQLite SQL syntax, not PostgreSQL syntax

   • Use INTEGER PRIMARY KEY AUTOINCREMENT, not SERIAL
   • Use TEXT type, not VARCHAR with enforced length
   • SQLite type affinity rules apply

2. Parameter Placeholders: Client libraries use their standard placeholders

   • Python/Node.js: $1, $2, $3...
   • Go: $1, $2, $3...
   • PHP: ? or named parameters
   • These are automatically converted to PizzaSQL's ? placeholder

3. Compatibility: Works with PostgreSQL clients, not PostgreSQL-specific features

   • No schemas, roles, or permissions
   • No PostgreSQL-specific types (ARRAY, JSON, etc.)
   • Use SQLite functions, not PostgreSQL functions

---

Architecture

PizzaSQL is built with a clean, modular architecture that processes SQL queries through distinct stages:

graph TD
    Client[Client Applications<br/>CLI, HTTP API, PostgreSQL Protocol]

    Client --> Lexer

    subgraph PizzaSQL Core
        Lexer[Lexer - SQL Tokenizer<br/>• 100+ token types<br/>• 227,000 ops/sec]
        Parser[Parser - AST Builder<br/>• Hand-written recursive descent<br/>• 176,000 statements/sec<br/>• Operator precedence]
        Analyzer[Analyzer - Semantic Analysis<br/>• Type checking<br/>• Scope resolution<br/>• Function validation<br/>• Thread-safe sync.RWMutex]
        Executor[Executor - Query Engine<br/>• Query execution<br/>• Index optimization<br/>• Transaction management<br/>• Expression evaluation]

        Lexer --> Parser
        Parser --> Analyzer
        Analyzer --> Executor
    end

    Executor --> Storage[Storage Layer - PizzaKV<br/>• Custom high-performance Zig backend<br/>• Radix trie indexes<br/>• Persistent storage<br/>• Connection pooling]

    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
    style Lexer fill:#fff9c4,stroke:#fbc02d
    style Parser fill:#fff9c4,stroke:#fbc02d
    style Analyzer fill:#fff9c4,stroke:#fbc02d
    style Executor fill:#fff9c4,stroke:#fbc02d

---

Documentation

CLI Usage

Interactive REPL:

./pizzasql -kv

Built-in commands:

• help - Show available commands
• quit - Exit the REPL
• tables - List all tables
• clear - Clear screen

Single Statement:

./pizzasql -e "SELECT * FROM users LIMIT 10"

Piped Input:

cat schema.sql | ./pizzasql

Expression-Only Mode:

./pizzasql -e "SELECT 2 + 2 * 10"
# Result: 22

HTTP API

POST /query - Execute SQL Query

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

Response:

{
  "columns": [
    {"name": "id", "type": "INTEGER"},
    {"name": "name", "type": "TEXT"}
  ],
  "rows": [[1, "Alice"], [2, "Bob"]],
  "rowsAffected": 0,
  "lastInsertId": 0,
  "executionTime": "1.2ms"
}

POST /execute - Batch Execution

curl -X POST http://localhost:8080/execute \
  -H "Content-Type: application/json" \
  -d '{
    "statements": [
      {"sql": "INSERT INTO users (name) VALUES (?)", "params": ["Alice"]},
      {"sql": "INSERT INTO users (name) VALUES (?)", "params": ["Bob"]}
    ],
    "transaction": true
  }'

GET /schema/tables - List Tables

curl http://localhost:8080/schema/tables

GET /schema/tables/{name} - Table Schema

curl http://localhost:8080/schema/tables/users

GET /health - Health Check

curl http://localhost:8080/health

GET /metrics - Prometheus Metrics

curl http://localhost:8080/metrics

Database Export/Import

Export entire database:

./pizzasql -db mydb -o backup.sql

Export with DROP TABLE statements:

./pizzasql -db mydb -o backup.sql -drop

Export specific table:

./pizzasql -db mydb -table users -o users.sql

Export to CSV:

./pizzasql -db mydb -table users -o users.csv

Import SQL file:

./pizzasql -db mydb -i backup.sql

Import CSV:

./pizzasql -db mydb -table users -i users.csv -create-table

SQL Support

Data Types:

• INTEGER (INT, SMALLINT, BIGINT, BOOLEAN)
• REAL (FLOAT, DOUBLE, DECIMAL)
• TEXT (VARCHAR, CHAR, CHARACTER)
• BLOB (binary data)
• NUMERIC (flexible numeric)

Joins:

• INNER JOIN
• LEFT JOIN / LEFT OUTER JOIN
• RIGHT JOIN / RIGHT OUTER JOIN
• FULL OUTER JOIN
• CROSS JOIN

Aggregates:

• COUNT, COUNT(DISTINCT)
• SUM, AVG, MIN, MAX

Functions:

• String: UPPER, LOWER, LENGTH, SUBSTR, TRIM, REPLACE, CONCAT
• Numeric: ABS, ROUND, CEIL, FLOOR, MOD
• Null handling: COALESCE, NULLIF, IFNULL
• SQLite: printf, hex, random, glob, instr, zeroblob

Transaction Support:

BEGIN;
INSERT INTO accounts (name, balance) VALUES ('Alice', 1000);
UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
COMMIT;

-- Rollback on error
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE name = 'Alice';
ROLLBACK;

-- Savepoints
BEGIN;
INSERT INTO users (name) VALUES ('Alice');
SAVEPOINT sp1;
INSERT INTO users (name) VALUES ('Bob');
ROLLBACK TO SAVEPOINT sp1;
COMMIT;

Indexes:

-- Create index
CREATE INDEX idx_users_email ON users(email);

-- Multi-column index
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);

-- Unique index
CREATE UNIQUE INDEX idx_users_email ON users(email);

-- Drop index
DROP INDEX idx_users_email;

PRAGMA Statements:

-- Table schema
PRAGMA table_info(users);

-- List tables
PRAGMA table_list;

-- Database list
PRAGMA database_list;

-- Version
PRAGMA version;

Query Plans:

-- Show execution plan
EXPLAIN SELECT * FROM users WHERE id = 1;

-- Detailed query plan
EXPLAIN QUERY PLAN SELECT * FROM users WHERE id = 1;

Configuration

Command-Line Options:

# Database options
-kvaddr string    PizzaKV server address (default "localhost:8085")
-kv               Launch PizzaKV automatically
-kvflags string   Flags to pass to PizzaKV (e.g., "-iwal -port=9090")
-db string        Database name (default "pizzasql")
-e string         Execute single statement and exit

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

# HTTP server options
-http             Start HTTP server
-http-host string HTTP server host (default "localhost")
-http-port int    HTTP server port (default 8080)
-http-cors        Enable CORS headers
-http-auth        Enable authentication
-api-keys string  Comma-separated API keys

# Export/Import options
-o string         Output file for export
-i string         Input file for import
-table string     Specific table to export/import
-format string    Export/import format: sql, csv
-drop             Include DROP TABLE statements in export
-create-table     Create table if not exists (CSV import)
-ignore-errors    Continue import on errors

# Other options
-version          Print version and exit
-help             Show help message
-quiet            Disable query logging

---

Testing

PizzaSQL has comprehensive test coverage across all components.

Running Tests

# All tests
make test

# Specific component
make test-lexer
make test-parser
go test ./pkg/analyzer/...
go test ./pkg/executor/...

# With verbose output
make test-v

# With coverage report
make test-cover
open coverage.html

# With race detection
make test-race

# Benchmarks
make bench

Test Coverage

Component	Coverage	Test Count
Lexer	~95%	15 test functions
Parser	~90%	35+ test functions
Analyzer	~85%	20+ test functions
Executor	~80%	25+ test functions
HTTP Server	~75%	15+ test functions
PostgreSQL Server	~70%	10+ test functions

SQLLogicTest

PizzaSQL passes 100% of the SQLite SQLLogicTest suite - over 5 million individual SQL tests. This comprehensive test suite validates:

• Complex SQL query correctness
• SQLite compatibility and edge cases
• Type coercion and affinity
• Aggregates and joins
• Transaction semantics
• Index optimization