mysql-model

node-mysql-model

A backbone based model for communicating with a MySQL database using felixge/node-mysql.

Install

Install from npm package:

npm install mysql-model

Or install from git:

npm install git://github.com/michalkow/node-mysql-model.git

Usage

Add the mysql-model module to your application :

var mysqlModel = require('mysql-model');

Then create a model that will be main one for your application (all others will extend it):

var MyAppModel = mysqlModel.createConnection({
  host     : 'database-host',
  user     : 'database-user',
  password : 'database-password',
  database : 'database-name',
});
 
var Movie = MyAppModel.extend({
    tableName: "movies",
});
 
movie = new Movie();
 
// OR
 
movie = new MyAppModel({tableName: "movies"});

To see complete list of options for creating a connection with the database visit felixge/node-mysql readme.

API

Model Settable Options

tableName

Name of a MySQL table the model will refer to:

var Movie = MyAppModel.extend({
    tableName: "movies",
});

Methods

find

Retrieves records from database

Usage:

movie.find();
movie.find(method);
movie.find(callback);
movie.find(method, conditions);
movie.find(method, callback);
movie.find(method, conditions, callback);

Parameters:

• string method: uses one of find methods
• object conditions: set find conditions
• function callback: returns errors and results

Example:

movie.find('all', {where: "year > 2001"}, function(err, rows, fields) {
    // Do something...
});

save

Saves your model to database

Usage:

movie.save();
movie.save(where);
movie.save(callback);
movie.save(where, callback);

Parameters:

• string where: set condition for WHERE
• function callback: returns errors and results

Example:

movie = new Movie({
    name: 'Serenity',
    director: 'Joss Whedon',
    language: 'English',
    year: 2005
});
// Will create new record
movie.save();
movie.set('id', 4);
// Will update record if id exists
movie.save();

remove

Deletes your model from database and unsets it

Usage:

movie.remove();
movie.remove(where);
movie.remove(callback);
movie.remove(where, callback);

Parameters:

• string where: set condition for WHERE
• function callback: returns errors and results

Example:

// Will delete record from database matching id model
movie.set('id', 8);
movie.remove();
// Will delete records from database matching where condition
movie.remove('year < 1980');

read

Retrieves record from database and set it to current model

Usage:

movie.read();
movie.read(id);
movie.read(callback);
movie.read(id, callback);

Parameters:

• integer id: Id of record to read
• function callback: returns errors and results

Example:

movie.set('id', 6);
movie.read();
// or
movie.read(6);

query

Runs custom query

Usage:

movie.query(query);
movie.query(query, callback);

Parameters:

• string query: Your custom sql query to run
• function callback: returns errors and results

Example:

movie.query("SELECT name FROM movies WHERE director = 'James Cameron' ORDER BY year", function(err, rows, fields) {
    // Do something...
});

setSQL

Method to replace 'set', when setting results passed back by node-mysql

Usage:

movie.setSQL(result);

Parameters:

• object result: Results passed back by find or read

Example:

movie.find('first', {where: "id=12"}, function(err, row) {
    movie.setSQL(row);
});

'find' methods

'all'

Returns all the records matching conditions

Returns:

• array

Example:

movie.find('all', {where: "language = 'German'", limit: [0, 30]}, function(err, rows) {
    for(var i=0; i<rows.length; i++) {
        console.log(rows[i]);
    }
});

'count'

Returns number of records matching conditions

Returns:

• integer

Example:

movie.find('count', {where: "year = 2012"}, function(err, result) {
        console.log(result);
});

'first'

Returns first the records matching conditions

Returns:

• object (hash)

Example:

movie.find('first', {where: "id = 3"}, function(err, row) {
        console.log(row);
});

'field'

Returns field of the first record matching conditions

Returns:

• depends on field type

Example:

movie.find('field', {fields: ['name'], where: "id = 3"}, function(err, field) {
        console.log(field);
});

'find' conditions

fields

Fields to select from the table

Accepts:

• array
• string

Example:

movie.find('all', {fields: ['id', 'name', 'year']});
// SELECT id, name, year FROM movies
movie.find('all', {fields: "name"});
// SELECT name FROM movies

where

Operators for MySQL WHERE clause.

Accepts:

• string

Example:

movie.find('all', {where: "year > 1987"});
// SELECT * FROM movies WHERE year > 1987

group

Operators for MySQL GROUP BY clause.

Accepts:

• array
• string

Example:

movie.find('all', {group: ['year', 'name']});
// SELECT * FROM movies GROUP BY year, name
movie.find('all', {group: "name"});
// SELECT * FROM movies GROUP BY name

groupDESC

If true, sets descending order for GROUP BY

Accepts:

• boolean

Example:

movie.find('all', {group: ['year', 'name'], groupDESC:true});
// SELECT * FROM movies GROUP BY year, name DESC

having

Operators for MySQL HAVING clause.

Accepts:

• string

Example:

movie.find('all', {fields: ['name', 'COUNT(name)'], group: "name", having: "COUNT(name) = 1"});
// SELECT name, COUNT(name) FROM movies GROUP BY name HAVING COUNT(name) = 1

order

Operators for MySQL ORDER BY clause.

Accepts:

• array
• string

Example:

movie.find('all', {group: ['year', 'name']});
// SELECT * FROM movies ORDER BY year, name
movie.find('all', {group: "name"});
// SELECT * FROM movies ORDER BY name

orderDESC

If true, sets descending order for ORDER BY

Accepts:

• boolean

Example:

movie.find('all', {group: ['year', 'name'], orderDESC:true});
// SELECT * FROM movies ORDER BY year, name DESC

limit

Operators for MySQL LIMIT clause.

Accepts:

• array
• string

Example:

movie.find('all', {limit: [0, 30]});
// SELECT * FROM movies LIMIT 0, 30
movie.find('all', {limit: "10, 40"});
// SELECT * FROM movies LIMIT 10, 40

Todo

• validation
• relations

License

node-mysql-model is released under MIT license.

Credits

node-mysql-model was created by Michał Kowalkowski. You can contact me at [e-mail]