For the impatients, try the demo here: http://kripken.github.io/sql.js/examples/GUI
sql.js is a port of SQLite to Webassembly, by compiling the SQLite C code with Emscripten. 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.
There are no C bindings or node-gyp compilation here, sql.js is a simple JavaScript file, that 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.
SQLite is public domain, sql.js is MIT licensed.
Sql.js predates WebAssembly, and thus started as an asm.js project. It still supports asm.js for backwards compatibility.
A full documentation generated from comments inside the source code, is available.
varinitSqlJs=require('sql.js');// or if you are in a browser:// var initSqlJs = window.initSqlJs;initSqlJs().then(SQL=>{// Create a databasevardb=newSQL.Database();// NOTE: You can also use new SQL.Database(data) where// data is an Uint8Array representing an SQLite database file// Execute some sqlsqlstr="CREATE TABLE hello (a int, b char);";sqlstr+="INSERT INTO hello VALUES (0, 'hello');"sqlstr+="INSERT INTO hello VALUES (1, 'world');"db.run(sqlstr);// Run the query without returning anythingvarres=db.exec("SELECT * FROM hello");/* [{columns:['a','b'], values:[[0,'hello'],[1,'world']]} ] */// Prepare an sql statementvarstmt=db.prepare("SELECT * FROM hello WHERE a=:aval AND b=:bval");// Bind values to the parameters and fetch the results of the queryvarresult=stmt.getAsObject({':aval' : 1,':bval' : 'world'});console.log(result);// Will print{a:1, b:'world'}// Bind other valuesstmt.bind([0,'hello']);while(stmt.step())console.log(stmt.get());// Will print [0, 'hello']// You can also use JavaScript functions inside your SQL code// Create the js function you needfunctionadd(a,b){returna+b;}// Specifies the SQL function's name, the number of it's arguments, and the js function to usedb.create_function("add_js",add);// Run a query in which the function is useddb.run("INSERT INTO hello VALUES (add_js(7, 3), add_js('Hello ', 'world'));");// Inserts 10 and 'Hello world'// free the memory used by the statementstmt.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.// Export the database to an Uint8Array containing the SQLite database filevarbinaryArray=db.export();});There are a few examples available here. The most full-featured is the Sqlite Interpreter.
The test files provide up to date example of the use of the api.
<metacharset="utf8" /><html><scriptsrc='/dist/sql-wasm.js'></script><script>config={locateFile: filename=>`/dist/${filename}`}// The `initSqlJs` function is globally provided by all of the main dist files if loaded in the browser.// We must specify this locateFile function if we are loading a wasm file from anywhere other than the current html page's folder.initSqlJs(config).then(function(SQL){//Create the databasevardb=newSQL.Database();// Run a query without reading the resultsdb.run("CREATE TABLE test (col1, col2);");// Insert two rows: (1,111) and (2,222)db.run("INSERT INTO test VALUES (?,?), (?,?)",[1,111,2,222]);// Prepare a statementvarstmt=db.prepare("SELECT * FROM test WHERE col1 BETWEEN $start AND $end");stmt.getAsObject({$start:1,$end:1});//{col1:1, col2:111}// Bind new valuesstmt.bind({$start:1,$end:2});while(stmt.step()){//varrow=stmt.getAsObject();console.log('Here is a row: '+JSON.stringify(row));}});</script><body> Output is in Javascript console </body></html>SQL.Database constructor takes an array of integer representing a database file as an optional parameter. The following code uses an HTML input as the source for loading a database:
dbFileElm.onchange=()=>{varf=dbFileElm.files[0];varr=newFileReader();r.onload=function(){varUints=newUint8Array(r.result);db=newSQL.Database(Uints);}r.readAsArrayBuffer(f);}See : http://kripken.github.io/sql.js/examples/GUI/gui.js
varxhr=newXMLHttpRequest();// For example: https://github.com/lerocha/chinook-database/raw/master/ChinookDatabase/DataSources/Chinook_Sqlite.sqlitexhr.open('GET','/path/to/database.sqlite',true);xhr.responseType='arraybuffer';xhr.onload=e=>{varuInt8Array=newUint8Array(xhr.response);vardb=newSQL.Database(uInt8Array);varcontents=db.exec("SELECT * FROM my_table");// contents is now [{columns:['col1','col2',...], values:[[first row], [second row], ...]}]};xhr.send();See: https://github.com/kripken/sql.js/wiki/Load-a-database-from-the-server
sql.js is hosted on npm. To install it, you can simply run npm install sql.js. Alternatively, you can simply download sql-wasm.js and sql-wasm.wasm, from the download link below.
varfs=require('fs');varinitSqlJs=require('sql-wasm.js');varfilebuffer=fs.readFileSync('test.sqlite');initSqlJs().then(function(SQL){// Load the dbvardb=newSQL.Database(filebuffer);});You need to convert the result of db.export to a buffer
varfs=require("fs");// [...] (create the database)vardata=db.export();varbuffer=newBuffer(data);fs.writeFileSync("filename.sqlite",buffer);See : https://github.com/kripken/sql.js/blob/master/test/test_node_file.js
If you don't want to run CPU-intensive SQL queries in your main application thread, you can use the more limited WebWorker API.
You will need to download dist/worker.sql-wasm.jsdist/worker.sql-wasm.wasm.
Example:
<script>varworker=newWorker("/dist/worker.sql-wasm.js");worker.onmessage=()=>{console.log("Database opened");worker.onmessage=event=>{console.log(event.data);// The result of the query};worker.postMessage({id: 2,action: 'exec',sql: 'SELECT * FROM test'});};worker.onerror=e=>console.log("Worker error: ",e);worker.postMessage({id:1,action:'open',buffer:buf,/*Optional. An ArrayBuffer representing an SQLite Database file*/});</script>See examples/GUI/gui.js for a full working example.
This library includes both WebAssembly and asm.js versions of Sqlite. (WebAssembly is the newer, preferred way to compile to JavaScript, and has superceded asm.js. It produces smaller, faster code.) Asm.js versions are included for compatibility.
Version 1.0 of sql.js must be loaded asynchronously, whereas asm.js was able to be loaded synchronously.
So in the past, you would:
<scriptsrc='js/sql.js'></script><script>vardb=newSQL.Database();//...</script>or:
varSQL=require('sql.js');vardb=newSQL.Database();//...Version 1.x:
<scriptsrc='dist/sql-wasm.js'></script><script>initSqlJs({locateFile: filename=>`/dist/${filename}`}).then(function(SQL){vardb=newSQL.Database();//...});</script>or:
varinitSqlJs=require('sql-wasm.js');initSqlJs().then(function(SQL){vardb=newSQL.Database();//...});NOTHING is now a reserved word in SQLite, whereas previously it was not. This could cause errors like Error: near "nothing": syntax error
Although asm.js files were distributed as a single Javascript file, WebAssembly libraries are most efficiently distributed as a pair of files, the .js loader and the .wasm file, like dist/sql-wasm.js and dist/sql-wasm.wasm. The .js file is responsible for wrapping/loading the .wasm file.
Versions of sql.js included in the distributed artifacts
For each relase, you will find a file called sqljs.zip in the release assets. It will contain:
sql-wasm.js: The Web Assembly version of Sql.js. Minified and suitable for production. Use this. If you use this, you will need to include/shipsql-wasm.wasmas well.sql-wasm-debug.js: The Web Assembly, Debug version of Sql.js. Larger, with assertions turned on. Useful for local development. You will need to include/shipsql-wasm-debug.wasmif you use this.sql-asm.js: The older asm.js version of Sql.js. Slower and larger. Provided for compatibility reasons.sql-asm-memory-growth.js: Asm.js doesn't allow for memory to grow by default, because it is slower and de-optimizes. If you are using sql-asm.js and you see this error (Cannot enlarge memory arrays), use this file.sql-asm-debug.js: The Debug asm.js version of Sql.js. Use this for local development.worker.*- Web Worker versions of the above libraries. More limited API. See examples/GUI/gui.js for a good example of this.
- Install the EMSDK, as described here
- Run
npm run rebuild