@lossless.org/sqldb
SQLDB is an open-source Rust SQL database with MariaDB-compatible client connections and a TypeScript API. It combines transactional catalog snapshots with Arrow columnar batches, Zstandard compression, and DataFusion's parallel, vectorized query execution for metrics aggregation. It runs its own engine; it does not require a MariaDB or ClickHouse server.
This initial 0.1 implementation supports a defined subset of MariaDB's SQL and wire protocol. It is not a drop-in replacement for the complete MariaDB server. The compatibility and capacity boundaries below are part of the API.
Issue Reporting and Security
For reporting bugs, issues, or security vulnerabilities, please visit community.foss.global/. This is the central community hub for all issue reporting. Developers who sign and comply with our contribution agreement and go through identification can also get a code.foss.global/ account to submit Pull Requests directly.
Build and run
The initial package is developed in this repository. Build the TypeScript module and native binaries before running the examples or tests:
pnpm install --frozen-lockfile
pnpm build
pnpm test
pnpm test:rust
The build pins Rust 1.95.0 in rust-toolchain.toml and uses the package-manager version in package.json. Linux builds require the x86_64-linux-musl-gcc and aarch64-linux-musl-gcc toolchains. The @git.zone/rsrepo and @push.rocks/smartrust setup builds static dist_rust/rustsqldb_linux_amd64_musl and dist_rust/rustsqldb_linux_arm64_musl executables for both glibc and musl Linux hosts, including Alpine. The package's files list includes these artifacts. macOS binaries are not included in this initial build configuration.
The qualified cross-compiler distribution is cross-tools/musl-cross 20260823 (GCC 16.2.0, binutils 2.47, musl 1.2.6). Extract each archive using its upstream instructions, expose its bin directory on PATH, and provide the compiler names above as aliases for x86_64-unknown-linux-musl-gcc and aarch64-unknown-linux-musl-gcc. Verified archive SHA-256 values:
x86_64-unknown-linux-musl.tar.xz:9752ecb10bafc0fc2ea75b3ed864a78137f3e5ba9b1579f1f16923d444c48096aarch64-unknown-linux-musl.tar.xz:0fc483607d9ed83bdf75e7539bacc66721d7e37ca606377aed6a90cef82e45da
Rust supplies its pinned, patched musl runtime. Native runtime license materials and their provenance are included in third_party_licenses/native-inventory.json; keep them in distributions of the binaries. The built-artifact acceptance test test/verify-alpine.mjs checks authentication, parameter binding, transaction rollback and durable restart inside the Alpine runtime, using the packaged default binary lookup.
Releases use rsrepo matrix: pnpm run release:check-builders checks linker and emulation support, and pnpm run release:build-artifacts builds from an isolated clean Git commit. The native verifier runs locked Cargo tests, production/test type checks and the official MariaDB connector suite against both Linux binaries (ARM64 through configured executable emulation on an x86 builder). Assembly verifies the complete artifact set and its commit/version provenance before replacing dist_rust.
import { SqlDb } from './ts/index.js'; // use @lossless.org/sqldb after publication
import * as path from 'node:path';
const password = process.env.SQLDB_PASSWORD;
if (!password) throw new Error('SQLDB_PASSWORD is required');
const db = new SqlDb({
storage: { mode: 'file', path: path.resolve('metrics.redb') },
username: 'app',
password,
database: 'metrics',
});
await db.start();
try {
await db.query(`CREATE TABLE IF NOT EXISTS samples
(id BIGINT PRIMARY KEY, service VARCHAR(64) NOT NULL, latency DOUBLE)`);
await db.query('INSERT INTO samples VALUES (?, ?, ?)', [1, 'api', 12.5]);
const result = await db.query(`SELECT service, COUNT(*) AS requests,
AVG(latency) AS mean_latency FROM samples GROUP BY service`);
console.log(result.columns, result.rows);
console.log(await db.stats());
} finally {
await db.stop();
}
Choose { mode: 'memory' } explicitly for disposable data. File mode requires an absolute path, an existing parent directory, and exclusive ownership of the database file. There is no fallback from file storage to memory. Keep the configured database name unchanged when reopening a file. Part tables keep their data beside the file in the directory <path>.data. To copy a database for backup, stop its owner first and copy both the file and <path>.data; copying an open database is not a supported backup procedure.
start() and stop() are serialized and idempotent. stop() waits for admitted operations to drain within shutdownTimeoutMs (default 120 seconds). A successful stop closes connections, releases storage ownership, and confirms that the Rust process has exited. If that wait expires, stop rejects with an error and confirms termination using SIGTERM, followed by SIGKILL after five seconds if necessary. This failure does not report a successful drain: an unacknowledged commit may have persisted, and file storage must be reopened to determine its outcome. Increase the shutdown deadline for large or slow durable writes. address reports the actual host, assigned port, and database; port zero selects an available local port. isRunning becomes false when the child exits. A stopped memory instance starts empty.
query(sql, parameters) executes one autocommit statement. Parameters are strings, finite numbers, booleans, or null, bound structurally through the parsed SQL plan. Use strings with an explicit CAST(? AS BIGINT) for integers outside JavaScript's safe range. DDL cannot contain parameters. Transaction commands require a MariaDB connection. The IPC response wait is queryTimeoutMs + 30,000 milliseconds, separately from native execution and durable commit. A response timeout does not cancel an already admitted commit; inspect the resulting state before retrying a write.
Results contain columns: { name, type, nullable }[], positional rows, and affectedRows. Integer values with 64-bit types and decimal values are returned as strings to avoid precision loss. Smaller integers, finite floating-point values, and booleans use their JSON primitives. Temporal and extended analytic values use strings; binary values use 0x followed by hexadecimal bytes. SQL NULL is null.
MariaDB clients and transactions
The official mariadb Node connector is used in integration tests, including its binary prepared-statement protocol:
import * as mariadb from 'mariadb';
const connection = await mariadb.createConnection({
...db.address,
user: 'app',
password,
});
try {
await connection.beginTransaction();
await connection.execute('INSERT INTO samples VALUES (?, ?, ?)', [2n, 'worker', 18.5]);
await connection.commit();
} catch (error) {
await connection.rollback();
throw error;
} finally {
await connection.end();
}
Supported commands include connection authentication with mysql_native_password, database selection, ping, text queries, prepare/execute/close/reset, bounded long parameter data, and connection reset. Prepared results preserve integer and binary values. Temporal results are strings so Arrow precision is not silently reduced by a wire representation. The handshake version identifies SQLDB as 5.7.0-SQLDB-0.1.0; it is a protocol compatibility identifier, not a claim that this is MySQL 5.7.
BEGIN, START TRANSACTION, COMMIT, ROLLBACK, and SET autocommit = 0/1 support repeatable snapshot reads. Writes remain private until commit. A failed statement preserves earlier staged writes. Disconnect or reset rolls back uncommitted work. Result packets expose the actual transaction/autocommit status.
Every commit is a write-set applied to the current snapshot. Transactions, UPDATE, DELETE, INSERT … SELECT and DDL validate against the relational revision they read: if any other writer committed since, the commit fails with error 1213 and SQLSTATE 40001; roll back and retry the complete application transaction. This check covers all tables, including unrelated ones. An autocommit INSERT whose values read no table is an append instead: it applies to the state current at commit, so concurrent inserters into the same table do not conflict. Appends to part tables, and their flushes, do not change the relational revision, so they never make a relational transaction fail. Key constraints and the capacity limit are checked again against that state, so a key inserted concurrently by another session fails with error 1062. DDL implicitly commits earlier staged writes before executing. Nested transactions and savepoints are unsupported.
One group committer applies queued write-sets in order and persists each drained group in a single redb transaction, with one fsync, before it publishes the new in-memory snapshot and acknowledges the group. An acknowledgement always means the commit is durable; there is no relaxed mode. A write-set is applied completely or not at all. An I/O error during commit makes the engine reject further queries until reopened, because commit durability may be indeterminate. Tests cover constraint atomicity, competing transactions, eight concurrent insert streams, restart, and recovery after SIGKILL following acknowledged commits and during group commits.
Storage
File mode keeps the database in one redb file in storage format 2:
| redb table | Key | Value |
|---|---|---|
sqldb.meta |
name | Format 2, database UUID and name, revision, relational revision, and table, batch and part ID counters |
sqldb.tables |
table ID | Table name, kind and DDL |
sqldb.relational_batches |
table ID, batch ID | One Zstandard-compressed Arrow IPC batch |
sqldb.buffer |
table ID, batch ID | One Zstandard-compressed Arrow IPC batch of buffered part-table rows |
sqldb.parts |
table ID, part ID | JSON part record: row count, byte length, SHA-1 checksum, sort key, and each column's NULL count, minimum and maximum |
A table record's kind is relational or columnar. A commit writes only new and removed batches and parts, the records of created or dropped tables, and the counters; it never rewrites the whole catalog. Unchanged batches retain their durable IDs. Table, batch and part IDs are never reused, so a dropped and recreated table never receives rows computed for its predecessor. Opening fails if a stored batch, buffer entry or part has no owning table of the right kind. Part files are described in the next section.
Storage format 1, written by sqldb 0.3.0 and earlier, is refused when the file is opened, and the error names 0.3.0. There is no migration; the refused file keeps its tables and rows.
Part tables
ENGINE = COLUMNAR creates a part table for append-heavy metrics, logs and events which may be larger than memory. Its rows are stored on disk as immutable Parquet parts sorted by the table's ORDER BY:
CREATE TABLE requests (
recorded TIMESTAMP NOT NULL,
service VARCHAR(64) NOT NULL,
latency DOUBLE,
bytes BIGINT DEFAULT 0
) ENGINE = COLUMNAR ORDER BY (service, recorded);
ENGINE = COLUMNARfollows the column definitions and is followed byORDER BYwith one or more column names; a single name may omit the parentheses. EveryORDER BYcolumn must beNOT NULL; a nullable one is refused with an error naming it. Parts are sorted ascending. Expressions,ASC/DESC, other engines, other table options andORDER BYwithoutENGINE = COLUMNARare refused. The kind is fixed when the table is created.PRIMARY KEYandUNIQUEare refused.NOT NULL,DEFAULT,VARCHAR(n)and the BLOB limit are checked for every inserted row, as for relational tables.- Part tables accept
INSERTandSELECT, including joins with relational tables.UPDATEandDELETEfail with error 1235. Concurrent autocommit inserts whose values read no table never conflict. Rows inserted inside a transaction become visible and durable with its commit.
An acknowledged insert is durable in the buffer, sqldb.buffer. A background flusher sorts a table's buffered rows and writes them as one part when that table's buffer reaches maxMemoryBytes / 16 Arrow bytes (at most 128 MiB), or when the buffers of all part tables together exceed maxMemoryBytes / 4, largest buffer first. There is no time-based flush, because buffered rows are already durable; queries read them together with the parts. Parts use Zstandard compression and page-level statistics, which include the page index. FLOAT and DOUBLE columns carry no statistics in part files, because Parquet statistics leave out NaN; the catalog's minimum and maximum for those columns include NaN, so parts are still skipped by them.
A query first skips the parts whose catalog minimum, maximum and NULL counts rule out its filters, without opening their files. It reads the remaining parts through DataFusion's Parquet reader, which skips row groups and pages by their statistics and applies the filters while decoding, and adds the buffered rows. The scan declares the parts' sort order, so ordered queries over parts with disjoint key ranges need no sort. A query or transaction pins the parts of the snapshot it reads.
A background merger combines parts of similar size, so a table keeps few parts however long it ingests. A part's size tier is the base-4 logarithm of its file length. Once a tier holds four parts, up to eight of them are merged into one sorted part, usually of a higher tier; every merge removes at least three parts, so merging always ends, and a table with no merge due holds at most three parts per tier. The table with the most parts merges first, starting with its smallest due tier. One merge thread runs; a merge reads its parts a page at a time, merges their rows in sort key order with DataFusion's sort-preserving merge and writes them straight into the new part, so its memory does not grow with the size of the parts. The merged rows are checked to be in sort key order, and the catalog statistics of a merged part are computed from its rows, as for a flushed part. Parts being merged are reserved, so no two merges read the same part.
An INSERT into a part table waits while the table holds more than 64 parts, because merges are behind. When queryTimeoutMs runs out, it fails with error 1205 (SQLSTATE HY000) and inserts nothing; retry it. The timeout covers the wait and the statement together. Reads and inserts into other tables do not wait.
Part files live in <path>.data/parts/<table-id>/<part-id>.parquet; <path>.data/spill/ is reserved for query spill files. A flush or merge proceeds in this order:
- Write the part file.
- Sync the file, then its directory.
- Commit, in one durable redb transaction, the part record together with the removal of the flushed buffer batches, or of the merged parts.
- Publish the new snapshot.
- Delete a part file that a commit removed (by a merge or by
DROP TABLE) only after that commit is durable and no running query, open transaction or merge still reads the part.
If writing a part fails, for example on a full or read-only disk, the rows stay buffered, the error is written to standard error, and the flush is retried after one second, doubling up to 30 seconds; queries and inserts continue. A failed merge leaves its parts as they are and is retried the same way. Stopping the server cancels a running merge. A failed catalog commit stops the engine, as for every commit.
Opening a database checks that every part the catalog references exists with its recorded length; otherwise opening fails with an error naming the part file. Only then are files and directories in <path>.data/parts that the catalog does not reference deleted, and spill/ cleared. Checksums are recorded for backups and are not verified at open. Opening reads no part data, so open time and memory do not grow with the table.
In memory mode, part tables accept the same SQL. Their rows stay in the buffer and count toward maxMemoryBytes like relational rows; memory mode never writes parts.
Current limits:
- Inserts do not wait for the flusher. A writer that outpaces it fails with
Database exceeds configured memory capacityonce buffered rows reachmaxMemoryBytes; retry after the flush. - The merge factor, the parts limit and the single merge thread are fixed.
INSERT … SELECTcollects its input in memory, withinmaxMemoryBytes.- Partitions, TTL,
ALTER TABLEand skip indexes are not implemented.
SQL and metrics
Supported operations include basic CREATE TABLE, DROP TABLE, INSERT VALUES, INSERT SELECT, UPDATE, DELETE, SELECT, joins, CTEs, grouping, ordering, and window queries. Immediate primary/unique constraints, NULL restrictions, defaults, VARCHAR(n) character limits, and the 65,535-byte BLOB limit are enforced before commit. Text comparisons use Arrow's case-sensitive semantics; MariaDB's default case-insensitive collations are not emulated.
Common numeric types, DECIMAL, TEXT, VARCHAR, BLOB, DATE, and TIMESTAMP map to Arrow storage. Unsupported types or modifiers must return an error. DATETIME, fixed-width character padding, custom collations, foreign keys, CHECK constraints, AUTO_INCREMENT, ALTER TABLE, views, triggers, stored routines, replication, and secondary indexes are not implemented. Use explicit aliases for projected expressions that would otherwise have duplicate names. SQL functions and casts follow DataFusion semantics where they differ from MariaDB; clients must not assume server-wide MariaDB function parity.
The metrics engine provides column projection, parallel batch execution, exact aggregations, approximate percentiles, time bucketing, and windows:
SELECT service,
COUNT(*) AS requests,
AVG(latency) AS mean_latency,
APPROX_PERCENTILE_CONT(latency, 0.95) AS p95_latency
FROM samples
GROUP BY service;
-- For a table with a TIMESTAMP column named recorded:
SELECT date_trunc('hour', recorded) AS hour, COUNT(*) AS samples
FROM readings GROUP BY hour ORDER BY hour;
These are ClickHouse-inspired columnar analytics capabilities. SQLDB does not implement ClickHouse's protocol, MergeTree, distributed execution, or its full SQL dialect. Relational tables and part-table buffers are loaded into memory on startup and must fit the configured capacity; part tables keep their parts on disk. Constraint validation scans candidate keys, and updates/deletes can rebuild batches. No secondary-index or sustained-ingestion throughput guarantee is made.
Run pnpm benchmark for a reproducible one-million-row workload with 100 metric series. It validates counts, sums, means, and percentile bounds before reporting one warmup and five measured grouped queries. Timings include IPC and response encoding. It reports the CPU, architecture, runtime, ingestion time, and stored columnar bytes. Results describe that workload and machine, not comparative performance against MariaDB or ClickHouse.
pnpm benchmark:timeseries --scale 10m (or 500m) runs the larger-than-memory harness in Docker: the engine, a reference server and the load generator each run in their own container capped at two CPUs and 2 GiB without swap. A deterministic generator produces seven metric columns (ts TIMESTAMP(3), 1,000 services, 100 hosts, status, latency, bytes and a 32-character request ID). The harness measures durable ingestion with one and four clients in 10,000-row batches, a last-hour grouped query, a full-scan aggregate, a one-million-group GROUP BY and a request-ID lookup, validates every answer against the generator, and records rows per second, p50/p95 latency, bytes on disk, cgroup memory peaks, open time and RSS after open. Results stay in .nogit/bench/ and are not published in this document.
Limits and deployment boundary
The listener defaults to 127.0.0.1; ::1 is also supported. A wildcard host (0.0.0.0 or ::) requires explicit allowRemoteConnections: true, validated independently by TypeScript and Rust. This option supports container/private-network deployments and keeps authentication mandatory. It requires an explicit username and password of 8–1024 UTF-8 bytes. SQLDB does not support TLS, multiple users, privileges, or multiple databases per instance. Keep the plaintext MariaDB protocol on an isolated network or publish the container port only on host loopback. SQL cannot read arbitrary files, register external data sources, write exports, or change DataFusion's engine configuration. Run the engine with OS permissions appropriate for its database file.
| Option | Default | Meaning |
|---|---|---|
maxConnections |
64 | Concurrent sockets, including authentication |
maxMemoryBytes |
512 MiB | Capacity for relational rows and part-table buffers, committed and candidate, and, separately, the shared query scratch budget |
maxResultRows |
100,000 | Rows returned by one query |
maxResultBytes |
8 MiB | Arrow result and encoded response capacity |
queryTimeoutMs |
30,000 | Statement planning/execution deadline; durable commit is outside this deadline |
shutdownTimeoutMs |
120,000 | Maximum wait for shutdown drain before a failed stop terminates the child |
The memory setting is not a process RSS limit. Retained snapshots, simultaneous transactions, buffers, and engine overhead can consume additional memory. Query spilling to disk is disabled. Apply process/container limits when needed. There are also fixed limits of 64 KiB SQL, 256 parameters, 1 MiB wire packets and long data per connection, 128 prepared statements per connection, and 16 management queries in flight. Authentication has a five-second deadline, idle connections five minutes, and response writes ten seconds.
stats() returns the catalog revision as a string (it counts every commit, including part flushes and merges), table count, row count (including rows in parts), approximate in-memory Arrow bytes, partTables with each ENGINE = COLUMNAR table's part count and merge backlog (size tiers holding four or more parts, running merges included), and active socket count. binaryPath or SQLDB_RUST_BINARY explicitly selects a native executable; ordinary discovery uses packaged platform binaries and dist_rust. It does not search the system PATH.
License and Legal Information
This repository contains open-source code licensed under the MIT License. A copy of the license can be found in license.md. Third-party dependency licenses and notices are retained in third_party_licenses/; their terms apply to the corresponding components.
Please note: The MIT License does not grant permission to use the trade names, trademarks, service marks, or product names of the project, except as required for reasonable and customary use in describing the origin of the work and reproducing the content of the NOTICE file.
Trademarks
This project is owned and maintained by Task Venture Capital GmbH. The names and logos associated with Task Venture Capital GmbH and any related products or services are trademarks of Task Venture Capital GmbH or third parties, and are not included within the scope of the MIT license granted herein.
Use of these trademarks must comply with Task Venture Capital GmbH's Trademark Guidelines or the guidelines of the respective third-party owners, and any usage must be approved in writing. Third-party trademarks used herein are the property of their respective owners and used only in a descriptive manner, e.g. for an implementation of an API or similar.
Company Information
Task Venture Capital GmbH
Registered at District Court Bremen HRB 35230 HB, Germany
For any legal inquiries or further information, please contact us via email at hello@task.vc.
By using this repository, you acknowledge that you have read this section, agree to comply with its terms, and understand that the licensing of the code does not imply endorsement by Task Venture Capital GmbH of any derivative works.