better-sqlite3-patterns

# better-sqlite3 Patterns Skill

Patterns for using the better-sqlite3 library in Node.js.

## Installation and Setup

### Installation

```bash
npm install better-sqlite3
npm install --save-dev @types/better-sqlite3
```

### Database Connection

```typescript
import Database from 'better-sqlite3'

// Basic connection
const db = new Database('database.db')

// With options
const db = new Database('database.db', {
  readonly: false,
  fileMustExist: false,
  timeout: 5000,
  verbose: console.log,  // Log all queries
})

// In-memory database
const db = new Database(':memory:')

// Close connection
db.close()
```

### Initial Configuration

```typescript
function initDatabase(path: string): Database.Database {
  const db = new Database(path)

  // Performance settings
  db.pragma('journal_mode = WAL')
  db.pragma('synchronous = NORMAL')
  db.pragma('cache_size = -64000')
  db.pragma('foreign_keys = ON')
  db.pragma('temp_store = MEMORY')

  return db
}
```

## Prepared Statements

### Creating Statements

```typescript
// Prepare a statement
const stmt = db.prepare('SELECT * FROM users WHERE id = ?')

// Execute methods
const user = stmt.get(1)           // Single row
const users = stmt.all()           // All rows as array
for (const row of stmt.iterate()) { // Iterator
  console.log(row)
}

// Run for INSERT/UPDATE/DELETE
const insert = db.prepare('INSERT INTO users (email) VALUES (?)')
const info = insert.run('test@example.com')
console.log(info.lastInsertRowid)
console.log(info.changes)
```

### Parameter Binding

```typescript
// Positional parameters
const stmt = db.prepare('SELECT * FROM users WHERE id = ? AND active = ?')
stmt.get(1, true)

// Named parameters with @ prefix
const stmt = db.prepare('SELECT * FROM users WHERE id = @id')
stmt.get({ id: 1 })

// Named parameters with $ prefix
const stmt = db.prepare('SELECT * FROM users WHERE id = $id')
stmt.get({ id: 1 })

// Named parameters with : prefix
const stmt = db.prepare('SELECT * FROM users WHERE id = :i