Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with
or
.
Download ZIP
A SQL database abstraction layer strongly inspired by the PHP Zend Framework Zend_Db API, with support of multiple Node.js database engines
JavaScript Shell Makefile
tree: 5d279e500d

Fetching latest commit…

Cannot retrieve the latest commit at this time

Failed to load latest commit information.
lib
test
.gitignore
README.md
index.js
package.json

README.md

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: (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 )
        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.

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

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

Dependencies

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 !

Testing

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';
CREATE ROLE
postgres=# create database test owner test;
CREATE DATABASE

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.

License

Node-DBI is licensed under the MIT license.

Something went wrong with that request. Please try again.