npm package: @seedts/adapter-sqlite
Description: SQLite adapter for SeedTS
Author: SeedTS Contributors
Latest version: 0.1.1

# @seedts/adapter-sqlite

SQLite adapter for SeedTS using [better-sqlite3](https://github.com/WiseLibs/better-sqlite3).

## Installation

```bash
npm install @seedts/adapter-sqlite better-sqlite3
# or
pnpm add @seedts/adapter-sqlite better-sqlite3
# or
yarn add @seedts/adapter-sqlite better-sqlite3
```

**Note:** `better-sqlite3` is a peer dependency and must be installed separately.

## Quick Start

```typescript
import { SqliteAdapter } from '@seedts/adapter-sqlite';

// Create adapter
const adapter = new SqliteAdapter({
  filename: './database.db',
});

// Create table
adapter.exec(`
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )
`);

// Insert data
const users = await adapter.insert('users', [
  { name: 'Alice', email: 'alice@example.com' },
  { name: 'Bob', email: 'bob@example.com' },
]);

console.log(users[0].id); // Auto-generated ID

// Query data
const allUsers = await adapter.query('users');
const alice = await adapter.query('users', { name: 'Alice' });

// Update data
await adapter.update('users', [
  { id: 1, name: 'Alice Smith', email: 'alice@example.com' },
]);

// Delete data
await adapter.delete('users', [2]);

// Clean up
await adapter.disconnect();
```

## Configuration

### Constructor Options

```typescript
type SqliteConfig = {
  filename: string;
  options?: Database.Options;
};
```

**`filename`** (required)
- Path to the SQLite database file
- Use `':memory:'` for in-memory database
- Relative or absolute paths accepted

**`options`** (optional)
- Options passed to better-sqlite3
- See [better-sqlite3 API documentation](https://github.com/WiseLibs/better-sqlite3/blob/master/docs/api.md) for available options

### Examples

```typescript
// File-based database
const adapter = new SqliteAdapter({
  filename: './data/app.db',
});

// In-memory database (for testing)
const adapter = new SqliteAdapter({
  filename: ':memory:',
});

// With options
const adapter = new SqliteAdapter({
  filename: './app.db',
  options: {
    verbose: console.log,
    fileMustExist: false,
  },
});
```

## API Reference

### insert(tableName, data)

Insert records into the database.

```typescript
async insert<T>(tableName: string, data: T[]): Promise<T[]>
```

**Parameters:**
- `tableName` - Name of the table
- `data` - Array of records to insert

**Returns:** Array of inserted records with auto-generated IDs

**Example:**
```typescript
const users = await adapter.insert('users', [
  { name: 'Alice', email: 'alice@example.com' },
  { name: 'Bob', email: 'bob@example.com' },
]);

console.log(users[0].id); // 1
console.log(users[1].id); // 2
```

### query(tableName, conditions?)

Query records from the database.

```typescript
async query<T>(tableName: string, conditions?: Record<string, any>): Promise<T[]>
```

**Parameters:**
- `tableName` - Name of the table
- `conditions` - Optional WHERE conditions as key-value pairs

**Returns:** Array of matching records

**Example:**
```typescript
// Query all records
const allUsers = await adapter.query('users');

// Query with conditions
const activeUsers = await adapter.query('users', { active: true });
const alice = await adapter.query('users', { name: 'Alice', age: 30 });
```

### update(tableName, data)

Update existing records in the database.

```typescript
async update<T>(tableName: string, data: T[]): Promise<T[]>
```

**Parameters:**
- `tableName` - Name of the table
- `data` - Array of records to update (must include `id` field)

**Returns:** Array of updated records

**Throws:** Error if record doesn't have an `id` field

**Example:**
```typescript
await adapter.update('users', [
  { id: 1, name: 'Alice Updated', email: 'alice.new@example.com' },
]);
```

### delete(tableName, ids)

Delete records from the database.

```typescript
async delete(tableName: string, ids: any[]): Promise<void>
```

**Parameters:**
- `tableName` - Name of the table
- `ids` - Array of IDs to delete

**Example:**
```typescript
// Delete single record
await adapter.delete('users', [1]);

// Delete multiple records
await adapter.delete('users', [1, 2, 3]);
```

### beginTransaction()

Begin a database transaction.

```typescript
async beginTransaction(): Promise<void>
```

**Throws:** Error if transaction is already in progress

### commit()

Commit the current transaction.

```typescript
async commit(): Promise<void>
```

**Throws:** Error if no transaction is in progress

### rollback()

Rollback the current transaction.

```typescript
async rollback(): Promise<void>
```

**Throws:** Error if no transaction is in progress

### truncate(tableName)

Truncate a table and reset auto-increment counter.

```typescript
async truncate(tableName: string): Promise<void>
```

**Parameters:**
- `tableName` - Name of the table to truncate

**Example:**
```typescript
await adapter.truncate('users');
```

### disconnect()

Close the database connection. Automatically rolls back any active transaction.

```typescript
async disconnect(): Promise<void>
```

**Example:**
```typescript
await adapter.disconnect();
```

### exec(sql)

Execute raw SQL statements. Useful for schema creation, migrations, or bulk operations.

```typescript
exec(sql: string): void
```

**Parameters:**
- `sql` - SQL statements to execute

**Example:**
```typescript
adapter.exec(`
  CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
  );

  CREATE INDEX idx_email ON users(email);

  INSERT INTO users (name, email) VALUES ('Admin', 'admin@example.com');
`);
```

### getDatabase()

Get the underlying better-sqlite3 Database instance for advanced usage.

```typescript
getDatabase(): Database.Database
```

**Returns:** better-sqlite3 Database instance

**Example:**
```typescript
const db = adapter.getDatabase();
const result = db.prepare('SELECT COUNT(*) as count FROM users').get();
console.log(result.count);
```

## Usage Examples

### Basic CRUD Operations

```typescript
import { SqliteAdapter } from '@seedts/adapter-sqlite';

const adapter = new SqliteAdapter({ filename: './app.db' });

// Create table
adapter.exec(`
  CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    price REAL NOT NULL,
    stock INTEGER DEFAULT 0
  )
`);

// Insert
const products = await adapter.insert('products', [
  { name: 'Laptop', price: 999.99, stock: 10 },
  { name: 'Mouse', price: 29.99, stock: 50 },
]);

// Query
const allProducts = await adapter.query('products');
const inStockProducts = await adapter.query('products', { stock: 10 });

// Update
await adapter.update('products', [
  { id: 1, name: 'Laptop Pro', price: 1299.99, stock: 10 },
]);

// Delete
await adapter.delete('products', [2]);

await adapter.disconnect();
```

### Using Transactions

```typescript
const adapter = new SqliteAdapter({ filename: './app.db' });

try {
  await adapter.beginTransaction();

  // Insert order
  const orders = await adapter.insert('orders', [
    { user_id: 1, total: 99.99 },
  ]);

  // Insert order items
  await adapter.insert('order_items', [
    { order_id: orders[0].id, product_id: 1, quantity: 2 },
    { order_id: orders[0].id, product_id: 2, quantity: 1 },
  ]);

  await adapter.commit();
  console.log('Order created successfully');
} catch (error) {
  await adapter.rollback();
  console.error('Order creation failed:', error);
}

await adapter.disconnect();
```

### With SeedTS Seeds

```typescript
import { seed } from '@seedts/core';
import { SqliteAdapter } from '@seedts/adapter-sqlite';

const adapter = new SqliteAdapter({ filename: './app.db' });

// Setup schema
adapter.exec(`
  CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
  )
`);

// Define seed
const usersSeed = seed('users')
  .count(100)
  .factory(async (ctx) => ({
    name: `User ${ctx.index}`,
    email: `user${ctx.index}@example.com`,
  }))
  .adapter(adapter)
  .build();

// Execute seed
await usersSeed.execute();

await adapter.disconnect();
```

### In-Memory Database for Testing

```typescript
import { SqliteAdapter } from '@seedts/adapter-sqlite';

describe('User Repository', () => {
  let adapter: SqliteAdapter;

  beforeEach(() => {
    // Create fresh in-memory database for each test
    adapter = new SqliteAdapter({ filename: ':memory:' });

    adapter.exec(`
      CREATE TABLE users (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT NOT NULL
      )
    `);
  });

  afterEach(async () => {
    await adapter.disconnect();
  });

  it('should insert users', async () => {
    const users = await adapter.insert('users', [
      { name: 'Alice', email: 'alice@example.com' },
    ]);

    expect(users).toHaveLength(1);
    expect(users[0].id).toBe(1);
  });
});
```

### Advanced: Custom Queries

```typescript
const adapter = new SqliteAdapter({ filename: './app.db' });

// Get the underlying database instance
const db = adapter.getDatabase();

// Execute custom query with parameters
const stmt = db.prepare(`
  SELECT users.*, COUNT(orders.id) as order_count
  FROM users
  LEFT JOIN orders ON users.id = orders.user_id
  WHERE users.created_at > ?
  GROUP BY users.id
  HAVING order_count > ?
`);

const activeUsers = stmt.all('2024-01-01', 5);
console.log(activeUsers);

await adapter.disconnect();
```

## Features

- ✅ **Synchronous API** - Fast synchronous operations via better-sqlite3
- ✅ **Type-safe** - Full TypeScript support with generics
- ✅ **Auto-increment support** - Automatically handles AUTOINCREMENT fields
- ✅ **Transactions** - Full ACID transaction support
- ✅ **In-memory mode** - Perfect for testing
- ✅ **Raw SQL** - Execute any SQL via `exec()` method
- ✅ **Zero config** - Works out of the box
- ✅ **Lightweight** - Minimal dependencies

## Performance

better-sqlite3 is a synchronous SQLite library that's faster than asynchronous alternatives for most use cases. It's ideal for:

- Local development
- Testing environments
- Electron apps
- CLI tools
- Small to medium applications

## Testing

```bash
# Run tests
pnpm test

# Watch mode
pnpm test:watch

# Coverage
pnpm test:coverage
```

## Limitations

- **No async operations** - better-sqlite3 is synchronous (wrapped in async for adapter compatibility)
- **Single database file** - One adapter instance per database file
- **Simple WHERE clauses** - The `query()` method only supports simple equality conditions. Use `getDatabase()` for complex queries.

## Migration Guide

### From other libraries

If you're migrating from other SQLite libraries:

```typescript
// Before (with other library)
const db = new Database('app.db');
db.exec('CREATE TABLE...');
const stmt = db.prepare('INSERT INTO users VALUES (?, ?)');
stmt.run('Alice', 'alice@example.com');

// After (with SeedTS adapter)
const adapter = new SqliteAdapter({ filename: 'app.db' });
adapter.exec('CREATE TABLE...');
await adapter.insert('users', [
  { name: 'Alice', email: 'alice@example.com' },
]);
```

## Troubleshooting

### Database is locked

SQLite databases can only have one writer at a time. Ensure you're not opening multiple connections to the same database file.

```typescript
// ❌ Don't do this
const adapter1 = new SqliteAdapter({ filename: './app.db' });
const adapter2 = new SqliteAdapter({ filename: './app.db' });

// ✅ Do this instead - reuse the same adapter
const adapter = new SqliteAdapter({ filename: './app.db' });
```

### Installation errors

better-sqlite3 requires compilation. If you get installation errors:

```bash
# Clear node_modules and reinstall
rm -rf node_modules pnpm-lock.yaml
pnpm install

# Or use prebuild binaries
npm install --prefer-offline --no-audit better-sqlite3
```

## Related

- [@seedts/adapters](../adapters) - Base adapter classes
- [@seedts/adapter-postgresql](../adapter-postgresql) - PostgreSQL adapter
- [better-sqlite3](https://github.com/WiseLibs/better-sqlite3) - The underlying SQLite library

## License

MIT