コンテンツにスキップ
TomoriBot

Raw SQL Boundary

このコンテンツはまだ日本語訳がありません。

All raw SQL in TomoriBot must live in the repository layer: src/utils/db/repositories/. Application code (commands, events, tools, context builders, caches) must call a repository method instead of writing sql\…“ directly. This keeps queries, their validation, and their cache invalidation in one place, and keeps the rest of the codebase decoupled from the schema.

This rule is enforced automatically: see Enforcement below.

What counts as a violation

Section titled “What counts as a violation”

A violation is any raw sql/tx tagged template literal outside the repository layer and outside the allow-listed paths:

// ❌ In a command / event / tool / util — NOT allowed
const [row] = await sql`SELECT * FROM users WHERE user_disc_id = ${id}`;
// ✅ The same query, moved into a repository method, called from anywhere
const user = await userRepository.loadByDiscordId(id);

The scanner detects:

  • sql\…`andawait sql`…“ template literals
  • Typed forms including nested generics, e.g. sql<Array<{ id: number }>>\…“
  • Transaction callbacks written as tx\…`insidesql.transaction(async (tx) => …)`

It deliberately does not flag:

  • SQL written inside // line comments or /* … */ block comments
  • Plain strings that merely contain SQL text (only tagged templates count)
  • Identifiers that merely end in sql/tx (e.g. mysql\``, someSql“)
  • sql.transaction(…) / sql.begin(…) orchestration itself (only the query literals inside count), so a util may open a transaction and hand tx to a repository method without tripping the gate

Moving a query into a repository

Section titled “Moving a query into a repository”

  1. Pick the repository that owns the table (e.g. users → UserRepository, server_model_configs → ConfigRepository, personas → PersonaRepository). The repositories are singletons exported from src/utils/db/repositories/index.ts.

  2. Add a method that holds the query. Reads return typed rows; writes own their cache invalidation (invalidate after a successful write, in the same code path: never before, never on failure):

    src/utils/db/repositories/UserRepository.ts
    async setSomething(userId: number, value: string): Promise<UserRow | null> {
      const user = await this.update(userId, { some_column: value });
      // update() already invalidates the user cache on success
      return user;
    }

  3. Call the method from the application code, deleting the inline SQL and its now-unused import { sql } from "@/utils/db/client":

    import { userRepository } from "@/utils/db/repositories";
    const user = await userRepository.setSomething(userId, value);

  4. Before inventing a new method, check whether one already exists: duplicated reference-count and lookup queries are a common source of accidental violations.

Exemptions

Section titled “Exemptions”

Two allow-lists in scripts/checks/lib/sqlAudit.ts let SQL live outside repositories/:

  • IGNORE_PATHS: paths skipped entirely. These are the legitimate homes of raw SQL plumbing: the repository layer itself, the DB client, migrations, the migration runner, DB init, and src/types/ (which may embed SQL only in doc comments / string types). You should rarely touch this.

  • EXEMPT_PATHS: individual files allowed to keep raw SQL, each paired with a justification. Hits on these are reported as EXEMPTIONS rather than violations. Current entries are observability/ status helpers, security primitives (crypto/key rotation), the logger, and the RAG service facade.

Prefer moving SQL into a repository. Only add an EXEMPT_PATHS entry when a repository genuinely does not fit (e.g. a security primitive that must own its own table access), and always include a clear reason string:

scripts/checks/lib/sqlAudit.ts
export const EXEMPT_PATHS = new Map<string, string>([
  // …existing entries…
  ["src/utils/security/myPrimitive.ts", "security primitive; owns its own key table access"],
]);

Enforcement

Section titled “Enforcement”

The detector is shared by the CLI gate and a unit test, so they can never disagree:

  • scripts/checks/lib/sqlAudit.ts: the scanner (single source of truth).
  • bun run audit-sql: prints WRITES / READS / EXEMPTIONS and exits non-zero when any violation exists.
  • tests/unit/db/rawSqlBoundary.test.ts: a unit test asserting zero violations on the real tree, plus synthetic tests proving the detector has no false positives (comments/strings) or false negatives (real literals).

Both run as part of bun run vl: audit-sql is the “SQL Audit” item under Code Quality, and the unit test runs in the Unit Tests group.

Quality Gate

Section titled “Quality Gate”
Terminal window
bun run audit-sql   # raw-SQL boundary report; non-zero on violations
bun run check       # TypeScript strict mode (catches unused sql imports, etc.)
bun run lint        # Biome formatting
bun run vl          # full validation suite (includes both of the above)
Section titled “Related Docs”