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. no C bindings or node-gyp compilation here.
SQLite is public domain, sql.js is MIT licensed.
A full documentation generated from comments inside the source code, is available.
var sql = require('sql.js');
// or sql = window.SQL if you are in a browser
// in a browser, use the following callback before
// performing any action, since it is async
sql.onRuntimeInitialized = function() { ... }
// Create a database
var db = new sql.Database();
// NOTE: You can also use new sql.Database(data) where
// data is an Uint8Array representing an SQLite database file
// Execute some sql
sqlstr = "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 anything
var res = db.exec("SELECT * FROM hello");
/*
[
{columns:['a','b'], values:[[0,'hello'],[1,'world']]}
]
*/
// Prepare an sql statement
var stmt = db.prepare("SELECT * FROM hello WHERE a=:aval AND b=:bval");
// Bind values to the parameters and fetch the results of the query
var 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']
// 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'
// 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.
// Export the database to an Uint8Array containing the SQLite database file
var binaryArray = db.export();There is an online demo available here : http://kripken.github.io/sql.js/GUI
The test files provide up to date example of the use of the api.
<script src='js/sql.js'></script>
<script>
SQL.onRuntimeInitialized = function() {
//Create the database
var db = new SQL.Database();
// Run a query without reading the results
db.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 statement
var stmt = db.prepare("SELECT * FROM test WHERE col1 BETWEEN $start AND $end");
stmt.getAsObject({$start:1, $end:1}); // {col1:1, col2:111}
// Bind new values
stmt.bind({$start:1, $end:2});
while(stmt.step()) { //
var row = stmt.getAsObject();
// [...] do something with the row of result
}
}
</script>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 = function() {
var f = dbFileElm.files[0];
var r = new FileReader();
r.onload = function() {
var Uints = new Uint8Array(r.result);
db = new SQL.Database(Uints);
}
r.readAsArrayBuffer(f);
}See : http://kripken.github.io/sql.js/GUI/gui.js
var xhr = new XMLHttpRequest();
xhr.open('GET', '/path/to/database.sqlite', true);
xhr.responseType = 'arraybuffer';
xhr.onload = function(e) {
var uInt8Array = new Uint8Array(this.response);
var db = new SQL.Database(uInt8Array);
var contents = 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 the file sql.js, from the download link below.
var fs = require('fs');
var SQL = require('sql.js');
var filebuffer = fs.readFileSync('test.sqlite');
// Load the db
var db = new SQL.Database(filebuffer);You need to convert the result of db.export to a buffer
var fs = require("fs");
// [...] (create the database)
var data = db.export();
var buffer = new Buffer(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 worker.sql.js
Example:
<script>
var worker = new Worker("js/worker.sql.js"); // You can find worker.sql.js in this repo
worker.onmessage = function() {
console.log("Database opened");
worker.onmessage = function(event){
console.log(event.data); // The result of the query
};
worker.postMessage({
id: 2,
action: 'exec',
sql: 'SELECT * FROM test'
});
};
worker.onerror = function(e) {console.log("Worker error: ", e)};
worker.postMessage({
id:1,
action:'open',
buffer:buf, /*Optional. An ArrayBuffer representing an SQLite Database file*/
});
</script>See : https://github.com/kripken/sql.js/blob/master/test/test_worker.js
- You can download
sql.jshere : 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.jshere : 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.jshere : https://raw.githubusercontent.com/kripken/sql.js/master/js/sql-memory-growth.js