database-patterns

spences10 的头像作者 spences10

使用带预处理语句的 better-sqlite3 进行 SQLite 操作。在实现 CRUD 操作、时间戳和具有行级安全性的用户范围查询时使用。

📖 中文导读:使用带预处理语句的 better-sqlite3 进行 SQLite 操作。在实现 CRUD 操作、时间戳和具有行级安全性的用户范围查询时使用。 以下正文为该技能的原始 SKILL.md 内容(英文),上方安装命令可直接使用。

name: database-patterns

prettier-ignore

description: SQLite operations using better-sqlite3 with prepared statements. Use when implementing CRUD operations, timestamps, and user-scoped queries with row-level security.

Database Patterns

Quick Start

import { db } from '$lib/server/db';
import { nanoid } from 'nanoid';

// SELECT with user_id (row-level security)
const contact = db
	.prepare('SELECT * FROM contacts WHERE id = ? AND user_id = ?')
	.get(id, user_id) as Contact | undefined;

// INSERT with nanoid and timestamps
const stmt = db.prepare(
	'INSERT INTO contacts (id, user_id, name, created_at, updated_at) VALUES (?, ?, ?, ?, ?)',
);
stmt.run(nanoid(), user_id, name, Date.now(), Date.now());

Core Principles

  • Prepared statements: Use for all queries (SQL injection prevention)
  • ID generation: Use nanoid() for all primary keys (no auto-increment)
  • Timestamps: Store as Unix epoch with Date.now() (milliseconds)
  • Row-level security: Always include user_id in WHERE clause (never query by ID alone)
  • Transactions: Use for multi-table operations (all-or-nothing)
  • Synchronous: better-sqlite3 is sync - no async/await needed

Reference Files