database-patterns
SQLite operations using better-sqlite3 with prepared statements. Use when implementing CRUD operations, timestamps, and user-scoped queries with row-level security.
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.
| name | database-patterns |
|---|---|
| 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_idin 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
- schema.md - Complete schema with columns and types
- relationships.md - Table relationships and foreign keys
- query-examples.md - Joins, transactions, and advanced patterns