@operaide/database (Experimental)

This library provides aktors for integrating SQLite databases powered by libSQL. It serves as the foundation for persistent data storage, LLM memory, vector embeddings, and virtual file systems within Reaktor workflows.

Get Started

Head to the Data Studio → Agent Database Tab and create your first Agent Database. Copy the generated Database ID and use it to access the database inside your Reaktor:

import { aktorDatabase } from '@operaide/database';
import { aktorSetting } from '@operaide/aktor';
import { z } from 'zod';

const databaseId = aktorSetting(z.string(), '<database-id>', 'Database ID');
const db = aktorDatabase({ id: databaseId });

For testing and prototyping, use aktorInMemoryDatabase, which does not persist to disk:

import { aktorInMemoryDatabase } from '@operaide/database';

const db = aktorInMemoryDatabase({ id: aktorConst('test-db') });

Core Concepts

The libSQL Client

The aktorDatabase and aktorInMemoryDatabase aktors return a libSQL Client instance. This client provides low-level database operations:

• client.execute(sql, args?) - Execute parameterized SQL statements (SELECT, INSERT, UPDATE, DELETE)
• client.batch(statements, mode?) - Execute multiple statements atomically
• client.transaction(mode) - Start an explicit transaction with commit/rollback support

Helper Aktors vs. Raw Client

The library provides two approaches for database operations:

1. Helper Aktors (aktorQuery, aktorInsert) - Type-safe wrappers with Zod validation for common SELECT/INSERT operations
2. Raw Client (client.execute()) - Direct SQL execution for UPDATE, DELETE, complex queries, and operations not covered by helpers

When to use each:

Operation	Recommended Approach	Reason
SELECT with typed results	aktorQuery	Automatic Zod validation and type inference
INSERT with validation	aktorInsert	Schema validation before insertion
UPDATE statements	client.execute()	No helper provided; use parameterized queries
DELETE statements	client.execute()	No helper provided; use parameterized queries
Schema creation	client.batch()	Atomic multi-statement execution
Complex transactions	client.transaction()	Explicit commit/rollback control
Idempotent workflows	Custom aktors + client.execute()	Check-then-update patterns

SQL Injection Safety

Always use parameterized queries to prevent SQL injection:

// ✅ SAFE: Parameterized query
await client.execute({
    sql: 'DELETE FROM users WHERE id = ?',
    args: [userId]
});

// ❌ UNSAFE: String interpolation
await client.execute(`DELETE FROM users WHERE id = '${userId}'`);

Common Usage Patterns

Simple SELECT Queries

Use aktorQuery for type-safe SELECT operations with automatic validation:

import { aktorQuery } from '@operaide/database';
import { z } from 'zod';

const UserSchema = z.object({
    id: z.number(),
    name: z.string(),
    email: z.string().email()
});

const users = aktorQuery({
    client: db,
    sql: aktorConst('SELECT id, name, email FROM users WHERE active = ?'),
    params: aktorConst([true]),
    schema: UserSchema
});

// Type is automatically inferred as Aktor<Array<{ id: number; name: string; email: string }>>

Simple INSERT Operations

Use aktorInsert for validated insertions:

import { aktorInsert } from '@operaide/database';

const InsertUserSchema = z.object({
    name: z.string().min(1),
    email: z.string().email(),
    age: z.number().int().positive()
});

const dbWithNewUser = aktorInsert({
    client: db,
    table: aktorConst('users'),
    data: aktorConst({
        name: 'Alice',
        email: 'alice@example.com',
        age: 30
    }),
    schema: InsertUserSchema
});

UPDATE Operations

No helper aktor exists for UPDATE; use client.execute() directly:

import { createAktorFunctionAsync } from '@operaide/aktor';

const aktorUpdateUser = createAktorFunctionAsync('aktorUpdateUser', async (p: {
    client: Client;
    userId: string;
    updates: { name?: string; email?: string };
}) => {
    const updateFields: string[] = [];
    const args: any[] = [];

    if (p.updates.name !== undefined) {
        updateFields.push('name = ?');
        args.push(p.updates.name);
    }
    if (p.updates.email !== undefined) {
        updateFields.push('email = ?');
        args.push(p.updates.email);
    }

    if (updateFields.length === 0) {
        return p.client; // No changes
    }

    args.push(p.userId);

    await p.client.execute({
        sql: `UPDATE users SET ${updateFields.join(', ')} WHERE id = ?`,
        args: args
    });

    return p.client;
});

DELETE Operations

Similarly, DELETE requires direct client.execute():

const aktorDeleteUser = createAktorFunctionAsync('aktorDeleteUser', async (p: {
    client: Client;
    userId: string;
}) => {
    const result = await p.client.execute({
        sql: 'DELETE FROM users WHERE id = ?',
        args: [p.userId]
    });

    return result.rowsAffected > 0; // Returns true if deleted
});

Idempotent Upsert Patterns

For workflows that must be idempotent (check-then-insert/update), compose custom aktors:

const aktorUpsertUser = createAktorFunctionAsync('aktorUpsertUser', async (p: {
    client: Client;
    userId: string;
    name: string;
    email: string;
}) => {
    // Check if user exists
    const existing = await p.client.execute({
        sql: 'SELECT id FROM users WHERE id = ?',
        args: [p.userId]
    });

    if (existing.rows.length > 0) {
        // UPDATE existing record
        await p.client.execute({
            sql: 'UPDATE users SET name = ?, email = ? WHERE id = ?',
            args: [p.name, p.email, p.userId]
        });
    } else {
        // INSERT new record
        await p.client.execute({
            sql: 'INSERT INTO users (id, name, email) VALUES (?, ?, ?)',
            args: [p.userId, p.name, p.email]
        });
    }

    return p.client;
});

Alternative: Use SQLite UPSERT syntax (requires a unique constraint):

await client.execute({
    sql: `
        INSERT INTO users (id, name, email) VALUES (?, ?, ?)
        ON CONFLICT(id) DO UPDATE SET name=excluded.name, email=excluded.email
    `,
    args: [userId, name, email]
});

Batch Operations

Use client.batch() for atomic multi-statement execution (e.g., schema initialization):

const aktorInitSchema = createAktorFunctionAsync('aktorInitSchema', async (p: {
    client: Client;
}) => {
    await p.client.batch([
        `CREATE TABLE IF NOT EXISTS users (
            id TEXT PRIMARY KEY,
            name TEXT NOT NULL,
            email TEXT UNIQUE NOT NULL,
            created_at TEXT DEFAULT (datetime('now'))
        )`,
        `CREATE TABLE IF NOT EXISTS orders (
            order_id TEXT PRIMARY KEY,
            user_id TEXT NOT NULL,
            total REAL NOT NULL,
            FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
        )`,
        `CREATE INDEX IF NOT EXISTS idx_orders_user ON orders(user_id)`
    ], 'write');

    return p.client;
});

Transactions

For explicit multi-step operations with rollback capability:

const aktorTransferFunds = createAktorFunctionAsync('aktorTransferFunds', async (p: {
    client: Client;
    fromUserId: string;
    toUserId: string;
    amount: number;
}) => {
    const tx = await p.client.transaction('write');

    try {
        // Deduct from sender
        await tx.execute({
            sql: 'UPDATE accounts SET balance = balance - ? WHERE user_id = ?',
            args: [p.amount, p.fromUserId]
        });

        // Add to receiver
        await tx.execute({
            sql: 'UPDATE accounts SET balance = balance + ? WHERE user_id = ?',
            args: [p.amount, p.toUserId]
        });

        await tx.commit();
        return { success: true };
    } catch (error) {
        await tx.rollback();
        throw error;
    }
});

Vector Extension Support

Overview

@operaide/database can leverage libSQL's vector extension for semantic search and embeddings storage. This extension is deployment-specific—it must be enabled on the database server.

Vector Data Types

When the extension is available, you can use:

• F32_BLOB(dimensions) - Fixed-size float32 vector type (e.g., F32_BLOB(1536) for OpenAI embeddings)
• libsql_vector_idx(column, options) - Vector index for similarity search

Example: Vector Table Schema

await client.batch([
    `CREATE TABLE IF NOT EXISTS vectors (
        chunk_id TEXT PRIMARY KEY,
        vector F32_BLOB(1536) NOT NULL,
        embedding_model TEXT NOT NULL,
        FOREIGN KEY (chunk_id) REFERENCES chunks(chunk_id) ON DELETE CASCADE
    )`,
    `CREATE INDEX IF NOT EXISTS vectors_idx ON vectors(
        libsql_vector_idx(vector, 'type=diskann', 'metric=cosine')
    )`
], 'write');

Reference Implementation

See @operaide/vector for a complete vector database implementation:

• initVectorDatabase.aktor.ts - Schema initialization with vector tables
• CRUD aktors - File, document, chunk, and vector operations
• Query aktors - Similarity search using vector indexes

Location: shared/meteor_server/imports/@operaide/vector/src/database/

Deployment Requirements

• Platform databases created via Data Studio automatically support vector extensions
• Custom libSQL deployments must enable the vector extension at server startup
• In-memory databases (aktorInMemoryDatabase) may not support vector features depending on the libSQL build

Check availability before using vector features:

try {
    await client.execute('SELECT 1 WHERE libsql_vector_idx IS NOT NULL');
    // Vector extension is available
} catch (error) {
    // Vector extension not supported
}

Limitations and Best Practices

Current Limitations

1. No built-in UPDATE/DELETE helpers - You must write custom aktors using client.execute() for these operations.
2. No native ORM - The library provides minimal ORM-like functionality (aktorQuery, aktorInsert). Complex data models require custom aktors.
3. Transaction support varies - While client.transaction() is available, nested transactions are not supported. Keep transactions short and focused.
4. Schema migrations - No built-in migration system. Manage schema evolution manually or use external tools.
5. Connection pooling - The library does not manage connection pools. Each aktorDatabase call retrieves a managed client from the platform's database service.

When to Write Custom Aktors

Write custom aktors when you need:

• Complex queries - Multi-table JOINs, subqueries, window functions
• Batch updates/deletes - Bulk operations beyond simple inserts
• Idempotent workflows - Check-then-update/insert patterns
• Domain-specific logic - Validation, computed fields, business rules
• Transaction management - Multi-step operations requiring rollback

Example custom aktor structure:

export const aktorMyComplexOperation = createAktorFunctionAsync(
    'aktorMyComplexOperation',
    myComplexOperation
);

async function myComplexOperation(p: { client: Client; /* params */ }) {
    // 1. Validate inputs
    // 2. Execute database operations
    // 3. Return typed results
}

Performance Best Practices

1. Use indexes - Create indexes on frequently queried columns
2. Batch inserts - Use client.batch() for multiple inserts instead of individual aktorInsert calls
3. Limit result sets - Always use LIMIT clauses for large tables
4. Avoid N+1 queries - Fetch related data with JOINs instead of multiple queries
5. Test with realistic data - Performance characteristics change significantly with data volume

Schema Evolution Strategies

1. Additive changes - Use ALTER TABLE ADD COLUMN with defaults for backward compatibility
2. Non-destructive migrations - Create new columns/tables instead of dropping existing ones
3. Version tracking - Store schema version in a metadata table
4. Idempotent DDL - Always use IF NOT EXISTS / IF EXISTS clauses

await client.batch([
    `CREATE TABLE IF NOT EXISTS schema_version (version INTEGER PRIMARY KEY)`,
    `INSERT OR IGNORE INTO schema_version (version) VALUES (1)`,
    `ALTER TABLE users ADD COLUMN phone TEXT DEFAULT NULL`
], 'write');

Testing Strategies

1. Use aktorInMemoryDatabase for unit tests - Fast, isolated, no cleanup required
2. Seed test data - Create reusable aktors for common test scenarios
3. Test idempotency - Run operations multiple times to verify upsert logic
4. Test error paths - Verify rollback behavior in transactions

// Example test setup
const testDb = aktorInMemoryDatabase({ id: aktorConst('test') });
const dbWithSchema = aktorInitSchema({ client: testDb });
const dbWithData = aktorSeedTestData({ client: dbWithSchema });

// Run tests against dbWithData

Security Considerations

1. Always parameterize queries - Never interpolate user input into SQL strings
2. Validate schemas - Use Zod schemas to validate all inputs before database operations
3. Least privilege - Database users should have minimal required permissions
4. Audit sensitive operations - Log DELETE/UPDATE operations on critical tables

Available Aktors Reference

Database Connection Aktors

• aktorDatabase: Fetches a database client based on a provided database ID.
• aktorInMemoryDatabase: Creates and returns an in-memory database client for testing purposes.

ORM Aktors

• aktorQuery: Runs a SQL statement and validates each row with a Zod schema, returning typed results.
• aktorInsert: Inserts data into a specified table, validating the data against a Zod schema before insertion.

Utility Aktors

• aktorExtractResultSet: Extracts and stringifies a ResultSet object.

Virtual File System Aktors

• aktorInitSqlarSchema: Initializes the SQLar schema in the database for the virtual file system.
• aktorWriteFile: Writes a file to the virtual file system.
• aktorReadFile: Reads a file from the virtual file system as a Uint8Array.
• aktorReadTextFile: Reads a text file from the virtual file system as a string.
• aktorStat: Gets information about a file or directory.
• aktorListDir: Lists the contents of a directory.
• aktorDeleteFile: Deletes a file from the virtual file system.

Vector Database Aktors (from @operaide/vector)

For vector-specific operations, install and import @operaide/vector:

• aktorInitVectorDatabase: Initializes a vector database by creating necessary tables and indexes.
• aktorInsertVector: Inserts a vector (content and embedding) into the documents table.
• aktorQueryVector: Queries vectors by similarity using cosine distance.

See the @operaide/vector documentation for complete vector database workflows.

Use Cases

1. Persistent workflow data - Store sales orders, approvals, form submissions for Human-In-The-Loop flows
2. LLM memory - Maintain conversation history, user preferences, learned facts
3. Embeddings and semantic search - Store vector embeddings with metadata (requires vector extension)
4. Virtual file system - Centralize file storage with SQLar format, enabling content-based retrieval

Further Resources

• IntelliSense: Explore aktors with autocomplete in your IDE
• Registry: View latest versions at the Operaide Registry
• Source code: Reference implementation in shared/meteor_server/imports/@operaide/database/
• Examples: See operaide-apps/apps/op-demo-database/ and operaide-apps/apps/op-demo-email-history/ for real-world usage