A backbone based model for communicating with a MySQL database using felixge/node-mysql.
JavaScript
Switch branches/tags
Nothing to show
Clone or download
michalkow Revert "git only"
This reverts commit 3904c55.
Latest commit e20b871 Jul 3, 2017
Permalink
Failed to load latest commit information.
test cleanup and fix May 22, 2017
.gitignore testing May 19, 2017
.travis.yml cleanup and fix May 22, 2017
License Uploading files Oct 31, 2012
README.md Revert "git only" Jul 3, 2017
mysql-model.js more tests May 22, 2017
package.json cleanup and fix May 22, 2017

README.md

node-mysql-model

Build Status

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 kowalkowski.michal@gmail.com