ODBLite Client

A TypeScript/JavaScript client for ODBLite that provides a postgres.js-like interface for LibSQL databases. This isomorphic client transforms template string SQL to LibSQL JSON format while maintaining familiar syntax.

Features

• 🔄 Isomorphic: Works in Node.js, Bun, and browsers
• 📝 Template String SQL: postgres.js-style template literals
• 🛡️ Type Safe: Full TypeScript support with generics
• ⚡ HTTP-based: Communicates with ODBLite service over HTTP
• 🔄 Retry Logic: Built-in connection retry with exponential backoff
• 🎯 LibSQL Compatible: Optimized for LibSQL/SQLite databases

Installation

npm install @your-org/odblite-client
# or
bun add @your-org/odblite-client

Quick Start

🎯 SUPPORTS BOTH QUERY STYLES - See QUERY_STYLES.md for full documentation

Style 1: Template Strings (postgres.js-like)

import { odblite } from '@pineliner/odb-client';

const sql = odblite({
  baseUrl: 'http://localhost:8671',
  apiKey: 'your-api-key',
  databaseId: 'your-database-hash'
});

// Template string queries
const users = await sql`SELECT * FROM users WHERE age > ${25}`;
const user = await sql`SELECT * FROM users WHERE id = ${userId}`;

Style 2: Query Method (? placeholders)

import { odblite } from '@pineliner/odb-client';

const sql = odblite({
  baseUrl: 'http://localhost:8671',
  apiKey: 'your-api-key',
  databaseId: 'your-database-hash'
});

// Query method with parameters
const users = await sql.query('SELECT * FROM users WHERE age > ?', [25]);
const user = await sql.query('SELECT * FROM users WHERE id = ?', [userId]);

👉 Both styles work identically! Choose what you prefer.

// Context-aware sql() - detects WHERE conditions automatically! const activeUsers = await sql SELECT * FROM users WHERE ${sql({ active: true, role: ['admin', 'user'] })} // Arrays = IN clauses!;

// Transactions work exactly like postgres.js with context-aware tx()! const tx = await sql.begin(); await txINSERT INTO users ${tx({ name: 'John', age: 30 })}; await txUPDATE users SET ${tx({ active: true })} WHERE id = 1; await tx.commit();

## API Reference

### Factory Functions

```typescript
// With config object - returns callable sql function
const sql = odblite({
  baseUrl: 'http://localhost:3000',
  apiKey: 'your-api-key',
  databaseId: 'your-database-id',
  timeout: 30000,
  retries: 3
});

// With individual parameters - returns callable sql function
const sql = odblite('http://localhost:3000', 'your-api-key', 'your-database-id');

Template String Queries (Identical to postgres.js!)

// Basic queries - sql is directly callable!
const users = await sql`SELECT * FROM users`;
const user = await sql`SELECT * FROM users WHERE id = ${id}`;

// Array values (IN clauses)
const users = await sql`SELECT * FROM users WHERE id IN ${[1, 2, 3]}`;

// Object values - context-aware!
await sql`UPDATE users SET ${userUpdate} WHERE id = ${id}`;

Context-Aware sql() Function

The sql() function intelligently detects the context and formats accordingly:

// WHERE conditions - detects null/array values
const conditions = sql({
  active: true,
  deleted_at: null,     // Becomes "IS NULL"
  id: [1, 2, 3],       // Becomes "IN (?, ?, ?)"
  name: 'John'         // Becomes "= ?"
});
// Result: "active" = ? AND "deleted_at" IS NULL AND "id" IN (?, ?, ?) AND "name" = ?

// SET clauses - detects simple objects
const setData = sql({ name: 'John', age: 30 });
// Result: "name" = ?, "age" = ?

// INSERT VALUES - detects arrays of objects
const insertData = sql([
  { name: 'John', age: 30 },
  { name: 'Jane', age: 25 }
]);
// Result: ("name", "age") VALUES (?, ?), (?, ?)

// IN clauses - detects primitive arrays
const inClause = sql([1, 2, 3]);
// Result: (?, ?, ?)

Raw Query Method

const result = await sql.query('SELECT * FROM users WHERE id = ?', [123]);

Utility Methods (Minimal)

// Raw SQL (unescaped) - for table/column names
const tableName = sql.raw('users');
await sql`SELECT * FROM ${tableName}`;

// Identifier escaping - for dynamic column names
const columnName = sql.identifier('user-name');
await sql`SELECT ${columnName} FROM users`;

// Everything else uses context-aware sql()!
const whereClause = sql({
  active: true,
  role: ['admin', 'user'],
  created_at: null
});
await sql`SELECT * FROM users WHERE ${whereClause}`;

const insertClause = sql([
  { name: 'John', age: 30 },
  { name: 'Jane', age: 25 }
]);
await sql`INSERT INTO users ${insertClause}`;

const setClause = sql({ name: 'John', age: 31 });
await sql`UPDATE users SET ${setClause} WHERE id = ${id}`;

Transactions (Context-Aware Like Main sql Function!)

// Begin transaction - returns callable transaction function with context detection
const tx = await sql.begin();

try {
  // tx() is context-aware just like sql()!
  await tx`INSERT INTO users ${tx({ name: 'John', email: '[email protected]' })}`;
  await tx`UPDATE settings SET ${tx({ last_user: 'John', updated_at: new Date() })}`;

  // Use WHERE conditions in transactions
  await tx`DELETE FROM temp_data WHERE ${tx({ expired: true, created_at: null })}`;

  await tx.commit();
} catch (error) {
  await tx.rollback();
  throw error;
}

// Batch operations with context-aware tx()
const tx2 = await sql.begin();
const users = [
  { name: 'Alice', email: '[email protected]' },
  { name: 'Bob', email: '[email protected]' }
];

await tx2`INSERT INTO users ${tx2(users)}`; // Automatic bulk insert detection!
await tx2.commit();

Database Management

// Switch database
sql.setDatabase('another-database-id');

// Health check
const isHealthy = await sql.ping();

// Get database info
const info = await sql.getDatabaseInfo();

// Create new client with different config
const newSql = sql.configure({ timeout: 60000 });

Error Handling

import { QueryError, ConnectionError } from '@your-org/odblite-client';

try {
  await sql`SELECT * FROM users WHERE invalid_column = ${value}`;
} catch (error) {
  if (error instanceof QueryError) {
    console.log('SQL Error:', error.message);
    console.log('Query:', error.query);
    console.log('Params:', error.params);
  } else if (error instanceof ConnectionError) {
    console.log('Connection Error:', error.message);
  }
}

Type Safety

interface User {
  id: number;
  name: string;
  email: string;
  created_at: string;
}

// Typed query results
const users = await sql<User>`SELECT * FROM users`;
// users.rows is User[]

const user = await sql<User>`SELECT * FROM users WHERE id = ${1}`;
// user.rows[0] is User | undefined

Configuration Options

interface ODBLiteConfig {
  baseUrl: string;        // ODBLite service URL
  apiKey: string;         // API authentication key
  databaseId?: string;    // Target database ID
  timeout?: number;       // Request timeout (default: 30000ms)
  retries?: number;       // Retry attempts (default: 3)
}

Query Result Format

interface QueryResult<T> {
  rows: T[];              // Query result rows
  rowsAffected: number;   // Number of affected rows
  executionTime: number;  // Query execution time in ms
  databaseName?: string;  // Database name
}

Comparison with postgres.js

This client provides a similar API to postgres.js but optimized for LibSQL:

// postgres.js style - works the same!
const users = await sql`SELECT * FROM users WHERE age > ${25}`;
const user = await sql`SELECT * FROM users WHERE id = ${userId}`;

// Object destructuring
const [user] = await sql`SELECT * FROM users WHERE id = ${1}`;

// Template string power
const searchTerm = 'john';
const users = await sql`
  SELECT * FROM users
  WHERE name ILIKE ${'%' + searchTerm + '%'}
  ORDER BY created_at DESC
  LIMIT ${10}
`;

Development

# Install dependencies
bun install

# Run tests
bun test

# Build
bun run build

# Type check
bun run typecheck

License

MIT