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