import { DB } from "https://deno.land/x/stowrage@v0.4.2/deps.ts";
Properties
DB.changes
Return the number of rows modified, inserted or
deleted by the most recently completed query.
This corresponds to the SQLite function
sqlite3_changes
.
Methods
DB.close
Close database handle. This must be called if DB is no longer used, to avoid leaking file resources.
If force is specified, any on-going transactions will be closed.
DB.prepareQuery
This is similar to query()
, with the difference
that the returned function can be called multiple
times (with different values to bind each time).
Using a prepared query instead of query()
will
improve performance if the query is issued a lot,
e.g. when writing a web server, the queries used
by the server could be prepared once and then used
through it's runtime.
A prepared query must be finalized when it is no
longer in used by calling query.finalize()
. So
the complete lifetime of a query would look like
this:
// once
const query = db.prepareQuery("INSERT INTO messages (message, author) VALUES (?, ?)");
// many times
query([messageValueOne, authorValueOne]);
query([messageValueTwo, authorValueTwo]);
// ...
// once
query.finalize();
DB.query
Run a query against the database. The query can contain placeholder parameters, which are bound to the values passed in 'values'.
db.query("SELECT name, email FROM users WHERE subscribed = ? AND list LIKE ?", [true, listName]);
This supports positional and named parameters. Positional parameters can be set by passing an array for values. Named parameters can be set by passing an object for values.
While they can be mixed in principle, this is not recommended.
Parameter | Values |
---|---|
?NNN or ? |
NNN-th value in array |
:AAAA |
value AAAA or :AAAA |
@AAAA |
value @AAAA |
$AAAA |
value $AAAA |
(see https://www.sqlite.org/lang_expr.html)
Values may only be of the following types and are converted as follows:
JS in | SQL type | JS out |
---|---|---|
number | INTEGER or REAL | number or bigint |
bigint | INTEGER | number or bigint |
boolean | INTEGER | number |
string | TEXT | string |
Date | TEXT | string |
Uint8Array | BLOB | Uint8Array |
null | NULL | null |
undefined | NULL | null |
If no value is provided to a given parameter, SQLite will default to NULL.
If a bigint
is bound, it is converted to a
signed 64 big integer, which may not be lossless.
If an integer value is read from the database, which
is too big to safely be contained in a number
, it
is automatically returned as a bigint
.
If a Date
is bound, it will be converted to
an ISO 8601 string: YYYY-MM-DDTHH:MM:SS.SSSZ
.
This format is understood by built-in SQLite
date-time functions. Also see
https://sqlite.org/lang_datefunc.html.
This always returns an iterable Rows object. As a special case, if the query has no rows to return this returns the Empty row (which is also iterable, but has zero entries).
!> Any returned Rows object needs to be fully
iterated over or discarded by calling
.return()
or closing the iterator.