A Mutable Log

A blog by Devendra Tewari

---

Project maintained by tewarid Hosted on GitHub Pages — Theme by mattgraham

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

Head over to the command prompt and run

npm install sqlite3

You can add the -g option after npm if you want to install to the global node_modules folder.

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 the post Adding lots of source files to a WiX installer script 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.