tinysql

README

TinySQL

TinySQL is a lightweight, educational SQL database engine written in pure Go. It implements a comprehensive subset of SQL features using only Go's standard library, making it perfect for learning database internals and for applications that need a simple embedded SQL database.

Quick start

Install

go get github.com/SimonWaldherr/tinySQL@latest

Use the engine directly

package main

import (
    "context"
    "fmt"
    tsql "github.com/SimonWaldherr/tinySQL"
)

func main() {
    db := tsql.NewDB()

    p := tsql.NewParser(`CREATE TABLE users (id INT, name TEXT)`)
    st, _ := p.ParseStatement()
    tsql.Execute(context.Background(), db, "default", st)

    p = tsql.NewParser(`INSERT INTO users VALUES (1, 'Alice')`)
    st, _ = p.ParseStatement()
    tsql.Execute(context.Background(), db, "default", st)

    p = tsql.NewParser(`SELECT id, name FROM users`)
    st, _ = p.ParseStatement()
    rs, _ := tsql.Execute(context.Background(), db, "default", st)

    for _, row := range rs.Rows {
        fmt.Println(tsql.GetVal(row, "id"), tsql.GetVal(row, "name"))
    }
}

Use with database/sql

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/SimonWaldherr/tinySQL/internal/driver"
)

func main() {
    db, _ := sql.Open("tinysql", "mem://?tenant=default")
    defer db.Close()

    db.Exec(`CREATE TABLE t (id INT, name TEXT)`)
    db.Exec(`INSERT INTO t VALUES (?, ?)`, 1, "Alice")

    row := db.QueryRow(`SELECT name FROM t WHERE id = ?`, 1)
    var name string
    _ = row.Scan(&name)
    fmt.Println(name)
}

Run tests

# no cache
go test ./... -count=1

# with coverage output
go test -coverprofile=coverage.out ./...

Goals (and non-goals)

• Lightweight, educational SQL engine in pure Go
• Useful for embeddings, demos, and learning database internals
• Not intended as a production-grade relational database

Requirements

• Go 1.25+ (see go.mod)

DSN (Data Source Name) Format

When using the database/sql driver:

• In-memory database: mem://?tenant=<tenant_name>
• File-based database: file:/path/to/db.dat?tenant=<tenant_name>&autosave=1

Parameters:

• tenant - Tenant name for multi-tenancy (required)
• autosave - Auto-save to file (optional, for file-based databases)

Limitations

TinySQL is designed for educational purposes

Testing

Run the test suite:

# Run all tests
go test ./...

# Run tests with verbose output
go test -v ./...

# Run tests multiple times to check consistency
go test -v -count=3 ./...

Contributing

This is an educational project. Contributions that improve code clarity, add comprehensive examples, or enhance the learning experience are welcome.

Educational Goals

TinySQL demonstrates:

• SQL parsing and AST construction
• Query execution and optimization basics
• Database storage concepts
• Go's database/sql driver interface
• 3-valued logic (NULL semantics)
• JSON data handling in SQL
• Multi-tenancy patterns

Perfect for computer science students, developers learning database internals, or anyone who wants to understand how SQL databases work under the hood.

Documentation

Overview

Package tinysql provides a lightweight, embeddable SQL database for Go applications.

TinySQL is an educational SQL database that demonstrates core database concepts including:

• SQL parsing and execution (DDL, DML, SELECT with joins, aggregates, CTEs)
• Multi-Version Concurrency Control (MVCC) with snapshot isolation
• Write-Ahead Logging (WAL) for durability and crash recovery
• Multi-tenancy support for isolated data namespaces
• In-memory and persistent storage with GOB serialization

Basic Usage

Create a database, execute SQL, and query results:

db := tinysql.NewDB()
ctx := context.Background()

// Parse and execute DDL
stmt, _ := tinysql.ParseSQL("CREATE TABLE users (id INT, name TEXT)")
tinysql.Execute(ctx, db, "default", stmt)

// Insert data
stmt, _ = tinysql.ParseSQL("INSERT INTO users VALUES (1, 'Alice')")
tinysql.Execute(ctx, db, "default", stmt)

// Query data
stmt, _ = tinysql.ParseSQL("SELECT * FROM users WHERE id = 1")
rs, _ := tinysql.Execute(ctx, db, "default", stmt)
for _, row := range rs.Rows {
    fmt.Println(row)
}

Persistence

Save and load database snapshots:

// Save to file
tinysql.SaveToFile(db, "mydb.gob")

// Load from file
db, err := tinysql.LoadFromFile("mydb.gob")

Advanced Features

Enable MVCC for concurrent transactions:

mvcc := db.MVCC()
tx, _ := mvcc.BeginTx(tinysql.SnapshotIsolation)
// ... perform transaction operations ...
mvcc.CommitTx(tx.ID)

Enable WAL for durability:

wal, _ := tinysql.NewAdvancedWAL("data/wal.log")
db.AttachAdvancedWAL(wal)

Query Compilation

Pre-compile queries for better performance:

cache := tinysql.NewQueryCache(100)
query, _ := cache.Compile("SELECT * FROM users WHERE id = ?")
rs, _ := query.Execute(ctx, db, "default")

For more examples, see the example_test.go file in the repository.

Example

Example demonstrates the usage of the TinySQL engine

package main

import (
	"context"
	"fmt"
	"strings"

	tsql "github.com/SimonWaldherr/tinySQL"
)

func main() {
	db := tsql.NewDB()

	dedent := func(s string) string {
		trimmed := strings.TrimSpace(s)
		if !strings.Contains(trimmed, "\n") {
			return trimmed
		}
		lines := strings.Split(trimmed, "\n")
		indent := -1
		for _, line := range lines[1:] {
			if strings.TrimSpace(line) == "" {
				continue
			}
			leading := len(line) - len(strings.TrimLeft(line, " \t"))
			if indent == -1 || leading < indent {
				indent = leading
			}
		}
		if indent > 0 {
			for i := 1; i < len(lines); i++ {
				if strings.TrimSpace(lines[i]) == "" {
					lines[i] = ""
					continue
				}
				if len(lines[i]) >= indent {
					lines[i] = lines[i][indent:]
				}
			}
		}
		for i, line := range lines {
			lines[i] = strings.TrimRight(line, " \t")
		}
		return strings.Join(lines, "\n")
	}

	run := func(sql string) {
		display := dedent(sql)
		fmt.Println("SQL>", display)
		p := tsql.NewParser(sql)
		st, err := p.ParseStatement()
		if err != nil {
			fmt.Println("ERR:", err)
			fmt.Println()
			return
		}
		rs, err := tsql.Execute(context.Background(), db, "default", st)
		if err != nil {
			fmt.Println("ERR:", err)
			fmt.Println()
			return
		}
		if rs == nil {
			fmt.Println()
			return
		}
		if len(rs.Rows) == 1 && len(rs.Cols) == 1 && (rs.Cols[0] == "updated" || rs.Cols[0] == "deleted") {
			if val, ok := tsql.GetVal(rs.Rows[0], rs.Cols[0]); ok {
				fmt.Printf("%s: %v\n\n", rs.Cols[0], val)
				return
			}
		}
		displayCols := make([]string, len(rs.Cols))
		for i, col := range rs.Cols {
			parts := strings.Split(col, ".")
			displayCols[i] = parts[len(parts)-1]
		}
		fmt.Println(strings.Join(displayCols, " | "))
		for _, row := range rs.Rows {
			cells := make([]string, len(rs.Cols))
			for i, col := range rs.Cols {
				if v, ok := tsql.GetVal(row, col); ok {
					cells[i] = fmt.Sprint(v)
				} else {
					cells[i] = ""
				}
			}
			fmt.Println(strings.Join(cells, " | "))
		}
		fmt.Println()
	}

	// --- Create table and seed data ---
	run(`CREATE TABLE users (
		id INT,
		name TEXT,
		active BOOL,
		score INT
	)`)

	run(`INSERT INTO users (id, name, active, score) VALUES (1, 'Alice', true, 40)`)
	run(`INSERT INTO users (id, name, active, score) VALUES (2, 'Bob', false, 25)`)
	run(`INSERT INTO users (id, name, active, score) VALUES (3, 'Carol', true, 30)`)

	// --- Basic reads ---
	run(`SELECT id, name, active, score FROM users ORDER BY id`)
	run(`SELECT name, score FROM users WHERE active = true ORDER BY score DESC`)

	// --- Update a row ---
	run(`UPDATE users SET score = 50 WHERE name = 'Bob'`)
	run(`SELECT name, score FROM users ORDER BY id`)

	// --- Aggregate summary ---
	run(`SELECT COUNT(*) AS total_users, SUM(score) AS total_score FROM users`)

	// --- Delete inactive rows ---
	run(`DELETE FROM users WHERE active = false`)
	run(`SELECT name FROM users ORDER BY id`)

}

Output:

SQL> CREATE TABLE users (
	id INT,
	name TEXT,
	active BOOL,
	score INT
)

SQL> INSERT INTO users (id, name, active, score) VALUES (1, 'Alice', true, 40)

SQL> INSERT INTO users (id, name, active, score) VALUES (2, 'Bob', false, 25)

SQL> INSERT INTO users (id, name, active, score) VALUES (3, 'Carol', true, 30)

SQL> SELECT id, name, active, score FROM users ORDER BY id
id | name | active | score
1 | Alice | true | 40
2 | Bob | false | 25
3 | Carol | true | 30

SQL> SELECT name, score FROM users WHERE active = true ORDER BY score DESC
name | score
Alice | 40
Carol | 30

SQL> UPDATE users SET score = 50 WHERE name = 'Bob'
updated: 1

SQL> SELECT name, score FROM users ORDER BY id
name | score
Alice | 40
Bob | 50
Carol | 30

SQL> SELECT COUNT(*) AS total_users, SUM(score) AS total_score FROM users
total_users | total_score
3 | 120

SQL> DELETE FROM users WHERE active = false
deleted: 1

SQL> SELECT name FROM users ORDER BY id
name
Alice
Carol

Index

• Constants
• func GetVal(row Row, name string) (any, bool)
• func SaveToFile(db *DB, filename string) error
• type AdvancedWAL
  • func NewAdvancedWAL(path string) (*AdvancedWAL, error)
  • func OpenAdvancedWAL(config AdvancedWALConfig) (*AdvancedWAL, error)
• type AdvancedWALConfig
• type ColType
• type Column
• type CompiledQuery
  • func Compile(cache *QueryCache, sql string) (*CompiledQuery, error)
  • func MustCompile(cache *QueryCache, sql string) *CompiledQuery
• type DB
  • func LoadFromFile(filename string) (*DB, error)
  • func NewDB() *DB
• type IsolationLevel
• type LSN
• type MVCCManager
• type Parser
  • func NewParser(sql string) *Parser
• type QueryCache
  • func NewQueryCache(maxSize int) *QueryCache
• type ResultSet
  • func Execute(ctx context.Context, db *DB, tenant string, stmt Statement) (*ResultSet, error)
  • func ExecuteCompiled(ctx context.Context, db *DB, tenant string, compiled *CompiledQuery) (*ResultSet, error)
• type Row
• type Statement
  • func MustParseSQL(sql string) Statement
  • func ParseSQL(sql string) (Statement, error)
• type Table
  • func NewTable(name string, cols []Column, isTemp bool) *Table
• type Timestamp
• type TxContext
• type TxID
• type TxStatus
• type WALOperationType
• type WALRecord

Examples

• Package

Constants

const (
	TxStatusInProgress = storage.TxStatusInProgress // Transaction is active
	TxStatusCommitted  = storage.TxStatusCommitted  // Transaction committed successfully
	TxStatusAborted    = storage.TxStatusAborted    // Transaction was aborted
)

Transaction status constants

const (
	WALOpBegin      = storage.WALOpBegin      // Transaction begin
	WALOpInsert     = storage.WALOpInsert     // Row insert
	WALOpUpdate     = storage.WALOpUpdate     // Row update
	WALOpDelete     = storage.WALOpDelete     // Row delete
	WALOpCommit     = storage.WALOpCommit     // Transaction commit
	WALOpAbort      = storage.WALOpAbort      // Transaction abort
	WALOpCheckpoint = storage.WALOpCheckpoint // Checkpoint operation
)

WAL operation type constants

Functions

func GetVal

func GetVal(row Row, name string) (any, bool)

GetVal retrieves a value from a result row by column name (case-insensitive).

Returns the value and true if the column exists, or nil and false otherwise. This is the recommended way to access row data as it handles case-insensitivity.

Example:

for _, row := range rs.Rows {
    id, ok := tinysql.GetVal(row, "id")
    if ok {
        fmt.Printf("ID: %v\n", id)
    }

    name, _ := tinysql.GetVal(row, "Name") // Case-insensitive
    fmt.Printf("Name: %v\n", name)
}

For type-safe access, use type assertion after retrieving the value.

func SaveToFile

func SaveToFile(db *DB, filename string) error

SaveToFile serializes the entire database to a GOB file for persistence.

This creates a snapshot of all tables, rows, and metadata for all tenants. The file can be loaded later with LoadFromFile to restore the database state.

Example:

err := tinysql.SaveToFile(db, "mydb.gob")
if err != nil {
    log.Fatal(err)
}

Note: This saves the current state only. For durability during crashes, use AttachAdvancedWAL to enable write-ahead logging.

Types

type AdvancedWAL

type AdvancedWAL = storage.AdvancedWAL

AdvancedWAL manages row-level write-ahead logging with REDO/UNDO support. Provides durability, crash recovery, and point-in-time recovery.

func NewAdvancedWAL

func NewAdvancedWAL(path string) (*AdvancedWAL, error)

NewAdvancedWAL creates a new write-ahead log manager with default configuration.

The WAL logs all database modifications (INSERT, UPDATE, DELETE) to disk before applying them. This enables:

• Crash recovery: replay committed transactions after restart
• Point-in-time recovery: restore to any previous state
• Durability: changes survive system crashes

Example:

wal, err := tinysql.NewAdvancedWAL("data/wal.log")
if err != nil {
    log.Fatal(err)
}
defer wal.Close()

db.AttachAdvancedWAL(wal)

// Now all database modifications are logged
stmt, _ := tinysql.ParseSQL("INSERT INTO users VALUES (1, 'Alice')")
tinysql.Execute(ctx, db, "default", stmt)

The path parameter specifies the WAL file location.

func OpenAdvancedWAL

func OpenAdvancedWAL(config AdvancedWALConfig) (*AdvancedWAL, error)

OpenAdvancedWAL creates or opens a WAL with custom configuration.

This provides full control over WAL behavior including checkpoint intervals, compression, and buffer sizes.

Example:

wal, err := tinysql.OpenAdvancedWAL(tinysql.AdvancedWALConfig{
    Path:               "data/wal.log",
    CheckpointPath:     "data/checkpoint",
    CheckpointEvery:    5000,
    CheckpointInterval: 10 * time.Minute,
    Compress:           true,
    BufferSize:         128 * 1024,
})

Returns the WAL instance or an error if initialization fails.

type AdvancedWALConfig

type AdvancedWALConfig = storage.AdvancedWALConfig

AdvancedWALConfig configures the advanced WAL behavior.

type ColType

type ColType = storage.ColType

ColType enumerates supported column data types (INT, TEXT, BOOL, JSON, etc.).

const (
	// Integer types
	IntType   ColType = storage.IntType
	Int8Type  ColType = storage.Int8Type
	Int16Type ColType = storage.Int16Type
	Int32Type ColType = storage.Int32Type
	Int64Type ColType = storage.Int64Type
	UintType  ColType = storage.UintType
	Uint8Type ColType = storage.Uint8Type

	// Floating point types
	Float32Type ColType = storage.Float32Type
	Float64Type ColType = storage.Float64Type
	FloatType   ColType = storage.FloatType

	// String types
	StringType ColType = storage.StringType
	TextType   ColType = storage.TextType

	// Boolean type
	BoolType ColType = storage.BoolType

	// Time types
	TimeType      ColType = storage.TimeType
	DateType      ColType = storage.DateType
	DateTimeType  ColType = storage.DateTimeType
	TimestampType ColType = storage.TimestampType

	// Complex types
	JsonType  ColType = storage.JsonType
	JsonbType ColType = storage.JsonbType
)

type Column

type Column = storage.Column

Column represents a table column with a name and type.

type CompiledQuery

type CompiledQuery = engine.CompiledQuery

CompiledQuery represents a pre-parsed SQL statement that can be executed multiple times efficiently.

func Compile

func Compile(cache *QueryCache, sql string) (*CompiledQuery, error)

Compile parses and caches a SQL query for reuse, similar to regexp.Compile.

The compiled query can be executed multiple times without re-parsing. This is useful for queries executed repeatedly with different parameters.

Example:

cache := tinysql.NewQueryCache(100)
query, err := tinysql.Compile(cache, "SELECT * FROM users WHERE active = true")
if err != nil {
    log.Fatal(err)
}

// Execute the compiled query
rs, _ := query.Execute(ctx, db, "default")

Returns a CompiledQuery that can be executed via ExecuteCompiled or query.Execute.

func MustCompile

func MustCompile(cache *QueryCache, sql string) *CompiledQuery

MustCompile is like Compile but panics on error, similar to regexp.MustCompile.

Useful for static queries in tests or initialization where errors are unexpected.

Example:

cache := tinysql.NewQueryCache(100)
query := tinysql.MustCompile(cache, "SELECT * FROM users")

type DB

type DB = storage.DB

DB represents a multi-tenant database instance with support for MVCC and WAL. Use NewDB to create a new instance.

func LoadFromFile

func LoadFromFile(filename string) (*DB, error)

LoadFromFile deserializes a database from a GOB file created by SaveToFile.

This restores all tables, rows, and metadata from the file. The returned database instance is ready for use immediately.

Example:

db, err := tinysql.LoadFromFile("mydb.gob")
if err != nil {
    log.Fatal(err)
}
defer db.Close()

Returns a new DB instance or an error if the file cannot be read.

func NewDB

func NewDB() *DB

NewDB creates a new in-memory multi-tenant database instance with MVCC support.

The database starts empty with no tables. Use SQL DDL statements to create tables, or load from a file with LoadFromFile.

Example:

db := tinysql.NewDB()
defer db.Close() // Optional cleanup

The returned DB is safe for concurrent use and includes an integrated MVCC manager.

type IsolationLevel

type IsolationLevel = storage.IsolationLevel

IsolationLevel defines transaction isolation semantics.

const (
	ReadCommitted     IsolationLevel = storage.ReadCommitted     // Read only committed data
	RepeatableRead    IsolationLevel = storage.RepeatableRead    // Repeatable reads within transaction
	SnapshotIsolation IsolationLevel = storage.SnapshotIsolation // Full snapshot isolation
	Serializable      IsolationLevel = storage.Serializable      // Serializable transactions with conflict detection
)

Isolation level constants

type LSN

type LSN = storage.LSN

LSN (Log Sequence Number) provides total ordering of log records.

type MVCCManager

type MVCCManager = storage.MVCCManager

MVCCManager coordinates multi-version concurrency control with snapshot isolation. Provides transaction begin/commit/abort operations and visibility checking.

type Parser

type Parser = engine.Parser

Parser parses SQL text into executable Statement objects. Create with NewParser and call ParseStatement() to parse.

func NewParser

func NewParser(sql string) *Parser

NewParser creates a new SQL parser for the provided input string.

The parser supports a practical subset of SQL including:

• DDL: CREATE TABLE, DROP TABLE
• DML: INSERT, UPDATE, DELETE
• Queries: SELECT with WHERE, JOIN, GROUP BY, HAVING, ORDER BY, LIMIT, OFFSET
• Set operations: UNION, INTERSECT, EXCEPT
• CTEs: WITH clauses
• Expressions: arithmetic, comparisons, functions, aggregates

Example:

parser := tinysql.NewParser("SELECT * FROM users WHERE active = true")
stmt, err := parser.ParseStatement()
if err != nil {
    log.Fatal(err)
}

For better performance with repeated queries, consider using QueryCache.

type QueryCache

type QueryCache = engine.QueryCache

QueryCache stores compiled queries for reuse, similar to prepared statements. Create with NewQueryCache and use Compile() to cache queries.

func NewQueryCache

func NewQueryCache(maxSize int) *QueryCache

NewQueryCache creates a new query cache for compiling and reusing parsed queries.

Query compilation parses SQL once and reuses the AST for multiple executions, similar to prepared statements. This improves performance for repeated queries.

The maxSize parameter limits the number of cached queries (LRU eviction). Use 0 for unlimited cache size (not recommended for production).

Example:

cache := tinysql.NewQueryCache(100)
query, _ := cache.Compile("SELECT * FROM users WHERE id = ?")

// Execute multiple times
for _, id := range userIDs {
    rs, _ := query.Execute(ctx, db, "default")
    // process results...
}

type ResultSet

type ResultSet = engine.ResultSet

ResultSet holds query results with column names and data rows. Returned by SELECT queries and available for inspection.

func Execute

func Execute(ctx context.Context, db *DB, tenant string, stmt Statement) (*ResultSet, error)

Execute executes a parsed SQL statement against the database.

The context allows for cancellation and timeout control. The tenant parameter provides data isolation - each tenant has its own namespace of tables.

For DDL statements (CREATE TABLE, DROP TABLE), returns nil ResultSet. For DML statements (INSERT, UPDATE, DELETE), returns nil ResultSet. For SELECT queries, returns ResultSet with columns and rows.

Example:

ctx := context.Background()
stmt, _ := tinysql.ParseSQL("SELECT * FROM users WHERE age > 18")
rs, err := tinysql.Execute(ctx, db, "default", stmt)
if err != nil {
    log.Fatal(err)
}

// Process results
for _, row := range rs.Rows {
    name, _ := tinysql.GetVal(row, "name")
    fmt.Println(name)
}

The tenant parameter is required. Use "default" for single-tenant applications.

func ExecuteCompiled

func ExecuteCompiled(ctx context.Context, db *DB, tenant string, compiled *CompiledQuery) (*ResultSet, error)

ExecuteCompiled executes a pre-compiled query against the database.

This is more efficient than Execute for queries executed repeatedly, as parsing is done only once during compilation.

Example:

cache := tinysql.NewQueryCache(100)
query, _ := cache.Compile("SELECT * FROM users")
rs, err := tinysql.ExecuteCompiled(ctx, db, "default", query)

Returns ResultSet for SELECT queries, nil for DDL/DML statements.

type Row

type Row = engine.Row

Row represents a single result row mapped by column name (case-insensitive). Keys include both qualified (table.column) and unqualified (column) names.

type Statement

type Statement = engine.Statement

Statement is the base interface for all parsed SQL statements. Use Parser.ParseStatement() to obtain a Statement from SQL text.

func MustParseSQL

func MustParseSQL(sql string) Statement

MustParseSQL is like ParseSQL but panics if parsing fails. Useful for static SQL in tests or initialization code.

Example:

stmt := tinysql.MustParseSQL("CREATE TABLE users (id INT, name TEXT)")

func ParseSQL

func ParseSQL(sql string) (Statement, error)

ParseSQL is a convenience function that creates a parser and parses a SQL statement in one call. Equivalent to NewParser(sql).ParseStatement().

Example:

stmt, err := tinysql.ParseSQL("SELECT id, name FROM users")
if err != nil {
    log.Fatal(err)
}

Returns the parsed Statement or an error if parsing fails.

type Table

type Table = storage.Table

Table represents a database table with columns and rows. Tables are created via CREATE TABLE statements and accessed through the DB.

func NewTable

func NewTable(name string, cols []Column, isTemp bool) *Table

NewTable creates a new table with the specified columns.

This is a low-level API. Normally tables are created via CREATE TABLE statements. Use this when programmatically building table structures.

Example:

cols := []tinysql.Column{
    {Name: "id", Type: tinysql.IntType},
    {Name: "name", Type: tinysql.TextType},
}
table := tinysql.NewTable("users", cols, false)
db.Put("default", table)

The isTemp parameter indicates if this is a temporary table (not persisted).

type Timestamp

type Timestamp = storage.Timestamp

Timestamp represents a logical timestamp for MVCC visibility checks.

type TxContext

type TxContext = storage.TxContext

TxContext holds the state of an active transaction including read/write sets and isolation level.

type TxID

type TxID = storage.TxID

TxID represents a unique transaction identifier.

type TxStatus

type TxStatus = storage.TxStatus

TxStatus represents the current state of a transaction.

type WALOperationType

type WALOperationType = storage.WALOperationType

WALOperationType defines the type of WAL operation.

type WALRecord

type WALRecord = storage.WALRecord

WALRecord represents a single log entry with before/after images.