nara-database

Database (SQLite Usage)

Ownership

Feature repositories own SQL. Shared database lifecycle lives in src/shared/database/; route modules and browser code must not access SQLite directly.

import { getDatabase } from '../../../shared/database';

export function findUserById(userId: string): StoredUser | undefined {
  return getDatabase()
    .prepare(
      'SELECT id, name, email, password, avatar, created_at, updated_at FROM users WHERE id = ?',
    )
    .get(userId) as StoredUser | undefined;
}

Use better-sqlite3 prepared statements for values. Keep row interfaces near the repository that reads them or export them through the Feature's public boundary when another module needs the type.

Parameter binding

Never interpolate user-controlled values into SQL. Bind values through .get(), .all(), or .run():

const pattern = `%${search}%`;
const rows = getDatabase()
  .prepare(
    `SELECT id, name
     FROM products
     WHERE name LIKE ?
     ORDER BY created_at DESC
     LIMIT ? OFFSET ?`,
  )
  .all(pattern, limit, offset) as ProductRow[];

For dynamic IN clauses, generate one placeholder per validated value and spread the values into the prepared statement:

const placeholders = roleIds.map(() => '?').join(', ');
const rows = getDatabase()
  .prepare(`SELECT * FROM roles WHERE id IN (${placeholders})`)
  .all(...roleIds) as Role[];

Dynamic identifiers cannot be bound. Prefer fixed SQL; if an identifier must be dynamic, validate it against a closed allowlist before inserting it into the statement.

Transactions

Use a better-sqlite3 transaction for multi-statement writes that must be atomic:

const database = getDatabase();
const replace = database.transaction(() => {
  database.prepare('DELETE FROM user_roles WHERE user_id = ?').run(userId);
  const statement = database.prepare(
    `INSERT INTO user_roles (id, user_id, role_id, created_at)
     VALUES (?, ?, ?, ?)`,
  );
  const now = Date.now();
  for (const roleId of roleIds) {
    statement.run(randomUUID(), userId, roleId, now);
  }
});
replace();