Constructor
#
new Database(data)
Parameters:
Name |
Type |
Description |
data |
Array.<number>
|
An array of bytes representing
an SQLite database file |
Methods
#
["close"]()
Close the database, and all associated prepared statements.
The memory associated to the database and all associated statements
will be freed.
Warning: A statement belonging to a database that has been closed
cannot be used anymore.
Databases must be closed when you're finished with them, or the
memory consumption will grow forever
#
["create_aggregate"](name, aggregateFunctions) → {Database}
Register a custom aggregate with SQLite
Parameters:
Name |
Type |
Description |
name |
string
|
the name of the aggregate as referenced in
SQL statements. |
aggregateFunctions |
object
|
object containing at least a step function.
Properties
Name |
Type |
Attributes |
Default |
Description |
init |
function
|
<optional>
|
()=>null
|
a function receiving no arguments and returning an initial
value for the aggregate function. The initial value will be
null if this key is omitted. |
step |
function
|
|
|
a function receiving the current state and a value to aggregate
and returning a new state.
Will receive the value from init for the first step. |
finalize |
function
|
<optional>
|
(state)=>state
|
a function returning the result of the aggregate function
given its final state.
If omitted, the value returned by the last step
will be used as the final value. |
|
Returns:
The database object. Useful for method chaining
-
Type
-
Database
Example
Register a custom sum function
db.create_aggregate("js_sum", {
init: () => 0,
step: (state, value) => state + value,
finalize: state => state
});
db.exec("SELECT js_sum(column1) FROM (VALUES (1), (2))"); // = 3
#
["create_function"](name, func) → {Database}
Register a custom function with SQLite
Parameters:
Name |
Type |
Description |
name |
string
|
the name of the function as referenced in
SQL statements. |
func |
function
|
the actual function to be executed. |
Returns:
The database object. Useful for method chaining
-
Type
-
Database
Example
Register a simple function
db.create_function("addOne", function (x) {return x+1;})
db.exec("SELECT addOne(1)") // = 2
#
["each"](sql, paramsopt, callback, done) → {Database}
Execute an sql statement, and call a callback for each row of result.
Currently this method is synchronous, it will not return until the callback
has been called on every row of the result. But this might change.
Parameters:
Name |
Type |
Attributes |
Default |
Description |
sql |
string
|
|
|
A string of SQL text. Can contain placeholders
that will be bound to the parameters given as the second argument |
params |
Statement.BindParams
|
<optional>
|
[]
|
Parameters to bind to the query |
callback |
function
|
|
|
Function to call on each row of result |
done |
function
|
|
|
A function that will be called when
all rows have been retrieved |
Returns:
The database object. Useful for method chaining
-
Type
-
Database
Example
Read values from a table
db.each("SELECT name,age FROM users WHERE age >= $majority", {$majority:18},
function (row){console.log(row.name + " is a grown-up.")}
);
Execute an SQL query, and returns the result.
This is a wrapper against
Database.prepare,
Statement.bind,
Statement.step,
Statement.get,
and Statement.free.
The result is an array of result elements. There are as many result
elements as the number of statements in your sql string (statements are
separated by a semicolon)
Example use
We will create the following table, named test and query it with a
multi-line statement using params:
id |
age |
name |
1 |
1 |
Ling |
2 |
18 |
Paul |
We query it like that:
var db = new SQL.Database();
var res = db.exec(
"DROP TABLE IF EXISTS test;\n"
+ "CREATE TABLE test (id INTEGER, age INTEGER, name TEXT);"
+ "INSERT INTO test VALUES ($id1, :age1, @name1);"
+ "INSERT INTO test VALUES ($id2, :age2, @name2);"
+ "SELECT id FROM test;"
+ "SELECT age,name FROM test WHERE id=$id1",
{
"$id1": 1, ":age1": 1, "@name1": "Ling",
"$id2": 2, ":age2": 18, "@name2": "Paul"
}
);
res
is now :
[
{"columns":["id"],"values":[[1],[2]]},
{"columns":["age","name"],"values":[[1,"Ling"]]}
]
Parameters:
Name |
Type |
Attributes |
Description |
sql |
string
|
|
a string containing some SQL text to execute |
params |
Statement.BindParams
|
<optional>
|
When the SQL statement contains
placeholders, you can pass them in here. They will be bound to the statement
before it is executed. If you use the params argument as an array,
you cannot provide an sql string that contains several statements
(separated by ; ). This limitation does not apply to params as an object. |
#
["export"]() → {Uint8Array}
Exports the contents of the database to a binary array
Returns:
An array of bytes of the SQLite3 database file
-
Type
-
Uint8Array
#
["getRowsModified"]() → {number}
Returns the number of changed rows (modified, inserted or deleted)
by the latest completed INSERT, UPDATE or DELETE statement on the
database. Executing any other type of SQL statement does not modify
the value returned by this function.
Returns:
the number of rows modified
-
Type
-
number
#
["handleError"]()
Analyze a result code, return null if no error occured, and throw
an error with a descriptive message otherwise
Iterate over multiple SQL statements in a SQL string.
This function returns an iterator over Statement objects.
You can use a for..of loop to execute the returned statements one by one.
Parameters:
Name |
Type |
Description |
sql |
string
|
a string of SQL that can contain multiple statements |
Example
Get the results of multiple SQL queries
const sql_queries = "SELECT 1 AS x; SELECT '2' as y";
for (const statement of db.iterateStatements(sql_queries)) {
const sql = statement.getSQL(); // Get the SQL source
const result = statement.getAsObject({}); // Get the row of data
console.log(sql, result);
}
// This will print:
// 'SELECT 1 AS x;' { x: 1 }
// " SELECT '2' as y" { y: '2' }
#
["prepare"](sql, paramsopt) → {Statement}
Parameters:
Name |
Type |
Attributes |
Description |
sql |
string
|
|
a string of SQL, that can contain placeholders
(? , :VVV , :AAA , @AAA ) |
params |
Statement.BindParams
|
<optional>
|
values to bind to placeholders |
#
["run"](sql, paramsopt) → {Database}
Execute an SQL query, ignoring the rows it returns.
Parameters:
Name |
Type |
Attributes |
Description |
sql |
string
|
|
a string containing some SQL text to execute |
params |
Statement.BindParams
|
<optional>
|
When the SQL statement contains
placeholders, you can pass them in here. They will be bound to the statement
before it is executed. If you use the params argument, you cannot
provide an sql string that contains several statements (separated by ; ) |
Returns:
The database object (useful for method chaining)
-
Type
-
Database
Example
// Insert values in a table
db.run(
"INSERT INTO test VALUES (:age, :name)",
{ ':age' : 18, ':name' : 'John' }
);
Type Definitions
#
QueryExecResult
Properties
Name |
Type |
Description |
columns |
Array.<string>
|
the name of the columns of the result
(as returned by Statement.getColumnNames) |
values |
Array.<Array.<Database.SqlValue>>
|
one array per row, containing
the column values |
#
SqlValue
Type:
-
string
|
number
|
null
|
Uint8Array