Node-SQLite-NoDep : SQLite for node.js without NPM

Seeing as how Mozilla is slowly phasing out anything that’s not part of Firefox (sorry Mozilla, I think that’s the wrong call… we don’t need YACC… yet another Chrome clone), including XUL, XULRunner, JSShell and so on I’m slowly trying to replace these technologies on our servers. JSShell in particular has been invaluable in automating simple tasks in a sane language.

The obvious replacement is node.js… but as great as node.js is, it has a few shortcomings. In particular, it relies heavily on NPM to provide even basic functionality and as any admin knows, something that can break will eventually break if its too complex. An admin wants a solution that’s as complex as necessary, but still as simple as possible. So, installing node.js along with npm on a machine is a liability. Luckily node.js itself is portable, but since its library interface is unstable, depending on anything from npm is a big no-no.

One thing I frequently need is a little database. Simple text files work too, but eventually you’ll end up duplicating what any database already does. SQLite is an obvious choice for applications with very few users, for example reftest results. But connecting SQLite to node.js without anything from NPM is a pretty ugly task. Luckily, there’s also a commandline version of SQLite and while it may not be as fast as a binary interface, it can get the job done… with a little help.

Node-SQLite-NoDep does exactly that. It launches sqlite3.exe and sends any data back and forth between your node.js application and the child_process , converting the INSERT statements produced by sqlite3.exe into buffers and providing basic bind parameter support. The documentation is not entirely complete yet, but you can find a quick introduction here along with the jsdoc documentation available here.

Basically, all you have to do is grab SQLite.js, drop into into a folder, add a bin folder, drop node.exe and sqlite3.exe and you’re good to go.

const SQLite = require('./SQLite.js');

var testdb=new SQLite('.\\mytest.sqlite',[
    {name:"testTable",columns:"X int,LABEL varchar[64],data blob"},
],function(){
    testdb.sql("INSERT INTO ?testTable VALUES(#X,$LABEL,&DATA)",
    {
        testTable:"testTable",
        X:123,
        LABEL:"Hello World",
        DATA:new Buffer("DEADBEEF","hex")
    },
    function(data){
        testdb.sql("SELECT * from ?testTable",
        {
            testTable:"testTable"
        },
        function(data){
            console.log(JSON.stringify(data,null,"\t"));
            process.exit();
        });
    });
});

is really all you need to see it in action. Just put it into test.js file and launch it with

bin\node.exe test.js

to see it in action.