SQL Dialect

A3SQL — Arma 3 Database Engine

An embeddable SQL database engine for Arma 3 mods. Like SQLite for Arma — a Rust callExtension that lets modders write SQL directly in SQF.

["CREATE TABLE weapons (id STRING PRIMARY KEY, name STRING)"] call a3sql_fnc_execute;
["INSERT INTO weapons VALUES ('m4a1', 'M4A1')"] call a3sql_fnc_execute;
_result = ["SELECT * FROM weapons WHERE name %% 'm4'"] call a3sql_fnc_execute;

Features

Category	Features
SQL	CREATE/DROP TABLE/INDEX, INSERT, SELECT, UPDATE, DELETE, REPLACE INTO, TRUNCATE, RENAME, CREATE/DROP VIEW, CREATE/DROP TRIGGER
Advanced SQL	JOINs (CROSS/INNER/LEFT/FULL OUTER/NATURAL/USING), GROUP BY/HAVING, ORDER BY/LIMIT/OFFSET, UNION/EXCEPT/INTERSECT, CTE (WITH RECURSIVE), subqueries
Expressions	`%%` fuzzy match, LIKE, BETWEEN, IN, IS NULL, CASE WHEN, EXISTS, CAST
Functions	UPPER/LOWER, LENGTH, SUBSTR, TRIM/LTRIM/RTRIM, INSTR, CONCAT, COALESCE/IFNULL, ROUND, ABS, NOW()/CURRENT_TIMESTAMP, DATETIME() (with SQLite modifiers: `'+1 day'`, `'-30 days'`, `'+3 hours'`), DATE()/TIME()/STRFTIME()/UNIX_TIMESTAMP(), TYPEOF, CHAR, RANDOM, POW/POWER, SQRT, CEIL/CEILING, FLOOR, SIGN, REPLACE, COUNT(DISTINCT col), fn_* (plugin), aggregate (SUM/AVG/MIN/MAX)
SQF Eval	`SQF_EVAL(expr)` — evaluate SQF expressions inline, ~55 native commands (math, string, array, type), full wiki command dispatch (2,700+ commands), returns NULL for game-engine-only commands
Window	ROW_NUMBER, RANK, DENSE_RANK with OVER/PARTITION BY/ORDER BY / ROWS BETWEEN
Constraints	PRIMARY KEY, NOT NULL, DEFAULT, CHECK (enforced), FOREIGN KEY (enforced, CASCADE), AUTO_INCREMENT
Triggers	CREATE/DROP TRIGGER (BEFORE/AFTER INSERT/UPDATE/DELETE), per-row execution
Types	INT (BIGINT/SMALLINT/TINYINT), FLOAT (DECIMAL/NUMERIC/DOUBLE), STRING (VARCHAR/CHAR/TEXT), BOOL, DATE/TIMESTAMP, STRINGS[]/FLOATS[]
Indices	BTREE (exact/range), TRIGRAM (fuzzy GIN-style candidate filter), FTS (full-text trigram search)
Transactions	BEGIN/COMMIT/ROLLBACK (no-op when idle), SAVEPOINT/RELEASE
UPSERT	`INSERT ... ON CONFLICT (col) DO UPDATE SET ...`
RETURNING	`INSERT`/`UPDATE`/`DELETE ... RETURNING *`
Persistence	SAVE/LOAD (binary), export/import JSON/CSV/SQL, export_to_file, VACUUM/REINDEX
Security	Parameterized queries (`$1`,`$2`), TCP LOGIN auth, CBA credential settings
Plugins	`fn_echo('hello')`, C ABI dynamic `.so`/`.dll`, trait-based registration
Network	TCP listener (auto-start at game boot), standalone server (`a3sql-server`), remote connect (`connect <host> <port>`)
Multi-statement	Run `;`-separated SQL batches
Multi-dialect	Accepts PostgreSQL, MySQL/MariaDB, SQLite, DataFusion-style SQL

Quick Start

1. Add a3sql as a dependency

In your mod's CfgPatches:

requiredAddons[] = {"cba_main", "a3sql_main", "a3sql_database"};

2. Call from SQF

// Create
["CREATE TABLE players (uid STRING PRIMARY KEY, name STRING, score INT)"] call a3sql_fnc_execute;

// Insert
["INSERT INTO players VALUES ('76561198000000001', 'Scarface', 1500)"] call a3sql_fnc_execute;

// Query
_result = ["SELECT name, score FROM players WHERE score > 1000 ORDER BY score DESC"] call a3sql_fnc_execute;
// Returns: [0, "OK", [["name","score"],["Scarface",1500]]]

3. CBA Wrapper Functions

// Fuzzy search
_result = ["SELECT name FROM weapons WHERE name %% 'm4'"] call a3sql_fnc_execute;

// Transactions
["BEGIN"] call a3sql_fnc_execute;
["INSERT INTO log (action) VALUES ('mission_start')"] call a3sql_fnc_execute;
["COMMIT"] call a3sql_fnc_execute;

// Save/load persistence
["data.bin"] call a3sql_fnc_save;
["data.bin"] call a3sql_fnc_load;

// Export
_table_data = ["players"] call a3sql_fnc_exportJSON;
_sql_backup = [] call a3sql_fnc_exportSQL;

// External TCP query (from Python)
// ["listen"] call a3sql_fnc_execute;  // auto-starts at game boot

4. CBA Addon Settings

Options → Addon Configuration → A3SQL:

Setting	Type	Default	Purpose
Enable TCP Listener	CHECKBOX	true	Auto-start on game boot
Listener Port	EDIT	33306	TCP port
Listener Bind Address	EDIT	127.0.0.1	Bind IP
Listener Username	EDIT	(empty)	TCP login (empty = anonymous)
Listener Password	EDIT	(empty)	TCP login
Auto-Save	CHECKBOX	false	Save on mission end
Auto-Load	CHECKBOX	false	Load on mission start
Auto-Save Path	EDIT	a3sql_autosave.bin	File path
Log Level	LIST	INFO	RPT verbosity

Auto-Save: Save database when mission ends
Auto-Save File: File path for auto-save

5. Full example

if (isServer) then {
    // Create tables on mission start
    ["CREATE TABLE IF NOT EXISTS stats (uid STRING, name STRING, score INT)"] call a3sql_fnc_execute;

    // Restore from previous session
    ["stats_data.bin"] call a3sql_fnc_load;

    // Auto-save on mission end
    addMissionEventHandler ["Ended", {
        ["stats_data.bin"] call a3sql_fnc_save;
    }];
};

// Record event
["INSERT INTO stats VALUES ('76561198000000001', 'Scarface', 1500)"] call a3sql_fnc_execute;

// Query top scores
_result = ["SELECT name, score FROM stats ORDER BY score DESC LIMIT 10"] call a3sql_fnc_execute;

6. External query (TCP)

Enable the TCP listener in CBA settings, then connect from any tool:

import socket
s = socket.socket()
s.connect(("127.0.0.1", 33306))
s.sendall(b"SELECT * FROM stats ORDER BY score DESC LIMIT 5\n")
print(s.recv(65536).decode())
s.close()

7. Standalone server

Run the database as a standalone TCP server without Arma 3:

cargo run --manifest-path extension/Cargo.toml --bin a3sql-server -- --port 33307

Connect from any TCP client:

echo "SELECT * FROM weapons" | nc localhost 33307

Or from SQF in remote-connect mode:

["connect 192.168.1.100 33306"] call a3sql_fnc_execute;

SQL Dialect

-- Tables
CREATE TABLE weapons (id STRING PRIMARY KEY, name STRING, caliber STRING, barrelLength FLOAT);
CREATE TABLE attachments (id STRING PRIMARY KEY, weaponId STRING, name STRING, mass FLOAT);

-- Types: STRING, INT, FLOAT, BOOL, STRINGS[], FLOATS[]
-- Array columns accept ARRAY[...] literals:
--   INSERT INTO t (tags, weights) VALUES (ARRAY['a','b'], ARRAY[1.5, 2.5])

-- CRUD
INSERT INTO weapons VALUES ('rhs_m4a1', 'M4A1', '5.56x45mm', 368.3);
-- INTEGER PRIMARY KEY auto-assigns a rowid when omitted (SQLite semantics):
--   CREATE TABLE t (id INTEGER PRIMARY KEY, v TEXT);
--   INSERT INTO t (v) VALUES ('x');   -- id becomes 1, then 2, ...
SELECT * FROM weapons WHERE caliber = '5.56x45mm';
SELECT name, caliber FROM weapons WHERE barrelLength > 400.0 ORDER BY barrelLength DESC;
UPDATE weapons SET caliber = '7.62x39mm' WHERE id = 'ak74';
DELETE FROM weapons WHERE barrelLength IS NULL;
DROP TABLE weapons;

-- Fuzzy match (trigram similarity)
SELECT * FROM weapons WHERE id %% 'rhs_m4';
-- matches rhs_m4a1, rhs_m4a1_carryhandle, etc.

-- JOINS
SELECT w.name, a.name FROM weapons w INNER JOIN attachments a ON w.id = a.weaponId;
SELECT * FROM weapons w LEFT JOIN attachments a ON w.id = a.weaponId;

-- Aggregates
SELECT COUNT(*) FROM weapons;
SELECT AVG(barrelLength) FROM weapons;
SELECT caliber, COUNT(*) AS cnt FROM weapons GROUP BY caliber;

-- Ordering & limits
SELECT * FROM weapons ORDER BY name ASC LIMIT 10 OFFSET 5;

-- Transactions
BEGIN;
INSERT INTO weapons VALUES ('test', 'Test', '9x19mm', 200.0);
ROLLBACK;  -- or COMMIT

-- Savepoints
SAVEPOINT sp1;
INSERT INTO weapons VALUES ('tmp', 'Temp', '5.56x45mm', 300.0);
ROLLBACK TO sp1;
RELEASE SAVEPOINT sp1;

-- Indices
CREATE INDEX idx_caliber ON weapons (caliber) USING BTREE;
CREATE INDEX idx_name_fuzzy ON weapons (name) USING TRIGRAM;

-- REPLACE / UPSERT
REPLACE INTO weapons VALUES ('m4a1', 'M4A1', '5.56x45mm', 368.3);

-- UPSERT (INSERT … ON CONFLICT)
INSERT INTO weapons VALUES ('m4a1', 'M4A1', '5.56x45mm', 368.3)
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, caliber = EXCLUDED.caliber;

-- ALTER TABLE
ALTER TABLE weapons ADD COLUMN mass FLOAT;
ALTER TABLE weapons DROP COLUMN barrelLength;
ALTER TABLE weapons RENAME COLUMN name TO displayName;
ALTER TABLE weapons RENAME TO armory;

-- TRUNCATE
TRUNCATE TABLE weapons;

-- Window functions
SELECT id, name, ROW_NUMBER() OVER (ORDER BY name) AS rn FROM weapons;
SELECT id, name, RANK() OVER (PARTITION BY caliber ORDER BY name) FROM weapons;

-- Subqueries
SELECT * FROM weapons WHERE id IN (SELECT weaponId FROM attachments);
SELECT * FROM weapons WHERE EXISTS (SELECT 1 FROM attachments WHERE weaponId = weapons.id);

-- CTE
WITH top AS (SELECT * FROM weapons ORDER BY name LIMIT 5) SELECT * FROM top;

-- CAST
SELECT CAST(barrelLength AS INT) FROM weapons;
SELECT name || ' (' || caliber || ')' AS combined FROM weapons;

-- Functions
SELECT NOW(), CURRENT_TIMESTAMP;
SELECT COALESCE(barrelLength, 0.0) FROM weapons;
SELECT UPPER(name), LOWER(name), LENGTH(name), SUBSTR(name, 1, 3) FROM weapons;
SELECT POW(barrelLength, 2) FROM weapons;           -- exponentiation
SELECT SQRT(barrelLength) FROM weapons;              -- square root
SELECT CEIL(barrelLength), FLOOR(barrelLength) FROM weapons;  -- round up/down
SELECT SIGN(score) FROM players;                     -- signum (-1, 0, 1)
SELECT REPLACE(name, 'M4A1', 'M4A1+') FROM weapons; -- string replace

-- SQF_EVAL(expr) — evaluate an SQF expression inline
-- Supports ~55 native commands: math, string, array, type helpers
-- Falls back to NULL for game-engine-only commands
SELECT SQF_EVAL('sqrt 25') AS result;               -- 5.0
SELECT SQF_EVAL('pi') AS result;                     -- 3.14159...
SELECT SQF_EVAL('3 min 7') AS result;                -- 3 (binary command infix)
SELECT SQF_EVAL('"hello" find "ll"') AS result;      -- 2
SELECT SQF_EVAL('typeName 42') AS result;             -- "SCALAR"
SELECT SQF_EVAL('isNil nil') AS result;              -- true
SELECT SQF_EVAL('createVehicle') AS result;          -- NULL (game-engine-only)

-- COUNT DISTINCT
SELECT COUNT(DISTINCT caliber) FROM weapons;

-- Plugin functions
SELECT fn_echo('hello') FROM weapons;

-- RETURNING clause
INSERT INTO weapons VALUES ('test', 'Test', '9x19mm', 200.0) RETURNING *;
UPDATE weapons SET barrelLength = 300.0 WHERE id = 'test' RETURNING id, name;
DELETE FROM weapons WHERE id = 'test' RETURNING *;

-- Views
CREATE VIEW short_weapons AS SELECT id, name FROM weapons WHERE barrelLength < 300.0;
SELECT * FROM short_weapons;
DROP VIEW short_weapons;

-- EXPLAIN
EXPLAIN SELECT * FROM weapons WHERE caliber = '5.56x45mm';
EXPLAIN INSERT INTO weapons VALUES ('t', 'T', '9mm', 100.0);

-- Set operations
SELECT id FROM weapons EXCEPT SELECT weaponId FROM attachments;
SELECT id FROM weapons INTERSECT SELECT weaponId FROM attachments;

-- FULL OUTER JOIN
SELECT * FROM weapons w FULL OUTER JOIN attachments a ON w.id = a.weaponId;

-- NATURAL JOIN / JOIN USING
SELECT * FROM weapons NATURAL JOIN attachments;
SELECT * FROM weapons JOIN attachments USING (id);

-- Window frames
SELECT id, name, AVG(barrelLength) OVER (
    ORDER BY name ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) AS moving_avg FROM weapons;

-- Recursive CTE
WITH RECURSIVE nums(n) AS (
    SELECT 1 UNION ALL SELECT n + 1 FROM nums WHERE n < 10
) SELECT * FROM nums;

-- CHECK constraint (enforced)
CREATE TABLE prices (item STRING PRIMARY KEY, price INT CHECK (price > 0));
INSERT INTO prices VALUES ('a', -5);  -- error: CHECK constraint failed

-- FOREIGN KEY (enforced)
CREATE TABLE orders (id STRING PRIMARY KEY, item STRING REFERENCES prices(item));
INSERT INTO orders VALUES ('o1', 'nonexistent');  -- error: FK violation

-- FOREIGN KEY with CASCADE
CREATE TABLE line_items (
    id STRING PRIMARY KEY,
    order_id STRING REFERENCES orders(id) ON DELETE CASCADE,
    product STRING
);
DELETE FROM orders WHERE id = 'o1';  -- cascades to line_items

CREATE TABLE audits (
    id STRING PRIMARY KEY,
    order_id STRING REFERENCES orders(id) ON UPDATE SET NULL
);

-- Triggers
CREATE TRIGGER log_ins AFTER INSERT ON weapons
BEGIN
    INSERT INTO audit_log (table_name, action) VALUES ('weapons', 'INSERT');
END;

CREATE TRIGGER val_upd BEFORE UPDATE OF caliber ON weapons
BEGIN
    SELECT RAISE(ABORT, 'caliber cannot be changed');
END;

DROP TRIGGER log_ins;

-- VACUUM / REINDEX
VACUUM weapons;
REINDEX weapons;

SQF API

Initialization

// In init.sqf or CfgFunctions init:
private _version = "a3sql" callExtension "version";
diag_log text format ["[A3SQL] Loading: %1", _version];

SQL Execution

// Single SQL statement (STRING callExtension STRING):
private _result = "a3sql" callExtension "SELECT * FROM weapons";

// SQL with args (STRING callExtension ARRAY):
private _result = ["a3sql", "INSERT INTO weapons VALUES ('m4a1', 'M4A1', '5.56x45mm', 368.3)"] callExtension;

// Multi-statement (separate with semicolons):
private _result = "a3sql" callExtension "CREATE TABLE t (id STRING); INSERT INTO t VALUES ('a'); SELECT * FROM t";

Response Format

[returnCode, status, data]

Success: [0,"OK",result_data]
Error:   [-1,"ERR_CODE","error message"]

Error codes:
  ERR_PARSE    SQL parse error
  ERR_EXEC     Execution error
  ERR_TABLE    Table not found
  ERR_TYPE     Type mismatch
  ERR_PK       Primary key violation
  ERR_IO       File I/O error
  ERR_INTERNAL Internal error

result_data for SELECT is a JSON array of column names + rows:

[["id","name","caliber","barrelLength"],["rhs_m4a1","M4A1","5.56x45mm",368.3]]

For JOINs with prefixed column names:

[["weapons.id","weapons.name","attachments.name"],["rhs_m4a1","M4A1","M68 CCO"]]

Commands

// Version
private _version = "a3sql" callExtension "version";
// → [0,"OK","a3sql 0.2.0"]

// SQL dump
private _dump = "a3sql" callExtension "dump_sql";
// → [0,"OK","CREATE TABLE weapons (...);..."]

// Query with parameterized args (SQL injection safe)
private _result = ["a3sql", "SELECT * FROM weapons WHERE id = $1", ["m4a1"]] callExtension;

// TCP listener (auto-starts on game boot). Manual control:
private _result = ["a3sql", "listen", ["33306"]] callExtension;
private _result = ["a3sql", "stop"] callExtension;

CBA Functions

When using CBA (recommended), the addon registers these functions via CfgFunctions:

Function	Description
`a3sql_fnc_init`	Initialize extension, returns version string
`a3sql_fnc_execute`	Execute SQL, returns parsed result
`a3sql_fnc_loadJSON`	Import JSON data into a table
`a3sql_fnc_dumpSQL`	Export full database as SQL dump
`a3sql_fnc_exportJSON`	Export table as JSON
`a3sql_fnc_exportCSV`	Export table as CSV
`a3sql_fnc_exportSQL`	Export full database as SQL statements
`a3sql_fnc_save`	Persist database to binary file
`a3sql_fnc_load`	Restore database from binary file
`a3sql_fnc_init`	Initialize extension
`a3sql_fnc_settings`	Register CBA settings (auto-called via PreInit)
`a3sql_fnc_postInit`	Post-mission init (auto-save/load hooks)

Security

Parameterized Queries

Prevent SQL injection by passing user input as separate args with $1, $2 placeholders:

// UNSAFE — string interpolation (SQL injection possible)
private _sql = format ["SELECT * FROM users WHERE name = '%1'", _userInput];
["a3sql", _sql] callExtension;

// SAFE — parameterized query (injection prevented)
["a3sql", "SELECT * FROM users WHERE name = $1", [_userInput]] callExtension;

TCP Authentication

Set a username and password in CBA Settings (Options → Addon Configuration → A3SQL). When credentials are non-empty, clients must LOGIN before querying:

import socket
s = socket.socket()
s.connect(("127.0.0.1", 33306))
s.sendall(b"LOGIN admin mypassword\n")
print(s.recv(65536).decode())  # [0,"OK","Authenticated"]
s.sendall(b"SELECT * FROM weapons\n")
print(s.recv(65536).decode())
s.close()

Building

Prerequisites

Rust 1.80+ (for std::sync::LazyLock)
HEMTT 1.20+ — Arma 3 addon build tool
C++ build tools (for Rust cross-compilation to Windows)
MinGW-w64 (for Windows cross-compilation on Linux):
```
sudo apt-get install mingw-w64 gcc-multilib
```

Build the extension

# Build for Linux x86_64
cargo build --release --manifest-path extension/Cargo.toml

# Build for Windows (cross-compile from Linux)
cargo build --release --target x86_64-pc-windows-gnu --manifest-path extension/Cargo.toml
cargo build --release --target i686-pc-windows-gnu --manifest-path extension/Cargo.toml

Build the addon

hemtt build

Output goes to .hemttout/build/.

Run tests

# All 524 tests
cargo test --manifest-path extension/Cargo.toml

Linting & validation

# Rust
cargo fmt --check
cargo clippy --manifest-path extension/Cargo.toml --all-targets -- -D warnings

# SQF + config
python3 tools/sqfvmChecker.py
python3 tools/sqf_validator.py addons/
python3 tools/config_style_checker.py

# Arma addon structure
hemtt check -p -e

CI/CD

The project includes a GitHub Actions workflow (.github/workflows/ci.yml) that:

Runs cargo test
Builds for 4 targets: x86_64-linux, i686-linux, x86_64-windows, i686-windows
Runs hemtt build to produce the addon PBOs
On release: creates a a3sql-<tag>.zip with the complete mod

Test locally with ACT:

act -j test          # Run test job
act --list           # List all jobs

Project Structure

a3sql/
├── extension/                  # Rust extension workspace (cdylib + rlib)
│   ├── Cargo.toml
│   ├── .cargo/config.toml      # Cross-compilation linkers
│   └── src/
│       ├── lib.rs              # Library root + module layout
│       ├── ffi/                # C ABI (RVExtension, RVExtensionArgs, RVExtensionVersion,
│       │                       #   RVExtensionRegisterCallback)
│       ├── dispatch/           # Command routing (SQL + control commands: save/load,
│       │   │                   #   cursor*, prepare, listen, set_credentials, exports)
│       │   ├── commands.rs     # Control-command handlers
│       │   └── sql.rs          # SQL splitting + $1/$n parameter substitution
│       ├── parser/             # SQL parser (sqlparser-rs + custom A3sqlDialect)
│       ├── engine/             # In-memory database engine
│       │   ├── database/       # Database struct + persistence
│       │   ├── functions/      # Built-in SQL functions + expression eval
│       │   ├── index.rs        # BTreeIndex + TrigramIndex
│       │   ├── plugin.rs       # Plugin registry + C ABI loader
│       │   ├── serialize/      # JSON/CSV/Binary/SQL export/import
│       │   ├── stmts/          # Statement handlers (insert, select, ddl, etc.)
│       │   ├── table/          # Table struct, row ops, PK/UNIQUE sets
│       │   ├── trigger.rs      # Trigger execution
│       │   ├── value.rs        # ColumnType, Column, DbValue enums
│       │   └── error.rs        # Structured error codes
│       ├── server.rs           # TCP listener (loopback, LOGIN auth)
│       ├── config.rs           # Config (A3SQL_CONFIG env / a3sql.toml)
│       └── bin/                # Standalone server binary
│           └── a3sql-server.rs
├── addons/
│   ├── main/                   # Main addon (CBA macro includes + CfgPatches)
│   └── database/               # SQL engine addon (CfgFunctions + SQF API)
├── plugins/                    # Example C ABI plugin
├── include/                    # CBA header stubs
├── .hemtt/project.toml         # HEMTT v1 build config
├── tools/                      # Python dev tools
├── docs/wiki/                  # GitHub Wiki source
└── mod.cpp                     # Mod definition

Development

Built following the same conventions as ACE3 and CBA_A3:

Aspect	Convention
Prefix	`prefix = "a3sql"`, `mainprefix = "z"`
PBO path	`z\a3sql\addons\{addon_name}`
Include path	`\z\a3sql\addons\main\script_mod.hpp`
CBA dependency	CBA_A3 required (`cba_main`, `cba_xeh`)
Build system	HEMTT v1 (`.hemtt/project.toml`)
Rust workspace	`extension/` (own `Cargo.toml`; target under `extension/target/`)
Release profile	`opt-level = "z"`, `lto = true`, `strip = true`

License

MIT — use freely in your Arma 3 mods.

Uh oh!

SQL Dialect

A3SQL — Arma 3 Database Engine

Features

Quick Start

1. Add a3sql as a dependency

2. Call from SQF

3. CBA Wrapper Functions

4. CBA Addon Settings

5. Full example

6. External query (TCP)

7. Standalone server

SQL Dialect

SQF API

Initialization

SQL Execution

Response Format

Commands

CBA Functions

Security

Parameterized Queries

TCP Authentication

Building

Prerequisites

Build the extension

Build the addon

Run tests

Linting & validation

CI/CD

Project Structure

Development

License

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Clone this wiki locally