ULTSQL Documentation

# Add to Dart / Flutter pubspec.yaml
dart pub add ultsql
# Or Flutter:
flutter pub add ultsql

# Activate interactive CLI binary globally
dart pub global activate ultsql

# Verify installation
ultsql --version

# Install Node.js / TypeScript SDK
npm install ultsql

# Install Python client driver
pip install ultsql

# Install Go native driver
go get github.com/ompatel3158/ULTSQL/bindings/go

# Add to Cargo.toml
[dependencies]
ultsql = "1.0.26"

# Run standalone PGWire server on port 5432
docker run -d -p 5432:5432 -v $(pwd)/data:/data ompatel/ultsql:latest

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', 'alice@ultsql.io', '2026-10-01');
    INSERT INTO users VALUES (2, 'Bob Jones', 'bob@ultsql.io', '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();
}

-- Direct SQL Script execution
CREATE TABLE users (
  id INT PRIMARY KEY,
  name TEXT,
  email TEXT,
  created_at TEXT
);

INSERT INTO users VALUES (1, 'Alice Smith', 'alice@ultsql.io', '2026-10-01');
INSERT INTO users VALUES (2, 'Bob Jones', 'bob@ultsql.io', '2026-10-02');

SELECT name, email FROM users WHERE id = 1;

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

async function main() {
  const db = await UltSql.openFile('./my_database');
  await db.query(`
    CREATE TABLE users (id INT PRIMARY KEY, name TEXT, email TEXT);
    INSERT INTO users VALUES (1, 'Alice Smith', 'alice@ultsql.io');
  `);

  const res = await db.query("SELECT * FROM users WHERE id = 1;");
  console.log('Results:', res.rows);
  await db.close();
}
main();

import ultsql

# Connect to embedded or PGWire server
conn = ultsql.connect(database="./my_database")
cur = conn.cursor()

cur.execute("CREATE TABLE users (id INT PRIMARY KEY, name TEXT, email TEXT);")
cur.execute("INSERT INTO users VALUES (1, 'Alice Smith', 'alice@ultsql.io');")

cur.execute("SELECT * FROM users WHERE id = 1;")
print(cur.fetchall())
conn.close()

-- 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';

-- 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;

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}');

-- 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);

-- Drop Index
DROP INDEX idx_orders_customer;

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;

-- 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;

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

// 1. Insert single document (auto-generates 24-char unique _id)
final doc = await users.insertOne({
  'name': 'Sarah Connor',
  'email': 'sarah@resistance.org',
  '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'});

// 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');

-- 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;

-- 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;

// Open encrypted database with secret passphrase
final db = Database(
  './secure_db',
  passphrase: 'super_secret_master_key_99',
);
await db.init();

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.26 (Pure Dart)
# ultsql=> SELECT 1 + 1;

final rest = RestServer(db, port: 8080);
await rest.start();
print('REST API active on http://localhost:8080');

# 1. Launch interactive REPL
ultsql ./app.db

# 2. 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

# 3. Headless CI/CD execution with JSON output piped to jq
ultsql ./app.db -m json -c "SELECT * FROM users;" | jq .

# 4. Ingest via MongoDB syntax directly in CLI
db.users.insertOne({"name": "Diana", "role": "admin"})

import { Client } from 'pg';

const client = new Client({
  host: 'localhost',
  port: 5432,
  user: 'postgres',
  database: 'ultsql',
});

await client.connect();
const res = await client.query('SELECT * FROM users WHERE id = $1', [1]);
console.log(res.rows);
await client.end();

import psycopg2

conn = psycopg2.connect(
    host="localhost",
    port=5432,
    user="postgres",
    dbname="ultsql"
)
cur = conn.cursor()
cur.execute("SELECT name, email FROM users WHERE id = %s", (1,))
print(cur.fetchall())
cur.close()
conn.close()

package main

import (
    "context"
    "fmt"
    "github.com/jackc/pgx/v5"
)

func main() {
    conn, err := pgx.Connect(context.Background(), "postgres://postgres@localhost:5432/ultsql")
    if err != nil { panic(err) }
    defer conn.Close(context.Background())

    var name, email string
    err = conn.QueryRow(context.Background(), "SELECT name, email FROM users WHERE id=$1", 1).Scan(&name, &email)
    fmt.Printf("User: %s <%s>\n", name, email)
}

use tokio_postgres::{NoTls, Error};

#[tokio::main]
async fn main() -> Result<(), Error> {
    let (client, connection) =
        tokio_postgres::connect("host=localhost port=5432 user=postgres dbname=ultsql", NoTls).await?;

    tokio::spawn(async move {
        if let Err(e) = connection.await {
            eprintln!("connection error: {}", e);
        }
    });

    let rows = client.query("SELECT name, email FROM users WHERE id = $1", &[&1i32]).await?;
    for row in rows {
        let name: &str = row.get(0);
        let email: &str = row.get(1);
        println!("User: {} <{}>", name, email);
    }
    Ok(())
}