Node.js and Sqlite3


Node.js and Sqlite3 can be used as a foundation for apps that are cross-platform, browser-based (leverage HTML5), and network-intensive. In this post I comment about some of the work required in building such a foundation.

Install sqlite3 for Node.js on Windows

The installation procedure for the sqlite3 module is slightly complex and requires that you have installed the python interpreter and the VC++ compiler. Then just head over to the command prompt and run:

npm install sqlite3 --arch=ia32

You can add the -g option after npm if you want to install to the global node_modules folder. The arch option is required for the module to work on Windows 7 64-bit. Without it you’ll get a cryptic message like:

Error: %1 is not a valid Win32 application

Hopefully, at some point pre-compiled binaries will be provided for Windows.

Opening or creating a database

I like to maintain a database creation script. The code below is a simple example of how I detect and execute the creation script.

var fs = require('fs');
var sqlite3 = require('sqlite3').verbose();

fs.exists('database', function (exists) {
  db = new sqlite3.Database('database');

  if (!exists) {
    console.info('Creating database. This may take a while...');
    fs.readFile('create.sql', 'utf8', function (err, data) {
      if (err) throw err;
      db.exec(data, function (err) {
        if (err) throw err;
        console.info('Done.');
      });
    });
  }
});

Here’s how create.sql may look like:

CREATE TABLE customer (
  id INT NOT NULL,
  CONSTRAINT PK_customer PRIMARY KEY (id ASC)
);

CREATE TABLE sale (
  id INT NOT NULL,
  CONSTRAINT PK_sale PRIMARY KEY (id ASC)
);

Embedding Node.js

It is rather convenient if you can package Node.js and its modules in a single installer. I discussed this in a post about the Wix Toolset recently. I use npm without the -g option to download and install all modules in a node_modules folder. I place the single executable version of Node.js in the same folder. The installer just needs to package that folder, and you have a Windows-specific package of Node.js and your application’s module dependencies ready to install.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s