SQLite compiled to JavaScript
sql.js is a javascript SQL database. It allows you to create a relational database and query it entirely in the browser. You can try it in this online demo. It uses a virtual database file stored in memory, and thus doesn't persist the changes made to the database. However, it allows you to import any existing sqlite file, and to export the created database as a JavaScript typed array.
sql.js uses emscripten to compile SQLite to webassembly (or to javascript code for compatibility with older browsers). It includes contributed math and string extension functions.
sql.js can be used like any traditional JavaScript library. If you are building a native application in JavaScript (using Electron for instance), or are working in node.js, you will likely prefer to use a native binding of SQLite to JavaScript. A native binding will not only be faster because it will run native code, but it will also be able to work on database files directly instead of having to load the entire database in memory, avoiding out of memory errors and further improving performances.
SQLite is public domain, sql.js is MIT licensed.
API documentation
A full API documentation for all the available classes and methods is available. It is generated from comments inside the source code, and is thus always up to date.
Usage
By default, sql.js uses wasm, and thus needs to load a .wasm file in addition to the javascript library. You can find this file in ./node_modules/sql.js/dist/sql-wasm.wasm after installing sql.js from npm, and instruct your bundler to add it to your static assets or load it from a CDN. Then use the locateFile property of the configuration object passed to initSqlJs to indicate where the file is. If you use an asset builder such as webpack, you can automate this. See this demo of how to integrate sql.js with webpack (and react).
// or if you are in a browser:
// const initSqlJs = window.initSqlJs;
const SQL = await initSqlJs({
// Required to load the wasm binary asynchronously. Of course, you can host it wherever you want
// You can omit locateFile completely when running in node
locateFile: file => `https://sql.js.org/dist/${file}`
});
// Create a database
const db = new SQL.Database();
// NOTE: You can also use new SQL.Database(data) where
// data is an Uint8Array representing an SQLite database file
// Execute a single SQL string that contains multiple statements
let sqlstr = "CREATE TABLE hello (a int, b char); \
INSERT INTO hello VALUES (0, 'hello'); \
INSERT INTO hello VALUES (1, 'world');";
db.run(sqlstr); // Run the query without returning anything
// Prepare an sql statement
const stmt = db.prepare("SELECT * FROM hello WHERE a=:aval AND b=:bval");
// Bind values to the parameters and fetch the results of the query
const result = stmt.getAsObject({':aval' : 1, ':bval' : 'world'});
console.log(result); // Will print {a:1, b:'world'}
// Bind other values
stmt.bind([0, 'hello']);
while (stmt.step()) console.log(stmt.get()); // Will print [0, 'hello']
// free the memory used by the statement
stmt.free();
// You can not use your statement anymore once it has been freed.
// But not freeing your statements causes memory leaks. You don't want that.
const res = db.exec("SELECT * FROM hello");
/*
[
{columns:['a','b'], values:[[0,'hello'],[1,'world']]}
]
*/
// You can also use JavaScript functions inside your SQL code
// Create the js function you need
function add(a, b) {return a+b;}
// Specifies the SQL function's name, the number of it's arguments, and the js function to use
db.create_function("add_js", add);
// Run a query in which the function is used
db.run("INSERT INTO hello VALUES (add_js(7, 3), add_js('Hello ', 'world'));"); // Inserts 10 and 'Hello world'
// You can create custom aggregation functions, by passing a name
// and a set of functions to `db.create_aggregate`:
//
// - an `init` function. This function receives no argument and returns
// the initial value for the state of the aggregate function.
// - a `step` function. This function takes two arguments
// - the current state of the aggregation
// - a new value to aggregate to the state
// It should return a new value for the state.
// - a `finalize` function. This function receives a state object, and
// returns the final value of the aggregate. It can be omitted, in which case
// the final value of the state will be returned directly by the aggregate function.
//
// Here is an example aggregation function, `json_agg`, which will collect all
// input values and return them as a JSON array:
db.create_aggregate(
"json_agg",
{
init: () => [],
step: (state, val) => [...state, val],
finalize: (state) => JSON.stringify(state),
}
);
db.exec("SELECT json_agg(column1) FROM (VALUES ('hello'), ('world'))");
// -> The result of the query is the string '["hello","world"]'
// Export the database to an Uint8Array containing the SQLite database file
const binaryArray = db.export();
Demo
There are a few examples available here. The most full-featured is the Sqlite Interpreter.
Examples
The test files provide up to date example of the use of the api.