Sqlite Client

SQLite Client

Use the sqlite (v5+) + sqlite3 libraries to operate SQLite databases. All APIs return ES6 Promises and support async/await.

Use Cases

• Creating SQLite databases and tables
• Executing SQL queries (SELECT/INSERT/UPDATE/DELETE)
• Database migrations
• Reading or analyzing the contents of .db files
• Importing/exporting data to/from SQLite
• Using in-memory databases for rapid prototyping

Prerequisites

Before performing any database operations, ensure dependencies are installed in the project:

npm install sqlite3 sqlite

Quick Start

Opening a Database

const sqlite3 = require('sqlite3')
const { open } = require('sqlite')

async function getDb() {
  return open({
    filename: './data.db',       // File path, or ':memory:' for in-memory database
    driver: sqlite3.Database
  })
}

Using Cached Instances

driver: sqlite3.cached.Database  // Reuse connections for the same file

Closing the Database

await db.close()

Core Operations

Creating Tables & Inserting Data

await db.exec('CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)')
await db.exec(`INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com')`)

Querying a Single Row

const row = await db.get('SELECT * FROM users WHERE id = ?', [1])
// row = { id: 1, name: 'Alice', email: 'alice@example.com' } or undefined

Querying Multiple Rows

const rows = await db.all('SELECT * FROM users WHERE name LIKE ?', ['%li%'])
// rows = [{ id: 1, name: 'Alice', ... }]

Inserting a Row

const result = await db.run('INSERT INTO users (name, email) VALUES (?, ?)', ['Bob', 'bob@example.com'])
// result.lastID → New row ID
// result.changes → Number of rows affected

Updating / Deleting Rows

const result = await db.run('UPDATE users SET name = ? WHERE id = ?', ['Bob Updated', 2])
// result.changes → Number of rows affected

await db.run('DELETE FROM users WHERE id = ?', [2])

Named Parameters

await db.get('SELECT * FROM users WHERE name = :name', { ':name': 'Alice' })
await db.run('INSERT INTO users (name, email) VALUES (:name, :email)', { ':name': 'Carol', ':email': 'carol@example.com' })

Prepared Statements

const stmt = await db.prepare('INSERT INTO users (name, email) VALUES (?, ?)')
await stmt.run('Dave', 'dave@example.com')
await stmt.run('Eve', 'eve@example.com')
await stmt.finalize()  // Must finalize after use

Iterating Row by Row (each)

const rowCount = await db.each(
  'SELECT * FROM users',
  [],
  (err, row) => {
    if (err) throw err
    console.log(row.name)
  }
)
// rowCount → Total number of rows processed

Migrations

Create a migrations/ folder in the project directory, name SQL files sequentially (e.g., 001-init.sql), and then execute:

await db.migrate({
  force: false,                    // true to rollback and reapply the latest migration
  table: 'migrations',             // Name of the migration record table
  migrationsPath: './migrations'   // Path to migration files
})

Example migration file migrations/001-init.sql:

CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Utility Functions

Common patterns for reading the contents of a .db file:

// List all tables
const tables = await db.all("SELECT name FROM sqlite_master WHERE type='table'")

// Get table schema
const info = await db.all(`PRAGMA table_info(${tableName})`)

// Get row count
const { count } = await db.get(`SELECT COUNT(*) as count FROM ${tableName}`)

Debugging

const sqlite3 = require('sqlite3')
sqlite3.verbose()  // Enable verbose logging

db.on('trace', (sql) => {
  console.log('SQL:', sql)
})

Notes

• The db object returned by open() wraps sqlite3.Database; all methods return Promises.
• db.exec() is used for executing multiple SQL statements (no return value); db.run() is for single write operations.
• Prepared statements must be finalize()d after use to prevent memory leaks.
• SQLite supports a maximum database file size of 281 TB, with a maximum row size of approximately 1 GB.
• For concurrent writes, use WAL mode: await db.exec('PRAGMA journal_mode=WAL')

Advanced Reference

For detailed API documentation and more usage patterns, see references/api.md.