Database

Represents an SQLite database

Constructor

new Database(data)

Parameters:
NameTypeDescription
dataArray.<number>

An array of bytes representing an SQLite database file

Source

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

Source

["create_function"](name, func) → {Database}

Register a custom function with SQLite

Parameters:
NameTypeDescription
namestring

the name of the function as referenced in SQL statements.

funcfunction

the actual function to be executed.

Source
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 a 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:
NameTypeAttributesDefaultDescription
sqlstring

A string of SQL text. Can contain placeholders that will be bound to the parameters given as the second argument

paramsStatement.BindParams<optional>
[]

Parameters to bind to the query

callbackfunction

Function to call on each row of result

donefunction

A function that will be called when all rows have been retrieved

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

["exec"](sql, paramsopt) → {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:

idagename
11Ling
218Paul

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:
NameTypeAttributesDescription
sqlstring

a string containing some SQL text to execute

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

Source
Returns:

The results of each statement

Type: 
Array.<Database.QueryExecResult>

["export"]() → {Uint8Array}

Exports the contents of the database to a binary array

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

Source
Returns:

the number of rows modified

Type: 
number

["handleError"]()

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

Source

["iterateStatements"](sql) → {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:
NameTypeDescription
sqlstring

a string of SQL that can contain multiple statements

Source
Returns:

the resulting statement iterator

Type: 
StatementIterator
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}

Prepare an SQL statement

Parameters:
NameTypeAttributesDescription
sqlstring

a string of SQL, that can contain placeholders (?, :VVV, :AAA, @AAA)

paramsStatement.BindParams<optional>

values to bind to placeholders

Source
Throws:

SQLite error

Type
String
Returns:

the resulting statement

Type: 
Statement

["run"](sql, paramsopt) → {Database}

Execute an SQL query, ignoring the rows it returns.

Parameters:
NameTypeAttributesDescription
sqlstring

a string containing some SQL text to execute

paramsStatement.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 ;)

Source
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

Type:
  • Object
Properties
NameTypeDescription
columnsArray.<string>

the name of the columns of the result (as returned by Statement.getColumnNames)

valuesArray.<Array.<Database.SqlValue>>

one array per row, containing the column values

Source

SqlValue

Type:
  • string | number | null | Uint8Array
Source