database-patterns

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

⭐6stars🔀0forks📁View on GitHub🕐Updated Jan 1, 2026⬇️2downloads

When & Why to Use This Skill

This Claude skill provides a robust framework for implementing high-performance SQLite operations using the better-sqlite3 library. It emphasizes security and reliability through prepared statements to prevent SQL injection, row-level security for multi-tenant data isolation, and standardized patterns for ID generation and timestamp management in Node.js environments.

Use Cases

  • Developing secure CRUD operations with prepared statements to ensure protection against SQL injection attacks.
  • Implementing multi-user applications requiring row-level security to ensure users can only access their own data records.
  • Standardizing database architecture using nanoid for primary keys and Unix epoch timestamps for consistent data tracking.
  • Optimizing backend performance in Node.js applications by leveraging synchronous database operations and efficient transaction handling.
namedatabase-patterns
descriptionSQLite 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