PL/SQLite is a high-performance, loadable C extension for SQLite that introduces a fully transactional procedural language. It solves performance bottlenecks by using a Transpiler Architecture that runs logic directly inside the database memory space.

The core of PL/SQLite is implemented in Odin, a data-oriented language designed for high performance and low-level control.

• Deterministic Memory: Unlike Go or Java, Odin has no Garbage Collector (GC), allowing the VM to manage memory manually and predictably within SQLite's memory context.
• Safety & Productivity: Odin provides modern features like slices, tagged unions, and strong typing, making the transpiler and runtime logic safer and easier to maintain than pure C.
• LLVM-Powered: Leverages the LLVM backend for aggressive optimizations and easy cross-compilation to Windows, Linux, and macOS.

• Named Scopes: Secure namespacing for variables. Each procedure owns its scope, preventing accidental overlaps while allowing explicit cross-procedure access using the @proc_name.variable syntax.
• Transpiler Architecture: Automatically converts high-level PL/SQLite source code into optimized "Engine SQL" function calls.
• Zero-Copy Runtime: Executes logic without the overhead of context-switching between your application language and the database. Strings are passed as pointers (zero-copy) for reads.
• Statement Caching: Automatically caches prepared SQLite statements within procedure scopes, drastically reducing overhead for loops and recursive calls.
• Scope Persistence: Reuses memory scopes and variable maps during loops, eliminating allocation churn for high-performance iteration.
• Atomic Transactions: Procedures are wrapped in SQLite SAVEPOINTs, ensuring that any error triggers a full rollback.

The PL/SQLite extension integrates a transpiler and a virtual machine directly into the SQLite process.

+-----------------------+       +-------------------------+
|   PL/SQLite Source    | ----> |   register_plsql()      |
|                       |       |      (Transpiler)       |
|  DECLARE x = 10;      |       +-----------+-------------+
|  CALL other_proc();   |                   |
+-----------------------+                   v
                                +-------------------------+
                                |  __plsql_procedures DB  |
                                | (Stored Transpiled SQL) |
                                +-----------+-------------+
                                            |
                                            v
+-----------------------+       +-------------------------+
|    SQLite Engine      | <---- |       run_plsql()       |
|                       |       |    (VM Entry Point)     |
+-----------+-----------+       +-----------+-------------+
    ^       |                               |
    |       | SQL Calls                     | Scope/Var
    |       v                               v
    |  +-----------------------+       +-------------------------+
    |  |   Runtime Functions   |       |   Memory Context        |
    |  | (__env_set, __run_if) | ----> | - Scope Stack           |
    |  +-----------+-----------+       | - Variable Hash Maps    |
    |              |                   | - Savepoints            |
    |              |                   +-------------------------+
    |              v                               ^
    +------- run_plsql() (Nested) -----------------+

1. Transpiler: Converts procedural code into valid SQL queries with embedded runtime function calls.
2. Storage: Transpiled SQL is stored in a dedicated __plsql_procedures table.
3. VM Runtime: run_plsql manages the life-cycle of a procedure call. It pushes a new frame onto the Scope Stack, opens a SAVEPOINT, and executes the transpiled SQL.
4. Recursive Execution: If the transpiled SQL contains a CALL, the engine invokes run_plsql again, creating a nested scope and savepoint. This allows for deep recursion (up to 500 levels) while maintaining transactional atomicity.

Why move logic into the database? Here is how PL/SQLite compares to writing the same logic in your application layer (Python, Go, Node.js):

Custom application-level errors can be triggered using the RAISE statement. This will halt execution and trigger an automatic ROLLBACK of all changes made during the procedure call.

SELECT register_plsql('validate_user', 'id', '
    IF (@id < 0) THEN
        RAISE "Invalid user ID: Must be positive";
    END IF;
    -- proceed with logic
');

Verdict: Use PL/SQLite when you need to perform multiple reads/writes based on intermediate results without paying the round-trip cost for each step. Uses Application Logic when you need to integrate with external services or render UIs.

How does this extension compare to other attempts at bringing stored procedures to SQLite?

Key Differentiator: PL/SQLite uses a transpiler architecture. Unlike interpreters that evaluate an AST at runtime, PL/SQLite converts your code into optimized "Engine SQL" that SQLite executes directly. This allows us to beat even high-performance client libraries (like better-sqlite3) in bulk operations by eliminating the overhead of moving data between the host language and the engine for every statement.

Variables are local to the procedure by default but can be qualified for cross-procedure access.

PL/SQLite is fully typed and supports all standard SQLite data types:

• Integer: 64-bit signed integers.
• Float: 64-bit floating point numbers.
• Text: UTF-8 encoded strings.
• Blob: Binary large objects (Zero-copy support).
• Null: Explicit NULL support in assignments and returns.

Standard branching and iteration logic.

IF (@stock > 0) THEN
    -- True block
ELSE
    -- False block
END IF;

FOR r IN (SELECT id, price FROM items) LOOP
    -- Access via @r.id, @r.price
    SET total = @total + @r.price;
END LOOP;

-- Efficient numerical iteration
RANGE i IN (1, 100) LOOP
    INSERT INTO logs(val) VALUES (@i);
END LOOP;

Procedures can call other procedures using the CALL keyword.

-- Call as a statement
CALL update_inventory(@item_id, @qty);

-- Use the return value of a call
DECLARE res = CALL process_payment(@user_id, @amount);
IF (@res == "SUCCESS") THEN ...

A child procedure can read a parent's variable explicitly.

-- In 'sub_proc'
DECLARE factor = @main_proc.multiplier;
-- Modify parent variable directly
SET main_proc.success_count = @main_proc.success_count + 1;

make            # Compiles the extension to plsqlite.so
make test       # Runs the comprehensive Python test suite
make leak-check # Runs memory verification with Valgrind

PL/SQLite supports cross-compilation from Linux to Windows and can be tested locally using Wine.

1. Setup Windows SDK: Use xwin to splat Windows SDK libraries into the win-libs directory.

   make setup-win-libs

2. Download Windows SQLite: Fetches the required .dll, .lib, and .exe for Windows.

   make download-sqlite-win

3. Build Windows DLL:

   make windows

4. Run Tests under Wine:
   • Install Windows Python in your Wine prefix: make install-wine-python
   • Run the test suite: make test-wine

.load ./plsqlite

Load the compiled shared library into your SQLite session.

.load ./plsqlite

Note: This automatically creates the necessary __plsql_procedures table if it doesn't exist.

Create any tables your procedures will interact with.

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, balance INTEGER);
INSERT INTO users VALUES (1, 'Alice', 100);

Use register_plsql to compile your PL/SQLite code. The function signature is register_plsql(name, arguments, source_code).

SELECT register_plsql(
  'update_balance',
  'user_id, amount',
  '
  -- 1. Check current balance
  DECLARE current_bal = 0;
  FOR row IN (SELECT balance FROM users WHERE id = @user_id) LOOP
    SET current_bal = @row.balance;
  END LOOP;

  -- 2. Update if sufficient funds
  IF (@current_bal + @amount >= 0) THEN
    UPDATE users SET balance = balance + @amount WHERE id = @user_id;
    RETURN "SUCCESS: New balance is " || (@current_bal + @amount);
  ELSE
    RETURN "ERROR: Insufficient funds";
  END IF;
  '
);

If there are syntax errors, register_plsql will fail with a descriptive error message.

Run the procedure using run_plsql(name, arg1, arg2...).

-- Add 50 to Alice's balance
SELECT run_plsql('update_balance', 1, 50);
-- Result: "SUCCESS: New balance is 150"

-- Verify the change in the table
SELECT * FROM users WHERE id = 1;

Every procedure runs within a SAVEPOINT. If any SQL error occurs inside the procedure (e.g., a constraint violation, division by zero, or invalid table name), the entire procedure and all its nested calls are rolled back.

PL/SQLite supports recursive procedure calls (e.g., for factorial or tree traversal).

• Recursion Limit: The default stack depth limit is 500. Exceeding this will trigger a runtime error and a full rollback.
• Savepoints per Call: Each nested call consumes one SQLite Savepoint.

SELECT register_plsql(
    'process_order',
    'item_id, qty',
    '
    DECLARE stock = 0;
    FOR row IN (SELECT quantity FROM inventory WHERE id = @item_id) LOOP
        SET stock = @row.quantity;
    END LOOP;

    IF (@stock >= @qty) THEN
        UPDATE inventory SET quantity = quantity - @qty WHERE id = @item_id;
        RETURN "ORDER_SUCCESS";
    ELSE
        RETURN "INSUFFICIENT_STOCK";
    END IF;
    '
);

-- Run the procedure
SELECT run_plsql('process_order', 101, 5);

Break down complex logic into reusable sub-procedures.

-- 1. Register a validation helper
SELECT register_plsql(
    'validate_qty',
    'qty',
    'IF (@qty <= 0) THEN RETURN "INVALID_QUANTITY"; END IF;'
);

-- 2. Register the main logic that calls the helper
SELECT register_plsql(
    'place_order',
    'item_id, qty',
    '
    -- Call validation helper and check its return value
    DECLARE err = CALL validate_qty(@qty);
    IF (@err IS NOT NULL) THEN RETURN @err; END IF;

    -- Proceed with order logic...
    UPDATE inventory SET quantity = quantity - @qty WHERE id = @item_id;
    RETURN "ORDER_PLACED";
    '
);

-- 3. Execute
SELECT run_plsql('place_order', 101, 5);

Shared variables across procedure calls.

SELECT register_plsql('main_proc', 'val', '
    DECLARE multiplier = 2;
    CALL child_proc(@val);
    RETURN "Done";
');

SELECT register_plsql('child_proc', 'input', '
    -- Access "multiplier" from the "main_proc" scope
    DECLARE result = @input * @main_proc.multiplier;
    INSERT INTO logs(msg) VALUES ("Result: " || @result);
');

We compared In-Database PL/SQLite against Application-Layer Logic across multiple languages.

Before running, install dependencies and initialize the database:

make setup-bench     # Installs Node.js, Go, and Rust dependencies
make setup-bench-db  # Seeds 100k rows in databases/bench.db

Then run the full suite or individual languages:

make bench           # Run all benchmarks (Python, Node, Go, Rust, SQL)
make bench-python    # Run Python-only benchmarks
make bench-node      # Run Node.js benchmarks (requires better-sqlite3)
make bench-go        # Run Go benchmarks
make bench-rust      # Run Rust benchmarks
make bench-sql       # Run pure SQL CLI benchmarks

Logic: for row in data: if val > 50: insert weighted else: insert normal

Logic: Native RANGE loop vs application-layer for loop.

Logic: One procedure call vs 10k separate INSERT statements in a transaction.

Logic: RETURN n + CALL rec_sum(n - 1)

Logic: Account transfer involving multiple selects and updates within a single procedural call vs. multiple app-layer boundaries.

1. Networked Databases (Massive Win): On a networked database (e.g., Turso, rqlite, sqlite-server), the round-trip overhead makes app-layer transactions take seconds or minutes. PL/SQLite executes them in milliseconds.
2. Complex State Logic: Use PL/SQLite for multi-statement sequences that require atomic consistency and depend on intermediate results.
3. Driver Reduction: It eliminates the need to pay "FFI taxes" (context switching) between high-level languages like Python/Go and the C-engine.
4. Local Iteration: For simple read-only loops over local files, native driver iterators (especially in Rust/Python) remain faster due to highly optimized cursor fetching.

PL/SQLite is built for production environments:

• Zero-Copy: Data stays in SQLite's memory pages during execution.
• Memory Safe: Verified with Valgrind (0 leaks reported) and internal Tracking_Allocator.
• Leak Detection: Built-in leak reporting via SELECT __plsql_leak_report();.
• Transactional: Fully integrated with SQLite's ACID properties (Atomic Rollbacks).
• Infinite Recursion Protection: Stack depth limits (500) prevent crashes.