Sqlite Client

SQLite database operations. Use this skill when users need to create, read, query, or modify SQLite databases (.db files).

Security Scan

The skill's files and instructions match its stated purpose (SQLite client helpers); it does not request unrelated credentials, strange installs, or hidden network endpoints.

Purpose & Capability

Name/description, SKILL.md examples, reference doc, and the two included Node scripts all consistently implement SQLite read/write/query functionality using the sqlite3/sqlite npm libraries. There are no unrelated credentials, binaries, or config paths requested.

Instruction Scope

Instructions and examples focus on opening .db files, running queries, migrations, and inspecting schemas. The included CLI scripts accept a user-supplied DB path and SQL and will execute arbitrary SQL provided by whoever runs them — this is expected for a DB client but means the runner must avoid giving it untrusted or system database paths.

Install Mechanism

No install spec is present (instruction-only). The SKILL.md recommends installing sqlite3 and sqlite from npm, which is appropriate. Note: sqlite3 is a native module and may require build tools on some systems; this is operational, not malicious.

Credentials

The skill requests no environment variables or credentials. All filesystem access is driven by user-supplied database file paths, which is proportionate to the stated purpose.

Persistence & Privilege

The skill is not always-enabled and does not request elevated persistence or modify other skills/settings. It can be invoked by the model (normal behavior) but has no special privileges.

Assessment

This skill appears coherent for inspecting and querying SQLite files. Before using it: only open database files you trust (don’t point it at system or sensitive DBs), review the included scripts if you will run them, install sqlite3/sqlite from the official npm registry, and run queries in a safe/test environment if you’re unsure about destructive SQL. The CLI scripts execute whatever SQL you pass, so treat inputs as authoritative and avoid exposing them to untrusted users or networks.

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', '[e-mail]')`)

Querying a Single Row

const row = await db.get('SELECT * FROM users WHERE id = ?', [1])
// row = { id: 1, name: 'Alice', email: '[e-mail]' } 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', '[e-mail]'])
// 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': '[e-mail]' })

Prepared Statements

const stmt = await db.prepare('INSERT INTO users (name, email) VALUES (?, ?)')
await stmt.run('Dave', '[e-mail]')
await stmt.run('Eve', '[e-mail]')
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.