ULTSQL Documentation
Complete technical guide for ULTSQL: the converged multi-model database engine combining Relational SQL, NoSQL Dotted JSON, Redis-style KV caching, and AI Vector RAG Search on a unified slotted-page core.
1. System Architecture & Overview #
ULTSQL is an autonomous, converged database engine implemented in 100% pure Dart. Unlike traditional systems that require running separate database daemons for relational queries (PostgreSQL/SQLite), document storage (MongoDB), high-speed key-value caching (Redis), and vector embeddings (Milvus/Pinecone), ULTSQL unifies all four operational models into a single engine binary and single storage container.
pip install ultsql) or Node.js (npm install ultsql), a small bundled engine process starts automatically on a free port and manages lifecycle cleanlyβno external services, background daemons, or C-compiler toolchains required. On pub.dev, package:ultsql currently bundles Flutter dev-tooling & UI services (compatible with all Flutter platforms). For standalone CLI usage, install the native binary via install.sh / install.ps1 or deploy the official Docker container.
Storage is structured around a slotted-page architecture with an ARIES-compliant Write-Ahead Log (WAL), CRC32 integrity checksums, bottom-up B+ Tree construction, and graph-based HNSW vector indexing.
2. Installation & SDKs #
Install ULTSQL in your language environment or install the standalone CLI globally.
# Add to Dart / Flutter pubspec.yaml
dart pub add ultsql
# Or Flutter:
flutter pub add ultsql
3. Quickstart Guide #
Initialize an embedded database instance, create a table with primary key constraints, insert records, and query with high-speed interpreted execution.
import 'package:ultsql/ultsql.dart';
void main() async {
// 1. Open persistent disk database (or ':memory:' for RAM)
final db = Database('./my_database');
await db.init();
final it = Interpreter(db);
// 2. Create relational schema
await it.executeScript('''
CREATE TABLE users (
id INT PRIMARY KEY,
name TEXT,
email TEXT,
created_at TEXT
);
''');
// 3. Insert records
await it.executeScript('''
INSERT INTO users VALUES (1, 'Alice Smith', '[email protected]', '2026-10-01');
INSERT INTO users VALUES (2, 'Bob Jones', '[email protected]', '2026-10-02');
''');
// 4. Query with standard SQL
final res = await it.executeScript("SELECT name, email FROM users WHERE id = 1;");
for (final row in res.rows) {
print('User: ${row[0]} <${row[1]}>');
}
// 5. Clean shutdown (flushes cache and closes WAL)
await db.close();
}
4. Storage Modes #
ULTSQL offers four switchable persistence profiles depending on performance and durability requirements:
| Storage Mode | Initialization | Throughput (Measured) | Crash Safety | Recommended Use Case |
|---|---|---|---|---|
| β‘ In-Memory | Database(':memory:') |
~140kβ200k rows/s | Ephemeral (RAM) | Automated unit tests, fast ephemeral caches, session states |
| πΎ Durable Disk | Database('./path') |
~670kβ718k rows/s | ACID WAL + CRC32 | Production transactional OLTP, embedded applications, microservices |
| π Hybrid Snapshot | Database('./path', syncWal: false) |
~1.2M rows/s | Periodic Snapshots | Time-series ingestion, sensory IoT streams, high-volume event logs |
| β‘ Public Batch API | insertBatchRecordsSync() |
~2.08M rows/s | Slotted-Page Direct Packing | ETL bulk loading, initial data migrations, parquet conversions |
5. SQL & Dialect Reference #
ULTSQL supports an ANSI-SQL 92/99 dialect with modern PostgreSQL-style extensions including JSON operators, vector types, and procedural scripting.
Data Types
| SQL Type | Dart Representation | Storage Footprint | Description |
|---|---|---|---|
INT / INTEGER / BIGINT |
DbInt (int) |
8 bytes | 64-bit signed integer |
DOUBLE / FLOAT / REAL |
DbDouble (double) |
8 bytes | 64-bit IEEE floating point |
TEXT / VARCHAR |
DbText (String) |
Variable (UTF-8) | Arbitrary-length text string |
BOOLEAN / BOOL |
DbBool (bool) |
1 byte | True or false boolean |
BLOB |
DbBlob (Uint8List) |
Variable | Raw binary byte array |
JSON |
DbJson (Map/List) |
Variable (Structured) | Dotted schema-less JSON object |
VECTOR |
DbVector (List<double>) |
4 bytes/dimension | Fixed-dimension float32 embedding vector |
UUID |
DbUuid (String) |
16 bytes packed | Standard RFC 4122 UUID |
DATETIME / TIMESTAMP |
DbDateTime (DateTime) |
8 bytes (epoch ms) | ISO 8601 UTC timestamp |
DECIMAL(p, s) |
DbDecimal |
Variable | Exact fixed-precision decimal arithmetic |
DDL (Data Definition Language)
-- Create table with constraints
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
amount DOUBLE DEFAULT 0.0,
status TEXT,
metadata JSON,
embedding VECTOR,
placed_at DATETIME
);
-- Modify schema
ALTER TABLE orders ADD COLUMN discount DOUBLE;
DROP TABLE IF EXISTS old_orders;
-- Inspect database metadata
SELECT * FROM information_schema.tables;
SELECT * FROM information_schema.columns WHERE table_name = 'orders';
DML & Query Capabilities
-- Multi-row batch insert
INSERT INTO orders VALUES
(101, 1, 149.99, 'completed', '{"tier": "gold"}', '[0.1, 0.2, 0.3]', '2026-10-01T12:00:00Z'),
(102, 2, 89.50, 'pending', '{"tier": "silver"}', '[0.4, 0.5, 0.6]', '2026-10-02T14:30:00Z');
-- UPSERT / REPLACE
REPLACE INTO orders VALUES (101, 1, 139.99, 'refunded', '{"tier": "gold"}', '[0.1, 0.2, 0.3]', '2026-10-01T12:00:00Z');
-- Aggregations with GROUP BY and HAVING
SELECT
status,
COUNT(*) AS total_orders,
AVG(amount) AS avg_amount,
SUM(amount) AS gross_revenue
FROM orders
WHERE amount > 50.0
GROUP BY status
HAVING COUNT(*) >= 1
ORDER BY gross_revenue DESC
LIMIT 10 OFFSET 0;
-- Query Planner Execution Plan
EXPLAIN SELECT * FROM orders WHERE order_id = 101;
6. Prepared Statements & Batch API #
Prepared statements compile the SQL query string into an execution plan once, allowing parameterized parameters to be executed repeatedly without re-parsing overhead.
final stmt = db.prepare('INSERT INTO metrics VALUES (?, ?, ?);');
// 1. Single parameterized execution (sub-5Β΅s point write)
stmt.executeSync([DbInt(101), DbText('cpu_load'), DbDouble(0.42)]);
// 2. High-speed synchronized batch insertion (~670k rows/sec)
final batch = List<List<DbValue>>.generate(50000, (i) {
return [DbInt(i), DbText('metric_$i'), DbDouble(i * 0.1)];
});
stmt.executeBatchSync(batch);
// 3. Query parameterized prepared statement
final queryStmt = db.prepare('SELECT val FROM metrics WHERE tag = ?;');
final result = queryStmt.executeSync([DbText('cpu_load')]);
print('Metric value: ${result.rows.first.first}');
7. B+ Tree Indexes & Bottom-Up Construction #
ULTSQL builds standard B+ Tree indexes on disk with bottom-up bulk index construction. Instead of performing N random top-down tree insertions, creating an index on existing tables sorts leaf keys sequentially and constructs upper index layers in a single cache-coherent pass.
-- Standard B+ Tree Index on single column
CREATE INDEX idx_orders_customer ON orders(customer_id);
-- Compound / Multi-column Index
CREATE INDEX idx_orders_status_amount ON orders(status, amount);
-- Unique Constraint Index
CREATE UNIQUE INDEX idx_users_email ON users(email);
-- Trigram Substring & Wildcard Index (Accelerates LIKE '%pattern%')
CREATE INDEX idx_products_name ON products(name) USING TRIGRAM;
SELECT * FROM products WHERE name LIKE '%quantum%';
-- Drop Index
DROP INDEX idx_orders_customer;
8. ACID Transactions & MVCC #
Transactions in ULTSQL guarantee full ACID semantics:
- Atomicity: Statements executed inside
BEGIN...COMMITeither all commit to disk or are completely discarded onROLLBACK. - Consistency: Schema constraints, unique keys, and primary key requirements are validated before slot assignment.
- Isolation: Multi-Version Concurrency Control (MVCC) ensures read operations never block write operations and writes never block reads.
- Durability: Changes are recorded sequentially in the Write-Ahead Log before acknowledging commit.
BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100.0 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100.0 WHERE account_id = 2;
-- In case of failure: ROLLBACK;
COMMIT;
Multi-Process Concurrency & Shared Read Locks
In addition to in-process MVCC, ULTSQL provides OS-level shared file locks (FileLock.shared) when opening a database with readOnly: true. Multiple separate OS processes and background isolates can read and query the same database file simultaneously without lock collisions, with configurable lockTimeout retry escalation.
final readerDb = Database(
'./production.ultsql',
readOnly: true,
lockTimeout: Duration(seconds: 5),
);
await readerDb.init(); // Acquires shared lock, allowing other processes to read concurrently
9. ARIES WAL, Checkpoints & Crash Recovery #
ULTSQL utilizes an ARIES-style Write-Ahead Log (`wal.log`) fortified with CRC32 checksums on every log
entry. If the host machine loses power, crashes, or terminates abruptly, the engine automatically engages
recovery upon startup during db.init():
- Analysis Phase: Identifies unwritten dirty pages in the slotted-page cache and locates active uncommitted transactions.
- Redo Phase: Replays committed page updates from the log sequentially to recover database state.
- Undo Phase: Rolls back operations from transactions that were active during the crash.
-- Checkpoint active WAL into main database table pages
SET engine_option wal_checkpoint = true;
-- Enable automated autovacuum to reclaim fragmented slots
SET engine_option enable_autovacuum = true;
10. Converged NoSQL Document Store #
Store and query schema-less JSON documents using MongoDB-compatible collection APIs directly inside ULTSQL with zero configuration:
final users = db.collection('customers');
// 1. Insert single document (auto-generates 24-char unique _id)
final doc = await users.insertOne({
'name': 'Sarah Connor',
'email': '[email protected]',
'profile': {
'score': 95000,
'tags': ['vip', 'verified'],
'location': {'city': 'Los Angeles', 'country': 'USA'}
}
});
// 2. High-speed batch ingestion (~92k docs/sec)
await users.insertMany([
{'name': 'John Connor', 'profile': {'score': 88000, 'tags': ['member']}},
{'name': 'Kyle Reese', 'profile': {'score': 72000, 'tags': ['veteran']}},
]);
// 3. Fast point-read by _id (direct B+ Tree index lookup: ~4Β΅s)
final found = await users.findOne({'_id': doc.id});
print('Found user: ${found?.getByPath('name')}');
// 4. Deep nested dotted-path queries with pagination & sorting
final cursor = users.find({
'profile.score': {r'$gte': 80000},
'profile.location.country': 'USA',
})
.sort({'profile.score': -1})
.limit(10);
final vips = await cursor.toList();
// 5. In-place atomic mutations ($set, $inc, $unset, $push)
await users.updateOne(
filter: {'_id': doc.id},
update: {
r'$set': {'profile.location.city': 'San Francisco'},
r'$inc': {'profile.score': 500},
},
);
// 6. Delete document
await users.deleteOne({'name': 'Kyle Reese'});
Supported MongoDB Filter Operators
| Filter Operator | Meaning | Example Syntax |
|---|---|---|
$eq / $ne |
Equality / Inequality | {'role': {'$eq': 'admin'}} |
$gt / $gte |
Greater than / Greater or equal | {'profile.score': {'$gte': 90000}} |
$lt / $lte |
Less than / Less or equal | {'age': {'$lt': 30}} |
$in / $nin |
Array membership | {'role': {'$in': ['admin', 'dev']}} |
$exists |
Key existence check | {'profile.location': {'$exists': true}} |
$regex |
Regular expression match | {'email': {'$regex': r'@domain\.io$'}} |
11. Redis-Style Embedded Key-Value Engine #
ULTSQL embeds an ultra-high-throughput Key-Value caching engine with in-memory hot lookups (876,000+ ops/sec), atomic WAL persistence, TTL expiration, and atomic counters:
// 1. Set key with TTL expiration
await db.kv.set('session:user_101', 'token_xyz123', ttl: const Duration(hours: 2));
// 2. High-speed hot in-memory get (< 1.5 Β΅s latency)
final token = await db.kv.get('session:user_101');
// 3. Batch set in single WAL transaction (~342k ops/sec)
await db.kv.mset({
'config:timeout': 30,
'config:max_conns': 100,
'config:endpoint': 'https://api.ultsql.io',
});
// 4. Multi-key read
final configs = await db.kv.mget(['config:timeout', 'config:max_conns']);
// 5. Atomic increment and decrement counters
final views = await db.kv.incr('stats:page_views'); // 1
final views5 = await db.kv.incr('stats:page_views', 5); // 6
final active = await db.kv.decr('stats:page_views', 2); // 4
// 6. Pattern wildcard key scanning
final keys = await db.kv.keys(pattern: 'config:*');
// 7. Delete key
await db.kv.delete('session:user_101');
12. Cross-Model SQL β NoSQL Bridge #
Query NoSQL document collections directly from SQL using the collection('name') function and
dotted navigation operators (-> and ->>), or join relational SQL tables with
schema-less collections in the same query:
-- Query document collection using relational SQL with dotted navigation
SELECT
_id,
doc->>'serial' AS dev_serial,
(doc->'telemetry'->>'battery')::INT AS battery
FROM collection('devices')
WHERE (doc->'telemetry'->>'battery')::INT > 80;
-- Join a relational table with a NoSQL document collection
SELECT
o.order_id,
o.amount,
u.doc->>'name' AS customer_name,
u.doc->>'email' AS customer_email
FROM orders o
JOIN collection('customers') u ON o.customer_id = u._id
WHERE o.amount > 100.0;
13. AI-Native Vector Search & RAG (HNSW) #
ULTSQL natively implements Hierarchical Navigable Small World (HNSW) graph indexes directly inside the database engine. There is no need for external vector databases like Pinecone or Milvus. Store high-dimensional embeddings and execute sub-millisecond Approximate Nearest Neighbor (ANN) vector similarity search with full ACID durability.
-- 1. Create table with VECTOR column
CREATE TABLE documents (
id INT PRIMARY KEY,
title TEXT,
content TEXT,
embedding VECTOR
);
-- 2. Insert embeddings
INSERT INTO documents VALUES
(1, 'Database Engine', 'ULTSQL combines SQL and NoSQL in pure Dart', '[0.12, 0.88, 0.45]'),
(2, 'Machine Learning', 'Vector search powers generative AI RAG applications', '[0.15, 0.82, 0.40]'),
(3, 'Computer Vision', 'Deep CNNs analyze visual patterns in images', '[-0.85, 0.12, -0.35]');
-- 3. Construct HNSW Vector Index on disk
CREATE INDEX idx_docs_emb ON documents(embedding) USING HNSW;
-- 4. Execute Top-K Nearest Neighbor Semantic Search
SELECT
id,
title,
content,
vector_distance(embedding, '[0.14, 0.85, 0.42]') AS distance
FROM documents
ORDER BY distance ASC
LIMIT 2;
14. Security, Encryption & Envelopes #
ULTSQL provides enterprise-grade data security at rest and in transit:
- AES-256-GCM Authenticated Encryption: Encrypt database files, pages, and WAL records with military-grade authenticated ciphers.
- Dual Authenticated Envelopes: Choose between speed-optimized and security-hardened cryptographic envelope modes.
- Tamper Traps: Any unauthorized bit modification on disk triggers active tamper trap
detection, raising
DatabaseIntegrityException. - Zeroization: Cryptographic keys in memory are automatically zeroized upon database closure.
// Open encrypted database with secret passphrase
final db = Database(
'./secure_db',
passphrase: 'super_secret_master_key_99',
);
await db.init();
15. PostgreSQL Wire Protocol Server #
ULTSQL can run as a standalone or embedded network database daemon implementing the PostgreSQL Wire Protocol
v3.0 (port 5432). Connect using existing tools like psql, DBeaver, TablePlus, or native drivers
across all languages:
import 'package:ultsql/ultsql.dart';
void main() async {
final db = Database('./production_db');
await db.init();
// Start PGWire TCP server on port 5432
final server = PgWireServer(db, port: 5432);
await server.start();
print('ULTSQL PostgreSQL Wire Server listening on port 5432...');
}
# Connect using standard PostgreSQL CLI
psql -h localhost -p 5432 -U postgres -d ultsql
# Output:
# Welcome to psql 16.2.
# Connected to: ULTSQL Engine v1.0.32 (Pure Dart)
# ultsql=> SELECT 1 + 1;
Distributed Raft Consensus & Delta-CRDT Replication
ULTSQL includes production-grade clustering and synchronization:
- Raft Consensus Engine: Multi-node clustering with leader election, randomized election timers (150β300ms), term leases, heartbeat loops, and Quorum Log Replication with state machine callbacks.
- Delta-CRDT with Hybrid Logical Clocks (HLC): Offline peer-to-peer state merging with causal ordering and tombstone deletion propagation.
final cluster = RaftCluster('node-1', peerNodes: ['node-2', 'node-3']);
await cluster.start(); // Initiates leader election and quorum replication
16. Embedded REST Server #
Need HTTP access from web applications or serverless lambdas? Start the built-in REST server:
// Defaults to strictly 127.0.0.1 for local network security
final rest = RestServer(db, host: '127.0.0.1', port: 8080);
await rest.start();
print('REST API active on http://127.0.0.1:8080');
// Or launch from terminal via: ultsql dev --db ./app.ultsql
| Method | Route | Payload | Description |
|---|---|---|---|
POST |
/api/query |
{"sql": "SELECT * FROM users"} |
Executes SQL query and returns JSON rows |
GET |
/api/health |
None | Returns engine health, uptime, and page cache stats |
GET |
/api/metrics |
None | Returns Prometheus-compatible telemetry metrics |
17. CLI & REPL Commands #
The ultsql CLI provides an interactive REPL with syntax highlighting, autocomplete,
dot-commands, and headless CI/CD batch execution:
# 1. Launch interactive REPL
ultsql ./app.ultsql
# 2. Local development server on 127.0.0.1:8080 with live logs
ultsql dev --db ./app.ultsql
# 3. Production REST & OpenAPI daemon
ultsql serve --host 127.0.0.1 --port 8080 --db ./app.ultsql
# 4. Interactive dot-commands inside REPL:
.tables # List all tables and row counts
.schema users # Display CREATE TABLE DDL
.collections # List all NoSQL document collections
.kv list config:* # Scan KV keys matching pattern
.mode json # Switch output mode (box, json, csv, markdown)
.quit # Exit REPL
# 5. Headless CI/CD execution with JSON output piped to jq
ultsql ./app.ultsql -m json -c "SELECT * FROM users;" | jq .
# 6. Ingest via MongoDB syntax directly in CLI
db.users.insertOne({"name": "Diana", "role": "admin"})
18. Native Multi-Language SDK Bindings #
Native client SDKs connect seamlessly to embedded or remote ULTSQL instances. For Python and Node.js, the package operates as a Client SDK that connects to a server or manages an opt-in local engine helper process on loopback (127.0.0.1) with automatic process reuse and clean exit hooks:
Python / Node.js Application β UltSQL Client SDK β local UltSQL engine (127.0.0.1) β database file
const { UltSQL, UltSQLClient } = require('ultsql');
// Mode 1: Convenient Local Mode (auto-starts engine on 127.0.0.1 with dynamic port)
const db = await UltSQL.open('./app.ultsql', { auto_start: true });
console.log(await db.status()); // { status: 'online', host: '127.0.0.1', port: 54321, pid: ... }
await db.sql('CREATE TABLE users (id INT PRIMARY KEY, name TEXT);');
await db.insert('users', { id: 1, name: 'Alice' });
const res = await db.query('users');
console.log(res.rows);
// Clean shutdown (also auto-cleaned on process exit)
db.close();
// Mode 2: Client Mode (connecting to existing server)
const remote = new UltSQLClient('http://127.0.0.1:8080');
19. Engine Configuration & Pragmas #
Fine-tune runtime engine parameters via SQL pragmas or Dart constructors:
| Option | Default | Values | Description |
|---|---|---|---|
page_size |
4096 | 1024β65536 | Storage page size in bytes |
cache_capacity |
10000 | 100β1000000 | Maximum pages retained in hot LRU cache |
wal_synchronous |
NORMAL | OFF, NORMAL, FULL | WAL fsync flush behavior on commit |
enable_autovacuum |
true | true, false | Automatic background slot compaction |
auto_create_indexes |
false | true, false | Autonomous index creation based on query telemetry |
20. Errors, Troubleshooting & FAQ #
Frequently Encountered Exceptions
| Exception Class | Cause | Resolution |
|---|---|---|
DatabaseLockException |
Another process holds an exclusive lock on the database file. | Ensure only one process opens the database in read-write mode, or use PGWire/REST server for multi-process concurrency. |
DatabaseIntegrityException |
CRC32 checksum mismatch or disk tampering detected. | Check disk hardware integrity; restore from WAL or backup snapshot. |
TableNotFoundException |
The referenced table or collection does not exist in the catalog. | Verify spelling or run CREATE TABLE / db.collection('name') before
querying. |
Frequently Asked Questions
21. Model Context Protocol (MCP) Server & AI Agent Skills #
ULTSQL includes native, first-class support for AI agent integration via the standardized Model Context Protocol (MCP) (JSON-RPC over stdio) and official agent skills. This allows AI assistants such as Claude Desktop, Cursor, Antigravity, and VS Code Roo Code to execute embedded database operations autonomously.
Starting the MCP Server
# Start via standalone CLI
ultsql mcp
# Or start via Dart SDK directly
dart run bin/ultsql_mcp_server.dart
Client Configuration (Claude Desktop / Cursor / Antigravity)
{
"mcpServers": {
"ultsql": {
"command": "dart",
"args": ["run", "bin/ultsql_mcp_server.dart"]
}
}
}
Available MCP Tools
| Tool Name | Description |
|---|---|
ultsql_query |
Executes ANSI SQL, DDL, DML, CTEs, Window functions, and PL/SQL procedural blocks. |
ultsql_describe_schema |
Introspects tables, views, columns, types, primary keys, and real-time row counts. |
ultsql_batch_insert |
Vector-fast batch ingestion (up to 4.07M+ rows/sec) directly into 4KB slotted pages. |
ultsql_doc_insert |
Stores schema-less JSON documents with auto-generated UUIDs and secondary indexes. |
ultsql_doc_find |
Queries documents with rich MongoDB-style filters ($gt, $in, dotted paths). |
ultsql_kv_set / ultsql_kv_get |
Sub-microsecond key-value caching with optional TTL expiration. |
ultsql_vector_search |
Sub-2ms HNSW k-NN nearest-neighbor vector similarity search (cosine, l2, dot). |
ultsql_explain |
Generates physical Volcano iterator execution plan diagnostics (EXPLAIN). |
.agents/skills/ultsql/SKILL.md. AI agents automatically discover and activate this skill when working on local storage, data analytics, or embedding search tasks.