Adaptor for node-mysql and node-sqlite3
Switch branches/tags
Nothing to show
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Failed to load latest commit information.
.gitignore
LICENSE.md
README.md
gruntfile.js
nodesql.js
package.json
test.js

README.md

#NodeSQL Adaptor that wraps node-mysql and node-sqlite3 databases with a common interface. The intention is to allow fast unit testing of the database with sqlite's in memory database.

NOTE * indicates an optional parameter

###Install

via npm

npm install nodesql

###Setup

//using mysql
var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'example.org',
  user     : 'bob',
  password : 'secret',
  database : 'databaseName',
  //only set the multipleStatements setting if you plan on using the transaction
  //method (and be careful) as it potentially exposes you to sql injection.
  multipleStatements: true
});

//or for sqlite3
var sqlite3 = require('sqlite3').verbose();
var connection = new sqlite3.Database(':memory:');

//wrap the database connection in a nodeSql adaptor.
var nodeSql = require('nodesql');
var db = nodeSql.createMySqlStrategy(connection);
//or
var db = nodeSql.createSqliteStrategy(connection);

###db.query(sqlStatement, *values, *callback)

SELECT If no error occurs err will be null, and the second parameter will contain an array of rows.

db.query('SELECT * FROM Table', function (err, rows) {
    if(!err) {
        console.log(rows);
    }
    else {
        console.log("An Error Occured");
        console.log(err);
    }
});

INSERT The second parameter will be the id of the row that was just inserted.

db.query('INSERT INTO Table (col) VALUES (?)', ['columnValue'], function (err, insertId) {
    if(!err) {
        console.log(insertId)
    }
})

UPDATE

db.query('UPDATE ...', ..., function (err) {});

DELETE

db.query('DELETE ...', ..., function (err) {});

###db.one(statement, *values, *callback) returns the first result of a select statement.

db.one('SELECT * FROM Table WHERE id = ?', [5], function (err, row) {
    //row is the first element of what would normally be
    //an array of rows.
});

###db.select(table, *whereEqualsObject, *callback)

//equivalent to db.query('SELECT * FROM Table WHERE id = ?', [5], function (err, rows) {});
db.select('Table', { id: 5 }, function (err, rows) {});

###db.selectOne(table, whereEqualsObject, *callback) returns the first result of select

db.selectOne('Table', { id: 5 }, function (err, row) {
    //row is the first element of what would normally be
    //an array of rows.
})

###db.insert(table, row, *callback)

//equivalent to db.query('INSERT INTO Table (col) VALUES (?)', ['foo'], function (err, insertId) {});
db.insert('Table', { col: 'foo' }, function (err, insertId) {});

###db.update(table, rowEdits, whereEqualsObject, *callback)

//equivalent to db.query('UPDATE Table SET col = ? WHERE id = ?', ['edit', 5], function (err) {});
db.update('Table', { col: 'edit' }, { id: 5 }, function (err) {});

###db.delete(table, whereEqualsObject, *callback)

//equivalent to db.query('DELETE FROM Table WHERE id = ?', [5], function (err) {});
db.delete('Table', { id: 5 }, function (err) {});

###db.transaction(statements, *callback) perform multiple sql statements that will be wrapped in a transaction.

db.transaction(['INSERT INTO ...', 'INSERT INTO ...']);

##db.escape(value) escapes a value used in query to protect against sql injection. using a prepared statement is preferred. Warning: No Escaping is actually implemented for sqlite! (simply returns value)

##Promises

All NodeSQL that take callbacks also return Q promises.

db.select('Table', {id: 5}).then(
    function (rows) {},
    function (err) {}
);