@seedts/adapter-sqlite

SQLite adapter for SeedTS using better-sqlite3.

Installation

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

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: '[email protected]' },
  { name: 'Bob', email: '[email protected]' },
]);

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: '[email protected]' },
]);

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

// Clean up
await adapter.disconnect();

Configuration

Constructor Options

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 for available options

Examples

// 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.

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:

const users = await adapter.insert('users', [
  { name: 'Alice', email: '[email protected]' },
  { name: 'Bob', email: '[email protected]' },
]);

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

query(tableName, conditions?)

Query records from the database.

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:

// 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.

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:

await adapter.update('users', [
  { id: 1, name: 'Alice Updated', email: '[email protected]' },
]);

delete(tableName, ids)

Delete records from the database.

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

Parameters:

• tableName - Name of the table
• ids - Array of IDs to delete

Example:

// Delete single record
await adapter.delete('users', [1]);

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

beginTransaction()

Begin a database transaction.

async beginTransaction(): Promise<void>

Throws: Error if transaction is already in progress

commit()

Commit the current transaction.

async commit(): Promise<void>

Throws: Error if no transaction is in progress

rollback()

Rollback the current transaction.

async rollback(): Promise<void>

Throws: Error if no transaction is in progress

truncate(tableName)

Truncate a table and reset auto-increment counter.

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

Parameters:

• tableName - Name of the table to truncate

Example:

await adapter.truncate('users');

disconnect()

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

async disconnect(): Promise<void>

Example:

await adapter.disconnect();

exec(sql)

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

exec(sql: string): void

Parameters:

• sql - SQL statements to execute

Example:

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', '[email protected]');
`);

getDatabase()

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

getDatabase(): Database.Database

Returns: better-sqlite3 Database instance

Example:

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

Usage Examples

Basic CRUD Operations

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

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

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

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: '[email protected]' },
    ]);

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

Advanced: Custom Queries

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

# 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:

// 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', '[email protected]');

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

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.

// ❌ 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:

# 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 - Base adapter classes
• @seedts/adapter-postgresql - PostgreSQL adapter
• better-sqlite3 - The underlying SQLite library

License

MIT