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.
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.
The example below demonstates the Node-DBI usage:
var DBWrapper = DBWrapper;var DBExpr = 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 = '[DB engine adapter name]' dbConnectionConfig ;dbWrapper;// ** fetchAlldbWrapper;// ** fetchRow ( + a safely escaped value )dbWrapper;// ** fetchCol (if you dont' have values to escape, the 2nd param can be an empty Array or "null")dbWrapper;// ** fetchOnedbWrapper;// ** 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: 'NOW()' ;dbWrapper;// ** update ( here the fist name is used as a raw String, but the last name is safely escaped )var JohnDataUpdate = rank: '1' ;dbWrapper;// ** remove ( this time, both values are safely escaped )dbWrapper;// Easy SQL String buildingvar select = dbWrapper// null -> NULL// other falsy-but-not-Numbers values -> empty String;if reqparamsonlyVerifiedAccountsselect;console;//outputs the SQL query for debug purpose// You can retrieve the data of this DBSelect with a "fetch" method...dbWrapper;// ..or you can trigger a "fetch" method directly on it !select;// When you have finished working with the database, you can close the connectiondbWrapper;
See the unit tests in the "test/" folder for more examples.
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 :
.where('id=? OR name=?')will work like it does in ZendDb.
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 ! :-)
Node-DBI supports these database engines, which makes the really hard work :
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.
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
Currently the PostgreSQL driver does not support getLastInsertId().
Node-DBI is licensed under the MIT license.