import { Database } from "https://deno.land/x/sqlite3@0.6.1/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
Properties
Methods
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.
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.
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.
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" },
]);