SQLite

new SQLite()

new SQLite<R = Record<string, any>>(path?: string, options?: SQLiteOptions): SQLite<R>

Opens a database, creating the file and its folders if needed. Use ":memory:" for a throwaway in-memory database.

Throws SQLiteError If the file can't be opened.

Example

const db = new nc.SQLite();                                  // ./db.sqlite
const app = new nc.SQLite("data/app.sqlite", { dates: true });
const mem = new nc.SQLite(":memory:");

db.exec()method

exec(sql: string, params?: string | number | bigint | boolean | unknown[] | Record<string, unknown> | Date | null): { changes: number; lastInsertRowid: number | bigint; }

Runs SQL that doesn't return rows. Without parameters, you can pass several statements at once.

Returns { changes: number, lastInsertRowid: number | bigint }

Throws SQLiteError

Example

db.exec("UPDATE users SET active = ? WHERE last_login < ?", [false, cutoff]);
db.exec("DELETE FROM sessions WHERE user_id = $id", { $id: 42 });
db.exec(`
  CREATE TABLE tags (id INTEGER PRIMARY KEY, label TEXT UNIQUE);
  CREATE INDEX idx_tags_label ON tags (label);
`);

db.queryOne()method

queryOne<T = R>(sql: string, params?: string | number | bigint | boolean | unknown[] | Record<string, unknown> | Date | null): T | undefined

Runs a query and returns the first row. When it reads a single table, JSON and boolean columns are converted.

Returns T | undefined

Throws SQLiteError

Example

const user = db.queryOne("SELECT * FROM users WHERE email = ?", [email]);

db.queryAll()method

queryAll<T = R>(sql: string, params?: string | number | bigint | boolean | unknown[] | Record<string, unknown> | Date | null): T[]

Runs a query and returns every row.

Returns T[]

Throws SQLiteError

Example

const stats = db.queryAll("SELECT role, COUNT(*) AS n FROM users GROUP BY role");

db.iterate()method

iterate<T = R>(sql: string, params?: string | number | bigint | boolean | unknown[] | Record<string, unknown> | Date | null): Generator<T, void, undefined>

Goes through the rows of a query one at a time, without loading them all. For big exports.

Returns Generator<T, void, undefined>

Throws SQLiteError

Example

for (const row of db.iterate("SELECT * FROM events WHERE day = ?", [day])) {
  stream.write(JSON.stringify(row) + "\n");
}

db.createTable()method

createTable(table: TableDefinition): { ok: true; }

Creates a table with its constraints and indexes, unless it already exists. To change an existing table, use migrate().

Returns { ok: true }

Throws SQLiteError If the definition is invalid.

Example

db.createTable({
  name: "posts",
  columns: {
    id: { type: "INTEGER", primaryKey: true, autoincrement: true },
    user_id: { type: "INTEGER", notNull: true, references: { table: "users", column: "id" }, onDelete: "CASCADE" },
    status: { type: "TEXT", values: ["draft", "published"], defaultValue: "draft" },
    tags: { type: "JSON", defaultValue: [] },
  },
  indexes: [{ columns: ["user_id", "status"] }],
});

db.createIndex()method

createIndex(tableName: string, index: IndexDefinition): { ok: true; }

Creates an index, unless it already exists.

Returns { ok: true }

Throws SQLiteError

Example

db.createIndex("users", { columns: ["email"], unique: true });
db.createIndex("posts", { columns: ["slug"], unique: true, where: "deleted_at IS NULL" });

db.deleteTable()method

deleteTable(tableName: string): { ok: true; }

Drops a table and everything in it.

Returns { ok: true }

Throws SQLiteError If the table doesn't exist.

db.clearTable()method

clearTable(tableName: string): { ok: true; }

Deletes every row, keeping the table.

Returns { ok: true }

Throws SQLiteError If the table doesn't exist.

db.hasTable()method

hasTable(tableName: string): boolean

Does the table exist?

Returns boolean

db.tables()method

tables(): string[]

The tables in the database, sorted.

Returns string[]

db.columns()method

columns(tableName: string): ColumnInfo[]

The columns of a table.

Returns ColumnInfo[]

Throws SQLiteError If the table doesn't exist.

Example

db.columns("users");
// [{ name: "id", type: "INTEGER", notNull: false, defaultValue: null, primaryKey: true }, ...]

db.get()method

get<T = R>(tableName: string, where?: Where<R>, options?: Pick<QueryOptions, "columns" | "orderBy" | "direction">): T | undefined

The first row that matches.

Returns T | undefined

Throws SQLiteError

Example

const user = db.get("users", { email: "ada@example.com" });
const latest = db.get("posts", { status: "published" }, { orderBy: "created_at", direction: "DESC" });

db.getAll()method

getAll<T = R>(tableName: string, where?: Where<R>, options?: QueryOptions): T[]

The rows that match, with sorting and pagination.

Returns T[]

Throws SQLiteError

Example

db.getAll("users");
db.getAll("users", { role: ["admin", "editor"], age: { gte: 18 } });
db.getAll("posts", { $or: [{ pinned: true }, { views: { gt: 1000 } }] }, {
  orderBy: [["views", "DESC"], "title"],
  limit: 20,
  offset: 40,
});

db.count()method

count(tableName: string, where?: Where<R>): number

Counts the rows that match.

Returns number

Throws SQLiteError

Example

db.count("users", { active: true });

db.exists()method

exists(tableName: string, where?: Where<R>): boolean

Does any row match?

Returns boolean

Throws SQLiteError

Example

if (db.exists("users", { email })) throw new Error("Email already registered");

db.insert()method

insert(tableName: string, data: Partial<R> & Record<string, unknown>): InsertResult

Inserts a row. Objects and arrays are stored as JSON, booleans as 1 and 0, dates as ISO strings. undefined values are skipped, so column defaults apply.

Returns InsertResult

Throws SQLiteError On a constraint failure; sqliteCode tells you which, like "SQLITE_CONSTRAINT_UNIQUE".

Example

const { lastInsertRowid } = db.insert("users", { email: "ada@example.com", settings: { theme: "dark" } });

db.insertMany()method

insertMany(tableName: string, rows: (Partial<R> & Record<string, unknown>)[]): ChangeResult

Inserts many rows in one transaction: all or nothing, and much faster than one by one.

Returns ChangeResult

Throws SQLiteError If a row fails; nothing is inserted then.

Example

db.insertMany("products", rowsFromCsv);

db.update()method

update(tableName: string, data: Partial<R> & Record<string, unknown>, where: Where<R>): ChangeResult

Updates the rows that match. The filter is required, so a forgotten condition can't rewrite the whole table.

Returns ChangeResult

Throws SQLiteError

Example

db.update("users", { active: false }, { last_login: { lt: "2025-01-01" } });

db.upsert()method

upsert(tableName: string, data: Partial<R> & Record<string, unknown>, conflict: string[]): InsertResult

Inserts a row, or updates it in place if it clashes with an existing one on the conflict columns.

Returns InsertResult

Throws SQLiteError If conflict doesn't match a unique constraint.

Example

db.upsert("settings", { user_id: 1, key: "theme", value: "dark" }, ["user_id", "key"]);

db.set()method

set(tableName: string, data: Partial<R> & Record<string, unknown>, where?: Where<R> | null): { ok: true; }

Updates the row matching where (or { id: data.id }), or inserts it. Kept from 1.x; upsert() does the same in one atomic statement.

Returns { ok: true }

Throws SQLiteError

db.delete()method

delete(tableName: string, where: Where<R>): ChangeResult

Deletes the rows that match. The filter is required; clearTable() deletes everything on purpose.

Returns ChangeResult

Throws SQLiteError

Example

db.delete("sessions", { expires_at: { lt: new Date() } });

db.table()method

table<T = R>(tableName: string): TableHandle<T>

Shortcuts bound to one table, so you don't repeat its name.

Returns TableHandle<T>

Example

const users = db.table("users"); // in TypeScript: db.table<User>("users")
users.insert({ email: "ada@example.com" });
users.count({ active: true });

db.transaction()method

transaction<T>(callback: () => T): T

Runs callback in a transaction: all its changes are saved together, or none are if it throws. The error is then thrown again. Transactions can nest; an inner failure can be caught without losing the outer work.

Returns T

Throws Whatever callback threw, after rolling back.

Example

db.transaction(() => {
  db.update("accounts", { balance: from.balance - amount }, { id: from.id });
  db.update("accounts", { balance: to.balance + amount }, { id: to.id });
});

db.inTransactionproperty

inTransaction: boolean

Whether a transaction is open.

db.migrate()method

migrate(migrations: Migration[]): { from: number; to: number; }

Applies migrations in order, each one once. The current version is kept in the database, and each migration runs in its own transaction. Only ever add migrations at the end of the list.

Returns { from: number, to: number }: The version before and after.

Throws SQLiteError If a migration fails. It's rolled back; earlier ones stay.

Example

db.migrate([
  "CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE)",
  "ALTER TABLE users ADD COLUMN name TEXT",
  (db) => db.update("users", { name: "unknown" }, { name: null }),
]);

db.versionproperty

version: number

The migration version, stored in PRAGMA user_version.

db.backup()method

backup(path: string): string

Copies the database to a new file. Safe while the app is running.

Returns string: The absolute path of the copy.

Throws SQLiteError If the file exists or can't be written.

Example

db.backup(`backups/app-${nc.time.format(new Date(), "YYYY-MM-DD")}.sqlite`);

db.vacuum()method

vacuum(): void

Compacts the database file to reclaim unused space.

db.fn()method

fn(name: string, implementation: (...args: any[]) => string | number | bigint | Uint8Array<ArrayBufferLike> | null, options?: { deterministic?: boolean; varargs?: boolean; } | undefined): SQLite<R>

Makes a JavaScript function callable from SQL.

Returns this

Example

db.fn("slugify", (text) => nc.str.slugify(String(text)));
db.queryAll("SELECT slugify(title) AS slug FROM posts");

db.pathproperty

path: string

The absolute path of the database file, or ":memory:".

db.isOpenproperty

isOpen: boolean

Whether the connection is open.

db.nativeproperty

native: DatabaseSync

The underlying node:sqlite database, for anything not covered here.

db.close()method

close(): void

Closes the connection. Calling it twice is fine.