Documentation & API Reference

⚡ 5-Line Quickstart

Embed directly in your Dart or Flutter project:

main.dart

import 'package:ultsql/ultsql.dart';

void main() async {
  final db = Database('./app.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;
  ''');
}

⚖️ Storage Modes

Switch between in-memory speed and durable disk storage:

In-Memory Storage Mode

Ideal for streaming, tests, and temporary caches:

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

Durable Disk Mode

ACID slotted-page persistence with WAL crash replay:

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

📊 Relational SQL Dialect

Standard DDL, DML, and UPSERT syntax:

-- CREATE TABLE with rich data types
CREATE TABLE IF NOT EXISTS accounts (
  id UUID PRIMARY KEY,
  name VARCHAR(100),
  active BOOL DEFAULT true,
  balance DECIMAL,
  created_at TIMESTAMP,
  metadata JSON,
  embedding VECTOR
);

-- UPSERT support (PostgreSQL & SQLite syntax)
INSERT INTO accounts (id, name, balance) VALUES ('uuid-1', 'Alice', 1200.0)
ON CONFLICT (id) DO UPDATE SET balance = 1500.0;

🧠 AI Vector RAG (HNSW)

Perform approximate nearest-neighbor queries across high-dimensional embeddings:

-- Create HNSW graph index on embedding column
CREATE INDEX idx_products_vector ON products (embedding) USING HNSW;

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

📄 NoSQL Dotted JSON Documents

Direct dotted path navigation inside JSON fields:

SELECT title, 
  JSON_EXTRACT(metadata, 'user.profile.city') AS city,
  metadata->>'tier' AS tier
FROM documents
WHERE metadata->>'tier' = 'Enterprise';

⚡ PL/SQL Procedural Scripting

Execute procedural routines with loops and conditionals:

DECLARE
  counter INT := 0;
  total DOUBLE := 0.0;
BEGIN
  WHILE counter < 5 LOOP
    counter := counter + 1;
    total := total + (counter * 100.0);
    DBMS_OUTPUT.PUT_LINE('Step ' || counter || ': ' || total);
  END LOOP;
END;

🌐 PostgreSQL Wire Protocol Server

Connect any PostgreSQL driver or BI tool to ULTSQL on port 5432:

// Start the embedded wire server
final pgServer = PgWireServer(db: db, port: 5432);
await pgServer.start();

// Connect via standard psql CLI
// psql -h localhost -p 5432 -U postgres

⚠️ Known Limitations

Single-Node Engine: ULTSQL is focused on fast embedded and single-node workloads. It does not provide multi-master clustering or distributed consensus.
Substring Filters: Arbitrary wildcards like LIKE '%pattern%' perform sequential page scans unless accompanied by indexed filters.
Multi-Process Write Locks: Multi-process concurrency uses operating system file locking; high write contention is best channeled through the ultsql serve daemon.