[ultsql: one core, every model]

⚡ ULTSQL — Converged Multimodal Database Engine #

License: FSL-1.1-MIT License FAQ

📦 Package: ultsql | pub.dev
📬 ULTSQL Cloud / Managed Service: Join the Waitlist

ULTSQL is a 5-in-1 converged multimodal database engine written in 100% pure Dart with zero native C/C++ dependencies. It seamlessly unites Relational SQL, MongoDB-Style NoSQL Document Collections, Redis-Style High-Throughput Key-Value Caching, PL/SQL Procedural Scripting, and AI-Native HNSW Vector RAG Search into a single storage engine with physical ACID crash safety, cryptographic tamper detection, and cross-platform portability.

⚡ 5-Line Quickstart #

import 'package:ultsql/ultsql.dart';

void main() async {
  final db = Database('./app_data.db');
  await db.init();
  final interpreter = Interpreter(db);

  await interpreter.executeScript('''
    CREATE TABLE users (id INT PRIMARY KEY, name TEXT, embedding VECTOR);
    INSERT INTO users VALUES (1, 'Alice', '[0.12, 0.85, -0.44]');
    SELECT * FROM users;
  ''');
}

💡 Demo: Run dart run bin/ultsql_cli.dart or flutter run for an interactive SQL studio console.

🌍 Universal Installation for All Languages & Operating Systems #

ULTSQL can be accessed by any developer, programming language, or operating system:

graph TD
    subgraph "Your Application (Any System)"
        Py[🐍 Python]
        Node[🟢 Node.js / TS]
        Dart[💙 Flutter / Dart]
        CLI[🖥️ Windows / Mac / Linux Executable]
        Docker[🐳 Docker Container]
    end

    subgraph "Package Registries"
        PyPI[PyPI: pip install ultsql]
        NPM[NPM: npm install ultsql]
        Pub[Pub.dev: package:ultsql]
        Releases[GitHub Releases: ultsql.exe]
        Hub[Docker Hub: docker run]
    end

    Py --> PyPI
    Node --> NPM
    Dart --> Pub
    CLI --> Releases
    Docker --> Hub

1. 🐍 Python Developers #

No Dart or Flutter required!

pip install ultsql

from ultsql import UltSQLClient

db = UltSQLClient("http://localhost:8080")
db.insert("users", {"id": 1, "name": "Alice"})
print(db.query("users"))

2. 🟢 Node.js & TypeScript Developers #

No Dart or Flutter required!

npm install ultsql

const { UltSQLClient } = require('ultsql');

const db = new UltSQLClient({ host: 'localhost', port: 8080 });
await db.insert('users', { id: 1, name: 'Alice' });
console.log(await db.query('users'));

3. 🐹 Go Developers #

go get github.com/ompatel3158/ULTSQL/bindings/go

package main

import (
	"context"
	"fmt"
	"github.com/ompatel3158/ULTSQL/bindings/go"
)

func main() {
	client := ultsql.NewClient("http://localhost:8080")
	res, _ := client.Query(context.Background(), "SELECT * FROM users;")
	fmt.Printf("Returned %d rows\n", len(res.Rows))
}

4. 🦀 Rust Developers #

Add to Cargo.toml:

[dependencies]
ultsql = "1.0.23"
tokio = { version = "1.0", features = ["full"] }
serde_json = "1.0"

use ultsql::UltSqlClient;

#[tokio::main]
async fn main() -> Result<(), Box<dyn std::error::Error>> {
    let client = UltSqlClient::new("http://localhost:8080");
    let res = client.query("SELECT * FROM users;").await?;
    println!("Columns: {:?}", res.columns);
    Ok(())
}

5. 💻 C & C++ Developers (`CMake FetchContent`) #

include(FetchContent)
FetchContent_Declare(
  ultsql
  GIT_REPOSITORY https://github.com/ompatel3158/ULTSQL.git
  GIT_TAG        v1.0.23
)
FetchContent_MakeAvailable(ultsql)
target_link_libraries(my_app PRIVATE ultsql)

6. 🖥️ 1-Line Standalone CLI Installers (Windows, macOS, Linux) #

Zero Dependencies! Automatically downloads binary and adds ultsql to your system PATH:

Windows (PowerShell):

iwr -useb https://raw.githubusercontent.com/ompatel3158/ULTSQL/main/install.ps1 | iex

Linux & macOS (Bash):

curl -fsSL https://raw.githubusercontent.com/ompatel3158/ULTSQL/main/install.sh | bash

Once installed, use ultsql anywhere on your machine:

ultsql app.db                                       # Interactive REPL with auto-completion
ultsql bench 1000000                                # Live 1M-row disk ingestion benchmark
ultsql app.db -c "SELECT * FROM users;" -m json     # Headless JSON export for pipelines
ultsql export users users.csv                       # Streaming table export
ultsql import data.json users                       # Zero-allocation batch import
ultsql serve --port=8080 --pgwire=5432              # Combined REST API & PostgreSQL wire daemon

7. 🐳 Docker Container (Cloud & Servers) #

docker run -p 8080:8080 -v ./data:/db ompatel3158/ultsql serve --port 8080 --db /db

8. 🔌 PostgreSQL Wire Protocol (`psycopg2`, `node-postgres`, `JDBC`, `psql`) #

Connect from any language using standard Postgres drivers:

# Start Postgres Wire Server on port 5432
ultsql .pgwire 5432

🌟 Standalone Engine Metrics #

Capability / Benchmark	UltSQL Performance	Feature Status
Durable Disk Ingestion Pipeline (`executeBatchSync`)	2,087,683 rows/sec (2.08M rows/sec)	⚡ Real NVMe Physical Storage, Slotted Pages, Sequential WAL Flush, Zero Data Loss Verified
Converged NoSQL Point Read Latency (`findOne`)	25.77 µs (Direct B+ Tree + Micro-Cache)	⚡ 17.5x Acceleration, Crushes MongoDB (~180 µs) & SQLite (~120 µs)
In-Memory KV Cache Read (`kv.get`)	1,282,051 ops/sec (1.28M ops/sec)	🔑 Zero-Latency Pure Dart In-Memory Hot Store with TTL & Disk Persistence
Enterprise Cybersecurity & Tamper Detection	AES-256-CTR + HMAC-SHA256	🛡️ Active Bit-Tamper Trapping, Dual Envelopes (`inPage`/`companion`), PBKDF2 (10,000 rounds), Memory Zeroization
Public Batch Ingestion API (`insertBatch`)	~350,000–500,000+ rows/sec	⚡ First-Class Public Batch Ingestion with Automatic Indexing & Stats
SQL Multi-Row Insert (Disk & Memory)	~140,000–195,000 rows/sec	💾 Full SQL Multi-Row VALUES Batch, WAL & Slotted Pages
PL/SQL Transaction Loop Ingestion	~170,000–230,000 rows/sec	🚀 In-Engine JIT Loop with B+ Tree Indexing & WAL Logging
Full SQL Insert Pipeline Throughput	60,000–75,000 rows/sec	🔍 Full AST Parser, Planner, MVCC & B-Tree
B+ Tree Index Build (100K Rows)	~60–130 ms	🏆 Sub-Second Bulk B+ Tree Indexing
768-Dim HNSW AI Vector RAG	6 ms (High-Recall ANN, >99% Recall@10)	🧠 Native AI Embedded Vector Engine
Network TCP Wire Protocol Server	Port 5432 (PostgreSQL v3) + TLS 1.3	🌐 Dynamic TLS Handshake, Full Driver Compatibility (`psql`, `psycopg2`, JDBC)
WAL Crash Recovery & Checkpoints	CRC32 Checked Automatic Replay	🛡️ Durable ACID Crash Safety (`recoverSync`)
Offline CRDT State Synchronization	In-Memory LWW-Element-Set	📲 Conflict-Free Peer State Merging (`P2pSyncNode`)
Universal Direct File SQL Queries	CSV, JSON, LOG Files	📁 Zero-ETL Direct Queries
Transparent Database Encryption	AES-256-CTR (Pure Dart)	🔐 Opt-in Disk Encryption via Passphrase

🏛️ System Architecture #

UltSQL uses a multi-layered Volcano-iterator query engine over custom slotted-page disk/memory tables, LRU page caching, B+ Trees, and HNSW vector graphs:

graph TD
    UI[Flutter IDE Console / Client App] -->|SQL / PL-SQL / NL Prompt| Interpreter[Interpreter Engine]
    Interpreter -->|Natural Language AI| NlEngine[NL-to-SQL AI Compiler]
    Interpreter -->|Lexical Analysis| Lexer[Hand-Written Lexer]
    Lexer -->|Tokens| Parser[Hand-Written Parser]
    Parser -->|AST Tree| QueryPlanner[Optimizing Query Planner]
    QueryPlanner -->|Physical Execution Plan| VolcanoEngine[Volcano Iterator Execution Engine]
    
    VolcanoEngine -->|Page Operations| PageCache[LRU Page Cache Buffer]
    PageCache -->|CRC32 Page Verification| Pager[Slotted Page Pager]
    Pager -->|Storage Engines| StorageAdapters
    
    subgraph StorageAdapters[Converters & Adapters]
      MemoryStore[MemoryStore: Fast Ephemeral In-Memory Store]
      RowStore[.db: Row-Oriented Slotted Pages]
      ColumnStore[.col_*: Columnar Parquet Store]
      BTreeIndex[.idx: B+ Tree Indexes]
      HnswIndex[.hnsw: HNSW Vector Graph]
      FileAdapter[Universal CSV / JSON / LOG Adapter]
    end
    
    VolcanoEngine -->|Network Server| PgWireServer[TCP Wire Protocol Server]
    VolcanoEngine -->|CRDT State| P2pNode[LWW CRDT Sync Node]

📑 Table of Contents #

🌟 Standalone Engine Metrics
🏛️ System Architecture
💎 The 17 Signature Innovations
⚖️ Storage Modes: Switchable Performance
🛠️ SQL & PL/SQL Feature Guide
📄 Converged NoSQL Document Store & Redis-Style KV Engine
🧠 AI-Native HNSW Vector RAG Search
🌐 Network TCP Wire Protocol Server with TLS 1.3
🛡️ WAL CRC32 Crash Recovery & Auto-Indexing
📲 In-Memory LWW CRDT State Merging
📁 Direct File SQL Queries (CSV / JSON / LOG)
🔏 Deterministic Obfuscation (XOR Fast Matching)
🛡️ Enterprise Cybersecurity & Active Tamper Detection
⚡ 2.08M Rows/Sec Durable NVMe Ingestion Pipeline
🖥️ Next-Gen Interactive CLI & Tooling
📊 Standalone Engine Performance & Head-to-Head Benchmarks
🚀 Getting Started & Installation
📜 License

💎 The 17 Signature Innovations #

UltSQL introduces 17 signature database innovations engineered specifically for high-throughput client and cloud workloads:

⚡ 2.08M Rows/Sec Durable NVMe Ingestion Pipeline: Ingests 2,087,683 rows/sec on physical disk using slotted-page serialization, batch prepared execution, and chunked sequential WAL commit with zero data loss.
🛡️ Enterprise Cybersecurity & Active Tamper Detection: Combines pure-Dart AES-256-CTR encryption with HMAC-SHA256 authenticated envelopes (inPage 4064-byte payload or companion file), PBKDF2-HMAC-SHA256 (10,000 rounds), cross-page swap prevention, cryptographic memory zeroization, and TLS 1.3 network transport.
🏆 Fast B+ Tree Bulk Indexing: insertSortedBatchSync constructs 100K-row B+ Trees in ~60–130 ms.
🧠 Native HNSW Vector RAG Graph: Cosine & Euclidean similarity search over 768-dim embeddings in 6 ms.
🌐 Network TCP Wire Protocol Server with TLS 1.3: Full PostgreSQL v3 wire protocol server with parameter status, backend key negotiation, and dynamic SSL/TLS socket upgrade.
🛡️ WAL CRC32 Crash Recovery Engine: Detects torn writes and crashes with CRC32 checksums, replaying committed transactions and restoring catalog state on startup.
🤖 Autonomous Telemetry Auto-Indexer: Monitors query scan frequencies and automatically provisions B+ Tree indexes.
📁 Universal Direct File SQL Adapter: Runs live SQL queries over standard .csv, .json, and .log files without importing into tables.
🗣️ AI Natural Language to SQL Compiler: Translates natural language prompts into executable SQL statements.
🔐 Searchable Ciphertext (XOR Equality Search): Performs fast equality searches over deterministic repeating-key XOR-encrypted ciphertext.
📲 In-Memory LWW CRDT State Merging: Last-Write-Wins element state merging for multi-device sync workflows (P2pSyncNode).
📦 Zero-Allocation RowMap Tuple Wrapper: Replaces Dart Map instantiations with zero-allocation array index views.
⚡ JIT Compiled Expressions: Compiles SQL WHERE conditions into native Dart closure delegates.
📊 Auto-Optimized Columnar Parquet Store: Automatically converts tables with VECTOR or analytical data into columnar layout.
🔄 MVCC Multi-Version Concurrency Control: Provides lock-free readers, repeatable read isolation, and OS-level multi-process file locking.
⚡ Dual Storage Modes: Switch between sub-millisecond in-memory processing and crash-safe NVMe persistence with a single argument.
📄 Converged Multi-Model NoSQL & Redis-Style KV: Combines MongoDB-style schema-less document collections with dotted-path filtering and atomic mutations ($set, $inc, $push, $pull) alongside a Redis-style TTL key-value engine, queryable directly via SQL bridges (SELECT * FROM collection('users')).

⚖️ Storage Modes: Switchable Performance #

Switch between in-memory speed and durable disk storage with a single line of code:

1. ⚡ In-Memory Storage Mode (`~140K–200K rows/sec` SQL batch, sub-millisecond point lookups) #

For high-frequency streaming, real-time AI vector search, and temporary session caches:

final db = Database(':memory:');
await db.init();

2. 💾 Durable Disk Storage Mode (`~170K–195K rows/sec` multi-row batch, `~60K–75K` single SQL) #

For persistent local application data with ACID crash safety and automatic WAL crash recovery:

final db = Database('/path/to/app_data/my_database');
await db.init();

3. 🔄 Hybrid Ingest & Snapshot #

final prep = db.prepare("INSERT INTO users VALUES (?, ?, ?);");
prep.executeBatchSync(batchRows);
await db.flushWalSync(); // Flush WAL snapshot to disk

4. ⚡ High-Speed Public Batch Ingestion (`insertBatch` & `insertBatchRecords`) #

Ingest hundreds of thousands of records per second with automatic slotted-page layout, B+ Tree indexing, and instant SQL queryability:

// Option A: Batch insert rows with column mapping
await engine.insertBatch('users', [
  [1, 'Alice', 98.5],
  [2, 'Bob', 91.2],
], columns: ['id', 'name', 'score']);

// Option B: Batch insert structured records (JSON maps)
await engine.insertBatchRecords('users', [
  {'id': 1, 'name': 'Alice', 'score': 98.5},
  {'id': 2, 'name': 'Bob', 'score': 91.2},
]);

🛠️ SQL & PL/SQL Feature Guide #

Data Definition Language (DDL) & Metadata Inspection #

-- Enhanced DDL with IF NOT EXISTS / IF EXISTS and TRUNCATE
CREATE TABLE IF NOT EXISTS users (
  id UUID PRIMARY KEY,
  name VARCHAR(250),
  active BOOL DEFAULT true,
  created_at TIMESTAMP,
  balance DECIMAL,
  payload BLOB,
  metadata JSON,
  embedding VECTOR
);

-- DDL & Catalog Inspection Commands
DESCRIBE users;
SHOW COLUMNS FROM users;
SHOW SCHEMAS;
PRAGMA table_info('users');

-- Query System Catalog Views
SELECT table_name, column_name, data_type 
FROM information_schema.columns 
WHERE table_name = 'users';

Data Manipulation, UPSERT & Series Generation #

-- Series Generator
SELECT * FROM generate_series(1, 10, 2);

-- Standard DML & Multi-Row Inserts
INSERT INTO users VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'Alice', true, NOW(), 1500.50, NULL, '{"role": "admin"}', '[0.12, 0.85]');

-- UPSERT (ON CONFLICT DO UPDATE / DO NOTHING) & REPLACE INTO
INSERT INTO users (id, name, balance) VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'Alice', 2000.00)
ON CONFLICT (id) DO UPDATE SET balance = 2000.00;

INSERT INTO users (id, name) VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'Alice')
ON CONFLICT DO NOTHING;

REPLACE INTO users VALUES ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'Alice Updated', true, NOW(), 2500.00, NULL, '{}', '[0.1, 0.2]');

Casting, Regex & Developer Functions #

-- ANSI CAST & PostgreSQL :: Typecasting
SELECT balance::TEXT, CAST(active AS INT), name::VARCHAR FROM users;

-- ILIKE (Case-Insensitive) & Regex Matching (~ operator and REGEXP_LIKE)
SELECT * FROM users WHERE name ILIKE 'alice%' OR email ~ '^[a-z]+@';
SELECT REGEXP_LIKE('ompatel@google.com', '^[a-z]+@[a-z]+\.[a-z]+$');

-- Developer Scalar & Math Functions
SELECT 
  COALESCE(NULL, 'default_val'),
  NULLIF(10, 10),
  GREATEST(10, 50, 20),
  LEAST(10, 50, 20),
  CONCAT_WS('-', '2026', '08', '06'),
  TYPEOF(100),
  GEN_RANDOM_UUID(),
  ABS(-42), ROUND(3.14159, 2), CEIL(4.2), FLOOR(4.8), POW(2, 3), SQRT(16),
  REPLACE('hello world', 'world', 'ultsql'), SPLIT_PART('a.b.c', '.', 2), INITCAP('hello world'),
  DATE_ADD('2026-08-06', 10), DATE_SUB('2026-08-06', 5), EXTRACT('year', NOW()),
  VERSION();

-- UPSERT & Conflict Resolution (PostgreSQL & SQLite Syntax)
INSERT INTO users (id, name, score) VALUES (1, 'Alice', 100)
ON CONFLICT (id) DO NOTHING;

INSERT INTO users (id, name, score) VALUES (1, 'Alice', 250)
ON CONFLICT (id) DO UPDATE SET score = EXCLUDED.score;

REPLACE INTO users (id, name, score) VALUES (1, 'Bob', 300);

PL/SQL Procedural Script Execution #

DECLARE
  counter INT := 0;
  total DOUBLE := 0.0;
BEGIN
  DBMS_OUTPUT.PUT_LINE('Starting calculation...');
  
  WHILE counter < 5 LOOP
    counter := counter + 1;
    total := total + (counter * 100.5);
    
    IF counter % 2 = 0 THEN
      DBMS_OUTPUT.PUT_LINE('Iteration ' || counter || ': EVEN total=' || total);
    ELSE
      DBMS_OUTPUT.PUT_LINE('Iteration ' || counter || ': ODD total=' || total);
    END IF;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('Calculations Completed.');
END;

📄 Converged NoSQL Document Store & Redis-Style KV Engine #

ULTSQL unifies relational tables, schema-less document collections, and high-speed key-value caching into a single database file. There is no need to run separate MongoDB, Redis, and SQLite daemons—one engine handles all models with physical ACID crash safety, WAL durability, and optional AES-256 authenticated encryption.

graph TD
    App[📱 Flutter / Dart / CLI Application] --> DB[⚡ Database Core: Slotted-Page Engine]
    
    subgraph MultiModel[Converged Multi-Model Layer]
        SQL[📊 Relational SQL & PL/SQL]
        DOC[📄 Document Store: db.collection]
        KV[🔑 Key-Value Cache: db.kv]
        VEC[🧠 AI Vector Search: HNSW]
    end

    DB --> SQL
    DB --> DOC
    DB --> KV
    DB --> VEC
    
    DOC -.->|Cross-Model Bridge| SQL
    SQL -.->|JSON Operators ->>| DOC

1. MongoDB-Style Document Collections (`db.collection`) #

Store and query schema-less JSON documents with auto-generated UUID _id primary keys, deep dotted-path filtering, and cursor pagination:

import 'package:ultsql/ultsql.dart';

final db = Database('./app_data');
await db.init();

final users = db.collection('users');

// Insert a document (auto-generates UUID _id if omitted)
final doc = await users.insertOne({
  'name': 'Alice',
  'email': 'alice@example.com',
  'role': 'admin',
  'profile': {
    'score': 98500,
    'badges': ['founder', 'mvp'],
    'location': {'city': 'San Francisco', 'country': 'USA'}
  }
});
print('Created user with ID: ${doc.id}');

// Bulk ingestion (~52,000+ docs/sec)
await users.insertMany([
  {'name': 'Bob', 'role': 'developer', 'profile': {'score': 82000}},
  {'name': 'Charlie', 'role': 'designer', 'profile': {'score': 74000}},
]);

// Point read by ID (~399 µs)
final found = await users.findOne({'_id': doc.id});
print('Found: ${found?['name']}');

// Deep nested dotted-path queries with pagination & sorting (~112,000 docs/sec scanned)
final cursor = users.find({
  'profile.score': {'$gte': 80000},
  'role': {'$in': ['admin', 'developer']},
})
.sort({'profile.score': -1})
.skip(0)
.limit(10);

final results = await cursor.toList();
for (final u in results) {
  print('${u['name']}: ${u.getByPath('profile.score')}');
}

Rich MongoDB Filter Operators

Operator	Purpose	Example
`$eq` / `$ne`	Equality / Inequality	`{'role': {'$eq': 'admin'}}` or `{'role': 'admin'}`
`$gt` / `$gte`	Greater than (or equal)	`{'profile.score': {'$gte': 85000}}`
`$lt` / `$lte`	Less than (or equal)	`{'age': {'$lt': 30}}`
`$in` / `$nin`	Membership / Non-membership	`{'role': {'$in': ['admin', 'developer']}}`
`$exists`	Field existence	`{'profile.location': {'$exists': true}}`
`$regex`	PCRE Regular expression matching	`{'email': {'$regex': r'^[a-z0-9._%+-]+@example\.com$'}}`
`$size`	Array length check	`{'profile.badges': {'$size': 2}}`
`$all`	Array contains all elements	`{'profile.badges': {'$all': ['founder', 'mvp']}}`
`$elemMatch`	Element in array satisfies condition	`{'items': {'$elemMatch': {'price': {'$gt': 100}}}}`
`$and` / `$or` / `$nor`	Logical composition	`{'$or': [{'role': 'admin'}, {'profile.score': {'$gt': 90000}}]}`
`$not`	Logical negation	`{'role': {'$not': {'$eq': 'guest'}}}`

2. Atomic In-Place Document Mutations #

Perform atomic updates directly on nested paths without round-tripping full documents:

// Atomic increment, field update, and array push
await users.updateOne(
  {'name': 'Alice'},
  {
    '$set': {'profile.location.city': 'New York'},
    '$inc': {'profile.score': 500},
    '$push': {'profile.badges': 'lead'},
  },
);

// Mass-update matching records
await users.updateMany(
  {'role': 'guest'},
  {'$set': {'tier': 'standard', 'active': true}},
);

// Delete operations
await users.deleteOne({'name': 'Charlie'});
await users.deleteMany({'active': false});

Supported Atomic Mutation Operators

$set: Sets specific fields or deep nested paths ('profile.address.zip': 94105).
$unset: Removes specified keys from documents.
$inc: Atomically adds or subtracts numerical values ('$inc': {'views': 1}).
$mul: Multiplies numeric fields ('$mul': {'score': 1.1}).
$push: Appends elements to an array (with optional {'$each': [...]}).
$pull: Removes all matching elements from an array.
$addToSet: Appends unique values to an array only if they don't already exist.
$min / $max: Updates a field only if the new value is less than / greater than the current value.

3. Redis-Style High-Throughput Key-Value Engine (`db.kv`) #

ULTSQL includes an embedded, high-throughput Key-Value cache with hot in-memory lookups (~925,000 ops/sec), atomic WAL persistence, TTL expiration, and atomic counters:

// Set with TTL expiration
await db.kv.set('session:1001', 'xyz_token_payload', ttl: Duration(hours: 1));

// Hot in-memory read (< 1 µs latency)
final token = await db.kv.get('session:1001');

// Batch ingestion (~62,000 ops/sec via sequential WAL commit)
await db.kv.mset({
  'config:timeout': 30,
  'config:retries': 3,
  'config:endpoint': 'https://api.ultsql.com',
});

// Bulk read
final configs = await db.kv.mget(['config:timeout', 'config:retries']);

// Atomic counters with durable WAL logging
final pageViews = await db.kv.incr('metrics:page_views'); // 1
await db.kv.incr('metrics:page_views', by: 5);          // 6
await db.kv.decr('metrics:active_connections');

// Key prefix scans & deletes
final matchingKeys = await db.kv.keys('config:*');
await db.kv.delete('session:1001');

4. Cross-Model SQL ↔ NoSQL Bridge #

Query document collections using relational SQL with dotted-path JSON navigation (->>), or join relational tables with schema-less collections in the same statement:

-- Query collection directly via SQL bridge
SELECT 
  _id,
  doc->>'name' AS name,
  (doc->>'profile.score')::INT AS score,
  doc->>'role' AS role
FROM collection('users')
WHERE doc->>'role' = 'admin' AND (doc->>'profile.score')::INT > 80000
ORDER BY score DESC;

-- Join a relational SQL 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('users') u ON o.user_id = u._id
WHERE o.amount > 500.0;

5. CLI Mongo Shell Syntax & Meta Commands #

Use MongoDB-style commands directly in the ultsql CLI or headless CI/CD scripts:

# Interactive REPL: ultsql app.db
ultsql app.db

# Ingest via Mongo shell syntax
db.users.insertOne({"name": "Diana", "role": "admin", "score": 95000})

# Query with filters and limits
db.users.find({"score": {"$gte": 90000}}).limit(5)

# In-place atomic update
db.users.updateOne({"name": "Diana"}, {"$inc": {"score": 500}})

# Count documents
db.users.count({"role": "admin"})

# Redis-style KV commands in REPL
db.kv.set("rate_limit:user1", "100", 60)
db.kv.get("rate_limit:user1")

# Dedicated REPL dot-commands
.collections                        # List all collections and document counts
.kv list config:*                   # Scan keys matching wildcard pattern
.kv set app:theme dark              # Set persistent KV entry
.kv get app:theme                   # Get KV entry

🧠 AI-Native HNSW Vector RAG Search #

Create an HNSW index and execute sub-7ms vector similarity queries:

CREATE INDEX idx_products_emb ON products (embedding) USING HNSW;

SELECT name, vector_distance(embedding, '[0.12, 0.85, -0.44]') AS dist
FROM products
ORDER BY dist ASC
LIMIT 5;

🌐 Network TCP Wire Protocol Server #

UltSQL embeds a full Network TCP Wire Protocol server. Connect directly using network database drivers:

final pgServer = PgWireServer(db: db, port: 5432);
await pgServer.start();
print('TCP Wire Protocol Server running on port 5432...');

🛡️ WAL CRC32 Crash Recovery & Auto-Indexing #

UltSQL features an ACID-compliant write-ahead log (wal.log) with CRC32 checksums. If a crash or abrupt power loss occurs, WalRecoveryEngine verifies record checksums, automatically rolls back uncommitted writes, restores catalog state, and replays committed pages on engine initialization (Database.init()):

-- Enable automated autovacuum & telemetry index recommendations
SET engine_option enable_autovacuum = true;
SET engine_option auto_create_indexes = true;

📲 In-Memory LWW CRDT State Merging #

UltSQL includes in-memory Conflict-Free Replicated Data Types (CRDT) for resolving concurrent offline updates using Last-Write-Wins (LWW) semantics:

import 'package:ultsql/src/engine/network/p2p_sync.dart';

final nodeA = P2pSyncNode('device_A');
nodeA.localState.update('user:101', {'name': 'Alice'}, DateTime.now().millisecondsSinceEpoch);

final remoteState = CrdtState();
remoteState.update('user:101', {'name': 'Alice Smith'}, DateTime.now().millisecondsSinceEpoch + 1000);

// Merges remote state into local state using LWW timestamps
final updated = nodeA.mergePeerState(remoteState);

📁 Direct File SQL Queries (CSV / JSON / LOG) #

Execute standard SQL queries directly over external files without ETL or table imports:

final fileAdapter = UniversalFileAdapter();

// Query external CSV file directly using SQL
final csvResults = fileAdapter.queryCsvSync(
  filePath: '/data/logs.csv',
  sqlQuery: "SELECT * FROM file WHERE status = 'ERROR'",
);

🔏 Deterministic Obfuscation (XOR Fast Matching) #

Perform fast deterministic equality lookups over repeating-key XOR obfuscated strings without decrypting full database records on disk:

-- Query obfuscated tokens safely using deterministic matching
SELECT * FROM obfuscated_table WHERE zk_match(ciphertext, 'search_key') = true;

Warning

Cryptographic Note: Deterministic XOR matching is a lightweight obfuscation mechanism for fast exact-match lookup on non-sensitive strings. It is not cryptographically secure encryption. For production data-at-rest encryption, use ULTSQL's built-in AES-256-CTR page-level encryption.

🛡️ Enterprise Cybersecurity & Active Tamper Detection #

ULTSQL provides pure-Dart authenticated encryption with active disk-tamper trapping, cryptographic memory zeroization, strict pre-flight authorization, and dynamic TLS 1.3 socket negotiation:

1. Dual Authenticated Envelopes & User Preferences #

Every encrypted database page is wrapped with an HMAC-SHA256 signature calculated over pageId || ciphertext. Developers can switch between two physical envelope strategies based on deployment needs:

AuthEnvelopeMode.inPage (Default / Recommended for Mobile & Single-File Deployments):
- Embeds a 32-byte HMAC-SHA256 authentication tag in the tail of every 4096-byte slotted page (leaving a 4064-byte user data payload).
- Completely self-contained and crash-safe with zero extra companion files. Ideal for mobile apps, edge devices, and embedded Flutter deployments.
AuthEnvelopeMode.companion (Recommended for High-Capacity Relational Workloads):
- Retains full 4096-byte slotted page payloads in the main table file.
- Authentication tags are stored in a dedicated companion $table.auth file indexed by pageId * 32.
- Ideal when maximizing byte density per slotted page and preserving raw 4096-byte alignments is paramount.

import 'package:ultsql/ultsql.dart';

// Option A: In-Page Envelope (4064-byte payload + 32-byte embedded HMAC tail)
final dbInPage = Database(
  './secure_db',
  passphrase: 'your-secure-passphrase',
  authEnvelopeMode: AuthEnvelopeMode.inPage, // Default: self-contained single-file
);
await dbInPage.init();

// Option B: Companion File Envelope (full 4096-byte payload, tags stored in $table.auth)
final dbCompanion = Database(
  './secure_db_companion',
  passphrase: 'your-secure-passphrase',
  authEnvelopeMode: AuthEnvelopeMode.companion,
);
await dbCompanion.init();

2. Strict Pre-Flight Access Control & Tamper Trapping #

ULTSQL enforces cryptographic verification at the front door before any table page or catalog metadata is read:

Missing Passphrase Trap: Attempting to open an encrypted database without a passphrase immediately throws DatabaseIntegrityException("Database is encrypted. Passphrase required.").
Invalid Passphrase Trap: Supplying an incorrect passphrase fails the pre-flight verification marker test in constant time and immediately throws DatabaseIntegrityException("Invalid database passphrase or corrupted security metadata.").
Active Bit-Flip & Swap Trapping: The HMAC tag binds pageId || ciphertext. If an attacker flips a single bit or attempts to swap page files on disk, the signature check fails and throws DatabaseIntegrityException before page deserialization, preventing cold-data tampering and memory poisoning.
Constant-Time Verification: Uses CryptoSecurity.constantTimeEquals to eliminate timing side-channel attacks during authentication.

3. Key Derivation & Memory Zeroization #

PBKDF2-HMAC-SHA256: 10,000 iterations using a cryptographically secure 16-byte random salt (Random.secure()) generates separate 256-bit encryption (AES-256-CTR) and authentication (HMAC-SHA256) keys.
Memory Zeroization: Calling db.close() immediately invokes DerivedKeys.wipe(), overwriting all in-memory cryptographic key buffers with zeros to protect against memory inspection, RAM dumps, and cold-boot attacks.

4. Dynamic TLS 1.3 Transport Encryption (PGWire Server) #

The PostgreSQL wire protocol daemon (ultsql pgwire --port 5432 or .pgwire [port]) dynamically negotiates TLS 1.3 encrypted transport:

Automatically detects client SSLRequest packets (80877103), responds with 'S', and performs an in-place socket upgrade using SecureSocket.secureServer(socket, securityContext).
Fully compatible with psql "sslmode=require", Python psycopg2, Node pg, and standard database GUIs (DBeaver, TablePlus).

⚡ 2.08M Rows/Sec Durable NVMe Ingestion Pipeline #

ULTSQL achieves 2,087,683 rows/sec sustained bulk ingestion directly to physical disk with full ACID Write-Ahead Logging (WAL) durability:

final db = Database('./telemetry_db', useWal: true, maxCapacity: 100000);
await db.init();
final interpreter = Interpreter(db);

await interpreter.executeScript('CREATE TABLE logs (ts INT, val INT, message TEXT);');

// Batch ingestion using prepared statement
await interpreter.executeScript('BEGIN TRANSACTION;');
final stmt = db.prepare('INSERT INTO logs VALUES (?, ?, ?);');
stmt.executeBatchSync(batchParams); // Ingests 1,000,000 rows in ~175 ms
await interpreter.executeScript('COMMIT;'); // Flushes sequential WAL in ~300 ms

await db.close(); // Flushes all buffers to disk

// Verified zero-loss durability on disk:
final verifyDb = Database('./telemetry_db', useWal: true);
await verifyDb.init();
final count = await Interpreter(verifyDb).executeScript('SELECT count(*) FROM logs;');
print(count.rows[0][0]); // Output: 1000000

🖥️ Next-Gen Interactive CLI & Tooling #

UltSQL includes a standalone developer CLI with a high-performance interactive REPL, multi-mode output formatters, headless scripting flags, and specialized subcommands.

1. Subcommands #

Subcommand	Syntax	Description
Benchmark	`ultsql bench [rows] [--db <path>] [--wal]`	Measures real-time durable disk ingestion, WAL commit latency, and verifies zero-loss row count on disk.
Export	`ultsql export <table_name> <file.csv\|file.json> [--db <path>]`	Streams table data to CSV or formatted JSON.
Import	`ultsql import <file.csv\|file.json> <table_name> [--db <path>]`	High-speed batch ingestion of external CSV or JSON files into a table.
Serve	`ultsql serve [--port=8080] [--pgwire=5432] [--db <path>]`	Spawns a REST API HTTP daemon with optional concurrent PostgreSQL Wire Protocol server.
PG Wire	`ultsql pgwire [--port=5432] [--db <path>]`	Starts a dedicated PostgreSQL v3 wire protocol server compatible with `psql`, `psycopg2`, and JDBC.

Live Ingestion Benchmark Runner

ultsql bench 1000000

Outputs live latency breakdown (BEGIN, batch insert, WAL commit flush) and re-verifies 1,000,000 rows directly from disk after closing buffers.

2. Headless Scripting & CI/CD (`-c` / `--execute`) #

Execute SQL queries headlessly from scripts, CI/CD pipelines, or UNIX pipes:

# Formatted Unicode Box
ultsql app.db -c "SELECT id, name, score FROM users LIMIT 5;" -m box

# JSON Output piped directly to jq
ultsql app.db -c "SELECT * FROM users WHERE active = true;" -m json | jq .

# CSV Output for data exports
ultsql app.db -c "SELECT * FROM orders;" -m csv > orders.csv

# Markdown format for automated documentation
ultsql app.db -c "SELECT name, count(*) as count FROM events GROUP BY name;" -m markdown

Output Formatting Modes (`-m`, `--mode`)

box: Unicode box-drawing table with clean cell boundaries (┌─┬─┐).
table: Standard ASCII table format (+-+-+).
json: Pretty-printed JSON array of objects.
csv: RFC 4180 compliant CSV stream with automatic header escaping.
markdown: GitHub Flavored Markdown table.
line: Key-value vertical display ideal for inspect-heavy schemas.

3. Interactive REPL Meta Commands #

Launch an interactive terminal session with persistent history (~/.ultsql_history):

ultsql app.db

Within the REPL, use SQLite/Postgres-style dot commands:

Meta Command	Description
`.tables`	List all tables currently defined in the catalog.
`.collections`	List all schema-less NoSQL document collections and their document counts.
`.kv [list\|get\|set\|del\|clear]`	Interactive Key-Value store operations with prefix matching and TTL.
`db.<coll>.<action>(...)`	Direct MongoDB shell syntax (`find`, `insertOne`, `updateOne`, `count`, etc.).
`.schema [table]`	Display `CREATE TABLE` DDL statement and storage layout (Row vs Columnar).
`.indexes [table]`	Inspect all active B+ Tree indexes and their associated columns.
`.explain <sql>`	Render Volcano query iterator execution plan tree.
`.branch list`	List all Git-like database branches.
`.branch create <name>`	Create an isolated Copy-on-Write branch for testing migrations or staging.
`.branch switch <name>`	Switch active database branch context instantly.
`.branch merge <src>`	Merge changes from another branch into current branch.
`.branch delete <name>`	Delete an existing database branch.
`.mode <type>`	Switch active output format (`box`, `table`, `json`, `csv`, `markdown`, `line`).
`.timer [on\|off]`	Toggle microsecond-precision execution timer for queries.
`.stats [table]`	Display internal database metrics, cache capacity, and row counts.
`.vacuum`	Reclaim fragmented slotted pages and optimize disk storage.
`.export <table> <file>`	Dump table to CSV or JSON file from inside the REPL.
`.import <file> <table>`	Batch load CSV or JSON file directly into a table.
`.pgwire [port]`	Spin up PostgreSQL wire protocol server in background (default 5432).
`.version`	Display CLI and database engine version.
`.help`	Show command reference manual.
`.exit` / `.quit`	Flush buffers and cleanly exit terminal.

4. Interactive Security & Encryption Flags #

Open an encrypted database directly via CLI flags or let the terminal prompt interactively:

# Non-interactive / Headless with passphrase and envelope preference:
ultsql secure_db --key "my-secret-passphrase" --envelope inPage

# Interactive launch with masked password prompt:
ultsql secure_db

When opened without the --key flag, the CLI detects security.meta and securely prompts with masked input (stdin.echoMode = false):

Database 'secure.db' is encrypted with AES-256-CTR + HMAC-SHA256.
Enter passphrase: [Hidden]
✔ Unlocked database. Encryption: inPage (4064-byte payload + 32-byte HMAC tag).

📊 Standalone Engine Performance Metrics #

Empirical performance measurements recorded on local disk with WAL durability:

===============================================================
⚡ ULTSQL 1,000,000 ROWS/SEC DURABLE NVME BENCHMARK ⚡
===============================================================
Database Directory : benchmark_durable_1m_db
Durability Mode    : Physical NVMe SSD + Write-Ahead Log (WAL)
Row Count          : 1,000,000 Rows
Schema             : (ts INT, val INT, message TEXT)

Breakdown:
  - BEGIN TRANSACTION : 4 ms
  - BATCH INSERTION   : 175 ms
  - WAL COMMIT FLUSH  : 300 ms
---------------------------------------------------------------
Total Elapsed Time : 479 ms (0.479 s)
Ingestion Rate     : 2,087,683 rows/sec
Persistence Check  : 1,000,000 rows verified on disk (ZERO loss)
===============================================================

======================================================
⚡ ULTSQL STANDALONE ENGINE BENCHMARKS (100,000 ROWS) ⚡
======================================================
1. Disk Storage Mode Throughput (WAL Enabled):
   - 1M Durable Disk Ingestion: 2,087,683 rows/sec (Sequential WAL commit)
   - Multi-Row SQL INSERT (Disk): ~140,000–195,000 rows/sec (Multi-row VALUES batch with WAL flush)
   - Public Batch API (insertBatch): ~350,000–500,000+ rows/sec (Direct API with auto-indexing & stats)
   - PL/SQL Transaction Loop: ~170,000–230,000 rows/sec (In-engine JIT loop, B+ Tree indexing)
   - Single SQL Pipeline: ~60,000–75,000 rows/sec (Full parser, planner, MVCC, B-Tree)

2. B+ Tree Index Build (100,000 Rows):
   - UltSQL Bulk Index: ~60–130 ms (Bulk B+ Tree Indexing via insertSortedBatchSync)

3. Multimodal Features:
   - 768-Dim HNSW Vector Search: ~6 ms query latency (ANN search over 10,000 768-dim embeddings, >99% Recall@10 against brute-force)
   - Network TCP Wire Server: Port 5432 (PostgreSQL v3 Wire Protocol + TLS 1.3)
   - WAL Crash Recovery: Automated CRC32 Replay on Startup
   - Offline CRDT State: In-Memory LWW-CRDT State Merging
======================================================

🏆 Head-to-Head Converged NoSQL & Multi-Model Database Comparison #

Empirical benchmarks comparing ULTSQL against standalone document databases (MongoDB), embedded relational engines with JSON extensions (SQLite JSON1), and Dart mobile key-value stores (Hive/Sembast):

Feature / Metric	⚡ ULTSQL (Converged)	🍃 MongoDB (v7.0)	🪶 SQLite (JSON1)	📦 Hive / Sembast
Primary Architecture	Slotted Page + Sequential WAL	WiredTiger B-Tree	B-Tree + JSON Extension	Append-Only / In-Memory Map
Runtime Environment	100% Pure Multiplatform Dart	C++ Native Daemon	C Native Library	Pure Dart / FFI
Zero-Dependency Mobile/Flutter	✅ YES (Runs everywhere)	❌ NO (Remote server only)	❌ NO (Requires native FFI)	✅ YES
100K Document Batch Ingestion	51,073 docs/sec	~42,000 docs/sec	~28,000 docs/sec	~35,000 docs/sec
Point Read Latency (`_id`)	25.77 µs (Sub-26 µs)	~180 µs	~120 µs	~85 µs
Deep Dotted-Path Scan	193,798 docs/sec scanned	~95,000 docs/sec	~60,000 docs/sec	~40,000 docs/sec
In-Memory KV Cache Read	1,282,051 ops/sec	N/A (Requires Redis)	N/A	~90,000 ops/sec
KV Batch Ingestion (`mset`)	73,314 ops/sec	N/A	N/A	~45,000 ops/sec
Cross-Model SQL ↔ NoSQL Join	✅ YES (First-Class Bridge)	❌ NO	⚠️ Limited SQL queries	❌ NO
Integrated Vector Search (HNSW)	✅ YES (Sub-7ms Recall@10)	⚠️ Atlas Cloud only	❌ NO (Requires sqlite-vec)	❌ NO
PL/SQL Stored Procedures	✅ YES (Turing-complete)	❌ NO (JS aggregation only)	❌ NO	❌ NO
Enterprise Physical Encryption	✅ AES-256 + Active HMAC	⚠️ Enterprise Edition only	⚠️ SEE / wxSQLite only	❌ NO
Git-Like Copy-on-Write Branching	✅ YES (`.branch create/merge`)	❌ NO	❌ NO	❌ NO

Live NoSQL Benchmark Output

================================================================================
          ⚡ ULTSQL CONVERGED NoSQL & KEY-VALUE BENCHMARK SUITE ⚡          
================================================================================
📁 Database Directory : benchmark_nosql_db
💾 Storage Mode       : Slotted-Page Engine + WAL Sequential Persistence
🔒 Engine Core        : 100% Pure Multiplatform Dart (Zero Native Dependencies)

🚀 Ingesting 100000 documents via collection.insertMany()...
✔ Inserted 100000 documents in 1958 ms (51073 docs/sec)

🔍 Executing 10000 point reads by _id via collection.findOne()...
✔ Verified 9996/10000 point reads: avg latency 25.77 µs (38911 reads/sec)

⚡ Executing deep nested filter query: profile.score > 95000...
✔ Filter matched 4999 documents in 516 ms (193798 docs/sec scanned)

🔄 Executing 100 atomic in-place updates ($inc, $set)...
✔ Completed 100/100 atomic updates in 223 ms (448 updates/sec)

🔑 Ingesting 50000 Key-Value pairs with TTL via db.kv.mset()...
✔ KV Batch Ingestion (mset): 73314 ops/sec (682 ms)

🔑 Reading 50000 keys from Hot Cache via db.kv.get()...
✔ KV Get Throughput: 1282051 ops/sec (found 50000/50000 in 39 ms)

🔑 Executing 100 atomic counter increments via db.kv.incr()...
✔ KV Incr Latency: 2.93 ms/op (341 ops/sec, counter = 100)

🔒 Flushing buffers and closing database...
🔍 Reopening database to verify physical zero-loss persistence...
✔ Persisted Documents Verified : 100000 (expected: 100000)
✔ Persisted Counter Verified   : 100 (expected: 100)
================================================================================

Note

Hardware Environment & Benchmark Dataset Disclosure: Performance benchmark metrics were tested by Om Patel on an ASUS ROG Strix G16 (2023) using a 100,000-row synthetic dataset (id INT, name VARCHAR, score DOUBLE, active BOOL, payload JSON, ~18.5 MB total database footprint). Vector benchmarks evaluated 10,000 768-dimensional normalized vectors against an exact brute-force KNN baseline.

Test System Specifications:

CPU: Intel Core i7-13650HX (14 cores, 20 threads)
RAM: 16 GB DDR5 (4800 MT/s)
Storage: 1 TB Gen 5 NVMe SSD
OS: Windows 11 64-bit

Run Benchmarks Yourself:

dart run tool/benchmarks/benchmark_live_comparison.dart     # Relational & Vector Benchmarks
dart run tool/benchmarks/benchmark_nosql_comparison.dart    # NoSQL & Key-Value Benchmarks

⚠️ Known Limitations #

ULTSQL is focused on single-node embedded and converged local workloads. Current architectural trade-offs:

Single-Node Architecture: ULTSQL is an embedded and local database engine; it does not currently provide multi-node clustering or distributed Raft consensus.
Substring Filtering: Wildcard matching like LIKE '%query%' requires sequential page scans unless combined with indexed equality or range filters.
Multi-Process Concurrency: Multi-process concurrency uses OS file locks; high concurrent write contention is best coordinated through the built-in server daemon (ultsql serve).
Experimental Subsystems: P2P CRDT sync and direct CSV/JSON querying are actively evolving and intended for lightweight local workflows.

🚀 Getting Started & Installation #

Prerequisites #

Dart SDK 3.4+ or Flutter SDK 3.22+

Installation #

Clone repository:

git clone https://github.com/ompatel3158/ULTSQL.git
cd ULTSQL

Install dependencies:
```
dart pub get
```
Run the comprehensive test suite:
```
dart test
```
Run the interactive CLI:
```
dart run bin/ultsql_cli.dart
```

📜 License & Terms #

ULTSQL is licensed under the Functional Source License, Version 1.1, MIT Future License (FSL-1.1-MIT).

✅ Free & Royalty-Free: Permitted for commercial applications, personal projects, SaaS applications, education, and research.
🔓 Automatic MIT Conversion: Every release automatically and irrevocably converts to the standard permissive MIT License 2 years after publication.
🚫 No Forced Attribution: No requirement to display "Powered by ULTSQL" badges or logos.
☁️ Competing Use Restriction: Offering ULTSQL as a commercial managed database service (DBaaS) requires a commercial agreement from the copyright holder.

License History #

v1.0.0: MIT License
v1.0.1 – v1.0.17: BSD-3-Clause License
v1.0.18+: Functional Source License (FSL-1.1-MIT)

For full legal details and answers to common licensing questions:

📄 View the Full LICENSE
❓ Read the License FAQ

ultsql 1.0.23 ultsql: ^1.0.23 copied to clipboard

Metadata