JavaScript sqlite [closed]

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.

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);

Well, if you are working on client side JavaScript, I think you will be out of luck... browsers tend to sandbox the JavaScript environment so you don't have access to the machine in any kind of general capacity like accessing a database.

If you are talking about an SQLite DB on the server end accessed from the client end, you could set up an AJAX solution that invokes some server side code to access it.

If you are talking about Rhino or some other server side JavaScript, you should look into the host language's API access into SQLite (such as the JDBC for Rhino).

Perhaps clarify your question a bit more...?