A lightweight MySQL query builder on top of the node-mysql module.
npm install mysql-activerecordMySQL ActiveRecord Adapter for Node.js
======================================
Query builder on top of node-mysql module (https://github.com/felixge/node-mysql).
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
=======
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 …
db.select(['id', 'first_name', 'last_name']);
// This would produce: SELECT id, first_name, last_name …
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.
db.where('first_name', ['John', 'Maria', 'Jason', 'Herbert']);
// This would produce: … WHERE first_name in ('John', 'Maria', 'Jason', 'Herbert') …
db.where('first_name', 'John');
// This would produce: … WHERE first_name = 'John' …
var conditions = {
first_name: 'John',
last_name: 'Smith'
};
db.where(conditions);
// This would produce: … WHERE first_name = 'John' AND last_name = 'Smith' …
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.
db.order_by(['name asc', 'last_name desc']);
// This would produce: … ORDER BY name asc, last_name desc …
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.
db.group_by(['name asc', 'last_name desc']);
// This would produce: … GROUP BY name asc, last_name desc …
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 …
db.limit(10);
// Limits query results to 10 rows.
db.limit(10, 30);
// Limits query results to 10 rows, starting from the 30th row in the full matching set.
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
db.update('people', { first_name: 'John', last_name: 'Smith' }, function(err) { ... });
// This would produce: … UPDATE people SET first_name = 'John', last_name = 'Smith' …
db.delete('people', function(err) { ... });
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' …
db.get('people', function(err, rows, fields) { ... });
// This would produce: SELECT … FROM people …
db.get('people', function(err, rows, fields) { ... });
// This would produce: SELECT count(*) FROM people …
db.query('SHOW TABLES FROM test_database', function(err, results) { ... });
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: 'martin@example.com'
};
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: 'martin@example.com'
};
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: 'john@doe.com'
};
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:
* http://www.opensource.org/licenses/GPL-license.php
* http://www.opensource.org/licenses/mit-license.php