Database | /mod.ts | sqlite3@0.4.4

class Database

Re-export

import { Database } from "https://deno.land/x/sqlite3@0.4.4/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, cab be disabled using BEGIN statement.

readonly

changes: number

Number of rows changed by the last executed statement.

readonly

lastInsertRowId: number

Gets last inserted Row ID

readonly

path: string

Path of the database file.

readonly

totalChanges: number

Number of rows changed since the database connection was opened.

readonly

unsafeRawHandle: 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.

execute(strings: TemplateStringsArray, ...args: BindValue[]): void

Simply executes the SQL, without returning anything.

Example:

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

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

// Or run SQL safely using Template Strings!
db.execute`insert into users (id, username) values(${id}, ${username})`;

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

// Pragma statements
db.execute("pragma journal_mode = WAL");
db.execute("pragma synchronous = normal");
db.execute("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.

execute(sql: string, ...args: BindValue[]): void

execute(sql: string, args: Record<string, BindValue>): void

openBlob(options: BlobOpenOptions): SQLBlob

Open a Blob for incremental I/O.

Make sure to close the blob after you are done with it, otherwise you will have memory leaks.

prepare(sql: string): PreparedStatement

Creates a new prepared statement.

Example:

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

for (const user of usersToInsert) {
  stmt.execute(id, user);
}

stmt.finalize();

queryArray<T extends unknown[] = any[]>(strings: TemplateStringsArray, ...args: BindValue[]): T[]

Runs an SQL query with given parameters, and returns rows as array of columns. If you need the rows as objects, use queryObject instead. However, it is recommended to use queryArray because of the extra overhead added by FFI calls to get column names to create row objects.

Example:

const users = db.queryArray<[number, string]>("select id, username from users");

// Using bind parameters
const [user] = db.queryArray<[number, string]>("select id, username from users where email = ?", email);

// Using template strings
const [user] = db.queryArray<[number, string]>`select id, username from users where email = ${email}`;

// Using named bind parameters
const [user] = db.queryArray<[number, string]>("select id, username from users where email = :email", { email });

queryArray<T extends unknown[] = any[]>(sql: string, ...args: BindValue[]): T[]

queryArray<T extends unknown[] = any[]>(sql: string, args: Record<string, BindValue>): T[]

queryObject<T extends Record<string, unknown> = Record<string, any>>(strings: TemplateStringsArray, ...args: BindValue[]): T[]

Executes an SQL query and returns the rows as objects.

Note: if you do not need the column names, consider calling queryArray instead. As this method does an extra FFI call to get the column names, it is more expensive than queryArray.

Example:

const users = db.queryObject<{
  id: number,
  username: string,
}>("select id, username from users");

// Using bind parameters
const [user] = db.queryObject<{
  id: number,
  username: string,
}>("select id, username from users where email = ?", email);

// Using template strings
const [user] = db.queryObject<{
 id: number,
 username: string,
}>`select id, username from users where email = ${email}`;

// Using named bind parameters
const [user] = db.queryObject<{
  id: number,
  username: string,
}>("select id, username from users where email = :email", { email });

queryObject<T extends Record<string, unknown> = Record<string, any>>(sql: string, ...args: BindValue[]): T[]

queryObject<T extends Record<string, unknown> = Record<string, any>>(sql: string, args: Record<string, BindValue>): T[]

[Symbol.for("Deno.customInspect")](): string