Skip to content
Find file
Fetching contributors…
Cannot retrieve contributors at this time
202 lines (139 sloc) 8.78 KB


Node-DBI is a SQL database abstraction layer library, strongly inspired by the PHP Zend Framework Zend_Db API. It provides unified functions to work with multiple database engines, through Adapters classes. At this time, supported engines are mysql, mysql-libmysqlclient, sqlite3 and pg.

It provides DBWrapper and DBSelect Javascript classes, described later on this document.


Node-DBI is primarily an abstraction layer library ; it allows you to have a "database-agnostic" application, with a single API for multiple databases engines.

It provides high-level functions to fecth, insert, update and remove data from the database. It is also bundled with a DBSelect component, used to build SQL queries in a more readable, more flexible and more secure (thanks to is params escaping policy) way than long SQL strings.

Node-DBI imitates the API of the great Open Source PHP database abstraction layer of the Zend Framework, Zend_Db, used by thousands of Web developers for several years.

The example below demonstates the Node-DBI usage: (you can also look this Gist for the same code with syntax highlighting)

var DBWrapper = require('node-dbi').DBWrapper; 
var DBExpr = require('node-dbi').DBExpr; 
var dbConnectionConfig = { host: 'localhost', user: 'test', password: 'test', database: 'test' };

// Replace the adapter name with "mysql", "mysql-libmysqlclient", "sqlite3" or "pg" on the following line :
dbWrapper = new DBWrapper( '[DB engine adapter name]', dbConnectionConfig );

// ** fetchAll
dbWrapper.fetchAll('SELECT * FROM user', null, function(err, result) {
    if( ! result )
    // "result" is an Array with a hash for every returned row
} );

// ** fetchRow ( +  a safely escaped value ) 
dbWrapper.fetchRow('SELECT * FROM user WHERE first_name=?', ['John'], function(err, result) {
    if( ! result )
    // this time, "result" is a single hash (the first returned row)
} );

// ** fetchCol  (if you dont' have values to escape, the 2nd param can be an empty Array or "null")
dbWrapper.fetchCol('SELECT first_name FROM user ORDER BY fist_name', null, function(err, result) {
    if( ! err )
    // "result" is an Array with all the names of our users, sorted alphabetically
} );

// ** fetchOne
dbWrapper.fetchOne('SELECT fist_name FROM user ORDER BY rank DESC LIMIT 1', [], function(err, result) {
    if( ! err )
    // "result" is the first_name of our best user
} );

// ** insert   (DBExpr force somes values to be used "as is", without safe escape : it is useful for SQL functions like "NOW()", "COUNT(*)", "SUM(rank)"... )
var JohnData = { first_name: 'John', last_name: 'Foo', rank: '3', date_created: new DBExpr('NOW()') };
dbWrapper.insert('user', JohnData , function(err) {
    if( ! err )
        console.log( 'John ID : ' + dbWrapper.getLastInsertId() );
    // John has been inserted in our table, with its properties safely escaped
} );

// ** update  ( here the fist name is used as a raw String, but the last name is safely escaped ) 
var JohnDataUpdate = { rank: '1' };
    dbWrapper.update('user', JohnDataUpdate , [ 'first_name=\'John\'', ['last_name=?', 'Foo'] ], function(err) {
    // John is now our best user. Congratulations, John !
} );

// ** remove  ( this time, both values are safely escaped ) 
dbWrapper.remove('user', [ ['first_name LIKE ?', '%John%'], ['last_name=?', 'Foo'] ] , function(err) {
    // John left at the height of its glory.
} );

// Easy SQL String building
var select = dbWrapper.getSelect()
    .from('user', ['first_name', 'last_name'] )
    .where( 'enabled=1' )
    .where( 'id=?', 10 )
    .where( 'last_name LIKE ?', '%Foo%' )
    .order( 'last_name' )
    .limit( 10 );

if( req.params.onlyVerifiedAccounts )

console.log( select.assemble() );//outputs the SQL query for debug purpose 

// You can retrieve the data of this DBSelect with a "fetch" method...
dbWrapper.fetchAll( select, function(err) {} );

// ..or you can trigger a "fetch" method directly on it ! 
select.fetchAll( function(err) {} );

// When you have finished working with the database, you can close the connection
dbWrapper.close( function(err) {console.log('Connection closed !');} );

See the unit tests in the "test/" folder for more examples.

DBWrapper Class

The DBWrapper Javascript class, which is the only visible part on top on the different database engines adapters, provides the following methods :

  • fetchAll( sql, bind, callback ) : fetches all SQL result rows as a Array.
  • fetchRow( sql, bind, callback ) : fetches the first row of the SQL result.
  • fetchCol( sql, bind, callback ) : fetches the first column of all SQL result rows as an Array.
  • fetchOne( sql, bind, callback ) : fetches the first column of the first row of the SQL result.
  • insert( tableName, data, callback ) : inserts a table row with specified data, as a hash.
  • update( tableName, data, where, callback ) : updates table rows with specified data (as a hash) based on a WHERE clause.
  • remove( tableName, where, callback ) : deletes table rows based on a WHERE clause.
  • getLastInsertId() : returns the last inserted Id
  • isConnected() : tells us if the DbWrapper is connected to its database.
  • getSelect() : returns a DBSelect

All these methods returns exactly the sames results, whatever the chosen database engine is.

DBSelect Class

Furthermore, Node-DBI provides a DBSelect class which allows easy and readable SQL "SELECT" Strings building. At the moment, it provides the following methods :

  • from( tableName, fieldsArray ) : adds a table in the FROM clause, and adds its fields to the SELECT
  • where( whereStr, value ) : adds a WHERE clause ; if "value" is not null, all the "?" occurences of the "whereStr" will be replaced with the safely escaped value
  • limit( nbResults, startIndex ) : set the LIMIT clause ; "startIndex" param is optionnal
  • order( fieldName, direction ) : adds a ORDER BY clause ; if "direction" is not set, it will be set to "ASC"
  • join( tableName, joinStr, fieldsArray, joinType ) : adds a JOIN clause ; if "joinType" is not set, it will be set to "INNER"
  • distinct() : adds a DISTINCT() to the query
  • groupyBy( fieldName ) : adds a GROUPY BY clause
  • assemble() : converts ou DBSelect object to an SQL SELECT string.


You can clone the project from GitHub. Alternatively, you can install using Node Package Manager (npm):

npm install node-dbi

This is a first version ; all my unit tests run successfully, but it may have bugs. Tell me if you find one ! :-)


Thanks to these great modules, which Node-DBI relies on :

And of course, these database engines, which makes the really hard work in Node-DBI :

Any SQL database engine can theorically be added, with only a quick Adapter writing. See the existing Adapters or contact me for help, if you want to add one !


To run tests manual DB configuration is required first.

MySQL is expected to be available on localhost with user, pass and DB all set to "test".

E.g. this should work:

$ mysql -hlocalhost -utest -ptest test

This can usually be achieved by installing mysql and at the mysql interactive prompt issuing the following commands:

mysql> create database test;
Query OK, 1 row affected (0.00 sec)
mysql> grant all on test.* to 'test'@'localhost' identified by 'test';
Query OK, 0 rows affected (0.08 sec)

PostgreSQL is similar to MySQL, e.g. this should work:

$ $ psql -U test -W test
Password for user test: (manually typing "test" here)

Once PostgreSQL is installed this can usually be achieved with by issuing the following commands at the psql interactive prompt:

postgres=# create user test password 'test';
postgres=# create database test owner test;

Driver Differences

Currently the PostgreSQL driver does not support getLastInsertId().

MySQL drivers return JavaScript Date objects when the table data is a date, other drivers do not.


Node-DBI is licensed under the MIT license.

Jump to Line
Something went wrong with that request. Please try again.