Giter Club home page Giter Club logo

node-mysql-activerecord's Introduction

Note from author: I don't actively maintain this repository anymore. Huge thanks to Kyle Farris who has a more actively maintained version of the same thing available at (https://github.com/kylefarris/node-querybuilder) Please use that instead.

MySQL ActiveRecord Adapter for Node.js

Query builder on top of node-mysql module (https://github.com/felixge/node-mysql).

npm version

To me, the main benefit of is the ability to direct JavaScript objects straight to MySQL query components without having to worry about constructing the query itself. Although this query builder is a tiny step towards an ORM, I see a lot of value in the query builder as it allows more control over database queries than traditional ORM where queries are hidden behind the business logic and may become executed in an unoptimized way. (It is named after a popular PHP framework CodeIgniter's "Active Record" class, and thus the whole library does not have much in common with the active record pattern as such.)

This query builder is

  • Light-weight
  • Supports all basic MySQL commands
  • Supports method chaining
  • Automatically escapes field values
  • Has no dependencies (it already includes the node-mysql module)
  • Supports raw queries

How to install

npm install mysql-activerecord

Get started

var Db = require('mysql-activerecord');
var db = new Db.Adapter({
	server: 'localhost',
	username: 'root',
	password: '12345',
	database: 'test',
	reconnectTimeout: 2000
});
  • server: the IP address or hostname to connect to
  • username: MySQL username to connect with
  • password: MySQL password to connect with
  • database: database to switch to initially (optional). If omitted, no database will be selected.
  • port: which port to connect to (optional). If omitted, 3306 will be used.
  • reconnectTimeout: milliseconds after which to try to reconnect to the MySQL server if a disconnect happens (optional). If omitted, the default value of 2000 will be used. If set to false, no reconnecting will take place.

Support of MySQL commands

  • SELECT
  • UPDATE
  • INSERT (single-row and multi-row)
  • INSERT IGNORE
  • DELETE
  • JOIN
  • LIMIT and OFFSET
  • ORDER BY
  • GROUP BY
  • COUNT
  • HAVING

Methods

.select()

.select(selectFieldName)

Specifies the field(s) to use in the SELECT query as a atring.

db.select("id, CONCAT(first_name, ' ', last_name) as full_name, email");
// This would produce: SELECT id, CONCAT(first_name, ' ', last_name) as full_name, email …

You can call .select() multiple times within the scope of one query — all parameters will be used in the final query. E.g.

db.select('id');
// do some advanced checking and calculations here (only synchronous work, though!)
db.select('first_name, last_name');
// This would procude: SELECT id, first_name, last_name …

.select([selectFieldName, selectFieldName, … ])

Same as above, with a difference of taking in fields list as an array.

db.select(['id', 'first_name', 'last_name']);
// This would produce: SELECT id, first_name, last_name …

.where()

.where(rawClause)

Specifies a where clause component.

db.where('add_time is null');
// This would produce: … WHERE add_time is null …

You can call .where() multiple times within the scope of one query — all parameters will be used in the final query.

.where(fieldName, [possibleWhereInValue, possibleWhereInValue])

Specifies a WHERE IN structure to use in the query.

db.where('first_name', ['John', 'Maria', 'Jason', 'Herbert']);
// This would produce: … WHERE first_name in ('John', 'Maria', 'Jason', 'Herbert') …

.where(fieldName, fieldValue)

Specifies a single WHERE condition to use in the query.

db.where('first_name', 'John');
// This would produce: … WHERE first_name = 'John' …

.where({ fieldName: fieldValue, fieldName: fieldValue, … })

Specifies multiple WHERE conditions to use in the query.

var conditions = {
	first_name: 'John',
	last_name: 'Smith'
};
db.where(conditions);
// This would produce: … WHERE first_name = 'John' AND last_name = 'Smith' …

.order_by()

.order_by(orderByCondition)

Specifies the ORDER BY condition as a full string.

db.order_by('name asc');
// This would produce: … ORDER BY name asc …

You can call .order_by() multiple times within the scope of one query — all parameters will be used in the final query.

.order_by([orderByCondition, orderByCondition, … ])

Specifies multiple ORDER BY conditions as an array.

db.order_by(['name asc', 'last_name desc']);
// This would produce: … ORDER BY name asc, last_name desc …

.group_by()

.group_by(groupByCondition)

Specifies the GROUP BY condition as a full string.

db.group_by('name asc');
// This would produce: … GROUP BY name asc …

You can call .group_by() multiple times within the scope of one query — all parameters will be used in the final query.

.group_by([groupByCondition, groupByCondition, … ])

Specifies the GROUP BY condition as a full string.

db.group_by(['name asc', 'last_name desc']);
// This would produce: … GROUP BY name asc, last_name desc …

.join()

.join(tableName, joinCondition, joinDirection)

Join additional tables to the query.

db.join('pets', 'pets.owner_id = people.id', 'LEFT');
// This would produce: … LEFT JOIN pets ON pets.owner_id = people.id …

db.join('pets', 'pets.owner_id = people.id');
// This would produce: … JOIN pets ON pets.owner_id = people.id …

.limit()

.limit(limitNumber)

Adds a row limit to query results.

db.limit(10);
// Limits query results to 10 rows.

.limit(limitNumber, offsetNumber)

Adds a row limit with an offset pointer position to query results.

db.limit(10, 30);
// Limits query results to 10 rows, starting from the 30th row in the full matching set.

Query execution commands

After execution of a query, all query conditions are cleared. Results are passed down to responseCallback function. The parameters handed over to responseCallback match exactly what the underlying node-mysql module produces. See documentation from https://github.com/felixge/node-mysql

.update(tableName, newData, responseCallback)

Produces and executes UPDATE query.

db.update('people', { first_name: 'John', last_name: 'Smith' }, function(err) { ... });
// This would produce: … UPDATE people SET first_name = 'John', last_name = 'Smith' …

.delete(tableName, responseCallback)

Produces and executes DELETE query. Be sure to specify some WHERE clause components using .where() not to truncate an entire table. ✌

db.delete('people', function(err) { ... });

.insert(tableName, newData, responseCallback)

Produces and executes a single-row INSERT query.

db.insert('people', { first_name: 'John', last_name: 'Smith' }, function(err, info) { ... });
// This would produce: … INSERT INTO people SET first_name = 'John', last_name = 'Smith' …

.insert(tableName, [newData, newData, newData, …], responseCallback)

Produces and executes a multi-row INSERT query.

var person1 = { first_name: 'John', last_name: 'Smith' };
var person2 = { first_name: 'Jason', last_name: 'Binder' };
var person3 = { first_name: 'Herbert', last_name: 'von Kellogg' };
db.insert('people', [person1, person2, person3], function(err, info) { ... });
// This would produce: … INSERT INTO people (first_name, last_name) VALUES (('John','Smith'),('Jason','Binder'),('Herbert','von Kellogg')) …

.insert_ignore(tableName, newData, responseCallback, onDuplicateKeyClause)

Produces and executes an INSERT IGNORE query. Note that the newData parameter can be either a string (produces single-row INSERT) or an array (produces multi-row INSERT). You can also specify an optional onDuplicateKeyClause, e.g.

db.insert_ignore('people', { first_name: 'John', last_name: 'Smith' }, function(err, info) { ... }, 'ON DUPLICATE KEY UPDATE duplicate_count = duplicate_count + 1');
// This would produce: … INSERT IGNORE INTO people SET first_name = 'John', last_name = 'Smith' … ON DUPLICATE KEY UPDATE duplicate_count = duplicate_count + 1

.get(tableName, responseCallback)

Produces and executes a SELECT query.

db.get('people', function(err, rows, fields) { ... });
// This would produce: SELECT … FROM people …

.count(tableName, responseCallback)

Produces and executes a SELECT query with count.

db.get('people', function(err, rows, fields) { ... });
// This would produce: SELECT count(*) FROM people …

.query(sqlQueryString, responseCallback)

Produces and executes a raw query. Note that while no set query conditions will be used in this query, they will all be reset nevertheless with the execution.

db.query('SHOW TABLES FROM test_database', function(err, results) { ... });

.ping()

Pings the connection. This is useful when extending idle timeouts.

._last_query()

Returns the last executed query as a string.

.connection()

Returns the underlying database connection object, ultimately what https://github.com/felixge/node-mysql .createConnection() returns.

Pooling connections

Single or multiple connections can be pooled with the Pool object.

var Db = require('mysql-activerecord');

var pool = new Db.Pool({
	server: 'localhost',
	username: 'root',
	password: '12345',
	database: 'test'
});

pool.getNewAdapter(function(db) {
	db
		.where({ name: 'Martin' })
		.get('people', function(err, results, fields) {
			console.log(results);
			db.releaseConnection();
			// do not do anything with db that has been released.
		});
});

Some more usage examples

Establishing a connection

var Db = require('mysql-activerecord');
var db = new Db.Adapter({
	server: 'localhost',
	username: 'root',
	password: '12345',
	database: 'test'
});

Basic SELECT query

db.get('people', function(err, results, fields) {
	console.log(results);
});

INSERT query

var data = {
	name: 'Martin',
	email: '[email protected]'
};

db.insert('people', data, function(err, info) {
	console.log('New row ID is ' + info.insertId);
});

INSERT IGNORE query with ON DUPLICATE KEY clause

var data = {
	name: 'Martin',
	email: '[email protected]'
};

db.insert_ignore('people', data, function(err, info) {
	console.log('New row ID is ' + info.insertId);
}, 'ON DUPLICATE KEY SET counter = counter + 1');

SELECT query with WHERE clause

db
	.where({ name: 'Martin' })
	.get('people', function(err, results, fields) {
		console.log(results);
	});

SELECT query with custom fields, WHERE, JOIN and LIMIT

db
	.select(['people.id', 'people.name', 'people.email', 'songs.title'])
	.join('songs', 'people.favorite_song_id', 'left')
	.where({
		'people.name': 'Martin',
		'songs.title': 'Yesterday'
	})
	.limit(5, 10)
	.order_by('people.name asc')
	.get('people', function(err, results, fields) {
		console.log(results);
	});

Basic counting

db
	.where({
		'people.name': 'Martin',
		'songs.title': 'Yesterday'
	})
	.count('people', function(err, results, fields) {
		console.log(results);
	});

SELECT query with custom fields and GROUP BY

db
	.select('name, COUNT(name) AS name_count')
	.group_by('name')
	.order_by('name_count DESC')
	.get('people', function(err, results, fields) {
		console.log(results);
	});

Basic UPDATE query

var newData = {
	name: 'John',
	email: '[email protected]'
};

db
	.where({ id: 1 })
	.update('people', newData, function(err) {
		if (!err) {
			console.log('Updated!');
		}
	});

Basic DELETE query

db
	.where({ id: 1 })
	.delete('people', function(err) {
		if (!err) {
			console.log('Deleted!')
		}
	});

Advanced WHERE conditions

db
	.where("title not like '%Jackson%'")
	.where("date_created > '2012-03-10'")
	.where({ owner_id: 32 })
	.delete('records', function(err) {
		if (!err) {
			console.log('Deleted!')
		}
	});

Contribute

Got a missing feature you'd like to use? Found a bug? Go ahead and fork this repo, build the feature and issue a pull request.

Licence info

Licensed under the GPL license and MIT:

node-mysql-activerecord's People

Contributors

bearburger avatar danielb2 avatar jutaz avatar kylefarris avatar martintajur avatar mpukk avatar sankar4n avatar serkanserttop avatar

Stargazers

 avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar

Watchers

 avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar

node-mysql-activerecord's Issues

handler to connection

it would be nice if there was a way to get a hold of connection directly.

just this.connection = connection after the connection declaration.

Otherwise, how can I use this to truncate a table? I'd have to create another connection handler.

Update increment is not possible via update()

I'm trying to run the following query:

 var newData = {stock: 'stock + 1'};
 db.where({id: data.id}).update('products', newData, ...

The generated sql is

UPDATE `products` SET `stock`='stock+1' WHERE `id`=1

which - due to the single quotation around stock+1 - will result in a 0 stock value.
As a workaround I can still run the query directly, however it'd be nice to have this to work.

Not relevant name of the module

Actually a functionality in this module isn't could be called 'Active Record'.

In Active Record every table in the database is represented by the separate class in the code, and any instance of this class represents an one record of appropriate table.

The using of this module is sort of using helpers for building SQL queries and running them (I don't know the name of a pattern for this, if one exists).

While an example of mapping as Active Record would be like this:

// Defining the model
Person = db.ActiveRecord.extend({
    fields :[name, email, phone, city],
    tableName: 'people'
});


// Using it for creating records
var newPerson = new Person({
    name : 'John',
    email: '[email protected]', 
});

newPerson.save(function() {
    // now the table is updated
});


// Using the model for selecting and changing
Person.find({
    city: 'London'
}, doSomeProcessingAndUpdatePhone);


function doSomeProcessingAndUpdatePhone(people) {
    people.each(function(person) {
        var phone = anotherModule.getPhoneByPersonId(person.id);
        person.phone = phone;

        anotherModule.doSomeProcessing(person);

        // at this moment changes aren't stored in database

        person.save(function() {
            // now the record in the table 'people' is updated                        
        });
    });
}

That's why, the module name without "activerecord" would be more relevant.

When I pass an object with a null property, I get a misformed sql statement

When I pass an object with a null property, I get a misformed sql statement:

db.where({ id: node.id })
        .update('nodes', {something: 1, somethingElse: null}, function(err) {
            console.log(err);
        });

Produces something like:

UPDATE nodes SET something=1, WHERE id=4

Note the extraneous comma after something=1

typo

search in readme~
where({ id: 1 });
.update('people', newData

the ; is useless

disconnet from mysql

hello, when i used 'db.releaseConnection()' to disconnect from msyql,i got error. How can i disconnect from mysql? thank you.

Multiple tables

Please add the ability to specify multiple tables.
Sample:

    this.get = function(tableName, responseCallback) {
        if (typeof tableName.push === 'function') {
            for (var i = 0; i < tableName.length; i++) {
                if (typeof tableName[i] === 'string') {
                    tableName[i] = escapeFieldName(tableName[i]);
                } else {
                    tableName.splice(i,1);
                }
            }
            tableName = tableName.join(',');
        }
        if (typeof tableName === 'string') {
            var combinedQueryString = 'SELECT ' + (selectClause.length === 0 ? '*' : selectClause.join(','))
            + ' FROM ' + tableName
            + buildJoinString()
            + buildDataString(whereClause, ' AND ', 'WHERE')
            + (orderByClause !== '' ? ' ORDER BY ' + orderByClause : '')
            + (limitClause !== -1 ? ' LIMIT ' + limitClause : '')
            + (offsetClause !== -1 ? ' OFFSET ' + offsetClause : '');

            connection.query(combinedQueryString, responseCallback);
            resetQuery(combinedQueryString);
        }

        return that;
    };

Thanks!

Update the module on NPM

Please update the module on NPM. I got a version that doesn't have the Pool object as described on your documentation.

Thanks.

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.