dlang-sqlite/README.md

dlang.sqlite

🤖 AI-generated

This project, its source code and this documentation were (largely) generated by AI (GitHub Copilot). The code has not been fully reviewed by a human for correctness, security or production readiness.

Use at your own risk. Review the code before putting it into production, and be extra careful about the limitations listed below (e.g. no parameter binding).

SQLite module (plugin) for the Dlang interpreter. The module lets a Dlang program open a SQLite database, run queries, and run queries asynchronously without blocking the VM.

---

Contents

• How it works
• Requirements
• Building
• Installing as a plugin
• Quick start
• API reference
• Result format
• Asynchronous usage
• Limitations and known issues

---

How it works

The plugin builds a native shared library (dlang.sqlite.dll on Windows, dlang.sqlite.so on Linux). The Dlang interpreter loads it dynamically via require("sqlite"), which pulls the file from the ./plugins/ folder next to the interpreter.

The plugin exports a single function, initialize, which creates a global sqlite table in the VM containing the functions open, query, query_async, poll and close.

Queries are executed with sqlite3_prepare_v2 and the results are pushed onto the VM stack as Dlang tables and lists.

---

Requirements

Component	Version / note
CMake	3.10 or newer
C++ compiler	C++20 (MSVC, GCC or Clang)
Git	required for the SQLite FetchContent
Ninja	used by the CMake presets
SQLite	3.49.1, fetched automatically via FetchContent (sjinks/sqlite3-cmake)

A working Dlang interpreter is needed to actually load the plugin.

---

Building

The project uses CMakePresets.json. Use the CMake extension in VS Code, or build manually from the command line:

# Configure (pick the preset that matches your platform)
cmake --preset x64-release

# Build
cmake --build --preset x64-release

Available presets:

Preset	Platform
x64-debug	Windows x64, Debug
x64-release	Windows x64, Release
x86-debug	Windows x86, Debug
x86-release	Windows x86, Release
linux-debug	Linux, Debug
macos-debug	macOS, Debug

The build output lands in out/build/<preset>/dlang.sqlite/:

• Windows: dlang.sqlite.dll
• Linux: dlang.sqlite.so

Note: the project only builds the module (a SHARED library). There is no executable; the module is meant to be loaded by the Dlang interpreter.

---

Installing as a plugin

The plugin loader (plugins.hpp) looks for the file as ./plugins/<name>.dll (Windows) or ./plugins/<name>.so (Linux), relative to the interpreter's working directory.

1. Create a plugins folder next to the Dlang interpreter.

2. Copy the built library there and rename it to the name you use in require(...):

   my-dlang-app/
   ├── dlang.exe            # or dlang
   └── plugins/
       └── sqlite.dll       # Windows  (built as dlang.sqlite.dll)
          sqlite.so         # Linux    (built as dlang.sqlite.so)
   

3. In your Dlang script:

   require("sqlite");
   

   The require call returns the string "plugin loaded!" or "Failed to load plugin!".

---

Quick start

// main.dl
require("sqlite");

// 1. Open the database (creates the file if it does not exist)
if (sqlite.open("test.db")) then
    print("Database opened");
else
    print("Could not open the database");
end

// 2. Create and populate a table
sqlite.query("CREATE TABLE IF NOT EXISTS users (id INTEGER, name TEXT);");

var ins = sqlite.query("INSERT INTO users (id, name) VALUES (1, 'Ada');");
print("Rows changed:", ins.rowsAffected);

// 3. A query that returns rows
var res = sqlite.query("SELECT id, name FROM users;");

if (res.success) then
    foreach (row in res.rows) do
        print(row.name);
    end
else
    print("Error:", res.error);
end

// 4. Shut down
sqlite.close();

The syntax above follows the Dlang language (func, if ... then ... end, foreach (x in list) do ... end, comments with // or --).

---

API reference

The module is available as the global sqlite table (after require("sqlite")).

sqlite.open(path) -> bool

Opens (or creates) a database file at path.

• If a connection is already open, it is closed first.
• Sets PRAGMA journal_mode = WAL and PRAGMA synchronous = NORMAL for better performance.
• Returns true on success, false on failure or if the argument is not a string.
• The path is taken from the argument; there is a single global connection (g_db).

sqlite.query(sql) -> table

Runs the SQL synchronously and returns a result table (see Result format). Works for SELECT, INSERT, UPDATE, DELETE, DDL and multiple statements in one string.

sqlite.query_async(sql, callback)

Runs the SQL on a background thread and calls callback later with the result. The call does not block the VM.

• sql must be a string and callback must be a function; otherwise nothing happens.
• The result is pushed onto a queue; the callback is only invoked during a call to sqlite.poll().
• Returns nothing.

sqlite.poll()

Drains all completed asynchronous tasks and invokes their callbacks (each with one argument: the result table). Call this regularly in your main loop.

sqlite.close() -> bool

Closes the global database connection and returns true.

---

Result format

query and the query_async callback return a table with these fields:

Field	Type	Meaning
success	bool	true if the query ran without error
error	string	SQLite error message; only present when success is false
rowsAffected	number	Number of changed rows (sqlite3_changes)
rows	list	List of rows; each row is a table of column name → value

Values inside a row are converted as follows:

SQLite type	Dlang type
INTEGER	number (via double, so 64-bit precision may be lost)
FLOAT	number
TEXT	string
NULL / other	nil

Example of reading a row:

var res = sqlite.query("SELECT id, name FROM users;");
foreach (row in res.rows) do
    print(row.id, row.name);
end

---

Asynchronous usage

require("sqlite");
sqlite.open("test.db");

func onResult(res)
    if (res.success) then
        print("Query finished, rows:", res.rowsAffected);
    else
        print("Query failed:", res.error);
    end
end

// Start the query on a background thread
sqlite.query_async("SELECT * FROM users;", onResult);

// Process completed tasks in the main loop
while (true) do
    sqlite.poll();
end

Important:

• Without regular sqlite.poll() calls, callbacks are never invoked.
• The callback always runs on the main thread (during poll), never on the background thread.

---

Limitations and known issues

These are deliberate simplifications or real risks in the current, AI-generated code:

1. No parameter binding. SQL is passed to SQLite as a plain string. Never put user input directly into a query → SQL injection. This is the most important limitation to fix.
2. Single global connection. g_db is a single sqlite3*; concurrent queries or multiple databases are not supported.
3. Thread safety. sqlite3_open, sqlite3_close and query run without a lock alongside the background threads of query_async. A close() during an in-flight async query can cause crashes or undefined behaviour. SQLite is also not opened in serialized mode by default.
4. 64-bit integers. SQLITE_INTEGER is converted to double; values above 2^53 may lose precision.
5. Column names as keys. For columns with the same name (e.g. from a JOIN) and for unnamed expressions, the last value wins; the column name can be empty.
6. No transactions or busy timeout. There is no API for BEGIN/COMMIT or for sqlite3_busy_timeout; on a locked database a query fails immediately with SQLITE_BUSY.
7. Windows and Linux only. The plugin loader supports no other platforms.
8. AI code. This module is AI-generated and not fully tested. The project contains no tests.

---

License

There is no license file in this repository yet. Add a license before reusing or distributing this project.