JavaScript sqlite [closed]

Question

Best recommendations for accessing and manipulation of sqlite databases from JavaScript.

Answer 1

There a project called sql.js which is a port of SQLite in JavaScript.

sql.js is a port of SQLite to JavaScript, by compiling the SQLite C code with Emscripten.

Answer 2

Panorama of javascript SQLite solutions

In the browser

If you want to access a SQLite database from inside a web browser, you don't have many solutions.

sql.js

The SQLite C library has been ported to javascript using emscripten. The port was started under the name of sql.js by Alon Zakai (who is also the author of emscripten). I am the current maintainer of this library.

The API goes like:

<script src='js/sql.js'></script> <script>     //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 a 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> 

Web SQL

The W3C had started to work on a native API for executing SQL inside the browser, called web sql. An example of use of that API:

var db = openDatabase('mydb', '1.0', 'my first database', 2 * 1024 * 1024); db.transaction(function (tx) {   tx.executeSql('CREATE TABLE IF NOT EXISTS foo (id unique, text)');   tx.executeSql('INSERT INTO foo (id, text) VALUES (1, "synergies")'); }); 

However, the project has been abandoned. Thus it's not widely supported. See: http://caniuse.com/sql-storage

In node

If you write client-side javascript, in node, you have a little more choices. See: https://www.npmjs.org/search?q=sqlite .

node-sqlite3

If you have a compilation toolchain, and can don't care about having to compile your application for different platforms (or target only one platform), I would advise that you use node-sqlite3. It is fast (much faster than sql.js), has a complete API and a good documentation. An example of the API is as follow:

var sqlite3 = require('sqlite3').verbose(); var db = new sqlite3.Database(':memory:');  db.serialize(function() {   db.run("CREATE TABLE lorem (info TEXT)");    var stmt = db.prepare("INSERT INTO lorem VALUES (?)");   for (var i = 0; i < 10; i++) {       stmt.run("Ipsum " + i);   }   stmt.finalize();    db.each("SELECT rowid AS id, info FROM lorem", function(err, row) {       console.log(row.id + ": " + row.info);   }); });  db.close(); 

sql.js

Yes, again. sql.js can be used from node. This is the solution if you want a pure javascript application. However, it will be slower than the previous solution.

Here is an example of how to use sql.js from node:

var fs = require('fs'); var SQL = require('sql.js'); var filebuffer = fs.readFileSync('test.sqlite');  db.run("INSERT INTO test VALUES (?,?,?)", [1, 'hello', true]);  -- corrected INT to INTO   var data = db.export(); var buffer = new Buffer(data); fs.writeFileSync("filename.sqlite", buffer);