Scripting

title: Database description: It is the core object of the SQLite API: all SQL execution, querying, schema inspection, transaction handling, and savepoint management are performed through Database.

Database represents a concrete database access context. It is the core object of the SQLite API: all SQL execution, querying, schema inspection, transaction handling, and savepoint management are performed through Database.

In Scripting, a Database instance is never created directly. It is provided by DatabaseQueue or DatabasePool inside read / write callbacks.

Type Definition

class Database {
  readonly changesCount: number
  readonly totalChangesCount: number
  readonly isInsideTransaction: boolean
  readonly lastErrorMessage: string | null
  readonly lastInsertedRowID: number

  schemaVersion(): number

  tableExists(tableName: string, schemaName?: string): boolean
  isTableHasUniqueKeys(tableName: string, uniqueKeys: string[]): boolean

  columnsIn(tableName: string, schemaName?: string): ColumnInfo[]

  primaryKey(tableName: string, schemaName?: string): PrimaryKeyInfo
  foreignKeys(tableName: string, schemaName?: string): ForeignKeyInfo[]
  indexes(tableName: string, schemaName?: string): IndexInfo[]

  makeStatement(sql: string): Statement
  cachedStatement(sql: string): Statement

  inTransaction(operations: () => TransactionCompletion): void
  inSavepoint(operations: () => TransactionCompletion): void

  execute(sql: string, arguments?: StatementArguments): void

  createTable(name: string, options: {
    columns: ColumnDefinition[]
    ifNotExists?: boolean
  }): void
  renameTable(name: string, newName: string): void
  dropTable(name: string): void

  createIndex(name: string, options: {
    table: string
    columns: string[]
    unique?: boolean
    ifNotExists?: boolean
    condition?: string
  }): void
  dropIndex(name: string): void
  dropIndexOn(tableName: string, columns: string[]): void

  fetchAll<T>(sql: string, arguments?: StatementArguments): T[]
  fetchSet<T>(sql: string, arguments?: StatementArguments): T[]
  fetchOne<T>(sql: string, arguments?: StatementArguments): T
  fetchCursor<T>(
    sql: string,
    perform: (next: () => {
      row: T | null
      error: string | null
    }) => void,
    arguments?: StatementArguments
  ): void
}

Role and Responsibilities

Database is responsible for:

Executing SQL statements (DDL and DML)
Creating and managing Statement instances
Querying and fetching data
Exposing schema metadata
Managing transactions and savepoints

Database is not responsible for:

Concurrency scheduling (handled by Queue / Pool)
Lifecycle management (handled by Queue / Pool)
Cross-callback or cross-thread reuse

Obtaining a Database Instance

A Database instance is only available inside DatabaseQueue or DatabasePool callbacks.

queue.write(db => {
  db.execute("INSERT INTO users (name) VALUES (?)", ["Alice"])
})

const users = queue.read(db =>
  db.fetchAll("SELECT * FROM users")
)

State Properties

changesCount

Returns the number of rows affected by the most recent SQL statement.

db.execute("DELETE FROM logs")
console.log(db.changesCount)

totalChangesCount

Returns the total number of rows affected since the database connection was opened.

console.log(db.totalChangesCount)

isInsideTransaction

Indicates whether the database is currently inside a transaction or savepoint.

if (db.isInsideTransaction) {
  console.log("Inside transaction")
}

lastErrorMessage

Returns the last SQLite error message, if any.

if (db.lastErrorMessage) {
  console.error(db.lastErrorMessage)
}

lastInsertedRowID

Returns the row ID generated by the most recent insert operation.

db.execute("INSERT INTO users (name) VALUES (?)", ["Bob"])
console.log(db.lastInsertedRowID)

Schema Version

schemaVersion

Returns the current schema version of the database.

const version = db.schemaVersion()

This is commonly used in migration logic:

if (db.schemaVersion() < 2) {
  // perform migration
}

Schema Inspection APIs

tableExists

Checks whether a table exists.

if (db.tableExists("users")) {
  // ...
}

isTableHasUniqueKeys

Checks whether a table defines a unique constraint on the specified column set.

const exists = db.isTableHasUniqueKeys(
  "users",
  ["email"]
)

columnsIn

Returns column metadata for a table.

const columns = db.columnsIn("users")

primaryKey / foreignKeys / indexes

Returns primary key, foreign key, and index metadata.

const pk = db.primaryKey("users")
const fks = db.foreignKeys("orders")
const indexes = db.indexes("users")

Statement Creation

makeStatement

Creates a new, non-cached Statement.

const stmt = db.makeStatement(
  "SELECT * FROM users WHERE id = ?"
)

cachedStatement

Creates or retrieves a cached Statement.

const stmt = db.cachedStatement(
  "SELECT * FROM users WHERE id = ?"
)

Transactions and Savepoints

inTransaction

Executes a block inside a transaction.

db.inTransaction(() => {
  db.execute("INSERT INTO users (name) VALUES (?)", ["Tom"])
  db.execute("INSERT INTO users (name) VALUES (?)", ["Jerry"])
  return "commit"
})

inSavepoint

Executes a block inside a savepoint. Savepoints may be nested.

db.inSavepoint(() => {
  db.execute("DELETE FROM cache")
  return "rollback"
})

Notes:

Return "commit" to commit
Return "rollback" to roll back
Throwing an error automatically rolls back

Executing SQL

execute

Executes an SQL statement without returning rows.

db.execute(
  "UPDATE users SET name = ? WHERE id = ?",
  ["Alice", 1]
)

Suitable for:

DDL statements
INSERT / UPDATE / DELETE
Statements where no result set is required

Table and Index Management

createTable

Creates a table using structured column definitions.

db.createTable("tasks", {
  ifNotExists: true,
  columns: [
    { name: "id", type: "integer", primaryKey: true },
    { name: "title", type: "text", notNull: true },
    { name: "done", type: "boolean", defaultValue: false }
  ]
})

renameTable / dropTable

db.renameTable("tasks", "todos")
db.dropTable("todos")

createIndex

db.createIndex("tasks_title_idx", {
  table: "tasks",
  columns: ["title"],
  ifNotExists: true
})

dropIndex / dropIndexOn

db.dropIndex("tasks_title_idx")
db.dropIndexOn("tasks", ["title"])

Query APIs

fetchAll

Returns all rows.

const users = db.fetchAll<{ id: number; name: string }>(
  "SELECT * FROM users"
)

fetchSet

Returns a set-like result with duplicate elimination.

const names = db.fetchSet<{ name: string }>(
  "SELECT name FROM users"
)

fetchOne

Returns a single row.

const user = db.fetchOne<{ id: number; name: string }>(
  "SELECT * FROM users WHERE id = ?",
  [1]
)

fetchCursor

Reads rows incrementally using a cursor.

db.fetchCursor(
  "SELECT * FROM logs",
  next => {
    let result
    while ((result = next()).row) {
      console.log(result.row)
    }
  }
)

Suitable for:

Large result sets
Streaming-style processing
Avoiding loading all rows into memory

Usage Constraints and Notes

Database must only be used within its owning read / write callback
Do not store or pass Database instances outside the callback
Do not use across threads
Transactions must complete within the same Database context

Common Mistakes

Retaining a Database reference outside the callback
Executing transaction-dependent logic outside a transaction
Using fetchCursor unnecessarily for small queries
Mixing Queue and Pool semantics incorrectly

Summary

Database is the core execution unit of the SQLite API:

Provides full SQL execution and querying capabilities
Manages statements, transactions, and schema access
Enforces clear concurrency and lifecycle boundaries
Works in tandem with Queue and Pool for safe access

#Type Definition

#Role and Responsibilities

#Obtaining a Database Instance

#State Properties

#changesCount

#totalChangesCount

#isInsideTransaction

#lastErrorMessage

#lastInsertedRowID

#Schema Version

#schemaVersion

#Schema Inspection APIs

#tableExists

#isTableHasUniqueKeys

#columnsIn

#primaryKey / foreignKeys / indexes

#Statement Creation

#makeStatement

#cachedStatement

#Transactions and Savepoints

#inTransaction

#inSavepoint

#Executing SQL

#execute

#Table and Index Management

#createTable

#renameTable / dropTable

#createIndex

#dropIndex / dropIndexOn

#Query APIs

#fetchAll

#fetchSet

#fetchOne

#fetchCursor

#Usage Constraints and Notes

#Common Mistakes

#Summary