Skip to content

SQL Dialect

Jump to bottom
Matthew Barker edited this page Jul 26, 2026 · 10 revisions

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, CONCAT, COALESCE/IFNULL, ROUND, ABS, NOW()/CURRENT_TIMESTAMP, COUNT(DISTINCT col), fn_* (plugin), aggregate (SUM/AVG/MIN/MAX)
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_sql"};

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 --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[]

-- CRUD
INSERT INTO weapons VALUES ('rhs_m4a1', 'M4A1', '5.56x45mm', 368.3);
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;

-- 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.1.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

# Build for Windows (cross-compile from Linux)
cargo build --release --target x86_64-pc-windows-gnu
cargo build --release --target i686-pc-windows-gnu

Build the addon

hemtt build

Output goes to .hemttout/build/.

Run tests

# All 165 tests (0.01s)
cargo test --lib -p a3sql

Linting & validation

# Rust
cargo fmt --check
cargo clippy --all-targets

# SQF
sqflint addons/a3sql/*.sqf
sqfvm --parse-only -i addons/a3sql/fn_init.sqf

# Arma addon structure
hemtt check

CI/CD

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

  1. Runs cargo test
  2. Builds for 4 targets: x86_64-linux, i686-linux, x86_64-windows, i686-windows
  3. Runs hemtt build to produce the addon PBOs
  4. 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/
├── Cargo.toml                  # Workspace → extension/
├── extension/                  # Rust extension crate (cdylib + rlib)
│   ├── Cargo.toml
│   ├── .cargo/config.toml      # Cross-compilation linkers
│   └── src/
│       ├── lib.rs              # C ABI (RVExtension, RVExtensionArgs, RVExtensionVersion)
│       ├── parser/             # SQL parser (sqlparser-rs + custom A3SqlDialect)
│       │   ├── dialect.rs      # A3SqlDialect (GenericDialect-based, multi-dialect)
│       │   ├── preprocessor.rs # %% → fuzzy_match(), string-literal-aware
│       │   └── mod.rs          # parse_sql() entry point
│       ├── bin/                # Standalone server binary
│       │   └── a3sql-server.rs
│       └── engine/             # In-memory database engine
│           ├── execute.rs      # Main dispatch + CTE + shared helpers
│           ├── 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, schema
│           ├── trigger.rs      # Trigger execution
│           ├── value.rs        # ColumnType, Column, DbValue enums
│           └── error.rs        # Structured error codes
├── addons/
│   ├── main/                   # Main addon (CBA macro includes + CfgPatches)
│   └── sql/                    # 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 Workspace at root, crate in extension/
Release profile opt-level = "z", lto = true, strip = true

License

MIT — use freely in your Arma 3 mods.

Clone this wiki locally