plugins/aai-stack-sqlite/skills/sqlite-patterns/SKILL.md
February 1, 2026
Select agents to install to:
npx add-skill https://github.com/the-answerai/alphaagent-team/blob/main/plugins/aai-stack-sqlite/skills/sqlite-patterns/SKILL.md -a claude-code --skill sqlite-patternsInstallation paths:
.claude/skills/sqlite-patterns/# SQLite Patterns Skill
Patterns for using SQLite databases effectively.
## Database Setup
### Creating Database
```typescript
import Database from 'better-sqlite3'
// Open or create database
const db = new Database('app.db')
// With options
const db = new Database('app.db', {
readonly: false,
fileMustExist: false,
timeout: 5000,
verbose: console.log,
})
// In-memory database
const db = new Database(':memory:')
```
### Table Creation
```sql
-- Basic table
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
email TEXT UNIQUE NOT NULL,
name TEXT,
created_at TEXT DEFAULT (datetime('now'))
);
-- With foreign key
CREATE TABLE IF NOT EXISTS posts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
content TEXT,
user_id INTEGER NOT NULL,
created_at TEXT DEFAULT (datetime('now')),
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
-- Enable foreign keys (must be done per connection)
PRAGMA foreign_keys = ON;
```
### Indexes
```sql
-- Single column index
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
-- Composite index
CREATE INDEX IF NOT EXISTS idx_posts_user_date ON posts(user_id, created_at);
-- Unique index
CREATE UNIQUE INDEX IF NOT EXISTS idx_users_email ON users(email);
-- Partial index
CREATE INDEX IF NOT EXISTS idx_active_users
ON users(last_login)
WHERE status = 'active';
```
## Basic CRUD
### Insert
```typescript
// Single insert
const insert = db.prepare('INSERT INTO users (email, name) VALUES (?, ?)')
const result = insert.run('john@example.com', 'John')
console.log(result.lastInsertRowid)
// Named parameters
const insert = db.prepare('INSERT INTO users (email, name) VALUES (@email, @name)')
insert.run({ email: 'john@example.com', name: 'John' })
// Insert or replace
const upsert = db.prepare(`
INSERT INTO users (email, name)
VALUES (@email, @name)
ON CONFLICT(email) DO UPDATE SET name = excluded.name
`)
upsert.run({ email: 'john@example.com', name: 'Jo