Node-DBI

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.

Usage

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:

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%' )
	.where( 'removal_date=?', null ) // null -> NULL
	.where( 'nickname=?', undefined ) // other falsy-but-not-Numbers values -> empty String
	.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.

DBWrapper Class

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
• close() : tell DbWrapper to close the database connection.

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 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.

Install

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

npm install node-dbi

All my unit tests run successfully, but well, it still may have bugs. Tell me if you find one ! 😃

Dependencies

Node-DBI supports these database engines, which makes the really hard work :

• mysql
• mysql-libmysqlclient
• sqlite3
• pg

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 !

Testing

To run tests manual DB configuration is required first.

Copy test/config.js.dist to test/config.js, and edit this JS file for databases setup if needed.

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

Driver Differences

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.

License

Node-DBI is licensed under the MIT license.