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
