SQLite - express-examples/sqlite

Installing SQLite3

SQLite3 is very simple, and perfect for examples "plug & play". For installing it:

$ sudo aptitude install sqlite3

Creating a Database

In my case I just entered:

$ sqlite3 db/db.sqlite

Once inside, you can enter SQL commands. Following sqlite example table:

sqlite> CREATE TABLE user (name VARCHAR(50)); 

In SQLite exists a default auto-increment field named `rowid`, that means you can make queries like:

sqlite> SELECT rowid AS id, name FROM user WHERE id=?

Installing SQLite plugin for Node.js

I'm using node-sqlite3. I found this plugin immature and not working properly under several scenarios, since I'm not really interested in developing something complex with sqlite, not going to invest more time on this, but maybe node-sqlite is a better option.

For installing it:

$ npm install sqlite3

No matter which of the 2 previously mentioned plugins you use, you have to use callback programming style.

var sqlite3 = require('sqlite3').verbose();
var db = new sqlite3.Database('./db/db.sqlite');

// Querying
db.all("SELECT rowid AS id, name FROM user WHERE id=?", [ id ], function(err, rows) {
  if (err) 
    console.log(err);

  callback((rows)?rows[0]:null);
});

// Adding
db.run("INSERT INTO user(name) VALUES(?)", [ name ]/*, function(error) {
  if (error) 
    console.log(error);

  callback((error)?false:true);
}*/);
callback(true); // TODO: Callback is not working!!

As you can see in the code above, callbacks are not working properly in `db.run` method. But, the really interesting thing I want to emphasize is the use of them (callbacks), this adds a-synchronicity to your code, which means things are not blocked until getting a response. Of course if you're experienced with thread capable languages (e.g. Java), this may not surprise you, but if you're PHP programmer you should be surprised. Another thing, the simplicity and let me say "harmony" of this approach is outstanding.