import { Database } from "https://deno.land/x/sqlite3@0.5.1/src/database.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
Whether autocommit is enabled. Enabled by default, cab be disabled using BEGIN statement.
Unsafe Raw (pointer) to the sqlite object
Methods
Closes the database connection.
Calling this method more than once is no-op.
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.
Alias for exec
.
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" },
]);