import { DB } from "https://deno.land/x/sqlite@v3.5.0/src/db.ts";
A database handle that can be used to run queries.
Constructors
Create a new database. The file at the
given path will be opened with the
mode specified in options. The default
mode is create
.
If no path is given, or if the memory
option is set, the database is opened in
memory.
Examples
Create an in-memory database.
const db = new DB();
Open a database backed by a file on disk.
const db = new DB("path/to/database.sqlite");
Pass options to open a read-only database.
const db = new DB("path/to/database.sqlite", { mode: "read" });
Properties
Return the number of rows modified, inserted or
deleted by the most recently completed query.
This corresponds to the SQLite function
sqlite3_changes
.
Get last inserted row id. This corresponds to
the SQLite function sqlite3_last_insert_rowid
.
Before a row is inserted for the first time (since
the database was opened), this returns 0
.
Methods
Close the database. This must be called if the database is no longer used to avoid leaking open file descriptors.
If force = true
is passed, any non-finalized
PreparedQuery
objects will be finalized. Otherwise,
this throws if there are active queries.
close
may safely be called multiple
times.
Run multiple semicolon-separated statements from a single string.
This method cannot bind any query parameters, and any result rows are discarded. It is only for running a chunk of raw SQL; for example, to initialize a database.
Examples
db.execute(`
CREATE TABLE people (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT,
age REAL,
city TEXT
);
INSERT INTO people (name, age, city) VALUES ("Peter Parker", 21, "nyc");
`);
Prepares the given SQL query, so that it can be run multiple times and potentially with different parameters.
If a query will be issued a lot, this is more
efficient than using query
. A prepared
query also provides more control over how
the query is run, as well as access to meta-data
about the issued query.
The returned PreparedQuery
object must be
finalized by calling its finalize
method
once it is no longer needed.
Typing Queries
Prepared query objects accept three type parameters to specify precise types for returned data and query parameters.
-
The first type parameter
R
indicates the tuple type for rows returned by the query. -
The second type parameter
O
indicates the record type for rows returned as entries (mappings from column names to values). -
The third type parameter
P
indicates the type this query accepts as parameters.
Note, that the correctness of those types must be guaranteed by the caller of this function.
Examples
const query = db.prepareQuery<
[string, number],
{ name: string, age: number },
{ city: string },
>("SELECT name, age FROM people WHERE city = :city");
// use query ...
query.finalize();
Query the database and return all matching rows.
This is equivalent to calling all
on
a prepared query which is then immediately
finalized.
The type parameter R
may be supplied by
the user to indicated the type for the rows returned
by the query. Notice that the user is responsible
for ensuring the correctness of the supplied type.
To avoid SQL injection, user-provided values should always be passed to the database through a query parameter.
See QueryParameterSet
for documentation on
how values can be bound to SQL statements.
See QueryParameter
for documentation on how
values are returned from the database.
Examples
const rows = db.query<[string, number]>("SELECT name, age FROM people WHERE city = ?", [city]);
// rows = [["Peter Parker", 21], ...]
const rows = db.query<[string, number]>(
"SELECT name, age FROM people WHERE city = :city",
{ city },
);
// rows = [["Peter Parker", 21], ...]
Like query
except each row is returned
as an object containing key-value pairs.
Examples
const rows = db.queryEntries<{ name: string, age: number }>("SELECT name, age FROM people");
// rows = [{ name: "Peter Parker", age: 21 }, ...]
const rows = db.queryEntries<{ name: string, age: number }>(
"SELECT name, age FROM people WHERE age >= :minAge",
{ minAge },
);
// rows = [{ name: "Peter Parker", age: 21 }, ...]
Run a function within the context of a database transaction. If the function throws an error, the transaction is rolled back. Otherwise, the transaction is committed when the function returns.
Calls to transaction
may be nested. Nested transactions
behave like SQLite save points.