Geeking Out: SQLite and Node.js

On March 12, 2013, in Articles, Geek, by mlesswing

NOTE: CRT is excited to have Mark Lesswing, Chief Technology Officer and SVP of ITS at NAR, write a series of posts on his experiences with node.js. In the article below, Mark covers the use of SQLite and node.js. Please note that this will be a pretty technical series and speaks to the diversity of topics CRT will cover. We will indicate when we have articles that are more technical as we post them. Thank you. – Chad

In a previous post, I discussed the concept of using node.js as a WebSocket server. This approach is characterized by many smaller node.js servers, each providing information to separate WebSockets. The user experiences the data through an HTML5 webpage. It is natural that the next topic of discussion would be persistence. There is a wealth of information that can be found on the web regarding node.js and robust SQL databases like MySQL, so I will not duplicate the information in this post.

In keeping with the “smallness” of specificity of purpose of the approach, I will now discuss the use of SQLite in conjunction with node.js. The source code for SQLite is in the public domain, so it is a good mate for node.js.

The first thing you need to do is install the node.js module for SQLite. If you have already installed node.js which included the npm tool (I covered this in a previous post) you need to execute the following from the command line in the root of you project directory:

sudo npm install -g node-gyp
npm install sqlite3

1 2	sudo npm install -g node-gyp npm install sqlite3

The node-gyp module is a dependency of the sqlite module.

Now let’s look at a quick code example:

#!/usr/bin/env node

"use strict";

//
// includes
//
var fs = require("fs");
var sqlite3 = require("sqlite3");

//
// only continue if the database exists
//
var repository = "./repository/crt_example.db";
fs.exists(repository, function(exists) {
if (exists) {

//
// open the database
//
var db = new sqlite3.Database(repository);
var stmt = "SELECT nrdsID, firstName, lastName FROM members";
db.each(stmt, function(err, row) {

//
// print out results
//
console.log(row.nrdsID + " " + row.lastName + ", " + row.firstName);

});
db.close();
} else {
console.log("Database does not exist, run broker_node_init.js first.");
}
});

#!/usr/bin/env node

"use strict";

// includes

var fs = require("fs");

var sqlite3 = require("sqlite3");

// only continue if the database exists

var repository = "./repository/crt_example.db";

fs.exists(repository, function(exists) {

if (exists) {

// open the database

var db = new sqlite3.Database(repository);

var stmt = "SELECT nrdsID, firstName, lastName FROM members";

db.each(stmt, function(err, row) {

// print out results

console.log(row.nrdsID + " " + row.lastName + ", " + row.firstName);

});

db.close();

} else {

console.log("Database does not exist, run broker_node_init.js first.");

}

});

The each function is self-explanatory, so I would like to talk about the all function. Let’s consider the case where you need to determine of any results came back. Look at this code fragment:

var stmt = "SELECT systemName FROM communicator WHERE lastMessage='AUTHENTICATED'";
db.all(stmt, function(err, rows) {
if (err) throw err;
if (rows.length == 0) {
console.log("Hey, there is nothing Authenticated");
} else {
//
// do something with the authenticated crowd
//
}
db.close();
});

var stmt = "SELECT systemName FROM communicator WHERE lastMessage='AUTHENTICATED'";

db.all(stmt, function(err, rows) {

if (err) throw err;

if (rows.length == 0) {

console.log("Hey, there is nothing Authenticated");

} else {

// do something with the authenticated crowd

}

db.close();

});

Another common use case is issuing SQL calls that depend on previous SQL results; in other words, the order of SQl calls is important. If you have been experimenting with node.js, you will appreciate this next sequence. The event-oriented nature of node.js takes some getting used to but here is how to use serialize():

var db2 = new sqlite3.Database(repository);
db2.serialize( function() {

//
// first call
//
var stmt = "UPDATE communicator set systemName='" + aMessage.account + + "',lastActivity='" + (new Date()) + "' WHERE ipAddress='" + connection.remoteAddress + "'";
db2.run(stmt);

//
// second call, not dependent, but order is important
//
stmt = "SELECT nrdsID,firstName,lastName FROM auth WHERE systemName='"+aMessage.account;
db2.all(stmt, function(err, rows) {
if (err) throw err;
if (rows.length != 0) {
rows.forEach(function (row) {

//
// third call which depends on the results of the second
//
var stmt = "UPDATE communicator set systemName='" + row.nrdsID + "',lastMessage='AUTHENTICATED'  WHERE ipAddress='" + connection.remoteAddress + "'";
db2.run(stmt);
}); // each for iterating the results
}
db2.close();
}); // all for the auth table
}); //serialize db

var db2 = new sqlite3.Database(repository);

db2.serialize( function() {

// first call

var stmt = "UPDATE communicator set systemName='" + aMessage.account + + "',lastActivity='" + (new Date()) + "' WHERE ipAddress='" + connection.remoteAddress + "'";

db2.run(stmt);

// second call, not dependent, but order is important

stmt = "SELECT nrdsID,firstName,lastName FROM auth WHERE systemName='"+aMessage.account;

db2.all(stmt, function(err, rows) {

if (err) throw err;

if (rows.length != 0) {

rows.forEach(function (row) {

// third call which depends on the results of the second

var stmt = "UPDATE communicator set systemName='" + row.nrdsID + "',lastMessage='AUTHENTICATED' WHERE ipAddress='" + connection.remoteAddress + "'";

db2.run(stmt);

}); // each for iterating the results

}

db2.close();

}); // all for the auth table

}); //serialize db

The serialize() call ensures that the underlying SQLite database executes the AQL in the proper order.

Hopefully this persistence discussion was helpful.

mlesswing

3 Responses to Geeking Out: SQLite and Node.js

Kenson Goo says:

March 18, 2013 at 6:08 pm

There is no data sanitization in UPDATE SQL queries, which might lead to SQL injection. Other than that, this is an awesome tutorial.

Kenson Goo
Contra Costa Assocation of Realtors
mlesswing says:

March 22, 2013 at 10:18 am

I try to be careful about entering user contributed data into the database. I use SQLite for non-consumer facing storage.
mlesswing says:

March 22, 2013 at 10:17 pm

A better answer would be:
var firstValue = "hello"; var secondValue = 4; db.prepare("INSERT INTO myExample (hostName,hitCount) VALUES(?,?)").run(firstValue,secondValue).finalize();