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 );
dbWrapper.connect();
// ** fetchAll
dbWrapper.fetchAll('SELECT * FROM user', null, function(err, result) {
if( ! result )
console.dir(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 )
console.dir(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 )
console.dir(result);
// "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 )
console.dir(result);
// "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 )
select.where('verified=1');
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.
The DBWrapper Javascript class, which is the only visible part on top on the different database engines adapters, provides the following methods :
- connect() : tell DbWrapper to connect to the database.
- 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.
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 using AND
- if value is not null, all the "?" occurences in whereStr will be replaced with the safely escaped value
- value may be an array, it will be mapped to a parenthesized SQL list
- the clause will be surrounded with parenthesis in the generated SQL, this way
.where('id=? OR name=?')will work like it does in ZendDb.
- orWhere( whereStr, value ) : just like where but adds a WHERE clause using OR
- whereGroup( num ) :
- opens num parenthetical groupings to WHERE clause (ie adds num open parentheses)
- num defaults to 1
- whereGroupClose( num ) :
- closes num parenthetical groupings of WHERE clause (ie adds num closed parentheses)
- num defaults to 1
- will not close groups that do not exist
- open groups will be closed automatically
- limit( nbResults, startIndex ) :
- set the LIMIT clause
- startIndex param is optional
- 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 :
- async
- underscore
- vows for the unit tests
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.
See test/config.js for databases setup.
According to Travis CI requirements, MySQL is expected to be available on localhost with user "root", empty password and DB "node_dbi_test".
E.g. this should work:
$ mysql -hlocalhost -uroot node_dbi_test
This can usually be achieved by installing mysql and at the mysql interactive prompt issuing the following commands:
mysql> create database node_dbi_test;
Query OK, 1 row affected (0.00 sec)
mysql> grant all on node_dbi_test.* to 'root'@'localhost';
Query OK, 0 rows affected (0.08 sec)
PostgreSQL is similar to MySQL, e.g. this should work:
$ $ psql -U postgres -W node_dbi_test
Password for user postgres: (manually typing empty password here)
Once PostgreSQL is installed this can usually be achieved with by issuing the following commands at the psql interactive prompt:
postgres=# create user postgres password '';
CREATE ROLE
postgres=# create database node_dbi_test owner postgres;
CREATE DATABASE
Currently the PostgreSQL driver does not support getLastInsertId().
MySQL and PostgreSQL drivers return JavaScript Date objects when the table data is a date, while SQLite driver do not.
Node-DBI is licensed under the MIT license.