npm package: @pineliner/odb-client
Description: Isomorphic client for ODB-Lite with postgres.js-like template string SQL support
Latest version: 1.1.4

# 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

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

## Quick Start

**🎯 SUPPORTS BOTH QUERY STYLES** - See [QUERY_STYLES.md](./QUERY_STYLES.md) for full documentation

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

```typescript
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)

```typescript
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 tx`INSERT INTO users ${tx({ name: 'John', age: 30 })}`;
await tx`UPDATE 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!)

```typescript
// 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:

```typescript
// 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

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

### Utility Methods (Minimal)

```typescript
// 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!)

```typescript
// 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: 'john@example.com' })}`;
  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: 'alice@example.com' },
  { name: 'Bob', email: 'bob@example.com' }
];

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

### Database Management

```typescript
// 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

```typescript
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

```typescript
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

```typescript
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

```typescript
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:

```typescript
// 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

```bash
# Install dependencies
bun install

# Run tests
bun test

# Build
bun run build

# Type check
bun run typecheck
```

## License

MIT