Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

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
tree: e93aa95b9c

Fetching latest commit…

Cannot retrieve the latest commit at this time

Failed to load latest commit information.
lib
test
.gitignore
.travis.yml
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 );
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.

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

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.

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

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.