{"_id":"node-dbi","_rev":"45-c053f14c709eed2278face61f5d0cca6","name":"node-dbi","description":"A Database abstraction layer for Node.js, bundled with several DB engines adapters","dist-tags":{"latest":"0.7.1"},"versions":{"0.2.0":{"name":"node-dbi","description":"A Database abstraction layer for Node.js, bundled with several DB engines adapters","version":"0.2.0","homepage":"https://github.com/DrBenton/Node-DBI","repository":{"type":"git","url":"git://github.com/DrBenton/Node-DBI.git"},"author":{"name":"Dr. Benton","url":"http://www.dr-benton.com"},"main":"dbWrapper.js","directories":{"lib":"lib","test":"test"},"dependencies":{"async":"","underscore":""},"scripts":{"test":"node test/all.js"},"keywords":["database","SQL","abstraction","common","mysql","sqlite"],"engines":{"node":">= 0.4.4 < 0.5.0"},"_id":"node-dbi@0.2.0","_engineSupported":true,"_npmVersion":"0.3.18","_nodeVersion":"v0.5.0-pre","files":[""],"_defaultsLoaded":true,"dist":{"shasum":"47b14cb0a30d7e764d54685c3f0315b1d6cd1fcf","tarball":"https://registry.npmjs.org/node-dbi/-/node-dbi-0.2.0.tgz","integrity":"sha512-TOkiKfCmKOO2m82pODHpV6EjAdaCcdMnvxUMv+rJq4J6udWfM18+F4Q/u0C7xClf0Q/cuoOCSfZHNdarNojhtA==","signatures":[{"keyid":"SHA256:jl3bwswu80PjjokCgh0o2w5c2U4LhQAE57gj9cz1kzA","sig":"MEQCIELjm1da9GYk817Fm+kJW+KSvJ+wut5TB1Afi4LG8zXmAiAEH3n0cXRgWWebn4R4NkT/mSKyAfEiWn7FtQv6Wb3wjg=="}]}},"0.3.0":{"name":"node-dbi","description":"A Database abstraction layer for Node.js, bundled with several DB engines adapters","version":"0.3.0","homepage":"https://github.com/DrBenton/Node-DBI","repository":{"type":"git","url":"git://github.com/DrBenton/Node-DBI.git"},"author":{"name":"Dr. Benton","url":"http://www.dr-benton.com"},"main":"dbWrapper.js","directories":{"lib":"lib","test":"test"},"dependencies":{"async":"","underscore":""},"scripts":{"test":"node test/all.js"},"keywords":["database","SQL","abstraction","common","mysql","sqlite"],"engines":{"node":">= 0.4.4 < 0.5.0"},"_id":"node-dbi@0.3.0","_engineSupported":true,"_npmVersion":"0.3.18","_nodeVersion":"v0.5.0-pre","dist":{"shasum":"f38fdf62344d745d0694f19f477746c3017c9a34","tarball":"https://registry.npmjs.org/node-dbi/-/node-dbi-0.3.0.tgz","integrity":"sha512-QafgMsvS2Q8RVQmhXEUx7jctSLSttViJMz6GcXZmt3n8jgqiWOgnBkqJGyCW2oEAzZbfIl+216pIPbHhJefoPA==","signatures":[{"keyid":"SHA256:jl3bwswu80PjjokCgh0o2w5c2U4LhQAE57gj9cz1kzA","sig":"MEUCIC7QN04qnhnQImVeA12F4t0+9n4VKzzeIkIaXpUlC6tJAiEAlRyDt2sl6O8QSFRGaugLfjISuCA4E2oXip0f/Wg4VVg="}]}},"0.4.0":{"name":"node-dbi","description":"A Database abstraction layer for Node.js, bundled with several DB engines adapters","version":"0.4.0","homepage":"https://github.com/DrBenton/Node-DBI","repository":{"type":"git","url":"git://github.com/DrBenton/Node-DBI.git"},"author":{"name":"Dr. Benton","url":"http://www.dr-benton.com"},"main":"dbWrapper.js","directories":{"lib":"lib","test":"test"},"dependencies":{"async":"","underscore":""},"scripts":{"test":"node test/all.js"},"keywords":["database","SQL","abstraction","common","mysql","sqlite"],"engines":{"node":">= 0.4.4 < 0.5.0"},"_id":"node-dbi@0.4.0","_engineSupported":true,"_npmVersion":"0.3.18","_nodeVersion":"v0.5.0-pre","files":[""],"_defaultsLoaded":true,"dist":{"shasum":"9be87882fd7caf18c9c03658edffdecbe24c9fc2","tarball":"https://registry.npmjs.org/node-dbi/-/node-dbi-0.4.0.tgz","integrity":"sha512-erftEJ2JEf3Q64dVZWED/1RqXSawG6qRwDEw59fv6lwreAcOOeqAXsHxc6U/sTS7xvv6dog+kHs6itIPghn9nA==","signatures":[{"keyid":"SHA256:jl3bwswu80PjjokCgh0o2w5c2U4LhQAE57gj9cz1kzA","sig":"MEYCIQDUScr7GFPW/f4/N/0pmtxe4bU0B5FdiGzbUD9vduOePAIhAOfwUsJykVZkorKOpZTgA8FeZCqfevaPI/2eNHjEKmgZ"}]}},"0.4.1":{"name":"node-dbi","description":"A Database abstraction layer for Node.js, bundled with several DB engines adapters","version":"0.4.1","homepage":"https://github.com/DrBenton/Node-DBI","repository":{"type":"git","url":"git://github.com/DrBenton/Node-DBI.git"},"author":{"name":"Dr. Benton","url":"http://www.dr-benton.com"},"main":"dbWrapper.js","directories":{"lib":"lib","test":"test"},"dependencies":{"async":"","underscore":""},"scripts":{"test":"node test/all.js"},"keywords":["database","SQL","abstraction","common","mysql","sqlite"],"engines":{"node":">= 0.4.4 < 0.5.0"},"_npmJsonOpts":{"file":"/home/olivier/.npm/node-dbi/0.4.1/package/package.json","wscript":false,"contributors":false,"serverjs":false},"_id":"node-dbi@0.4.1","devDependencies":{},"_engineSupported":true,"_npmVersion":"1.0.17","_nodeVersion":"v0.4.10-pre","_defaultsLoaded":true,"dist":{"shasum":"2a2af44d6466775656045993884080b398643ddb","tarball":"https://registry.npmjs.org/node-dbi/-/node-dbi-0.4.1.tgz","integrity":"sha512-TaH32h0YBqlMZdYEMNk7u2IDqM7m1Mi/KtZVkgOF4JNcYTurykwWIzrnoba8OkyZy+38nWIESA0YrtFWuGBWgw==","signatures":[{"keyid":"SHA256:jl3bwswu80PjjokCgh0o2w5c2U4LhQAE57gj9cz1kzA","sig":"MEQCICUxwfFRw6iAnsuv8YOi7Eqsd895RPTRfgubb054Be60AiAiAcluDPtwVWLWhC0kIu3JnBa+wgw9epHUqb2ah6dh/g=="}]}},"0.4.2":{"name":"node-dbi","description":"A Database abstraction layer for Node.js, bundled with several DB engines adapters","version":"0.4.2","homepage":"https://github.com/DrBenton/Node-DBI","repository":{"type":"git","url":"git://github.com/DrBenton/Node-DBI.git"},"author":{"name":"Dr. Benton","url":"http://www.dr-benton.com"},"main":"dbWrapper.js","directories":{"lib":"lib","test":"test"},"dependencies":{"async":"","underscore":""},"scripts":{"test":"node test/all.js"},"keywords":["database","SQL","abstraction","common","mysql","sqlite"],"engines":{"node":">= 0.4.4 < 0.5.0"},"_npmJsonOpts":{"file":"/home/olivier/.npm/node-dbi/0.4.2/package/package.json","wscript":false,"contributors":false,"serverjs":false},"_id":"node-dbi@0.4.2","devDependencies":{},"_engineSupported":true,"_npmVersion":"1.0.17","_nodeVersion":"v0.4.10-pre","_defaultsLoaded":true,"dist":{"shasum":"b807a1b32aa2c3964f7d174b3b48493fb84e1658","tarball":"https://registry.npmjs.org/node-dbi/-/node-dbi-0.4.2.tgz","integrity":"sha512-F/m+L/tExcitVYMDvcibFxBWgyMKV1r3+8c96H1z+QjYnbH0xbcqH6VglC2REaGDOUrqkgnXr6XCXIGw5JnJsA==","signatures":[{"keyid":"SHA256:jl3bwswu80PjjokCgh0o2w5c2U4LhQAE57gj9cz1kzA","sig":"MEYCIQDGahlsGoqnTGXsk38QqjCoXQcpOJrWUtwCO1QjgjPL4gIhAO2QoP5x2a0UyA0c/acXWMUTQ9wuFspS93wIYZ/ZgIjh"}]},"maintainers":[{"name":"dr-benton","email":"contact@dr-benton.com"}]},"0.5.0":{"name":"node-dbi","description":"A Database abstraction layer for Node.js, bundled with several DB engines adapters","version":"0.5.0","homepage":"https://github.com/DrBenton/Node-DBI","repository":{"type":"git","url":"git://github.com/DrBenton/Node-DBI.git"},"authors":["Dr. Benton (http://github.com/DrBenton)","David Schoen <dave@lyte.id.au>","Fabian Bornhofen (http://fabianbornhofen.blogspot.com)"],"main":"dbWrapper.js","directories":{"lib":"lib","test":"test"},"dependencies":{"async":"","underscore":"","mysql-libmysqlclient":"1.3.x","mysql":"0.9.x","sqlite3":"2.1.x","pg":"0.8.x"},"devDependencies":{"vows":"0.6.x"},"optionalDependencies":{"mysql-libmysqlclient":"1.3.x","mysql":"0.9.x","sqlite3":"2.1.x","pg":"0.8.x"},"scripts":{"test":"node test/all.js"},"keywords":["database","SQL","abstraction","common","mysql","sqlite","pg","postgres","postgresql"],"engines":{"node":">= 0.4.4 < 0.9.0"},"# vim: ts=2 sw=2 et ":null,"readme":"# Node-DBI\n\nNode-DBI is a SQL database abstraction layer library, strongly inspired by the PHP Zend Framework [Zend_Db API](http://framework.zend.com/manual/en/zend.db.html).\nIt provides unified functions to work with multiple database engines, through Adapters classes.\nAt this time, supported engines are [mysql](https://github.com/felixge/node-mysql), [mysql-libmysqlclient](https://github.com/Sannis/node-mysql-libmysqlclient), [sqlite3](https://github.com/developmentseed/node-sqlite3) and [pg](http://github.com/brianc/node-postgres).\n\nIt provides __DBWrapper__ and __DBSelect__ Javascript classes, described later on this document.\n\n## Usage\n\nNode-DBI is primarily an abstraction layer library ; it allows you to have a \"database-agnostic\" application, with a single API for multiple databases engines.\n\nIt provides high-level functions to fecth, insert, update and remove data from the database.\nIt 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.\n\nNode-DBI imitates the API of the great Open Source PHP database abstraction layer of the [Zend Framework](http://framework.zend.com/), [Zend_Db](http://framework.zend.com/manual/en/zend.db.html), used by thousands of Web developers for several years.\n\nThe example below demonstates the Node-DBI usage:\n(you can also look this [Gist](https://gist.github.com/923149) for the same code with syntax highlighting)\n\n    var DBWrapper = require('node-dbi').DBWrapper; \n    var DBExpr = require('node-dbi').DBExpr; \n    var dbConnectionConfig = { host: 'localhost', user: 'test', password: 'test', database: 'test' };\n    \n    // Replace the adapter name with \"mysql\", \"mysql-libmysqlclient\", \"sqlite3\" or \"pg\" on the following line :\n    dbWrapper = new DBWrapper( '[DB engine adapter name]', dbConnectionConfig );\n    \n    \n    // ** fetchAll\n    dbWrapper.fetchAll('SELECT * FROM user', null, function(err, result) {\n      if( ! result )\n        console.dir(result);\n      // \"result\" is an Array with a hash for every returned row\n    } );\n    \n    // ** fetchRow ( +  a safely escaped value ) \n    dbWrapper.fetchRow('SELECT * FROM user WHERE first_name=?', ['John'], function(err, result) {\n      if( ! result )\n        console.dir(result);\n      // this time, \"result\" is a single hash (the first returned row)\n    } );\n    \n    // ** fetchCol  (if you dont' have values to escape, the 2nd param can be an empty Array or \"null\")\n    dbWrapper.fetchCol('SELECT first_name FROM user ORDER BY fist_name', null, function(err, result) {\n      if( ! err )\n        console.dir(result);\n      // \"result\" is an Array with all the names of our users, sorted alphabetically\n    } );\n    \n    // ** fetchOne\n    dbWrapper.fetchOne('SELECT fist_name FROM user ORDER BY rank DESC LIMIT 1', [], function(err, result) {\n      if( ! err )\n        console.dir(result);\n      // \"result\" is the first_name of our best user\n    } );\n    \n    // ** insert   (DBExpr force somes values to be used \"as is\", without safe escape : it is useful for SQL functions like \"NOW()\", \"COUNT(*)\", \"SUM(rank)\"... )\n    var JohnData = { first_name: 'John', last_name: 'Foo', rank: '3', date_created: new DBExpr('NOW()') };\n    dbWrapper.insert('user', JohnData , function(err) {\n      if( ! err )\n        console.log( 'John ID : ' + dbWrapper.getLastInsertId() );\n      // John has been inserted in our table, with its properties safely escaped\n    } );\n    \n    // ** update  ( here the fist name is used as a raw String, but the last name is safely escaped ) \n    var JohnDataUpdate = { rank: '1' };\n    dbWrapper.update('user', JohnDataUpdate , [ 'first_name=\\'John\\'', ['last_name=?', 'Foo'] ], function(err) {\n      // John is now our best user. Congratulations, John !\n    } );\n    \n    // ** remove  ( this time, both values are safely escaped ) \n    dbWrapper.remove('user', [ ['first_name LIKE ?', '%John%'], ['last_name=?', 'Foo'] ] , function(err) {\n      // John left at the height of its glory.\n    } );\n    \n    \n    // Easy SQL String building\n    var select = dbWrapper.getSelect()\n      .from('user', ['first_name', 'last_name'] )\n      .where( 'enabled=1' )\n      .where( 'id=?', 10 )\n      .where( 'last_name LIKE ?', '%Foo%' )\n      .order( 'last_name' )\n      .limit( 10 );\n      \n    if( req.params.onlyVerifiedAccounts )\n       select.where('verified=1');\n       \n    console.log( select.assemble() );//outputs the SQL query for debug purpose \n      \n    // You can retrieve the data of this DBSelect with a \"fetch\" method...\n    dbWrapper.fetchAll( select, function(err) {} );\n    \n    // ..or you can trigger a \"fetch\" method directly on it ! \n    select.fetchAll( function(err) {} );\n\n\n    // When you have finished working with the database, you can close the connection\n    dbWrapper.close( function(err) {console.log('Connection closed !');} );\n    \n    \nSee the unit tests in the \"test/\" folder for more examples.\n           \n\n## DBWrapper Class\n\nThe __DBWrapper__ Javascript class, which is the only visible part on top on the different database engines adapters, provides the following methods :\n\n * __fetchAll( sql, bind, callback )__ : fetches all SQL result rows as a Array.\n * __fetchRow( sql, bind, callback )__ : fetches the first row of the SQL result.\n * __fetchCol( sql, bind, callback )__ : fetches the first column of all SQL result rows as an Array.\n * __fetchOne( sql, bind, callback )__ : fetches the first column of the first row of the SQL result.\n * __insert( tableName, data, callback )__ : inserts a table row with specified data, as a hash.\n * __update( tableName, data, where, callback )__ : updates table rows with specified data (as a hash) based on a WHERE clause.\n * __remove( tableName, where, callback )__ : deletes table rows based on a WHERE clause.\n * __getLastInsertId()__ : returns the last inserted Id\n * __isConnected()__ : tells us if the DbWrapper is connected to its database. \n * __getSelect()__ : returns a DBSelect\n\nAll these methods returns exactly the sames results, whatever the chosen database engine is.  \n\n\n## DBSelect Class\n\nFurthermore, Node-DBI provides a DBSelect class which allows easy and readable SQL \"SELECT\" Strings building. At the moment, it provides the following methods :\n\n * __from( tableName, fieldsArray )__ : adds a table in the FROM clause, and adds its fields to the SELECT\n * __where( whereStr, value )__ : adds a WHERE clause ; if \"value\" is not null, all the \"?\" occurences of the \"whereStr\" will be replaced with the safely escaped value\n * __limit( nbResults, startIndex )__ : set the LIMIT clause ; \"startIndex\" param is optionnal\n * __order( fieldName, direction )__ : adds a ORDER BY clause ; if \"direction\" is not set, it will be set to \"ASC\"\n * __join( tableName, joinStr, fieldsArray, joinType )__ : adds a JOIN clause ; if \"joinType\" is not set, it will be set to \"INNER\"\n * __distinct()__ : adds a DISTINCT() to the query\n * __groupyBy( fieldName )__ : adds a GROUPY BY clause\n * __assemble()__ : converts ou DBSelect object to an SQL SELECT string.\n\n\n## Install\n\nYou can clone the project from [GitHub](https://github.com/DrBenton/Node-DBI).\nAlternatively, you can install using Node Package Manager (npm):\n\n    npm install node-dbi\n    \nThis is a first version ; all my unit tests run successfully, but it may have bugs. Tell me if you find one ! :-)\n\n\n## Dependencies\n\nThanks to these great modules, which Node-DBI relies on :\n\n * [async](https://github.com/caolan/async)\n * [underscore](https://github.com/grayrest/underscore)\n * [vows](https://github.com/cloudhead/vows) for the unit tests\n\nAnd of course, these database engines, which makes the really hard work in Node-DBI :\n\n * [mysql](https://github.com/felixge/node-mysql)\n * [mysql-libmysqlclient](https://github.com/Sannis/node-mysql-libmysqlclient)\n * [sqlite3](https://github.com/developmentseed/node-sqlite3)\n * [pg](http://github.com/brianc/node-postgres)\n \nAny 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 !\n\n## Testing\n\nTo run tests manual DB configuration is required first.\n\nMySQL is expected to be available on localhost with user, pass and DB all set to \"test\".\n\nE.g. this should work:\n\n\t$ mysql -hlocalhost -utest -ptest test\n\nThis can usually be achieved by installing mysql and at the mysql interactive prompt issuing the following commands:\n\n\tmysql> create database test;\n\tQuery OK, 1 row affected (0.00 sec)\n\tmysql> grant all on test.* to 'test'@'localhost' identified by 'test';\n\tQuery OK, 0 rows affected (0.08 sec)\n\nPostgreSQL is similar to MySQL, e.g. this should work:\n\n\t$ $ psql -U test -W test\n\tPassword for user test: (manually typing \"test\" here)\n\nOnce PostgreSQL is installed this can usually be achieved with by issuing the following commands at the psql interactive prompt:\n\n\tpostgres=# create user test password 'test';\n\tCREATE ROLE\n\tpostgres=# create database test owner test;\n\tCREATE DATABASE\n\n## Driver Differences\n\nCurrently the PostgreSQL driver does not support getLastInsertId().\n\nMySQL drivers return JavaScript Date objects when the table data is a date, other drivers do not.\n\n## License\n\nNode-DBI is licensed under the MIT license.\n","_id":"node-dbi@0.5.0","dist":{"shasum":"d1bb22be8dec56edc4329924e25b66a500f48da3","tarball":"https://registry.npmjs.org/node-dbi/-/node-dbi-0.5.0.tgz","integrity":"sha512-73ZDAJY/7Ai1Zcv46VtaH/URmmVGBwKXxvL6OkZyXsPNTZwXlr5kAKYJuvf6pMwmY73nqSaMl2Tb0FcBAz2gYA==","signatures":[{"keyid":"SHA256:jl3bwswu80PjjokCgh0o2w5c2U4LhQAE57gj9cz1kzA","sig":"MEQCICARvH2PTyXlM123EobQL4/QrA+ioxsQhFZTqbTjgktFAiBBaLfrPl1ZAU/ibeZH5mY1NX9/J/4yZTJSxQ/Q2v67kg=="}]},"maintainers":[{"name":"dr-benton","email":"contact@dr-benton.com"}]},"0.6.0":{"name":"node-dbi","description":"A Database abstraction layer for Node.js, bundled with several DB engines adapters","version":"0.6.0","homepage":"https://github.com/DrBenton/Node-DBI","repository":{"type":"git","url":"git://github.com/DrBenton/Node-DBI.git"},"authors":["Dr. Benton (http://github.com/DrBenton)","David Schoen <dave@lyte.id.au>","Fabian Bornhofen (http://fabianbornhofen.blogspot.com)","Michael Dwyer (http://github.com/kalifg)"],"main":"dbWrapper.js","directories":{"lib":"lib","test":"test"},"dependencies":{"async":"","underscore":"","mysql-libmysqlclient":"1.3.x","mysql":"0.9.x","sqlite3":"2.1.x","pg":"0.8.x"},"devDependencies":{"vows":"0.6.x"},"optionalDependencies":{"mysql-libmysqlclient":"1.3.x","mysql":"0.9.x","sqlite3":"2.1.x","pg":"0.8.x"},"scripts":{"test":"node test/all.js"},"keywords":["database","SQL","abstraction","common","mysql","sqlite","pg","postgres","postgresql"],"engines":{"node":">= 0.4.4 < 0.9.0"},"# vim: ts=2 sw=2 et ":null,"readme":"# Node-DBI\n\nNode-DBI is a SQL database abstraction layer library, strongly inspired by the PHP Zend Framework [Zend_Db API](http://framework.zend.com/manual/en/zend.db.html).\nIt provides unified functions to work with multiple database engines, through Adapters classes.\nAt this time, supported engines are [mysql](https://github.com/felixge/node-mysql), [mysql-libmysqlclient](https://github.com/Sannis/node-mysql-libmysqlclient), [sqlite3](https://github.com/developmentseed/node-sqlite3) and [pg](http://github.com/brianc/node-postgres).\n\nIt provides __DBWrapper__ and __DBSelect__ Javascript classes, described later on this document.\n\n## Usage\n\nNode-DBI is primarily an abstraction layer library ; it allows you to have a \"database-agnostic\" application, with a single API for multiple databases engines.\n\nIt provides high-level functions to fecth, insert, update and remove data from the database.\nIt 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.\n\nNode-DBI imitates the API of the great Open Source PHP database abstraction layer of the [Zend Framework](http://framework.zend.com/), [Zend_Db](http://framework.zend.com/manual/en/zend.db.html), used by thousands of Web developers for several years.\n\nThe example below demonstates the Node-DBI usage:\n(you can also look this [Gist](https://gist.github.com/923149) for the same code with syntax highlighting)\n\n```javascript\nvar DBWrapper = require('node-dbi').DBWrapper; \nvar DBExpr = require('node-dbi').DBExpr; \nvar dbConnectionConfig = { host: 'localhost', user: 'test', password: 'test', database: 'test' };\n\n// Replace the adapter name with \"mysql\", \"mysql-libmysqlclient\", \"sqlite3\" or \"pg\" on the following line :\ndbWrapper = new DBWrapper( '[DB engine adapter name]', dbConnectionConfig );\n\n\n// ** fetchAll\ndbWrapper.fetchAll('SELECT * FROM user', null, function(err, result) {\n\tif( ! result )\n\t\tconsole.dir(result);\n\t// \"result\" is an Array with a hash for every returned row\n} );\n\n// ** fetchRow ( +  a safely escaped value ) \ndbWrapper.fetchRow('SELECT * FROM user WHERE first_name=?', ['John'], function(err, result) {\n\tif( ! result )\n\t\tconsole.dir(result);\n\t// this time, \"result\" is a single hash (the first returned row)\n} );\n\n// ** fetchCol  (if you dont' have values to escape, the 2nd param can be an empty Array or \"null\")\ndbWrapper.fetchCol('SELECT first_name FROM user ORDER BY fist_name', null, function(err, result) {\n\tif( ! err )\n\t\tconsole.dir(result);\n\t// \"result\" is an Array with all the names of our users, sorted alphabetically\n} );\n\n// ** fetchOne\ndbWrapper.fetchOne('SELECT fist_name FROM user ORDER BY rank DESC LIMIT 1', [], function(err, result) {\n\tif( ! err )\n\t\tconsole.dir(result);\n\t// \"result\" is the first_name of our best user\n} );\n\n// ** insert   (DBExpr force somes values to be used \"as is\", without safe escape : it is useful for SQL functions like \"NOW()\", \"COUNT(*)\", \"SUM(rank)\"... )\nvar JohnData = { first_name: 'John', last_name: 'Foo', rank: '3', date_created: new DBExpr('NOW()') };\ndbWrapper.insert('user', JohnData , function(err) {\n\tif( ! err )\n\t\tconsole.log( 'John ID : ' + dbWrapper.getLastInsertId() );\n\t// John has been inserted in our table, with its properties safely escaped\n} );\n\n// ** update  ( here the fist name is used as a raw String, but the last name is safely escaped ) \nvar JohnDataUpdate = { rank: '1' };\n\tdbWrapper.update('user', JohnDataUpdate , [ 'first_name=\\'John\\'', ['last_name=?', 'Foo'] ], function(err) {\n\t// John is now our best user. Congratulations, John !\n} );\n\n// ** remove  ( this time, both values are safely escaped ) \ndbWrapper.remove('user', [ ['first_name LIKE ?', '%John%'], ['last_name=?', 'Foo'] ] , function(err) {\n\t// John left at the height of its glory.\n} );\n\n\n// Easy SQL String building\nvar select = dbWrapper.getSelect()\n\t.from('user', ['first_name', 'last_name'] )\n\t.where( 'enabled=1' )\n\t.where( 'id=?', 10 )\n\t.where( 'last_name LIKE ?', '%Foo%' )\n\t.order( 'last_name' )\n\t.limit( 10 );\n\nif( req.params.onlyVerifiedAccounts )\n\tselect.where('verified=1');\n\nconsole.log( select.assemble() );//outputs the SQL query for debug purpose \n\n// You can retrieve the data of this DBSelect with a \"fetch\" method...\ndbWrapper.fetchAll( select, function(err) {} );\n\n// ..or you can trigger a \"fetch\" method directly on it ! \nselect.fetchAll( function(err) {} );\n\n\n// When you have finished working with the database, you can close the connection\ndbWrapper.close( function(err) {console.log('Connection closed !');} );\n```    \n    \nSee the unit tests in the \"test/\" folder for more examples.\n           \n\n## DBWrapper Class\n\nThe __DBWrapper__ Javascript class, which is the only visible part on top on the different database engines adapters, provides the following methods :\n\n * __fetchAll( sql, bind, callback )__ : fetches all SQL result rows as a Array.\n * __fetchRow( sql, bind, callback )__ : fetches the first row of the SQL result.\n * __fetchCol( sql, bind, callback )__ : fetches the first column of all SQL result rows as an Array.\n * __fetchOne( sql, bind, callback )__ : fetches the first column of the first row of the SQL result.\n * __insert( tableName, data, callback )__ : inserts a table row with specified data, as a hash.\n * __update( tableName, data, where, callback )__ : updates table rows with specified data (as a hash) based on a WHERE clause.\n * __remove( tableName, where, callback )__ : deletes table rows based on a WHERE clause.\n * __getLastInsertId()__ : returns the last inserted Id\n * __isConnected()__ : tells us if the DbWrapper is connected to its database. \n * __getSelect()__ : returns a DBSelect\n\nAll these methods returns exactly the sames results, whatever the chosen database engine is.  \n\n\n## DBSelect Class\n\nFurthermore, Node-DBI provides a DBSelect class which allows easy and readable SQL \"SELECT\" Strings building. At the moment, it provides the following methods :\n\n * __from( tableName, fieldsArray )__ : adds a table in the FROM clause, and adds its fields to the SELECT\n * __where( whereStr, value )__:  \n     * adds a WHERE clause using AND \n     * if __value__ is not null, all the \"?\" occurences in __whereStr__ will be replaced with the safely escaped value\n     * __value__ may be an array, it will be mapped to a parenthesized SQL list\n     * the clause will be surrounded with parenthesis in the generated SQL, this way ```.where('id=? OR name=?')``` will work like it does in ZendDb.\n * __orWhere( whereStr, value )__ : just like __where__ but adds a WHERE clause using OR\n * __whereGroup( num )__ : \n   * opens __num__ parenthetical groupings to WHERE clause (ie adds __num__ open parentheses) \n   * __num__ defaults to 1\n * __whereGroupClose( num )__ :  \n   * closes __num__ parenthetical groupings of WHERE clause (ie adds __num__ closed parentheses)\n   * __num__ defaults to 1\n   * will not close groups that do not exist\n   * open groups will be closed automatically\n * __limit( nbResults, startIndex )__ : \n   * set the LIMIT clause \n   * __startIndex__ param is optional\n * __order( fieldName, direction )__ : \n   * adds a ORDER BY clause \n   * if __direction__ is not set, it will be set to \"ASC\"\n * __join( tableName, joinStr, fieldsArray, joinType )__ : \n   * adds a JOIN clause \n   * if __joinType__ is not set, it will be set to \"INNER\"\n * __distinct()__ : adds a DISTINCT() to the query\n * __groupyBy( fieldName )__ : adds a GROUPY BY clause\n * __assemble()__ : converts ou DBSelect object to an SQL SELECT string.\n\n\n## Install\n\nYou can clone the project from [GitHub](https://github.com/DrBenton/Node-DBI).\nAlternatively, you can install using Node Package Manager (npm):\n\n    npm install node-dbi\n    \nThis is a first version ; all my unit tests run successfully, but it may have bugs. Tell me if you find one ! :-)\n\n\n## Dependencies\n\nThanks to these great modules, which Node-DBI relies on :\n\n * [async](https://github.com/caolan/async)\n * [underscore](https://github.com/grayrest/underscore)\n * [vows](https://github.com/cloudhead/vows) for the unit tests\n\nAnd of course, these database engines, which makes the really hard work in Node-DBI :\n\n * [mysql](https://github.com/felixge/node-mysql)\n * [mysql-libmysqlclient](https://github.com/Sannis/node-mysql-libmysqlclient)\n * [sqlite3](https://github.com/developmentseed/node-sqlite3)\n * [pg](http://github.com/brianc/node-postgres)\n \nAny 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 !\n\n## Testing\n\nTo run tests manual DB configuration is required first.\n\nMySQL is expected to be available on localhost with user, pass and DB all set to \"test\".\n\nE.g. this should work:\n\n\t$ mysql -hlocalhost -utest -ptest test\n\nThis can usually be achieved by installing mysql and at the mysql interactive prompt issuing the following commands:\n\n\tmysql> create database test;\n\tQuery OK, 1 row affected (0.00 sec)\n\tmysql> grant all on test.* to 'test'@'localhost' identified by 'test';\n\tQuery OK, 0 rows affected (0.08 sec)\n\nPostgreSQL is similar to MySQL, e.g. this should work:\n\n\t$ $ psql -U test -W test\n\tPassword for user test: (manually typing \"test\" here)\n\nOnce PostgreSQL is installed this can usually be achieved with by issuing the following commands at the psql interactive prompt:\n\n\tpostgres=# create user test password 'test';\n\tCREATE ROLE\n\tpostgres=# create database test owner test;\n\tCREATE DATABASE\n\n## Driver Differences\n\nCurrently the PostgreSQL driver does not support getLastInsertId().\n\nMySQL drivers return JavaScript Date objects when the table data is a date, other drivers do not.\n\n## License\n\nNode-DBI is licensed under the MIT license.\n","_id":"node-dbi@0.6.0","dist":{"shasum":"428f91dc56fe800ab9cb6adf1dc988ab1e8ac2e0","tarball":"https://registry.npmjs.org/node-dbi/-/node-dbi-0.6.0.tgz","integrity":"sha512-naMCT7eQxGx8AxrrJoPN6hrMeWme1QWhkJpkPExwMuYeNHZCwMLODAy77NSloFcvSQr52WewXZ9TWZ1E9mBq6w==","signatures":[{"keyid":"SHA256:jl3bwswu80PjjokCgh0o2w5c2U4LhQAE57gj9cz1kzA","sig":"MEQCIA4d+uuG7LBFDsqyXtLMclOxETI2cHZWPKtc7ZfriLK3AiAE+gc7ltfiAsRH2ynM+UAAYpdEOBHu3KfIYYDy2UcPiA=="}]},"maintainers":[{"name":"dr-benton","email":"contact@dr-benton.com"}]},"0.6.1":{"name":"node-dbi","description":"A Database abstraction layer for Node.js, bundled with several DB engines adapters","version":"0.6.1","homepage":"https://github.com/DrBenton/Node-DBI","repository":{"type":"git","url":"git://github.com/DrBenton/Node-DBI.git"},"authors":["Dr. Benton (http://github.com/DrBenton)","David Schoen <dave@lyte.id.au>","Fabian Bornhofen (http://fabianbornhofen.blogspot.com)","Michael Dwyer (http://github.com/kalifg)"],"main":"dbWrapper.js","directories":{"lib":"lib","test":"test"},"dependencies":{"async":"","underscore":"","mysql-libmysqlclient":"1.3.x","mysql":"0.9.x","sqlite3":"2.1.x","pg":"0.8.x"},"devDependencies":{"vows":"0.6.x"},"optionalDependencies":{"mysql-libmysqlclient":"1.3.x","mysql":"0.9.x","sqlite3":"2.1.x","pg":"0.8.x"},"scripts":{"test":"node test/all.js"},"keywords":["database","SQL","abstraction","common","mysql","sqlite","pg","postgres","postgresql"],"engines":{"node":">= 0.4.4 < 0.9.0"},"# vim: ts=2 sw=2 et ":null,"readme":"# Node-DBI\n\nNode-DBI is a SQL database abstraction layer library, strongly inspired by the PHP Zend Framework [Zend_Db API](http://framework.zend.com/manual/en/zend.db.html).\nIt provides unified functions to work with multiple database engines, through Adapters classes.\nAt this time, supported engines are [mysql](https://github.com/felixge/node-mysql), [mysql-libmysqlclient](https://github.com/Sannis/node-mysql-libmysqlclient), [sqlite3](https://github.com/developmentseed/node-sqlite3) and [pg](http://github.com/brianc/node-postgres).\n\nIt provides __DBWrapper__ and __DBSelect__ Javascript classes, described later on this document.\n\n## Usage\n\nNode-DBI is primarily an abstraction layer library ; it allows you to have a \"database-agnostic\" application, with a single API for multiple databases engines.\n\nIt provides high-level functions to fecth, insert, update and remove data from the database.\nIt 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.\n\nNode-DBI imitates the API of the great Open Source PHP database abstraction layer of the [Zend Framework](http://framework.zend.com/), [Zend_Db](http://framework.zend.com/manual/en/zend.db.html), used by thousands of Web developers for several years.\n\nThe example below demonstates the Node-DBI usage:\n(you can also look this [Gist](https://gist.github.com/923149) for the same code with syntax highlighting)\n\n```javascript\nvar DBWrapper = require('node-dbi').DBWrapper; \nvar DBExpr = require('node-dbi').DBExpr; \nvar dbConnectionConfig = { host: 'localhost', user: 'test', password: 'test', database: 'test' };\n\n// Replace the adapter name with \"mysql\", \"mysql-libmysqlclient\", \"sqlite3\" or \"pg\" on the following line :\ndbWrapper = new DBWrapper( '[DB engine adapter name]', dbConnectionConfig );\n\n\n// ** fetchAll\ndbWrapper.fetchAll('SELECT * FROM user', null, function(err, result) {\n\tif( ! result )\n\t\tconsole.dir(result);\n\t// \"result\" is an Array with a hash for every returned row\n} );\n\n// ** fetchRow ( +  a safely escaped value ) \ndbWrapper.fetchRow('SELECT * FROM user WHERE first_name=?', ['John'], function(err, result) {\n\tif( ! result )\n\t\tconsole.dir(result);\n\t// this time, \"result\" is a single hash (the first returned row)\n} );\n\n// ** fetchCol  (if you dont' have values to escape, the 2nd param can be an empty Array or \"null\")\ndbWrapper.fetchCol('SELECT first_name FROM user ORDER BY fist_name', null, function(err, result) {\n\tif( ! err )\n\t\tconsole.dir(result);\n\t// \"result\" is an Array with all the names of our users, sorted alphabetically\n} );\n\n// ** fetchOne\ndbWrapper.fetchOne('SELECT fist_name FROM user ORDER BY rank DESC LIMIT 1', [], function(err, result) {\n\tif( ! err )\n\t\tconsole.dir(result);\n\t// \"result\" is the first_name of our best user\n} );\n\n// ** insert   (DBExpr force somes values to be used \"as is\", without safe escape : it is useful for SQL functions like \"NOW()\", \"COUNT(*)\", \"SUM(rank)\"... )\nvar JohnData = { first_name: 'John', last_name: 'Foo', rank: '3', date_created: new DBExpr('NOW()') };\ndbWrapper.insert('user', JohnData , function(err) {\n\tif( ! err )\n\t\tconsole.log( 'John ID : ' + dbWrapper.getLastInsertId() );\n\t// John has been inserted in our table, with its properties safely escaped\n} );\n\n// ** update  ( here the fist name is used as a raw String, but the last name is safely escaped ) \nvar JohnDataUpdate = { rank: '1' };\n\tdbWrapper.update('user', JohnDataUpdate , [ 'first_name=\\'John\\'', ['last_name=?', 'Foo'] ], function(err) {\n\t// John is now our best user. Congratulations, John !\n} );\n\n// ** remove  ( this time, both values are safely escaped ) \ndbWrapper.remove('user', [ ['first_name LIKE ?', '%John%'], ['last_name=?', 'Foo'] ] , function(err) {\n\t// John left at the height of its glory.\n} );\n\n\n// Easy SQL String building\nvar select = dbWrapper.getSelect()\n\t.from('user', ['first_name', 'last_name'] )\n\t.where( 'enabled=1' )\n\t.where( 'id=?', 10 )\n\t.where( 'last_name LIKE ?', '%Foo%' )\n\t.order( 'last_name' )\n\t.limit( 10 );\n\nif( req.params.onlyVerifiedAccounts )\n\tselect.where('verified=1');\n\nconsole.log( select.assemble() );//outputs the SQL query for debug purpose \n\n// You can retrieve the data of this DBSelect with a \"fetch\" method...\ndbWrapper.fetchAll( select, function(err) {} );\n\n// ..or you can trigger a \"fetch\" method directly on it ! \nselect.fetchAll( function(err) {} );\n\n\n// When you have finished working with the database, you can close the connection\ndbWrapper.close( function(err) {console.log('Connection closed !');} );\n```    \n    \nSee the unit tests in the \"test/\" folder for more examples.\n           \n\n## DBWrapper Class\n\nThe __DBWrapper__ Javascript class, which is the only visible part on top on the different database engines adapters, provides the following methods :\n\n * __fetchAll( sql, bind, callback )__ : fetches all SQL result rows as a Array.\n * __fetchRow( sql, bind, callback )__ : fetches the first row of the SQL result.\n * __fetchCol( sql, bind, callback )__ : fetches the first column of all SQL result rows as an Array.\n * __fetchOne( sql, bind, callback )__ : fetches the first column of the first row of the SQL result.\n * __insert( tableName, data, callback )__ : inserts a table row with specified data, as a hash.\n * __update( tableName, data, where, callback )__ : updates table rows with specified data (as a hash) based on a WHERE clause.\n * __remove( tableName, where, callback )__ : deletes table rows based on a WHERE clause.\n * __getLastInsertId()__ : returns the last inserted Id\n * __isConnected()__ : tells us if the DbWrapper is connected to its database. \n * __getSelect()__ : returns a DBSelect\n\nAll these methods returns exactly the sames results, whatever the chosen database engine is.  \n\n\n## DBSelect Class\n\nFurthermore, Node-DBI provides a DBSelect class which allows easy and readable SQL \"SELECT\" Strings building. At the moment, it provides the following methods :\n\n * __from( tableName, fieldsArray )__ : adds a table in the FROM clause, and adds its fields to the SELECT\n * __where( whereStr, value )__:  \n     * adds a WHERE clause using AND \n     * if __value__ is not null, all the \"?\" occurences in __whereStr__ will be replaced with the safely escaped value\n     * __value__ may be an array, it will be mapped to a parenthesized SQL list\n     * the clause will be surrounded with parenthesis in the generated SQL, this way ```.where('id=? OR name=?')``` will work like it does in ZendDb.\n * __orWhere( whereStr, value )__ : just like __where__ but adds a WHERE clause using OR\n * __whereGroup( num )__ : \n   * opens __num__ parenthetical groupings to WHERE clause (ie adds __num__ open parentheses) \n   * __num__ defaults to 1\n * __whereGroupClose( num )__ :  \n   * closes __num__ parenthetical groupings of WHERE clause (ie adds __num__ closed parentheses)\n   * __num__ defaults to 1\n   * will not close groups that do not exist\n   * open groups will be closed automatically\n * __limit( nbResults, startIndex )__ : \n   * set the LIMIT clause \n   * __startIndex__ param is optional\n * __order( fieldName, direction )__ : \n   * adds a ORDER BY clause \n   * if __direction__ is not set, it will be set to \"ASC\"\n * __join( tableName, joinStr, fieldsArray, joinType )__ : \n   * adds a JOIN clause \n   * if __joinType__ is not set, it will be set to \"INNER\"\n * __distinct()__ : adds a DISTINCT() to the query\n * __groupyBy( fieldName )__ : adds a GROUPY BY clause\n * __assemble()__ : converts ou DBSelect object to an SQL SELECT string.\n\n\n## Install\n\nYou can clone the project from [GitHub](https://github.com/DrBenton/Node-DBI).\nAlternatively, you can install using Node Package Manager (npm):\n\n    npm install node-dbi\n    \nThis is a first version ; all my unit tests run successfully, but it may have bugs. Tell me if you find one ! :-)\n\n\n## Dependencies\n\nThanks to these great modules, which Node-DBI relies on :\n\n * [async](https://github.com/caolan/async)\n * [underscore](https://github.com/grayrest/underscore)\n * [vows](https://github.com/cloudhead/vows) for the unit tests\n\nAnd of course, these database engines, which makes the really hard work in Node-DBI :\n\n * [mysql](https://github.com/felixge/node-mysql)\n * [mysql-libmysqlclient](https://github.com/Sannis/node-mysql-libmysqlclient)\n * [sqlite3](https://github.com/developmentseed/node-sqlite3)\n * [pg](http://github.com/brianc/node-postgres)\n \nAny 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 !\n\n## Testing\n\nTo run tests manual DB configuration is required first.\n\nMySQL is expected to be available on localhost with user, pass and DB all set to \"test\".\n\nE.g. this should work:\n\n\t$ mysql -hlocalhost -utest -ptest test\n\nThis can usually be achieved by installing mysql and at the mysql interactive prompt issuing the following commands:\n\n\tmysql> create database test;\n\tQuery OK, 1 row affected (0.00 sec)\n\tmysql> grant all on test.* to 'test'@'localhost' identified by 'test';\n\tQuery OK, 0 rows affected (0.08 sec)\n\nPostgreSQL is similar to MySQL, e.g. this should work:\n\n\t$ $ psql -U test -W test\n\tPassword for user test: (manually typing \"test\" here)\n\nOnce PostgreSQL is installed this can usually be achieved with by issuing the following commands at the psql interactive prompt:\n\n\tpostgres=# create user test password 'test';\n\tCREATE ROLE\n\tpostgres=# create database test owner test;\n\tCREATE DATABASE\n\n## Driver Differences\n\nCurrently the PostgreSQL driver does not support getLastInsertId().\n\nMySQL drivers return JavaScript Date objects when the table data is a date, other drivers do not.\n\n## License\n\nNode-DBI is licensed under the MIT license.\n","_id":"node-dbi@0.6.1","dist":{"shasum":"6e61c839ba0c5c60d157186f86ae3c1195faae8c","tarball":"https://registry.npmjs.org/node-dbi/-/node-dbi-0.6.1.tgz","integrity":"sha512-mVR7qkCgndD++sgDhUQogYOVXVEPawIIjtaFGU9FYF6Z9FOGI8CJo5K7s9U57GeShj+tIUZX6qdl20fZ+acq2w==","signatures":[{"keyid":"SHA256:jl3bwswu80PjjokCgh0o2w5c2U4LhQAE57gj9cz1kzA","sig":"MEYCIQCI+MC2K6WRgwlP3WvyZ/ZEZm6r7aXXPul8ewt0IzfrdgIhAPrCWhsbkQ+/ws/YnMuG8RPEaZhzOGtfaApGr0BKzoRk"}]},"maintainers":[{"name":"dr-benton","email":"contact@dr-benton.com"}]},"0.6.2":{"name":"node-dbi","description":"A Database abstraction layer for Node.js, bundled with several DB engines adapters","version":"0.6.2","homepage":"https://github.com/DrBenton/Node-DBI","repository":{"type":"git","url":"git://github.com/DrBenton/Node-DBI.git"},"authors":["Dr. Benton (http://github.com/DrBenton)","David Schoen <dave@lyte.id.au>","Fabian Bornhofen (http://fabianbornhofen.blogspot.com)","Michael Dwyer (http://github.com/kalifg)"],"main":"dbWrapper.js","directories":{"lib":"lib","test":"test"},"dependencies":{"async":"","underscore":"","mysql-libmysqlclient":"1.3.x","mysql":"0.9.6","sqlite3":"2.1.x","pg":"0.8.x"},"devDependencies":{"vows":"0.6.x"},"optionalDependencies":{"mysql-libmysqlclient":"1.3.x","mysql":"0.9.6","sqlite3":"2.1.x","pg":"0.8.x"},"scripts":{"test":"node test/all.js"},"keywords":["database","SQL","abstraction","common","mysql","sqlite","pg","postgres","postgresql"],"engines":{"node":">= 0.4.4 < 0.9.0"},"# vim: ts=2 sw=2 et ":null,"readme":"# Node-DBI\n\n[![build status](https://secure.travis-ci.org/DrBenton/Node-DBI.png)](http://travis-ci.org/DrBenton/Node-DBI)\n\nNode-DBI is a SQL database abstraction layer library, strongly inspired by the PHP Zend Framework [Zend_Db API](http://framework.zend.com/manual/en/zend.db.html).\nIt provides unified functions to work with multiple database engines, through Adapters classes.\nAt this time, supported engines are [mysql](https://github.com/felixge/node-mysql), [mysql-libmysqlclient](https://github.com/Sannis/node-mysql-libmysqlclient), [sqlite3](https://github.com/developmentseed/node-sqlite3) and [pg](http://github.com/brianc/node-postgres).\n\nIt provides __DBWrapper__ and __DBSelect__ Javascript classes, described later on this document.\n\n## Usage\n\nNode-DBI is primarily an abstraction layer library ; it allows you to have a \"database-agnostic\" application, with a single API for multiple databases engines.\n\nIt provides high-level functions to fecth, insert, update and remove data from the database.\nIt 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.\n\nNode-DBI imitates the API of the great Open Source PHP database abstraction layer of the [Zend Framework](http://framework.zend.com/), [Zend_Db](http://framework.zend.com/manual/en/zend.db.html), used by thousands of Web developers for several years.\n\nThe example below demonstates the Node-DBI usage:\n(you can also look this [Gist](https://gist.github.com/923149) for the same code with syntax highlighting)\n\n```javascript\nvar DBWrapper = require('node-dbi').DBWrapper; \nvar DBExpr = require('node-dbi').DBExpr; \nvar dbConnectionConfig = { host: 'localhost', user: 'test', password: 'test', database: 'test' };\n\n// Replace the adapter name with \"mysql\", \"mysql-libmysqlclient\", \"sqlite3\" or \"pg\" on the following line :\ndbWrapper = new DBWrapper( '[DB engine adapter name]', dbConnectionConfig );\ndbWrapper.connect();\n\n// ** fetchAll\ndbWrapper.fetchAll('SELECT * FROM user', null, function(err, result) {\n\tif( ! result )\n\t\tconsole.dir(result);\n\t// \"result\" is an Array with a hash for every returned row\n} );\n\n// ** fetchRow ( +  a safely escaped value ) \ndbWrapper.fetchRow('SELECT * FROM user WHERE first_name=?', ['John'], function(err, result) {\n\tif( ! result )\n\t\tconsole.dir(result);\n\t// this time, \"result\" is a single hash (the first returned row)\n} );\n\n// ** fetchCol  (if you dont' have values to escape, the 2nd param can be an empty Array or \"null\")\ndbWrapper.fetchCol('SELECT first_name FROM user ORDER BY fist_name', null, function(err, result) {\n\tif( ! err )\n\t\tconsole.dir(result);\n\t// \"result\" is an Array with all the names of our users, sorted alphabetically\n} );\n\n// ** fetchOne\ndbWrapper.fetchOne('SELECT fist_name FROM user ORDER BY rank DESC LIMIT 1', [], function(err, result) {\n\tif( ! err )\n\t\tconsole.dir(result);\n\t// \"result\" is the first_name of our best user\n} );\n\n// ** insert   (DBExpr force somes values to be used \"as is\", without safe escape : it is useful for SQL functions like \"NOW()\", \"COUNT(*)\", \"SUM(rank)\"... )\nvar JohnData = { first_name: 'John', last_name: 'Foo', rank: '3', date_created: new DBExpr('NOW()') };\ndbWrapper.insert('user', JohnData , function(err) {\n\tif( ! err )\n\t\tconsole.log( 'John ID : ' + dbWrapper.getLastInsertId() );\n\t// John has been inserted in our table, with its properties safely escaped\n} );\n\n// ** update  ( here the fist name is used as a raw String, but the last name is safely escaped ) \nvar JohnDataUpdate = { rank: '1' };\n\tdbWrapper.update('user', JohnDataUpdate , [ 'first_name=\\'John\\'', ['last_name=?', 'Foo'] ], function(err) {\n\t// John is now our best user. Congratulations, John !\n} );\n\n// ** remove  ( this time, both values are safely escaped ) \ndbWrapper.remove('user', [ ['first_name LIKE ?', '%John%'], ['last_name=?', 'Foo'] ] , function(err) {\n\t// John left at the height of its glory.\n} );\n\n\n// Easy SQL String building\nvar select = dbWrapper.getSelect()\n\t.from('user', ['first_name', 'last_name'] )\n\t.where( 'enabled=1' )\n\t.where( 'id=?', 10 )\n\t.where( 'last_name LIKE ?', '%Foo%' )\n\t.order( 'last_name' )\n\t.limit( 10 );\n\nif( req.params.onlyVerifiedAccounts )\n\tselect.where('verified=1');\n\nconsole.log( select.assemble() );//outputs the SQL query for debug purpose \n\n// You can retrieve the data of this DBSelect with a \"fetch\" method...\ndbWrapper.fetchAll( select, function(err) {} );\n\n// ..or you can trigger a \"fetch\" method directly on it ! \nselect.fetchAll( function(err) {} );\n\n\n// When you have finished working with the database, you can close the connection\ndbWrapper.close( function(err) {console.log('Connection closed !');} );\n```    \n    \nSee the unit tests in the \"test/\" folder for more examples.\n           \n\n## DBWrapper Class\n\nThe __DBWrapper__ Javascript class, which is the only visible part on top on the different database engines adapters, provides the following methods :\n\n * __connect()__ : tell DbWrapper to connect to the database.\n * __fetchAll( sql, bind, callback )__ : fetches all SQL result rows as a Array.\n * __fetchRow( sql, bind, callback )__ : fetches the first row of the SQL result.\n * __fetchCol( sql, bind, callback )__ : fetches the first column of all SQL result rows as an Array.\n * __fetchOne( sql, bind, callback )__ : fetches the first column of the first row of the SQL result.\n * __insert( tableName, data, callback )__ : inserts a table row with specified data, as a hash.\n * __update( tableName, data, where, callback )__ : updates table rows with specified data (as a hash) based on a WHERE clause.\n * __remove( tableName, where, callback )__ : deletes table rows based on a WHERE clause.\n * __getLastInsertId()__ : returns the last inserted Id\n * __isConnected()__ : tells us if the DbWrapper is connected to its database. \n * __getSelect()__ : returns a DBSelect\n\nAll these methods returns exactly the sames results, whatever the chosen database engine is.  \n\n\n## DBSelect Class\n\nFurthermore, Node-DBI provides a DBSelect class which allows easy and readable SQL \"SELECT\" Strings building. At the moment, it provides the following methods :\n\n * __from( tableName, fieldsArray )__ : adds a table in the FROM clause, and adds its fields to the SELECT\n * __where( whereStr, value )__:  \n     * adds a WHERE clause using AND \n     * if __value__ is not null, all the \"?\" occurences in __whereStr__ will be replaced with the safely escaped value\n     * __value__ may be an array, it will be mapped to a parenthesized SQL list\n     * the clause will be surrounded with parenthesis in the generated SQL, this way ```.where('id=? OR name=?')``` will work like it does in ZendDb.\n * __orWhere( whereStr, value )__ : just like __where__ but adds a WHERE clause using OR\n * __whereGroup( num )__ : \n   * opens __num__ parenthetical groupings to WHERE clause (ie adds __num__ open parentheses) \n   * __num__ defaults to 1\n * __whereGroupClose( num )__ :  \n   * closes __num__ parenthetical groupings of WHERE clause (ie adds __num__ closed parentheses)\n   * __num__ defaults to 1\n   * will not close groups that do not exist\n   * open groups will be closed automatically\n * __limit( nbResults, startIndex )__ : \n   * set the LIMIT clause \n   * __startIndex__ param is optional\n * __order( fieldName, direction )__ : \n   * adds a ORDER BY clause \n   * if __direction__ is not set, it will be set to \"ASC\"\n * __join( tableName, joinStr, fieldsArray, joinType )__ : \n   * adds a JOIN clause \n   * if __joinType__ is not set, it will be set to \"INNER\"\n * __distinct()__ : adds a DISTINCT() to the query\n * __groupyBy( fieldName )__ : adds a GROUPY BY clause\n * __assemble()__ : converts ou DBSelect object to an SQL SELECT string.\n\n\n## Install\n\nYou can clone the project from [GitHub](https://github.com/DrBenton/Node-DBI).\nAlternatively, you can install using Node Package Manager (npm):\n\n    npm install node-dbi\n    \nThis is a first version ; all my unit tests run successfully, but it may have bugs. Tell me if you find one ! :-)\n\n\n## Dependencies\n\nThanks to these great modules, which Node-DBI relies on :\n\n * [async](https://github.com/caolan/async)\n * [underscore](https://github.com/grayrest/underscore)\n * [vows](https://github.com/cloudhead/vows) for the unit tests\n\nAnd of course, these database engines, which makes the really hard work in Node-DBI :\n\n * [mysql](https://github.com/felixge/node-mysql)\n * [mysql-libmysqlclient](https://github.com/Sannis/node-mysql-libmysqlclient)\n * [sqlite3](https://github.com/developmentseed/node-sqlite3)\n * [pg](http://github.com/brianc/node-postgres)\n \nAny 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 !\n\n## Testing\n\nTo run tests manual DB configuration is required first.\n\nSee ```test/config.js``` for databases setup.\n\nAccording to Travis CI requirements, MySQL is expected to be available on localhost with user \"root\", empty password and DB \"node_dbi_test\".\n\nE.g. this should work:\n\n\t$ mysql -hlocalhost -uroot node_dbi_test\n\nThis can usually be achieved by installing mysql and at the mysql interactive prompt issuing the following commands:\n\n\tmysql> create database node_dbi_test;\n\tQuery OK, 1 row affected (0.00 sec)\n\tmysql> grant all on node_dbi_test.* to 'root'@'localhost';\n\tQuery OK, 0 rows affected (0.08 sec)\n\nPostgreSQL is similar to MySQL, e.g. this should work:\n\n\t$ $ psql -U postgres -W node_dbi_test\n\tPassword for user postgres: (manually typing empty password here)\n\nOnce PostgreSQL is installed this can usually be achieved with by issuing the following commands at the psql interactive prompt:\n\n\tpostgres=# create user postgres password '';\n\tCREATE ROLE\n\tpostgres=# create database node_dbi_test owner postgres;\n\tCREATE DATABASE\n\n## Driver Differences\n\nCurrently the PostgreSQL driver does not support getLastInsertId().\n\nMySQL and PostgreSQL drivers return JavaScript Date objects when the table data is a date, while SQLite driver do not.\n\n## License\n\nNode-DBI is licensed under the MIT license.\n","readmeFilename":"README.md","bugs":{"url":"https://github.com/DrBenton/Node-DBI/issues"},"_id":"node-dbi@0.6.2","dist":{"shasum":"1ffc1e34db42d4d06077ebffb2a171e28a3932f8","tarball":"https://registry.npmjs.org/node-dbi/-/node-dbi-0.6.2.tgz","integrity":"sha512-WwUodKqDEFDnLW0uiZ+3lHkAQr+1efFUcRMtA3cG29mTM3p/rz0JnBa3X2k6fQGyKzBUrKVWcXlw4uVTlbzdPg==","signatures":[{"keyid":"SHA256:jl3bwswu80PjjokCgh0o2w5c2U4LhQAE57gj9cz1kzA","sig":"MEUCIEyL1raCh6EW+KFApet7azpPQnlAC92egZWutikT7eqlAiEA0seiwQ2Eodn3mJKFnmOy2AV6QE5v6U43xVXMvZdwvXI="}]},"_from":".","_npmVersion":"1.3.8","_npmUser":{"name":"dr-benton","email":"contact@dr-benton.com"},"maintainers":[{"name":"dr-benton","email":"contact@dr-benton.com"}]},"0.7.0":{"name":"node-dbi","description":"A Database abstraction layer for Node.js, bundled with several DB engines adapters","version":"0.7.0","homepage":"https://github.com/DrBenton/Node-DBI","repository":{"type":"git","url":"git://github.com/DrBenton/Node-DBI.git"},"authors":["Dr. Benton (http://github.com/DrBenton)","Michael Dwyer (http://github.com/kalifg)","David Schoen <dave@lyte.id.au>","Fabian Bornhofen (http://fabianbornhofen.blogspot.com)"],"main":"dbWrapper.js","directories":{"lib":"lib","test":"test"},"dependencies":{"async":"","lodash":"~2.4.1"},"devDependencies":{"mocha":"~1.17.0","chai":"~1.8.1","sqlite3":"~2.1.19","mysql":"~2.0.0","mysql-libmysqlclient":"~1.5.2","pg":"~2.11.0"},"scripts":{"test":"mocha"},"keywords":["database","SQL","abstraction","common","mysql","sqlite","pg","postgres","postgresql"],"engines":{"node":">= 0.8"},"# vim: ts=2 sw=2 et ":null,"readme":"# Node-DBI\n\n[![build status](https://secure.travis-ci.org/DrBenton/Node-DBI.png)](http://travis-ci.org/DrBenton/Node-DBI)\n\nNode-DBI is a SQL database abstraction layer library, strongly inspired by the PHP Zend Framework [Zend_Db API](http://framework.zend.com/manual/en/zend.db.html).\nIt provides unified functions to work with multiple database engines, through Adapters classes.\nAt this time, supported engines are [mysql](https://github.com/felixge/node-mysql), [mysql-libmysqlclient](https://github.com/Sannis/node-mysql-libmysqlclient), [sqlite3](https://github.com/developmentseed/node-sqlite3) and [pg](http://github.com/brianc/node-postgres).\n\nIt provides __DBWrapper__ and __DBSelect__ Javascript classes, described later on this document.\n\n## Usage\n\nNode-DBI is primarily an abstraction layer library ; it allows you to have a \"database-agnostic\" application, with a single API for multiple databases engines.\n\nIt provides high-level functions to fecth, insert, update and remove data from the database.\nIt 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.\n\nNode-DBI imitates the API of the great Open Source PHP database abstraction layer of the [Zend Framework](http://framework.zend.com/), [Zend_Db](http://framework.zend.com/manual/en/zend.db.html), used by thousands of Web developers for several years.\n\nThe example below demonstates the Node-DBI usage:\n\n```javascript\nvar DBWrapper = require('node-dbi').DBWrapper; \nvar DBExpr = require('node-dbi').DBExpr; \nvar dbConnectionConfig = { host: 'localhost', user: 'test', password: 'test', database: 'test' };\n\n// Replace the adapter name with \"mysql\", \"mysql-libmysqlclient\", \"sqlite3\" or \"pg\" on the following line :\ndbWrapper = new DBWrapper( '[DB engine adapter name]', dbConnectionConfig );\ndbWrapper.connect();\n\n// ** fetchAll\ndbWrapper.fetchAll('SELECT * FROM user', null, function(err, result) {\n\tif( ! result )\n\t\tconsole.dir(result);\n\t// \"result\" is an Array with a hash for every returned row\n} );\n\n// ** fetchRow ( +  a safely escaped value ) \ndbWrapper.fetchRow('SELECT * FROM user WHERE first_name=?', ['John'], function(err, result) {\n\tif( ! result )\n\t\tconsole.dir(result);\n\t// this time, \"result\" is a single hash (the first returned row)\n} );\n\n// ** fetchCol  (if you dont' have values to escape, the 2nd param can be an empty Array or \"null\")\ndbWrapper.fetchCol('SELECT first_name FROM user ORDER BY fist_name', null, function(err, result) {\n\tif( ! err )\n\t\tconsole.dir(result);\n\t// \"result\" is an Array with all the names of our users, sorted alphabetically\n} );\n\n// ** fetchOne\ndbWrapper.fetchOne('SELECT fist_name FROM user ORDER BY rank DESC LIMIT 1', [], function(err, result) {\n\tif( ! err )\n\t\tconsole.dir(result);\n\t// \"result\" is the first_name of our best user\n} );\n\n// ** insert   (DBExpr force somes values to be used \"as is\", without safe escape : it is useful for SQL functions like \"NOW()\", \"COUNT(*)\", \"SUM(rank)\"... )\nvar JohnData = { first_name: 'John', last_name: 'Foo', rank: '3', date_created: new DBExpr('NOW()') };\ndbWrapper.insert('user', JohnData , function(err) {\n\tif( ! err )\n\t\tconsole.log( 'John ID : ' + dbWrapper.getLastInsertId() );\n\t// John has been inserted in our table, with its properties safely escaped\n} );\n\n// ** update  ( here the fist name is used as a raw String, but the last name is safely escaped ) \nvar JohnDataUpdate = { rank: '1' };\n\tdbWrapper.update('user', JohnDataUpdate , [ 'first_name=\\'John\\'', ['last_name=?', 'Foo'] ], function(err) {\n\t// John is now our best user. Congratulations, John !\n} );\n\n// ** remove  ( this time, both values are safely escaped ) \ndbWrapper.remove('user', [ ['first_name LIKE ?', '%John%'], ['last_name=?', 'Foo'] ] , function(err) {\n\t// John left at the height of its glory.\n} );\n\n\n// Easy SQL String building\nvar select = dbWrapper.getSelect()\n\t.from('user', ['first_name', 'last_name'] )\n\t.where( 'enabled=1' )\n\t.where( 'id=?', 10 )\n\t.where( 'last_name LIKE ?', '%Foo%' )\n\t.order( 'last_name' )\n\t.limit( 10 );\n\nif( req.params.onlyVerifiedAccounts )\n\tselect.where('verified=1');\n\nconsole.log( select.assemble() );//outputs the SQL query for debug purpose \n\n// You can retrieve the data of this DBSelect with a \"fetch\" method...\ndbWrapper.fetchAll( select, function(err) {} );\n\n// ..or you can trigger a \"fetch\" method directly on it ! \nselect.fetchAll( function(err) {} );\n\n\n// When you have finished working with the database, you can close the connection\ndbWrapper.close( function(err) {console.log('Connection closed !');} );\n```    \n    \nSee the unit tests in the \"test/\" folder for more examples.\n           \n\n## DBWrapper Class\n\nThe __DBWrapper__ Javascript class, which is the only visible part on top on the different database engines adapters, provides the following methods :\n\n * __connect()__ : tell DbWrapper to connect to the database.\n * __fetchAll( sql, bind, callback )__ : fetches all SQL result rows as a Array.\n * __fetchRow( sql, bind, callback )__ : fetches the first row of the SQL result.\n * __fetchCol( sql, bind, callback )__ : fetches the first column of all SQL result rows as an Array.\n * __fetchOne( sql, bind, callback )__ : fetches the first column of the first row of the SQL result.\n * __insert( tableName, data, callback )__ : inserts a table row with specified data, as a hash.\n * __update( tableName, data, where, callback )__ : updates table rows with specified data (as a hash) based on a WHERE clause.\n * __remove( tableName, where, callback )__ : deletes table rows based on a WHERE clause.\n * __getLastInsertId()__ : returns the last inserted Id\n * __isConnected()__ : tells us if the DbWrapper is connected to its database. \n * __getSelect()__ : returns a DBSelect\n * __close()__ : tell DbWrapper to close the database connection.\n\nAll these methods returns exactly the sames results, whatever the chosen database engine is.  \n\n\n## DBSelect Class\n\nFurthermore, Node-DBI provides a DBSelect class which allows easy and readable SQL \"SELECT\" Strings building. At the moment, it provides the following methods :\n\n * __from( tableName, fieldsArray )__ : adds a table in the FROM clause, and adds its fields to the SELECT\n * __where( whereStr, value )__:  \n     * adds a WHERE clause using AND \n     * if __value__ is not null, all the \"?\" occurences in __whereStr__ will be replaced with the safely escaped value\n     * __value__ may be an array, it will be mapped to a parenthesized SQL list\n     * the clause will be surrounded with parenthesis in the generated SQL, this way ```.where('id=? OR name=?')``` will work like it does in ZendDb.\n * __orWhere( whereStr, value )__ : just like __where__ but adds a WHERE clause using OR\n * __whereGroup( num )__ : \n   * opens __num__ parenthetical groupings to WHERE clause (ie adds __num__ open parentheses) \n   * __num__ defaults to 1\n * __whereGroupClose( num )__ :  \n   * closes __num__ parenthetical groupings of WHERE clause (ie adds __num__ closed parentheses)\n   * __num__ defaults to 1\n   * will not close groups that do not exist\n   * open groups will be closed automatically\n * __limit( nbResults, startIndex )__ : \n   * set the LIMIT clause \n   * __startIndex__ param is optional\n * __order( fieldName, direction )__ : \n   * adds a ORDER BY clause \n   * if __direction__ is not set, it will be set to \"ASC\"\n * __join( tableName, joinStr, fieldsArray, joinType )__ : \n   * adds a JOIN clause \n   * if __joinType__ is not set, it will be set to \"INNER\"\n * __distinct()__ : adds a DISTINCT() to the query\n * __groupyBy( fieldName )__ : adds a GROUPY BY clause\n * __assemble()__ : converts ou DBSelect object to an SQL SELECT string.\n\n\n## Install\n\nYou can clone the project from [GitHub](https://github.com/DrBenton/Node-DBI).\nAlternatively, you can install using Node Package Manager (npm):\n\n    npm install node-dbi\n    \nAll my unit tests run successfully, but well, it still may have bugs. Tell me if you find one ! :-)\n\n\n## Dependencies\n\nNode-DBI supports these database engines, which makes the really hard work :\n\n * [mysql](https://github.com/felixge/node-mysql)\n * [mysql-libmysqlclient](https://github.com/Sannis/node-mysql-libmysqlclient)\n * [sqlite3](https://github.com/developmentseed/node-sqlite3)\n * [pg](http://github.com/brianc/node-postgres)\n \nAny 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 !\n\n## Testing\n\nTo run tests manual DB configuration is required first.\n\nCopy ```test/config.js.dist``` to ```test/config.js```, and edit this JS file for databases setup if needed.\n\nAccording to Travis CI requirements, MySQL is expected to be available on localhost with user \"root\", empty password and DB \"node_dbi_test\".\n\nE.g. this should work:\n\n\t$ mysql -hlocalhost -uroot node_dbi_test\n\nThis can usually be achieved by installing mysql and at the mysql interactive prompt issuing the following commands:\n\n\tmysql> create database node_dbi_test;\n\tQuery OK, 1 row affected (0.00 sec)\n\tmysql> grant all on node_dbi_test.* to 'root'@'localhost';\n\tQuery OK, 0 rows affected (0.08 sec)\n\nPostgreSQL is similar to MySQL, e.g. this should work:\n\n\t$ $ psql -U postgres -W node_dbi_test\n\tPassword for user postgres: (manually typing empty password here)\n\nOnce PostgreSQL is installed this can usually be achieved with by issuing the following commands at the psql interactive prompt:\n\n\tpostgres=# create user postgres password '';\n\tCREATE ROLE\n\tpostgres=# create database node_dbi_test owner postgres;\n\tCREATE DATABASE\n\n## Driver Differences\n\nCurrently the PostgreSQL driver does not support getLastInsertId().\n\nMySQL and PostgreSQL drivers return JavaScript Date objects when the table data is a date, while SQLite driver do not.\n\n## License\n\nNode-DBI is licensed under the MIT license.\n","readmeFilename":"README.md","bugs":{"url":"https://github.com/DrBenton/Node-DBI/issues"},"_id":"node-dbi@0.7.0","dist":{"shasum":"540e0efeef18c664e4316d17f4d23c1613aded43","tarball":"https://registry.npmjs.org/node-dbi/-/node-dbi-0.7.0.tgz","integrity":"sha512-xtd2rJjYyBvuHAXp3FZAze3IN6U+3QkUSyrn9GgJRlcxdmmPisc1wEZCpbrUIhbT1VeDP6YjXCyKhS18qJi/8A==","signatures":[{"keyid":"SHA256:jl3bwswu80PjjokCgh0o2w5c2U4LhQAE57gj9cz1kzA","sig":"MEQCIBnJlR8jIBPhLJV1Ehf2xJddtxPNpRKoB/vwBk0FZd5SAiB9ksMLB0rm5Oh4n37cBVl5taF5N6M5fHwG2enfnVXVrA=="}]},"_from":".","_npmVersion":"1.3.21","_npmUser":{"name":"dr-benton","email":"olivier@rougemine.com"},"maintainers":[{"name":"dr-benton","email":"olivier@rougemine.com"}]},"0.7.1":{"name":"node-dbi","description":"A Database abstraction layer for Node.js, bundled with several DB engines adapters","version":"0.7.1","homepage":"https://github.com/DrBenton/Node-DBI","repository":{"type":"git","url":"git://github.com/DrBenton/Node-DBI.git"},"authors":["Olivier Philippon (http://github.com/DrBenton)","Michael Dwyer (http://github.com/kalifg)","David Schoen <dave@lyte.id.au>","Fabian Bornhofen (http://fabianbornhofen.blogspot.com)"],"main":"dbWrapper.js","directories":{"lib":"lib","test":"test"},"dependencies":{"async":"","lodash":"~2.4.1"},"devDependencies":{"mocha":"~1.17.0","chai":"~1.8.1","sqlite3":"~2.1.19","mysql":"~2.0.0","mysql-libmysqlclient":"~1.5.2","pg":"~2.11.0"},"scripts":{"test":"mocha"},"keywords":["database","SQL","abstraction","common","mysql","sqlite","pg","postgres","postgresql"],"engines":{"node":">= 0.8"},"# vim: ts=2 sw=2 et ":null,"readme":"# Node-DBI\n\n[![build status](https://secure.travis-ci.org/DrBenton/Node-DBI.png)](http://travis-ci.org/DrBenton/Node-DBI)\n\nNode-DBI is a SQL database abstraction layer library, strongly inspired by the PHP Zend Framework [Zend_Db API](http://framework.zend.com/manual/en/zend.db.html).\nIt provides unified functions to work with multiple database engines, through Adapters classes.\nAt this time, supported engines are [mysql](https://github.com/felixge/node-mysql), [mysql-libmysqlclient](https://github.com/Sannis/node-mysql-libmysqlclient), [sqlite3](https://github.com/developmentseed/node-sqlite3) and [pg](http://github.com/brianc/node-postgres).\n\nIt provides __DBWrapper__ and __DBSelect__ Javascript classes, described later on this document.\n\n## Usage\n\nNode-DBI is primarily an abstraction layer library ; it allows you to have a \"database-agnostic\" application, with a single API for multiple databases engines.\n\nIt provides high-level functions to fecth, insert, update and remove data from the database.\nIt 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.\n\nNode-DBI imitates the API of the great Open Source PHP database abstraction layer of the [Zend Framework](http://framework.zend.com/), [Zend_Db](http://framework.zend.com/manual/en/zend.db.html), used by thousands of Web developers for several years.\n\nThe example below demonstates the Node-DBI usage:\n\n```javascript\nvar DBWrapper = require('node-dbi').DBWrapper; \nvar DBExpr = require('node-dbi').DBExpr; \nvar dbConnectionConfig = { host: 'localhost', user: 'test', password: 'test', database: 'test' };\n\n// Replace the adapter name with \"mysql\", \"mysql-libmysqlclient\", \"sqlite3\" or \"pg\" on the following line :\ndbWrapper = new DBWrapper( '[DB engine adapter name]', dbConnectionConfig );\ndbWrapper.connect();\n\n// ** fetchAll\ndbWrapper.fetchAll('SELECT * FROM user', null, function(err, result) {\n\tif( ! result )\n\t\tconsole.dir(result);\n\t// \"result\" is an Array with a hash for every returned row\n} );\n\n// ** fetchRow ( +  a safely escaped value ) \ndbWrapper.fetchRow('SELECT * FROM user WHERE first_name=?', ['John'], function(err, result) {\n\tif( ! result )\n\t\tconsole.dir(result);\n\t// this time, \"result\" is a single hash (the first returned row)\n} );\n\n// ** fetchCol  (if you dont' have values to escape, the 2nd param can be an empty Array or \"null\")\ndbWrapper.fetchCol('SELECT first_name FROM user ORDER BY fist_name', null, function(err, result) {\n\tif( ! err )\n\t\tconsole.dir(result);\n\t// \"result\" is an Array with all the names of our users, sorted alphabetically\n} );\n\n// ** fetchOne\ndbWrapper.fetchOne('SELECT fist_name FROM user ORDER BY rank DESC LIMIT 1', [], function(err, result) {\n\tif( ! err )\n\t\tconsole.dir(result);\n\t// \"result\" is the first_name of our best user\n} );\n\n// ** insert   (DBExpr force somes values to be used \"as is\", without safe escape : it is useful for SQL functions like \"NOW()\", \"COUNT(*)\", \"SUM(rank)\"... )\nvar JohnData = { first_name: 'John', last_name: 'Foo', rank: '3', date_created: new DBExpr('NOW()') };\ndbWrapper.insert('user', JohnData , function(err) {\n\tif( ! err )\n\t\tconsole.log( 'John ID : ' + dbWrapper.getLastInsertId() );\n\t// John has been inserted in our table, with its properties safely escaped\n} );\n\n// ** update  ( here the fist name is used as a raw String, but the last name is safely escaped ) \nvar JohnDataUpdate = { rank: '1' };\n\tdbWrapper.update('user', JohnDataUpdate , [ 'first_name=\\'John\\'', ['last_name=?', 'Foo'] ], function(err) {\n\t// John is now our best user. Congratulations, John !\n} );\n\n// ** remove  ( this time, both values are safely escaped ) \ndbWrapper.remove('user', [ ['first_name LIKE ?', '%John%'], ['last_name=?', 'Foo'] ] , function(err) {\n\t// John left at the height of its glory.\n} );\n\n\n// Easy SQL String building\nvar select = dbWrapper.getSelect()\n\t.from('user', ['first_name', 'last_name'] )\n\t.where( 'enabled=1' )\n\t.where( 'id=?', 10 )\n\t.where( 'last_name LIKE ?', '%Foo%' )\n\t.where( 'removal_date=?', null ) // null -> NULL\n\t.where( 'nickname=?', undefined ) // other falsy-but-not-Numbers values -> empty String\n\t.order( 'last_name' )\n\t.limit( 10 );\n\nif( req.params.onlyVerifiedAccounts )\n\tselect.where('verified=1');\n\nconsole.log( select.assemble() );//outputs the SQL query for debug purpose \n\n// You can retrieve the data of this DBSelect with a \"fetch\" method...\ndbWrapper.fetchAll( select, function(err) {} );\n\n// ..or you can trigger a \"fetch\" method directly on it ! \nselect.fetchAll( function(err) {} );\n\n\n// When you have finished working with the database, you can close the connection\ndbWrapper.close( function(err) {console.log('Connection closed !');} );\n```    \n    \nSee the unit tests in the \"test/\" folder for more examples.\n           \n\n## DBWrapper Class\n\nThe __DBWrapper__ Javascript class, which is the only visible part on top on the different database engines adapters, provides the following methods :\n\n * __connect()__ : tell DbWrapper to connect to the database.\n * __fetchAll( sql, bind, callback )__ : fetches all SQL result rows as a Array.\n * __fetchRow( sql, bind, callback )__ : fetches the first row of the SQL result.\n * __fetchCol( sql, bind, callback )__ : fetches the first column of all SQL result rows as an Array.\n * __fetchOne( sql, bind, callback )__ : fetches the first column of the first row of the SQL result.\n * __insert( tableName, data, callback )__ : inserts a table row with specified data, as a hash.\n * __update( tableName, data, where, callback )__ : updates table rows with specified data (as a hash) based on a WHERE clause.\n * __remove( tableName, where, callback )__ : deletes table rows based on a WHERE clause.\n * __getLastInsertId()__ : returns the last inserted Id\n * __isConnected()__ : tells us if the DbWrapper is connected to its database. \n * __getSelect()__ : returns a DBSelect\n * __close()__ : tell DbWrapper to close the database connection.\n\nAll these methods returns exactly the sames results, whatever the chosen database engine is.  \n\n\n## DBSelect Class\n\nFurthermore, Node-DBI provides a DBSelect class which allows easy and readable SQL \"SELECT\" Strings building. At the moment, it provides the following methods :\n\n * __from( tableName, fieldsArray )__ : adds a table in the FROM clause, and adds its fields to the SELECT\n * __where( whereStr, value )__:  \n     * adds a WHERE clause using AND \n     * if __value__ is not null, all the \"?\" occurences in __whereStr__ will be replaced with the safely escaped value\n     * __value__ may be an array, it will be mapped to a parenthesized SQL list\n     * the clause will be surrounded with parenthesis in the generated SQL, this way ```.where('id=? OR name=?')``` will work like it does in ZendDb.\n * __orWhere( whereStr, value )__ : just like __where__ but adds a WHERE clause using OR\n * __whereGroup( num )__ : \n   * opens __num__ parenthetical groupings to WHERE clause (ie adds __num__ open parentheses) \n   * __num__ defaults to 1\n * __whereGroupClose( num )__ :  \n   * closes __num__ parenthetical groupings of WHERE clause (ie adds __num__ closed parentheses)\n   * __num__ defaults to 1\n   * will not close groups that do not exist\n   * open groups will be closed automatically\n * __limit( nbResults, startIndex )__ : \n   * set the LIMIT clause \n   * __startIndex__ param is optional\n * __order( fieldName, direction )__ : \n   * adds a ORDER BY clause \n   * if __direction__ is not set, it will be set to \"ASC\"\n * __join( tableName, joinStr, fieldsArray, joinType )__ : \n   * adds a JOIN clause \n   * if __joinType__ is not set, it will be set to \"INNER\"\n * __distinct()__ : adds a DISTINCT() to the query\n * __groupyBy( fieldName )__ : adds a GROUPY BY clause\n * __assemble()__ : converts ou DBSelect object to an SQL SELECT string.\n\n\n## Install\n\nYou can clone the project from [GitHub](https://github.com/DrBenton/Node-DBI).\nAlternatively, you can install using Node Package Manager (npm):\n\n    npm install node-dbi\n    \nAll my unit tests run successfully, but well, it still may have bugs. Tell me if you find one ! :-)\n\n\n## Dependencies\n\nNode-DBI supports these database engines, which makes the really hard work :\n\n * [mysql](https://github.com/felixge/node-mysql)\n * [mysql-libmysqlclient](https://github.com/Sannis/node-mysql-libmysqlclient)\n * [sqlite3](https://github.com/developmentseed/node-sqlite3)\n * [pg](http://github.com/brianc/node-postgres)\n \nAny 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 !\n\n## Testing\n\nTo run tests manual DB configuration is required first.\n\nCopy ```test/config.js.dist``` to ```test/config.js```, and edit this JS file for databases setup if needed.\n\nAccording to Travis CI requirements, MySQL is expected to be available on localhost with user \"root\", empty password and DB \"node_dbi_test\".\n\nE.g. this should work:\n\n\t$ mysql -hlocalhost -uroot node_dbi_test\n\nThis can usually be achieved by installing mysql and at the mysql interactive prompt issuing the following commands:\n\n\tmysql> create database node_dbi_test;\n\tQuery OK, 1 row affected (0.00 sec)\n\tmysql> grant all on node_dbi_test.* to 'root'@'localhost';\n\tQuery OK, 0 rows affected (0.08 sec)\n\nPostgreSQL is similar to MySQL, e.g. this should work:\n\n\t$ $ psql -U postgres -W node_dbi_test\n\tPassword for user postgres: (manually typing empty password here)\n\nOnce PostgreSQL is installed this can usually be achieved with by issuing the following commands at the psql interactive prompt:\n\n\tpostgres=# create user postgres password '';\n\tCREATE ROLE\n\tpostgres=# create database node_dbi_test owner postgres;\n\tCREATE DATABASE\n\n## Driver Differences\n\nCurrently the PostgreSQL driver does not support getLastInsertId().\n\nMySQL and PostgreSQL drivers return JavaScript Date objects when the table data is a date, while SQLite driver do not.\n\n## License\n\nNode-DBI is licensed under the MIT license.\n","readmeFilename":"README.md","bugs":{"url":"https://github.com/DrBenton/Node-DBI/issues"},"_id":"node-dbi@0.7.1","dist":{"shasum":"44636b28651510de04f5c004db68ad316619aecf","tarball":"https://registry.npmjs.org/node-dbi/-/node-dbi-0.7.1.tgz","integrity":"sha512-gRAwso5CoRhgPnByDeHO89A0PUCioXdkRu4ZAomw+Lepq+pjtiblBrcmRw8LEPyAoCpSupjYzmng9KwKmESdQQ==","signatures":[{"keyid":"SHA256:jl3bwswu80PjjokCgh0o2w5c2U4LhQAE57gj9cz1kzA","sig":"MEUCIQDrOyZCu1vb2B5318grjQXKKs1qQgjYY7NgqdlTxf9MygIgIxZ2lH6Fk6cRKSrkdn8jY9ZY2bY7HLhLm1LYVuXXjWw="}]},"_from":".","_npmVersion":"1.3.21","_npmUser":{"name":"dr-benton","email":"olivier@rougemine.com"},"maintainers":[{"name":"dr-benton","email":"olivier@rougemine.com"}]}},"maintainers":[{"name":"dr-benton","email":"contact@dr-benton.com"}],"time":{"modified":"2022-06-21T13:31:41.338Z","created":"2011-04-15T15:30:17.596Z","0.2.0":"2011-04-15T15:30:18.124Z","0.3.0":"2011-04-20T14:26:33.987Z","0.4.0":"2011-04-24T11:31:15.265Z","0.4.1":"2011-07-26T14:04:16.187Z","0.4.2":"2011-08-24T16:25:09.043Z","0.5.0":"2012-07-27T16:31:53.813Z","0.6.0":"2012-08-14T16:24:13.503Z","0.6.1":"2012-08-17T15:02:02.666Z","0.6.2":"2013-09-06T08:51:12.654Z","0.7.0":"2014-01-13T16:16:11.636Z","0.7.1":"2014-01-14T10:22:57.202Z"},"author":{"name":"Dr. Benton","url":"http://www.dr-benton.com"},"repository":{"type":"git","url":"git://github.com/DrBenton/Node-DBI.git"},"users":{"fgribreau":true,"lc1200":true},"readme":"# Node-DBI\n\n[![build status](https://secure.travis-ci.org/DrBenton/Node-DBI.png)](http://travis-ci.org/DrBenton/Node-DBI)\n\nNode-DBI is a SQL database abstraction layer library, strongly inspired by the PHP Zend Framework [Zend_Db API](http://framework.zend.com/manual/en/zend.db.html).\nIt provides unified functions to work with multiple database engines, through Adapters classes.\nAt this time, supported engines are [mysql](https://github.com/felixge/node-mysql), [mysql-libmysqlclient](https://github.com/Sannis/node-mysql-libmysqlclient), [sqlite3](https://github.com/developmentseed/node-sqlite3) and [pg](http://github.com/brianc/node-postgres).\n\nIt provides __DBWrapper__ and __DBSelect__ Javascript classes, described later on this document.\n\n## Usage\n\nNode-DBI is primarily an abstraction layer library ; it allows you to have a \"database-agnostic\" application, with a single API for multiple databases engines.\n\nIt provides high-level functions to fecth, insert, update and remove data from the database.\nIt 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.\n\nNode-DBI imitates the API of the great Open Source PHP database abstraction layer of the [Zend Framework](http://framework.zend.com/), [Zend_Db](http://framework.zend.com/manual/en/zend.db.html), used by thousands of Web developers for several years.\n\nThe example below demonstates the Node-DBI usage:\n\n```javascript\nvar DBWrapper = require('node-dbi').DBWrapper; \nvar DBExpr = require('node-dbi').DBExpr; \nvar dbConnectionConfig = { host: 'localhost', user: 'test', password: 'test', database: 'test' };\n\n// Replace the adapter name with \"mysql\", \"mysql-libmysqlclient\", \"sqlite3\" or \"pg\" on the following line :\ndbWrapper = new DBWrapper( '[DB engine adapter name]', dbConnectionConfig );\ndbWrapper.connect();\n\n// ** fetchAll\ndbWrapper.fetchAll('SELECT * FROM user', null, function(err, result) {\n\tif( ! result )\n\t\tconsole.dir(result);\n\t// \"result\" is an Array with a hash for every returned row\n} );\n\n// ** fetchRow ( +  a safely escaped value ) \ndbWrapper.fetchRow('SELECT * FROM user WHERE first_name=?', ['John'], function(err, result) {\n\tif( ! result )\n\t\tconsole.dir(result);\n\t// this time, \"result\" is a single hash (the first returned row)\n} );\n\n// ** fetchCol  (if you dont' have values to escape, the 2nd param can be an empty Array or \"null\")\ndbWrapper.fetchCol('SELECT first_name FROM user ORDER BY fist_name', null, function(err, result) {\n\tif( ! err )\n\t\tconsole.dir(result);\n\t// \"result\" is an Array with all the names of our users, sorted alphabetically\n} );\n\n// ** fetchOne\ndbWrapper.fetchOne('SELECT fist_name FROM user ORDER BY rank DESC LIMIT 1', [], function(err, result) {\n\tif( ! err )\n\t\tconsole.dir(result);\n\t// \"result\" is the first_name of our best user\n} );\n\n// ** insert   (DBExpr force somes values to be used \"as is\", without safe escape : it is useful for SQL functions like \"NOW()\", \"COUNT(*)\", \"SUM(rank)\"... )\nvar JohnData = { first_name: 'John', last_name: 'Foo', rank: '3', date_created: new DBExpr('NOW()') };\ndbWrapper.insert('user', JohnData , function(err) {\n\tif( ! err )\n\t\tconsole.log( 'John ID : ' + dbWrapper.getLastInsertId() );\n\t// John has been inserted in our table, with its properties safely escaped\n} );\n\n// ** update  ( here the fist name is used as a raw String, but the last name is safely escaped ) \nvar JohnDataUpdate = { rank: '1' };\n\tdbWrapper.update('user', JohnDataUpdate , [ 'first_name=\\'John\\'', ['last_name=?', 'Foo'] ], function(err) {\n\t// John is now our best user. Congratulations, John !\n} );\n\n// ** remove  ( this time, both values are safely escaped ) \ndbWrapper.remove('user', [ ['first_name LIKE ?', '%John%'], ['last_name=?', 'Foo'] ] , function(err) {\n\t// John left at the height of its glory.\n} );\n\n\n// Easy SQL String building\nvar select = dbWrapper.getSelect()\n\t.from('user', ['first_name', 'last_name'] )\n\t.where( 'enabled=1' )\n\t.where( 'id=?', 10 )\n\t.where( 'last_name LIKE ?', '%Foo%' )\n\t.where( 'removal_date=?', null ) // null -> NULL\n\t.where( 'nickname=?', undefined ) // other falsy-but-not-Numbers values -> empty String\n\t.order( 'last_name' )\n\t.limit( 10 );\n\nif( req.params.onlyVerifiedAccounts )\n\tselect.where('verified=1');\n\nconsole.log( select.assemble() );//outputs the SQL query for debug purpose \n\n// You can retrieve the data of this DBSelect with a \"fetch\" method...\ndbWrapper.fetchAll( select, function(err) {} );\n\n// ..or you can trigger a \"fetch\" method directly on it ! \nselect.fetchAll( function(err) {} );\n\n\n// When you have finished working with the database, you can close the connection\ndbWrapper.close( function(err) {console.log('Connection closed !');} );\n```    \n    \nSee the unit tests in the \"test/\" folder for more examples.\n           \n\n## DBWrapper Class\n\nThe __DBWrapper__ Javascript class, which is the only visible part on top on the different database engines adapters, provides the following methods :\n\n * __connect()__ : tell DbWrapper to connect to the database.\n * __fetchAll( sql, bind, callback )__ : fetches all SQL result rows as a Array.\n * __fetchRow( sql, bind, callback )__ : fetches the first row of the SQL result.\n * __fetchCol( sql, bind, callback )__ : fetches the first column of all SQL result rows as an Array.\n * __fetchOne( sql, bind, callback )__ : fetches the first column of the first row of the SQL result.\n * __insert( tableName, data, callback )__ : inserts a table row with specified data, as a hash.\n * __update( tableName, data, where, callback )__ : updates table rows with specified data (as a hash) based on a WHERE clause.\n * __remove( tableName, where, callback )__ : deletes table rows based on a WHERE clause.\n * __getLastInsertId()__ : returns the last inserted Id\n * __isConnected()__ : tells us if the DbWrapper is connected to its database. \n * __getSelect()__ : returns a DBSelect\n * __close()__ : tell DbWrapper to close the database connection.\n\nAll these methods returns exactly the sames results, whatever the chosen database engine is.  \n\n\n## DBSelect Class\n\nFurthermore, Node-DBI provides a DBSelect class which allows easy and readable SQL \"SELECT\" Strings building. At the moment, it provides the following methods :\n\n * __from( tableName, fieldsArray )__ : adds a table in the FROM clause, and adds its fields to the SELECT\n * __where( whereStr, value )__:  \n     * adds a WHERE clause using AND \n     * if __value__ is not null, all the \"?\" occurences in __whereStr__ will be replaced with the safely escaped value\n     * __value__ may be an array, it will be mapped to a parenthesized SQL list\n     * the clause will be surrounded with parenthesis in the generated SQL, this way ```.where('id=? OR name=?')``` will work like it does in ZendDb.\n * __orWhere( whereStr, value )__ : just like __where__ but adds a WHERE clause using OR\n * __whereGroup( num )__ : \n   * opens __num__ parenthetical groupings to WHERE clause (ie adds __num__ open parentheses) \n   * __num__ defaults to 1\n * __whereGroupClose( num )__ :  \n   * closes __num__ parenthetical groupings of WHERE clause (ie adds __num__ closed parentheses)\n   * __num__ defaults to 1\n   * will not close groups that do not exist\n   * open groups will be closed automatically\n * __limit( nbResults, startIndex )__ : \n   * set the LIMIT clause \n   * __startIndex__ param is optional\n * __order( fieldName, direction )__ : \n   * adds a ORDER BY clause \n   * if __direction__ is not set, it will be set to \"ASC\"\n * __join( tableName, joinStr, fieldsArray, joinType )__ : \n   * adds a JOIN clause \n   * if __joinType__ is not set, it will be set to \"INNER\"\n * __distinct()__ : adds a DISTINCT() to the query\n * __groupyBy( fieldName )__ : adds a GROUPY BY clause\n * __assemble()__ : converts ou DBSelect object to an SQL SELECT string.\n\n\n## Install\n\nYou can clone the project from [GitHub](https://github.com/DrBenton/Node-DBI).\nAlternatively, you can install using Node Package Manager (npm):\n\n    npm install node-dbi\n    \nAll my unit tests run successfully, but well, it still may have bugs. Tell me if you find one ! :-)\n\n\n## Dependencies\n\nNode-DBI supports these database engines, which makes the really hard work :\n\n * [mysql](https://github.com/felixge/node-mysql)\n * [mysql-libmysqlclient](https://github.com/Sannis/node-mysql-libmysqlclient)\n * [sqlite3](https://github.com/developmentseed/node-sqlite3)\n * [pg](http://github.com/brianc/node-postgres)\n \nAny 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 !\n\n## Testing\n\nTo run tests manual DB configuration is required first.\n\nCopy ```test/config.js.dist``` to ```test/config.js```, and edit this JS file for databases setup if needed.\n\nAccording to Travis CI requirements, MySQL is expected to be available on localhost with user \"root\", empty password and DB \"node_dbi_test\".\n\nE.g. this should work:\n\n\t$ mysql -hlocalhost -uroot node_dbi_test\n\nThis can usually be achieved by installing mysql and at the mysql interactive prompt issuing the following commands:\n\n\tmysql> create database node_dbi_test;\n\tQuery OK, 1 row affected (0.00 sec)\n\tmysql> grant all on node_dbi_test.* to 'root'@'localhost';\n\tQuery OK, 0 rows affected (0.08 sec)\n\nPostgreSQL is similar to MySQL, e.g. this should work:\n\n\t$ $ psql -U postgres -W node_dbi_test\n\tPassword for user postgres: (manually typing empty password here)\n\nOnce PostgreSQL is installed this can usually be achieved with by issuing the following commands at the psql interactive prompt:\n\n\tpostgres=# create user postgres password '';\n\tCREATE ROLE\n\tpostgres=# create database node_dbi_test owner postgres;\n\tCREATE DATABASE\n\n## Driver Differences\n\nCurrently the PostgreSQL driver does not support getLastInsertId().\n\nMySQL and PostgreSQL drivers return JavaScript Date objects when the table data is a date, while SQLite driver do not.\n\n## License\n\nNode-DBI is licensed under the MIT license.\n"}