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

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

Advanced Reference

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

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()

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

npm install sqlite3 sqlite