To query, create a query object using ModelQuery.query first. Then build up a query by chaining methods, and run a query by Query::exec, Query::count, Query::update, or Query::delete.

Also, ModelQuery class has some methods that borrowed from Query to run simple queries easily.

User.query().where(age: 27).exec (error, users) ->
  console.log users

User.where(age: 27).exec (error, users) ->
  console.log users

User.where age:27, (error, users) ->
  console.log users
User.query().where({age: 27}).exec(function (error, users) {
  console.log(users);
});

User.where({age: 27}).exec(function (error, users) {
  console.log(users);
});

User.where({age:27}, function (error, users) {
  console.log(users);
});

Selection criteria

You can give criteria for selection with Query::where or Query::find. Query::where's criteria is similar to the MongoDB's. Two or more Query::wheres mean a logical and.

DescriptionCORMOSQLMongoDB
Equal User.where(age: 27).exec (error, users) -> SELECT * FROM users WHERE age=27 db.users.find({age: 27})
Logical and name: 'John Doe', age: 27 name='John Doe' AND age=27 { name: 'John Doe', age: 27 }
.where(name: 'John Doe').where(age: 27)
$and: [ { name: 'John Doe' }, { age: 27 } ]
[ { name: 'John Doe' }, { age: 27 } ]
Logical or $or: [ { name: 'John Doe' }, { age: 27 } ] name='John Doe' OR age=27 { $or: [ { name: 'John Doe' }, { age: 27 } ] }
Comparison ($lt, $gt, $lte, $gte) [ { age: { $gt: 30 } }, { age: { $lte: 45 } } ] age>30 AND age<=45 { $and: [ { age: { $gt: 30 } }, { age: { $lte: 45 } } ] }
Containing some text in case insensitive name: { $contains: 'smi' } name LIKE '%smi%' { name: /smi/i }
name: { $contains: ['smi', 'doe'] } name LIKE '%smi%' OR name LIKE '%doe%' { name: { $in: [/smi/i, /doe/i] } }
Starts with some text in case insensitive name: { $startswith: 'smi' } name LIKE 'smi%' { name: /^smi/i }
Ends with some text in case insensitive name: { $endswith: 'smi' } name LIKE '%smi' { name: /smi$/i }
Regular expression name: /smi/ name REGEXP 'smi' { name: /smi/i }
name: /smi|doe/ name REGEXP 'smi|doe' { name: /smi|doe/i }
Matches any of an array age: { $in: [ 10, 20, 30 ] } age IN (10,20,30) { age: { $in: [ 10, 20, 30 ] } }
age: [ 10, 20, 30 ]
Logical not age: $not: 27 NOT (age=27) OR age IS NULL { age: { $ne: 27 } }
age: $not: $lt: 27 NOT (age<27) OR age IS NULL { age: { $not: { $lt: 27 } } }
name: $not: $contains: 'smi' NOT (name LIKE '%smi%') OR name IS NULL { name: { $not: /smi/i } }
name: $not: $contains: ['smi', 'doe'] NOT (name LIKE '%smi%' OR name LIKE '%doe%') OR name IS NULL { name: { $nin: [/smi/i, /doe/i] } }
age: $not: $in: [ 10, 20, 30 ] NOT (age IN (10,20,30)) OR age IS NULL { age: { $nin: [10,20,30] } }
age: $not: [ 10, 20, 30 ]
name: $not: null NOT name IS NULL { age: { $ne: null } }

If you want find records based on the identifier, use Query::find that accepts an ID or an array of IDs. It is logically same to '.where(id: <given ID or array of IDs>)'.

Conditional criteria

If you want to apply different criteria in one query chain, you can use Query::if, Query::endif. It is helpful such as when you corresspond one REST API to one query.

getUsers = (options) ->
  User.query()
  .where(age: $gt: 30)
  .if(options.limit).limit(options.limit).endif()
  .if(options.fields).select(options.fields).endif()
  .exec()
function getUsers(options) {
  return User.query()
  .where({age: {$gt: 30}})
  ["if"](options.limit).limit(options.limit).endif()
  ["if"](options.fields).select(options.fields).endif()
  .exec();
};

Retrieve records

Query::exec retrieves records.

It normally returns an array of Model instances. But if you use Query::find for a single ID, it will return a single Model instance.

User.find 1, (error, user) ->
  console.log user

User.find [1,2,3], (error, users) ->
  console.log users
User.find(1, function (error, user) {
  console.log(user);
});

User.find([1,2,3], function (error, users) {
  console.log(users);
});

Query::find does not return error if any ID is found and does not preserve given order. If you want to guarantee that you get all records of IDs and order is preserved, use Query::findPreserve instead.

User.findPreserve [2,1,2,3], (error, users) ->
  # users[0].id is 2 and users[1].id is 1 and users[2].id is 2 and users[3].id is 3
User.findPreserve([2,1,2,3], function (error, users) {
  // users[0].id is 2 and users[1].id is 1 and users[2].id is 2 and users[3].id is 3
});

You can give some options to Query::exec.

DescriptionCORMOSQLMongoDB
Projection User.select('name age').exec SELECT id,name,age FROM users db.users.find({}, { name: 1, age: 1 })
Sort User.order('age -name').exec SELECT \* FROM users ORDER BY age ASC, name DESC db.users.find().sort({ age: 1, name: -1 })
Limit User.query().limit(3).exec SELECT \* FROM users LIMIT 3 db.users.find().limit(3)
Skip User.query().skip(3).exec SELECT * FROM users LIMIT 2147483647 OFFSET 3 db.users.find().skip(3)

Request only one record

If you know that there will be only one result (e.x. query on unique column), Query::one will be helpful. It makes a query return a single instance (or null) instead of array of instances.

User.where(age: 27).one().exec (error, user) ->
  console.log user
User.where({age: 27}).one().exec(function (error, user) {
  console.log(user);
});

Stream the result

If the result has many records, you can use Node.js stream API to reduce memory usage.

count = 0
User.where(age: 27).stream()
.on 'data', (user) ->
  count++
.on 'end', ->
count = 0;
User.where({age: 27}).stream()
.on('data', function (user) {
  count++;
}).on('end', function () {
});

Count records

Query::count returns the count of records.

CORMOSQLMongoDB
User.count (error, count) -> SELECT COUNT(\*) FROM users db.users.count()
User.count age: 27, (error, count) -> SELECT COUNT(\*) FROM users WHERE age=27 db.users.find({age: 27}).count()
User.where(age: 27).count (error, count) ->

Update records

To update records, ModelPersistence::save and Query::update are provided.

ModelPersistence::save is used to update a single retrieved record.

User.find 1, (error, user) ->
  user.age = 30
  user.save (error) ->
User.find(1, function (error, user) {
  user.age = 30;
  user.save(function (error) {
  });
});

But ModelPersistence::save has some weaknesses.

  • You must retrieve a record before modification.
    • In normal application, retrieved data may not be used usually.
  • This requires to read all fields from database and send all fields to database.
    • If you save projected(Query::select) record, other fields will set to null.
    • CORMO has the partial update option(Model.dirty_tracking). But currently it rather is slow. So it is turned off by default.

Query::update updates selected records.

CORMOSQLMongoDB
User.update { age: 10 }, age: 27, (error, count) -> UPDATE users SET age=10 WHERE age=27 db.users.update({age: 27}, {$set: {age: 10}}, {multi: true})
User.where(age: 27).update age:10, (error, count) ->
User.find(1).update age: 10, (error, count) -> UPDATE users SET age=10 WHERE id=1 db.users.update({_id: 1}, {$set: {age: 10}}, {multi: true})
User.find(2).update age: $inc: 3, (error, count) -> UPDATE users SET age=age+3 WHERE id=2 db.users.update({_id: 2}, {$inc: {age: 3}}, {multi: true})

But you cannot use other column's value as data like SQL does on Query::update. If you want this, you must retrieve the record first. But Query::update may be more efficient than ModelPersistence::save even if in that case.

User.find 1, (error, user) ->
  age = user.age + 1
  User.find(user.id).update age: age, (error, count) ->
User.find(1, function (error, user) {
  var age = user.age + 1;
  User.find(user.id).update({age: age}, function (error, count) {
  });
});

Delete records

Query::delete or Model::destroy deletes some records. Model::destroy is similar to Query::delete on the model's ID.

CORMOSQLMongoDB
User.delete age: 27, (error, count) -> DELETE FROM users WHERE age=27 db.users.remove({age: 27})
User.where(age: 27).delete (error, count) ->
User.delete (error, count) -> DELETE FROM users db.users.remove()
Fork me on GitHub