SQLite Adapter

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,
  }),
});

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

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,
  }),
});

// 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 } });

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
});

// 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
`);

// 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 database
await instance.database.raw(`
  VACUUM INTO '/backup/c15t-backup.db';
`);

// Optimize database
await instance.database.raw(`
  PRAGMA optimize;
  VACUUM;
`);