Database

Source: api.js:851

Represents an SQLite database

Alias: Database

Constructor

Parameters

• data (Array.<number>) — An array of bytes representing an SQLite database file

Instance Methods

run(sql: string, params?: Statement.BindParams): Database

Execute an SQL query, ignoring the rows it returns.

Parameters

• 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

• Database — The database object (useful for method chaining)

Example

// Insert values in a table
    db.run(
        "INSERT INTO test VALUES (:age, :name)",
        { ':age' : 18, ':name' : 'John' }
    );

exec( sql: string, params?: Statement.BindParams, ): Array.<Database.QueryExecResult>

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:

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

• 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.

Returns

• Array.<Database.QueryExecResult> — The results of each statement

each( sql: string, params=?: Statement.BindParams, callback: function, done: function, ): 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

• 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

• Database — The database object. Useful for method chaining

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.")}
    );

prepare(sql: string, params?: Statement.BindParams): Statement

Prepare an SQL statement

Parameters

• sql (string) — a string of SQL, that can contain placeholders (?, :VVV, :AAA, @AAA)
• params (Statement.BindParams, optional) — values to bind to placeholders

Returns

• Statement — the resulting statement

Throws

• String — SQLite error

iterateStatements(sql: string): StatementIterator

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

• sql (string) — a string of SQL that can contain multiple statements

Returns

• StatementIterator — the resulting statement iterator

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' }

export(): Uint8Array

Exports a snapshot of the main database to a binary array without closing the connection. Prepared statements, registered functions, update hooks and connection settings (including pragmas) are preserved. TEMP objects and attached databases remain available on this connection, but are not included in the exported file.

Exporting during an active write transaction throws an error. Commit or roll back the transaction before exporting. Read transactions are allowed. Exporting never commits or rolls back a transaction or changes savepoints, even when the export fails.

The exported file uses rollback-journal format, even if this connection uses WAL mode.

The returned array is an independent copy. It can be used to create a new Database, but JavaScript functions, hooks and connection settings are not stored in the file and must be configured on the new connection.

Returns

• Uint8Array — An array of bytes of the SQLite3 database file

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

handleError()

Analyze a result code, return null if no error occured, and throw an error with a descriptive message otherwise

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

• number — the number of rows modified

create_function(name: string, func: function): Database

Register a custom function with SQLite

Parameters

• name (string) — the name of the function as referenced in SQL statements.
• func (function) — the actual function to be executed.

Returns

• Database — The database object. Useful for method chaining

Example

Register a simple function

db.create_function("addOne", function (x) {return x+1;})
          db.exec("SELECT addOne(1)") // = 2

create_aggregate( name: string, aggregateFunctions: object, ): Database

Register a custom aggregate with SQLite

Parameters

• name (string) — the name of the aggregate as referenced in SQL statements.
• aggregateFunctions (object) — object containing at least a step function.
  • aggregateFunctions.init= (function, optional) — 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.
  • aggregateFunctions.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.
  • aggregateFunctions.finalize= (function, optional) — 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

• Database — The database object. Useful for method chaining

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

updateHook( callback: Database~UpdateHookCallback | null, ): Database

Registers an update hook with SQLite.

Every time a row is changed by whatever means, the callback is called once with the change ('insert', 'update' or 'delete'), the database name and table name where the change happened and the rowid of the row that has been changed.

The rowid is cast to a plain number. If it exceeds Number.MAX_SAFE_INTEGER (2^53 - 1), an error will be thrown.

Important notes:

• The callback MUST NOT modify the database in any way
• Only a single callback can be registered at a time
• Unregister the callback by passing null
• Not called for some updates like ON REPLACE CONFLICT and TRUNCATE (a DELETE FROM without a WHERE clause)

See SQLite documentation on sqlite3_update_hook for more details

Parameters

• callback (Database~UpdateHookCallback | null)

Returns

• Database — The database object. Useful for method chaining

Example

// Create a database and table
var db = new SQL.Database();
db.exec(`
CREATE TABLE users (
  id INTEGER PRIMARY KEY, -- this is the rowid column
  name TEXT,
  active INTEGER
)
`);

// Register an update hook
var changes = [];
db.updateHook(function(operation, database, table, rowId) {
  changes.push({operation, database, table, rowId});
  console.log(`${operation} on ${database}.${table} row ${rowId}`);
});

// Insert a row - triggers the update hook with 'insert'
db.run("INSERT INTO users VALUES (1, 'Alice', 1)");
// Logs: "insert on main.users row 1"

// Update a row - triggers the update hook with 'update'
db.run("UPDATE users SET active = 0 WHERE id = 1");
// Logs: "update on main.users row 1"

// Delete a row - triggers the update hook with 'delete'
db.run("DELETE FROM users WHERE id = 1");
// Logs: "delete on main.users row 1"

// Unregister the update hook
db.updateHook(null);

// This won't trigger any callback
db.run("INSERT INTO users VALUES (2, 'Bob', 1)");

Other

SqlValue: string | number | null | Uint8Array

QueryExecResult: Object

Properties

• 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

UpdateHookCallback: function

Parameters

• operation ('insert' | 'update' | 'delete')
• database (string)
• table (string)
• rowId (number)