Skip to content
This repository

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP

Common interface for accessing SQL databases from node.js

tag: v0.0.2

Fetching latest commit…

Cannot retrieve the latest commit at this time

README.mkd

node-sqli

Library that attempts to provide a common interface for SQL databases by wrapping third party drivers with a simple interface. Inspired by python's dbapi, java jdbc, etc. So far there's support for sqlite, mysql and postgres. See the end of README for info on how to wrap other drivers.

Instalation

$ npm install sqli

Usage

Acquire a connection:

var sqli = require('sqli')
, sqlite = sqli.getDriver('sqlite')
, conn = sqlite.connect(':memory:');

Connections can also be acquired via pools which are useful in web applications

// max 5 connections, 10 seconds before idle connections will be closed
pool = sqlite.createPool('some.db', 5, 10000);
conn = pool.get();

The pool is implemented using https://github.com/coopernurse/node-pool.

The API methods always return promises/futures, which will queue statements/callbacks to be executed when appropriate, creating the illusion of synchronous programming style:

conn.execute('CREATE TABLE tags (id TEXT, value TEXT)');
conn.execute('INSERT INTO tags (id, value) VALUES (?, ?)', ['id1', 'value1']);
conn.execute("INSERT INTO tags (id, value) VALUES ('id2', 'value2')");
conn.execute('SELECT * FROM tags').each(function(row) {
  console.log(row);
});

Transactions API

conn.execute('INSERT INTO test (id,stringcol) VALUES(?,?)', [1, 'abc']);
conn.execute('INSERT INTO test (id,stringcol) VALUES(?,?)', [2, 'def']);
conn.begin(); // This will disable autocommit
conn.execute('INSERT INTO test (id,stringcol) VALUES(?,?)', [3, 'ghi']);
conn.execute('INSERT INTO test (id,stringcol) VALUES(?,?)', [4, 'jkl']);
conn.commit(); // commits everything since 'begin'
conn.execute('SELECT COUNT(*) FROM test').scalar(function(value) {
  console.log(value); // 4
});

There's also limited support to isolation levels and savepoints:

conn.execute('INSERT INTO test (id,stringcol) VALUES(?,?)', [1, 'abc']);
conn.execute('INSERT INTO test (id,stringcol) VALUES(?,?)', [2, 'def']);
conn.begin(sqli.SERIALIZABLE);
conn.execute("UPDATE test SET stringcol = 'txt' WHERE id=2");
conn.save('s1');
conn.execute('DELETE FROM test WHERE id = 1');
conn.execute('SELECT stringcol AS s FROM test').all(function(rows) {
  console.log(rows);
});
conn.rollback('s1'); // Revert everything since 'save'
conn.execute('SELECT stringcol AS s FROM test').all(function(rows) {
  console.log(rows);
});
conn.rollback(); // Revert everything done since 'begin'
conn.execute('SELECT stringcol AS s FROM test ORDER BY id').first(function(row) {
  console.log(row.s); // 'abc'
});

Implementing custom drivers:

Here is node-sqlite3 wrapper:

function factory(sqlite3) {
  var sqli = require('./sqli');
  sqli.register('sqlite', {
    connect: function(filename, cb) {
      var db = new sqlite3.Database(filename, function(err) {
        if (err) return cb(err, null);
        cb(null, db);
      });
    },
    close: function(db) {
      db.close();
    },
    execute: function(db, sql, params, cb) {
      db.all(sql, params, function(err, rows) {
        if (err) cb(err);
        else cb(null, {
          each: function(rowCb, endCb) {
            for (var i = 0; i < rows.length; i++)
              rowCb(rows[i]);
            if (typeof endCb === 'function')
              endCb();
          }
        });
      });
    },
    begin: function(isolation) {
      switch (isolation) {
        case sqli.REPEATABLE_READ:
          return 'BEGIN IMMEDIATE TRANSACTION';
        case sqli.SERIALIZABLE:
          return 'BEGIN EXCLUSIVE TRANSACTION';
        default:
          return 'BEGIN TRANSACTION';
      }
    },
    save: function(savepoint) {
      return 'SAVEPOINT ' + savepoint;
    },
    commit: function() {
      return 'COMMIT';
    },
    rollback: function(savepoint) {
      if (!savepoint)
        return 'ROLLBACK';
      return 'ROLLBACK TO ' + savepoint;
    }
  });
};

try {
  factory(require('sqlite3'));
} catch (error) {
  console.log('Could not load sqlite3 wrapper ' + error.message);
}

If you do implement more wrappers, please send me pull requests :).

There's a generic test suite(test/common.js) that can be used as a quick sanity check.

To run the tests(will only run the tests for the available database systems)

$ npm install -d
$ make test
Something went wrong with that request. Please try again.