Database | /vendor/sqlite3@0.10.0/mod.ts | libpkgx@v0.20.0

class Database

Re-export

import { Database } from "https://deno.land/x/libpkgx@v0.20.0/vendor/sqlite3@0.10.0/mod.ts";

Represents a SQLite3 database connection.

Example:

// Open a database from file, creates if doesn't exist.
const db = new Database("myfile.db");

// Open an in-memory database.
const db = new Database(":memory:");

// Open a read-only database.
const db = new Database("myfile.db", { readonly: true });

// Or open using File URL
const db = new Database(new URL("./myfile.db", import.meta.url));

Constructors

new

Database(path: string | URL, options?: DatabaseOpenOptions)

Properties

readonly

autocommit: boolean

Whether autocommit is enabled. Enabled by default, can be disabled using BEGIN statement.

readonly

changes: number

Number of rows changed by the last executed statement.

enableLoadExtension: boolean

int64: boolean

Whether to support BigInt columns. False by default, integers larger than 32 bit will be inaccurate.

readonly

inTransaction: boolean

Whether DB is in mid of a transaction

readonly

lastInsertRowId: number

Gets last inserted Row ID

readonly

open: boolean

Whether DB connection is open

readonly

path: string

Path of the database file.

readonly

totalChanges: number

Number of rows changed since the database connection was opened.

unsafeConcurrency: boolean

readonly

unsafeHandle: Deno.PointerValue

Unsafe Raw (pointer) to the sqlite object

Methods

close(): void

Closes the database connection.

Calling this method more than once is no-op.

exec(sql: string, ...params: RestBindParameters): number

Simply executes the SQL statement (supports multiple statements separated by semicolon). Returns the number of changes made by last statement.

Example:

// Create table
db.exec("create table users (id integer not null, username varchar(20) not null)");

// Inserts
db.exec("insert into users (id, username) values(?, ?)", id, username);

// Insert with named parameters
db.exec("insert into users (id, username) values(:id, :username)", { id, username });

// Pragma statements
db.exec("pragma journal_mode = WAL");
db.exec("pragma synchronous = normal");
db.exec("pragma temp_store = memory");

Under the hood, it uses sqlite3_exec if no parameters are given to bind with the SQL statement, a prepared statement otherwise.

function(

name: string,

fn: CallableFunction,

options?: FunctionOptions,

): void

Creates a new user-defined function.

Example:

db.function("add", (a: number, b: number) => a + b);
db.prepare("select add(1, 2)").value<[number]>()!; // [3]

prepare(sql: string): Statement

Creates a new Prepared Statement from the given SQL statement.

Example:

const stmt = db.prepare("SELECT * FROM mytable WHERE id = ?");

for (const row of stmt.all(1)) {
  console.log(row);
}

Bind parameters can be either provided as an array of values, or as an object mapping the parameter name to the value.

Example:

const stmt = db.prepare("SELECT * FROM mytable WHERE id = ?");
const row = stmt.get(1);

// or

const stmt = db.prepare("SELECT * FROM mytable WHERE id = :id");
const row = stmt.get({ id: 1 });

Statements are automatically freed once GC catches them, however you can also manually free using finalize method.

run(sql: string, ...params: RestBindParameters): number

Alias for exec.

sql<T extends Record<string, unknown> = Record<string, any>>(strings: TemplateStringsArray, ...parameters: RestBindParameters): T[]

Safely execute SQL with parameters using a tagged template

transaction<T extends (this: Transaction<T>, ...args: any[]) => void>(fn: T): Transaction<T>

Wraps a callback function in a transaction.

When function is called, the transaction is started.
When function returns, the transaction is committed.
When function throws an error, the transaction is rolled back.

Example:

const stmt = db.prepare("insert into users (id, username) values(?, ?)");

interface User {
  id: number;
  username: string;
}

const insertUsers = db.transaction((data: User[]) => {
  for (const user of data) {
    stmt.run(user);
  }
});

insertUsers([
  { id: 1, username: "alice" },
  { id: 2, username: "bob" },
]);

// May also use `insertUsers.deferred`, `immediate`, or `exclusive`.
// They corresspond to using `BEGIN DEFERRED`, `BEGIN IMMEDIATE`, and `BEGIN EXCLUSIVE`.
// For eg.

insertUsers.deferred([
  { id: 1, username: "alice" },
  { id: 2, username: "bob" },
]);