LuaDB

LuaDB is a lightweight, embeddable, zero-dependency Relational Database Management System (RDBMS) written 100% from scratch in pure Lua (compatible with Lua 5.1+, 5.4, 5.5, and LuaJIT).

It provides full SQL execution, Write-Ahead Logging (WAL) for ACID transactions, a B+Tree indexing engine, a pluggable Virtual File System (VFS) layer (Local Disk, In-Memory RAM, and Amazon S3 Object Storage with AWS SigV4 authentication), a Native JSON/JSONB Engine, a PostgreSQL Wire Protocol Gateway, Multi-Region Master-Master Active-Active Cluster Replication with Hybrid Logical Clock (HLC) conflict resolution, ALTER TABLE Schema Migrations, Foreign Key ON DELETE CASCADE Constraints, Common Table Expressions (WITH CTEs), and an independent Dark Room conformance test harness that validates SQL correctness against SQLite 3 as an external oracle.

---

Target Use Cases & Sweet Spots

1. Game Development (LÖVE2D, Defold, Roblox, Custom Lua Engines):
   • Zero-dependency embedded database for player save data, inventory systems, and skill trees.
   • Eliminates the need to cross-compile C extensions (lsqlite3) for multiplatform builds (iOS, Android, Nintendo Switch, WebAssembly).
2. Serverless & Edge Lua Environments (OpenResty, Nginx, Cloudflare Workers):
   • High-speed transient state management and API caching without native C-binding nightmares.
3. Embedded Systems & IoT:
   • Tiny footprint (~300 KB Lua memory consumption) for resource-constrained embedded Linux boards.
4. Telecom & CDR Processing (Kamailio, Asterisk):
   • SIP Call Detail Record local buffering with Kafka failover drain (see examples/05_kamailio_cdr_drain.lua).

---

Engine Architecture & Runtime Compatibility

Runtime Transparency: The entire database engine (storage, B+Tree, WAL, SQL engine, VFS, JSON, and CLI) runs on standard, un-extended PUC-Rio Lua 5.1+. Only the optional standalone network gateway server (bin/luadb_server.lua) uses LuaJIT FFI for non-blocking socket I/O.

---

Performance Metrics & Benchmarks

Run the built-in performance benchmark suite: lua tests/benchmark_spec.lua

• Write Throughput (INSERT TPS): ~1,750+ Transactions Per Second (batch WAL commit).
• Point Query Latency: < 2.2 ms for index and table range queries.
• Active Memory Footprint: ~295 KB Lua memory consumption under active query load.

---

Key Features

• Zero External Dependencies: 100% pure Lua. Embed directly into LÖVE2D, Roblox, OpenResty, CLI applications, or embedded Linux environments.
• Pluggable Storage Layer (VFS):
  • local: Direct disk storage using Lua binary io.
  • memory: High-throughput RAM storage for transient state and testing.
  • s3: Cloud object storage backed by AWS S3 with page caching and AWS SigV4 authentication.
• B+Tree Indexing Engine: Slotted 4KB binary pages, automatic secondary index maintenance on INSERT, UPDATE, and DELETE.
• ACID Transactions & Deterministic Recovery: Write-Ahead Logging (WAL) with BEGIN, COMMIT, ROLLBACK, and automated crash recovery fuzzing (tests/crash_recovery_spec.lua).
• SQL Engine — Full Conformance:
  • SELECT with projection, WHERE, AND/OR, LIKE (case-insensitive, SQLite-compatible), IS NULL / IS NOT NULL
  • ORDER BY multi-column (ascending/descending; evaluates before projection; validates column names)
  • GROUP BY with aggregate functions (COUNT(*), COUNT(col) excluding NULLs, SUM, AVG, MIN, MAX) — NULLs and empty strings ('') produce distinct groups via typed key tagging
  • LIMIT / OFFSET
  • INSERT INTO with correct NULL column positional storage
  • UPDATE, DELETE with WHERE predicates
  • CREATE / DROP TABLE, CREATE / DROP INDEX, ALTER TABLE
  • Prepared statements with ? / $1 parameter binding
• Native JSON / JSONB Support:
  • Sub-object extraction operator: details->'address'
  • Unquoted text scalar operator: details->>'city'
  • Chained path extractions and direct WHERE clause filtering on nested attributes.
• Foreign Keys & Referential Integrity:
  • FOREIGN KEY (...) REFERENCES ... ON DELETE CASCADE enforcement.
  • Full catalog persistence in WAL metadata across database restarts.
• PostgreSQL Wire Protocol Server:
  • Connect standard tools (psql, DBeaver, DataGrip, TablePlus) directly via the built-in Postgres v3.0 gateway (bin/luadb_server.lua).
• Multi-Region Master-Master Cluster:
  • Active-active node topology, persistent Hinted Handoff queueing for offline nodes, 24-hour TTL expiration logging, and automatic snapshot catch-up sync.
  • HLC Conflict Resolution: Real Hybrid Logical Clock state (last_pt, logical_lc) updated on both local and remote events. Determines write winners in concurrent master-master scenarios. Version history is exportable/importable for node restart durability. Full conflict history exposed via pg_replication_conflicts.
• Dark Room Conformance Test (tests/darkroom_spec.lua):
  • Portable conformance test suite executing 50 SQL test cases against SQLite 3 via SQLITE_BIN or system PATH.
  • Zero shared code between oracle and subject — LuaDB is treated as a pure black box.
  • Results compared row-by-row, field-by-field. Current result: 50/50 MATCH.

---

Quickstart

Installation

Clone the repository into your Lua project path:

git clone https://github.com/jncastilho/luadb.git

Embedded Usage

local luadb = require("luadb")

-- Open database handle
local db = luadb.open({
    driver = "local",
    storage_path = "app.db"
})

-- Create schema with Foreign Key constraints
db:exec([[
CREATE TABLE departments (
    id INT PRIMARY KEY,
    name TEXT
);
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name TEXT,
    dept_id INT,
    details JSONB,
    salary REAL,
    FOREIGN KEY (dept_id) REFERENCES departments(id) ON DELETE CASCADE
);
]])

-- Basic CRUD
db:exec("INSERT INTO departments VALUES (1, 'Engineering');")
db:exec("INSERT INTO employees VALUES (101, 'Alice', 1, '{\"role\": \"Lead\", \"level\": 5}', 125000.0);")

-- NULL column support
db:exec("INSERT INTO employees VALUES (102, 'Bob', 1, NULL, NULL);")

-- Multi-column ORDER BY
local rows = db:exec("SELECT name, salary FROM employees ORDER BY salary DESC, name ASC;")

-- GROUP BY with aggregates
local stats = db:exec("SELECT dept_id, COUNT(*), COUNT(salary), AVG(salary) FROM employees GROUP BY dept_id;")

-- LIMIT / OFFSET pagination
local page = db:exec("SELECT name FROM employees ORDER BY id LIMIT 10 OFFSET 20;")

-- IS NULL / IS NOT NULL filtering
local unfilled = db:exec("SELECT name FROM employees WHERE salary IS NULL;")

-- JSON extraction & CTE
local result = db:exec([[
WITH eng_staff AS (
    SELECT name, salary, details->>'role' AS role
    FROM employees WHERE dept_id = 1
)
SELECT * FROM eng_staff WHERE salary > 100000;
]])

for _, row in ipairs(result) do
    print(row.name, row.role, row.salary)
end

-- Streaming coroutine cursor
for row in db:cursor("SELECT * FROM employees ORDER BY salary DESC;") do
    print(row.name, row.salary)
end

-- Prepared statements
local stmt = db:prepare("INSERT INTO employees VALUES (?, ?, ?, ?, ?);")
stmt:exec(103, "Carol", 1, nil, 98000)

-- Transactions
db:begin()
db:exec("UPDATE employees SET salary = 130000 WHERE name = 'Alice';")
db:rollback()   -- or db:commit()

db:gc()
db:close()

---

Interfaces & Server Modes

1. Interactive CLI (bin/luadb_cli.lua)

Run the interactive SQL console:

lua bin/luadb_cli.lua mydata.db

luadb=> CREATE TABLE demo (id INT PRIMARY KEY, title TEXT);
luadb=> INSERT INTO demo VALUES (1, 'Hello World');
luadb=> SELECT * FROM demo;
+----+-------------+
| id | title       |
+----+-------------+
| 1  | Hello World |
+----+-------------+
luadb=> \dt
luadb=> \q

2. Standalone PostgreSQL Wire Server (bin/luadb_server.lua)

Start the network database server listening on port 5433:

luajit bin/luadb_server.lua mydata.db 5433

Connect using standard psql:

psql -h 127.0.0.1 -p 5433 -U postgres -d luadb

---

Runnable Examples (examples/)

---

Verification Suite

To run the complete master test runner (16 suites — unit tests, dark room conformance, crash recovery fuzzing, and benchmarks):

lua tests/run_all.lua
luajit tests/run_all.lua

Test Suites

Dark Room Conformance Test

tests/darkroom_spec.lua is the project's independent comparative test harness:

SQLITE_BIN=sqlite3 lua tests/darkroom_spec.lua

• Oracle: SQLite 3 binary (SQLITE_BIN env var or PATH — zero LuaDB code in oracle path)
• Subject: LuaDB embedded API (treated as a pure black box)
• Method: Identical SQL fired at both engines; results compared row-by-row, field-by-field
• Result: 50/50 MATCH — LuaDB output is byte-identical to SQLite on all 50 test cases

---

Architecture Specification

For full technical specifications (slotted binary page structure, B+Tree split algorithm, WAL protocol, HLC conflict resolution, replication frame format, and VFS internals), see TECHNICAL_ARCHITECTURE.md.

---

License

MIT License. Free for personal and commercial use.