SQLite compiled to javascript
For the impatients, try the demo here: http://kripken.github.io/sql.js/GUI/
sql.js is a port of SQLite to JavaScript, 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 is 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.
Documentation
A full documentation generated from comments inside the source code, is available.
Usage
var sql = ;// or sql = window.SQL if you are in a browser // Create a databasevar db = ;// 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 the query without returning anything var res = db;/*[ {columns:['a','b'], values:[[0,'hello'],[1,'world']]}]*/ // Prepare an sql statementvar stmt = db; // Bind values to the parameters and fetch the results of the queryvar result = stmt;console; // Will print {a:1, b:'world'} // Bind other valuesstmt;while stmt console; // Will print [0, 'hello'] // You can also use javascript functions inside your SQL code// Create the js function you need {return a+b;}// Specifies the SQL function's name, the number of it's arguments, and the js function to usedb;// Run a query in which the function is useddb; // Inserts 10 and 'Hello world' // free the memory used by the statementstmt;// 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 filevar binaryArray = db;
Demo
There is an online demo available here : http://kripken.github.io/sql.js/GUI
Examples
The test files provide up to date example of the use of the api.
Inside the browser
Example HTML file:
Creating a database from a file choosen by the user
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 { var f = dbFileElmfiles0; var r = ; r { var Uints = rresult; db = Uints; } r;}
See : http://kripken.github.io/sql.js/GUI/gui.js
Loading a database from a server
var xhr = ;xhr;xhrresponseType = 'arraybuffer'; xhr { var uInt8Array = thisresponse; var db = uInt8Array; var contents = db; // contents is now [{columns:['col1','col2',...], values:[[first row], [second row], ...]}]};xhr;
See: https://github.com/kripken/sql.js/wiki/Load-a-database-from-the-server
Use from node.js
sql.js
is hosted on npm. To install it, you can simply run npm install sql.js
.
Alternatively, you can simply download the file sql.js
, from the download link below.
read a database from the disk:
var fs = ;var SQL = ;var filebuffer = fs; // Load the dbvar db = filebuffer;
write a database to the disk
You need to convert the result of db.export
to a buffer
var fs = ;// [...] (create the database)var data = db;var buffer = data;fs;
See : https://github.com/kripken/sql.js/blob/master/test/test_node_file.js
Use as web worker
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 worker.sql.js
Example:
See : https://github.com/kripken/sql.js/blob/master/test/test_worker.js
Downloads
- You can download
sql.js
here : https://raw.githubusercontent.com/kripken/sql.js/master/js/sql.js - And the Web Worker version: https://raw.githubusercontent.com/kripken/sql.js/master/js/worker.sql.js
- You can find a non minified or optimized version for debugging,
sql-debug.js
here : https://raw.githubusercontent.com/kripken/sql.js/master/js/sql-debug.js - If you see the message,
Cannot enlarge memory arrays
, try this version,sql-memory-growth.js
here : https://raw.githubusercontent.com/kripken/sql.js/master/js/sql-memory-growth.js