Complete API documentation for @photostructure/sqlite. This package provides 100% compatibility with Node.js's built-in SQLite module.
The main database class for synchronous SQLite operations.
new DatabaseSync(location: string, options?: DatabaseSyncOptions)
Creates a new database connection.
Parameters:
location - Path to database file. Special values:
:memory: - In-memory database"" (empty string) - Temporary on-disk databasefile:data.db?mode=ro)options - Optional configuration objectOptions:
interface DatabaseSyncOptions {
readOnly?: boolean; // Open in read-only mode (default: false)
enableForeignKeyConstraints?: boolean; // Enable foreign keys (default: true)
enableDoubleQuotedStringLiterals?: boolean; // Allow double-quoted strings (default: false)
timeout?: number; // Busy timeout in ms (default: 5000)
allowExtension?: boolean; // Allow loading extensions (default: false)
}
Examples:
// Basic usage
const db = new DatabaseSync("myapp.db");
// In-memory database
const memDb = new DatabaseSync(":memory:");
// Read-only with options
const readOnlyDb = new DatabaseSync("data.db", {
readOnly: true,
timeout: 10000,
});
// URI format
const uriDb = new DatabaseSync("file:data.db?mode=ro&cache=private");
close(): void
Closes the database connection. All prepared statements are finalized automatically.
db.close();
exec(sql: string): void
Executes one or more SQL statements. Does not return any results.
db.exec(`
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE INDEX idx_name ON users(name);
`);
prepare(sql: string, options?: StatementOptions): StatementSync
Prepares a SQL statement for execution.
Options:
interface StatementOptions {
expandedSQL?: boolean; // Include expanded SQL (default: false)
anonymousParameters?: boolean; // Enable anonymous parameters (default: false)
}
const stmt = db.prepare("SELECT * FROM users WHERE id = ?");
const stmtWithExpanded = db.prepare("SELECT * FROM users WHERE id = ?", {
expandedSQL: true,
});
function(name: string, options: FunctionOptions | Function, func?: Function): void
Registers a custom scalar SQL function.
Options:
interface FunctionOptions {
deterministic?: boolean; // Same input always gives same output
directOnly?: boolean; // Cannot be used in triggers/views
varargs?: boolean; // Accept variable number of arguments
}
// Simple function
db.function("double", (x) => x * 2);
// With options
db.function(
"hash",
{
deterministic: true,
directOnly: true,
},
(value) => {
return crypto.createHash("sha256").update(String(value)).digest("hex");
},
);
// Variable arguments
db.function("concat", { varargs: true }, (...args) => args.join(""));
aggregate(name: string, options: AggregateOptions): void
Registers a custom aggregate SQL function.
Options:
interface AggregateOptions {
start: any | (() => any); // Initial value or factory
step: (accumulator: any, ...values: any[]) => any; // Step function
result?: (accumulator: any) => any; // Final result transformer
deterministic?: boolean;
directOnly?: boolean;
varargs?: boolean;
}
// Sum aggregate
db.aggregate("custom_sum", {
start: 0,
step: (sum, value) => sum + value,
});
// Average aggregate
db.aggregate("custom_avg", {
start: { sum: 0, count: 0 },
step: (acc, value) => {
acc.sum += value;
acc.count += 1;
return acc;
},
result: (acc) => acc.sum / acc.count,
});
backup(destination: string, options?: BackupOptions): Promise<void>
Creates a backup of the database.
Options:
interface BackupOptions {
source?: string; // Source database name (default: 'main')
rate?: number; // Pages per iteration (default: 100)
progress?: (info: { totalPages: number; remainingPages: number }) => void;
}
// Simple backup
await db.backup("backup.db");
// With progress monitoring
await db.backup("backup.db", {
rate: 10,
progress: ({ totalPages, remainingPages }) => {
const percent = (
((totalPages - remainingPages) / totalPages) *
100
).toFixed(1);
console.log(`Backup progress: ${percent}%`);
},
});
createSession(options?: SessionOptions): Session
Creates a session for tracking changes.
Options:
interface SessionOptions {
table?: string; // Specific table to track
db?: string; // Database name (default: 'main')
}
const session = db.createSession({ table: "users" });
// Make changes...
const changeset = session.changeset();
session.close();
applyChangeset(changeset: Uint8Array, options?: ChangesetOptions): ApplyResult
Applies a changeset to the database.
const result = db.applyChangeset(changeset, {
onConflict: (conflict) => {
console.log(`Conflict on table ${conflict.table}`);
return constants.SQLITE_CHANGESET_REPLACE;
},
});
enableLoadExtension(enable: boolean): void
Enables or disables extension loading. Requires allowExtension: true in constructor.
db.enableLoadExtension(true);
db.loadExtension("./my-extension.so");
db.enableLoadExtension(false);
loadExtension(path: string, entryPoint?: string): void
Loads a SQLite extension.
db.loadExtension("./extensions/vector.so");
db.loadExtension("./custom.so", "sqlite3_custom_init");
readonly location: string
The path or URI of the database file.
console.log(db.location); // "myapp.db"
Represents a prepared SQL statement.
run(...params: any[]): RunResult
Executes the statement and returns information about changes.
Returns:
interface RunResult {
changes: number; // Number of rows affected
lastInsertRowid: number | bigint; // Last inserted row ID
}
const result = stmt.run("Alice", 30);
console.log(`Inserted row ${result.lastInsertRowid}`);
get(...params: any[]): any
Executes the statement and returns the first row.
const user = stmt.get(1);
console.log(user); // { id: 1, name: 'Alice', age: 30 }
all(...params: any[]): any[]
Executes the statement and returns all rows.
const users = stmt.all();
console.log(users); // Array of all user objects
Allows direct iteration over statement results.
for (const row of stmt) {
console.log(row);
}
finalize(): void
Finalizes the statement and frees resources.
stmt.finalize();
readonly sourceSQL: string
The original SQL text of the statement.
console.log(stmt.sourceSQL); // "SELECT * FROM users WHERE id = ?"
readonly expandedSQL: string
The SQL with bound parameters expanded (only if expandedSQL: true option was used).
const stmt = db.prepare("SELECT * FROM users WHERE id = ?", {
expandedSQL: true,
});
stmt.get(42);
console.log(stmt.expandedSQL); // "SELECT * FROM users WHERE id = 42"
interface DatabaseSyncOptions {
readOnly?: boolean;
enableForeignKeyConstraints?: boolean;
enableDoubleQuotedStringLiterals?: boolean;
timeout?: number;
allowExtension?: boolean;
}
interface StatementOptions {
expandedSQL?: boolean;
anonymousParameters?: boolean;
}
interface FunctionOptions {
deterministic?: boolean;
directOnly?: boolean;
varargs?: boolean;
}
interface AggregateOptions {
start: any | (() => any);
step: (accumulator: any, ...values: any[]) => any;
result?: (accumulator: any) => any;
deterministic?: boolean;
directOnly?: boolean;
varargs?: boolean;
}
interface BackupOptions {
source?: string;
rate?: number;
progress?: (info: { totalPages: number; remainingPages: number }) => void;
}
The package exports SQLite constants for use with sessions and changesets:
import { constants } from "@photostructure/sqlite";
// Conflict resolution constants
constants.SQLITE_CHANGESET_OMIT;
constants.SQLITE_CHANGESET_REPLACE;
constants.SQLITE_CHANGESET_ABORT;
// And many more SQLite constants...
All errors thrown include enhanced information:
interface SQLiteError extends Error {
code: string; // e.g., "SQLITE_CANTOPEN"
sqliteCode: number; // e.g., 14
sqliteExtendedCode: number;
sqliteErrorString: string; // Human-readable description
systemErrno?: number; // OS error code (when applicable)
}
try {
db.exec("INVALID SQL");
} catch (error) {
console.log(error.message); // Full error message
console.log(error.code); // "SQLITE_ERROR"
console.log(error.sqliteCode); // 1
console.log(error.sqliteErrorString); // "SQL logic error"
}