SQLite Adapter

The SQLite adapter provides a lightweight, file-based database solution perfect for small to medium applications, local development, and embedded systems.

Installation

Install the SQLite adapter and its dependencies:

npm install @c15t/backend-sqlite better-sqlite3 @types/better-sqlite3

Configuration

Configure the SQLite adapter with your database file path:

import { c15tInstance } from '@c15t/backend';
import { sqliteAdapter } from '@c15t/backend/db/adapters/sqlite';
 
const instance = c15tInstance({
  baseURL: 'http://localhost:3000',
  database: sqliteAdapter({
    // File path for the SQLite database (use :memory: for in-memory database)
    filename: './data/c15t.db',
    
    // Optional: Enable WAL mode for better concurrency (default: true)
    useWAL: true,
    
    // Optional: Set journal mode (default: 'wal')
    journalMode: 'wal', // 'delete', 'truncate', 'persist', 'memory', 'wal', 'off'
    
    // Optional: Enable foreign keys (default: true)
    foreignKeys: true,
    
    // Optional: Set busy timeout in milliseconds (default: 5000)
    busyTimeout: 5000,
    
    // Optional: Initialize schema if not exists
    initSchema: true,
  }),
});

In-Memory Database

For testing or development, you can use an in-memory database:

const instance = c15tInstance({
  database: sqliteAdapter({
    filename: ':memory:',
  }),
});

Schema Management

Initialize your database schema:

const createSchema = async (db) => {
  await db.exec(`
    CREATE TABLE IF NOT EXISTS users (
      id TEXT PRIMARY KEY,
      name TEXT NOT NULL,
      email TEXT UNIQUE NOT NULL,
      created_at INTEGER DEFAULT (unixepoch())
    );
    
    CREATE TABLE IF NOT EXISTS consents (
      id TEXT PRIMARY KEY,
      user_id TEXT NOT NULL,
      purpose TEXT NOT NULL,
      granted BOOLEAN NOT NULL,
      timestamp INTEGER DEFAULT (unixepoch()),
      FOREIGN KEY (user_id) REFERENCES users(id)
    );
    
    CREATE INDEX IF NOT EXISTS idx_consents_user_id ON consents(user_id);
  `);
};
 
// Use in initialization
const instance = c15tInstance({
  database: sqliteAdapter({
    filename: './data/c15t.db',
    onInit: createSchema,
  }),
});

Usage Examples

Basic CRUD Operations

// Create a new record
const user = await instance.database.create('users', {
  id: crypto.randomUUID(),
  name: 'John Doe',
  email: 'john@example.com'
});
 
// Find records
const users = await instance.database.find('users', {
  where: { email: 'john@example.com' },
  orderBy: { created_at: 'desc' },
  limit: 10
});
 
// Update a record
const updatedUser = await instance.database.update(
  'users',
  { where: { id: user.id } },
  { name: 'John Smith' }
);
 
// Delete a record
await instance.database.delete('users', { where: { id: user.id } });

Transactions

await instance.database.transaction(async (trx) => {
  // All operations inside use the same transaction
  const user = await trx.create('users', {
    id: crypto.randomUUID(),
    name: 'Alice Johnson',
    email: 'alice@example.com'
  });
  
  await trx.create('consents', {
    id: crypto.randomUUID(),
    user_id: user.id,
    purpose: 'marketing',
    granted: true
  });
  
  // Transaction automatically commits unless an error is thrown
});

Advanced Queries

// Complex where conditions
const users = await instance.database.find('users', {
  where: {
    $or: [
      { name: { $like: '%John%' } },
      { email: { $like: '%@example.com' } }
    ],
    created_at: { $gt: Date.now() - 86400000 } // Last 24 hours
  }
});
 
// Raw queries for specific needs
const stats = await instance.database.raw(`
  SELECT purpose, COUNT(*) as count
  FROM consents
  WHERE granted = true
  GROUP BY purpose
  ORDER BY count DESC
`);

Performance Optimization

// Enable pragmas for better performance
await instance.database.raw(`
  PRAGMA synchronous = NORMAL;
  PRAGMA cache_size = -64000; -- 64MB
  PRAGMA temp_store = MEMORY;
  PRAGMA mmap_size = 268435456; -- 256MB
`);

Backup and Maintenance

// Backup database
await instance.database.raw(`
  VACUUM INTO '/backup/c15t-backup.db';
`);
 
// Optimize database
await instance.database.raw(`
  PRAGMA optimize;
  VACUUM;
`);

Best Practices

Use WAL mode for better concurrency and performance
Create proper indexes on columns frequently used in WHERE clauses
Use transactions for operations that must be atomic
Implement regular backups using VACUUM INTO
Set busy timeout to handle concurrent access conflicts
Use prepared statements to prevent SQL injection (handled automatically by the adapter)

Limitations

Limited concurrency compared to client-server databases
No built-in user management or access control
Maximum database size limited to 281 TB (though practical limits are much lower)
Limited support for ALTER TABLE operations

SQLite Adapter

On this page