Query

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

BaseModel class has some methods that borrowed from Query to run simple queries easily.

const users = await User.query().where(\{ age: 27 }).exec();

// or

const users = await User.where(\{ age: 27 }).exec();

Filter

You can select records with Query#where or Query#find. where's criteria is similar to the MongoDB's. Two or more wheres mean a logical and.

Description	CORMO	SQL	MongoDB
Equal	User.where({ age: 27 })	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> }), but find throws an exception when records are not found while where does not.

Conditional activation

If you want to apply different criteria in one query chain, you can use Query#if and Query#endif. You can use them to simplify query statements.

async function getOldUsers(options: \{ limit?: number; columns?: string[] }) \{
  const query = User.query();
  query.where(\{ age: \{ $gt: 30 } });
  if (options.limit) \{
    query.limit(options.limit);
  }
  if (options.columns) \{
    query.select(options.columns as any);
  }
  return await query.exec();
}

// wiil be

async function getOldUsers(options: \{ limit?: number; columns?: string[] }) \{
  return await User.query()
    .where(\{ age: \{ $gt: 30 } })
    .if(options.limit != null)
    .limit(options.limit)
    .endif()
    .if(options.columns != null)
    .select(options.columns as any)
    .endif()
    .exec();
}

Retrieve records

Query#exec retrieves records.

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

const user = await User.find(1).exec();
const users = await User.find([1, 2, 3]).exec();

Query has the then method (i.e. thenable) which calls exec internally. So you can omit to call exec, just await.

const users = await User.where(\{ age: 30 });

Query#find will throw an error if any ID is not found. find does not preserve given order, so if you want to get same ordered array, use Query#findPreserve instead.

const users = await User.findPreserve([2, 1, 2, 3]).exec();
// users[0].id is 2, users[1].id is 1, users[2].id is 2 and users[3].id is 3

You can give some options for retrieving.

Description	CORMO	SQL	MongoDB
Projection	User.select(['id', 'name', 'age'])	SELECT id,name,age FROM users	db.users.find({}, { name: 1, age: 1 })
Sort	User.order('age -name')	SELECT * FROM users ORDER BY age ASC, name DESC	db.users.find().sort({ age: 1, name: -1 })
Limit	User.query().limit(3)	SELECT * FROM users LIMIT 3	db.users.find().limit(3)
Skip	User.query().skip(3)	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.

const user = await User.where(\{ age: 27 }).one();

Select single column

If you interest only a column, you can use Query#selectSingle. Then the query object will return a value or an array of values instead of Model instances.

const user_ids = await User.where(\{ age: 27 }).selectSingle('id');
const user_name = await User.find(1).selectSingle('name');

Stream the result

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

let count = 0;
await new Promise((resolve, reject) => \{
  const stream = User.where(\{ age: 27 }).stream();
  stream.on('data', function (user) \{
    count++;
  });
  stream.on('end', function () \{
    resolve();
  });
});

Count records

Query#count returns the count of records.

CORMO	SQL	MongoDB
User.count()	SELECT COUNT(*) FROM users	db.users.count()
User.count({age: 27})	SELECT COUNT(*) FROM users WHERE age=27	db.users.find({age: 27}).count()
User.where({age: 27}).count()

Update records

To update records, BaseModel#save and Query#update are provided.

BaseModel#save is used to update a single retrieved record.

const user = await User.find(1);
user.age = 30;
await user.save();

Meanwhile, Query#update updates filtered records.

CORMO	SQL	MongoDB
User.update({ age: 10 }, { age: 27 })	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 })
User.find(1).update({ age: 10 })	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 } })	UPDATE users SET age=age+3 WHERE id=2	db.users.update({_id: 2}, {$inc: {age: 3}}, {multi: true})

Query#update may be faster because only update command will be sent to the database system. Instead you will not get modified objects and update callbacks will not be called.

Choose one according to your needs.

In CORMO, Active Record pattern (i.e. BaseModel#save) is not battle-tested. So use it with caution.

Delete records

Query#delete or BaseModel#destroy deletes some records.

BaseModel#destroy deletes one record by ID similar to BaseModel#save. And Query#delete deletes filtered records like Query#update.

CORMO	SQL	MongoDB
User.delete({age: 27})	DELETE FROM users WHERE age=27	db.users.remove({age: 27})
User.where({age: 27}).delete()
User.delete()	DELETE FROM users	db.users.remove()