icMySQL
Functions
ORM
Basics

All these documentation has been taken from Sequelize (opens in a new tab) and modified to fit the project. Sequelize provides various methods to assist querying your database for data.

Important notice: to perform production-ready queries with Sequelize, make sure you have read the Transactions guide as well. Transactions are important to ensure data integrity and to provide other benefits.

This guide will show how to make the standard CRUD queries.

Simple INSERT queries

First, a simple example:

// Create a new user
const jane = await MySQL.ORM.Create("players", { firstName: "Jane", lastName: "Doe" });
console.log("Jane's auto-generated ID:", jane.id);

The Model.create() method is a shorthand for building an unsaved instance with Model.build() and saving the instance with instance.save().

It is also possible to define which attributes can be set in the create method. This can be especially useful if you create database entries based on a form which can be filled by a user. Using that would, for example, allow you to restrict the User model to set only an username but not an admin flag (i.e., isAdmin):

const user = await MySQL.ORM.Create({
  username: 'alice123',
  isAdmin: true
}, { fields: ['username'] });
// let's assume the default of isAdmin is false
console.log(user.username); // 'alice123'
console.log(user.isAdmin); // false

Simple SELECT queries

You can read the whole table from the database with the findAll method:

// Find all users
const users = await MySQL.ORM.FindAll();
console.log(users.every(user => user instanceof User)); // true
console.log("All users:", JSON.stringify(users, null, 2));
SELECT * FROM ...

Specifying attributes for SELECT queries

To select only some attributes, you can use the attributes option:

MySQL.ORM.FindAll({
  attributes: ['foo', 'bar']
});
SELECT foo, bar FROM ...

Attributes can be renamed using a nested array:

MySQL.ORM.FindAll({
  attributes: ['foo', ['bar', 'baz'], 'qux']
});
SELECT foo, bar AS baz, qux FROM ...

When using aggregation function, you must give it an alias to be able to access it from the model. In the example above you can get the number of hats with instance.n_hats.

Sometimes it may be tiresome to list all the attributes of the model if you only want to add an aggregation:

Similarly, it's also possible to remove a selected few attributes:

MySQL.ORM.FindAll({
  attributes: { exclude: ['baz'] }
});
-- Assuming all columns are 'id', 'foo', 'bar', 'baz' and 'qux'
SELECT id, foo, bar, qux FROM ...

Applying WHERE clauses

The where option is used to filter the query. There are lots of operators to use for the where clause, available as Symbols from Op.

The basics

MySQL.ORM.FindAll({
  where: {
    authorId: 2
  }
});
// SELECT * FROM post WHERE authorId = 2;

Observe that no operator (from Op) was explicitly passed, so Sequelize assumed an equality comparison by default. The above code is equivalent to:

MySQL.ORM.FindAll({
  where: {
    authorId: {
      ["="]: 2
    }
  }
});
// SELECT * FROM post WHERE authorId = 2;

Multiple checks can be passed:

MySQL.ORM.FindAll({
  where: {
    authorId: 12,
    status: 'active'
  }
});
// SELECT * FROM post WHERE authorId = 12 AND status = 'active';

Just like Sequelize inferred the Op.eq operator in the first example, here Sequelize inferred that the caller wanted an AND for the two checks. The code above is equivalent to:

MySQL.ORM.FindAll({
  where: {
    ["and"]: [
      { authorId: 12 },
      { status: 'active' }
    ]
  }
});
// SELECT * FROM post WHERE authorId = 12 AND status = 'active';

An OR can be easily performed in a similar way:

MySQL.ORM.FindAll({
  where: {
    ["or"]: [
      { authorId: 12 },
      { authorId: 13 }
    ]
  }
});
// SELECT * FROM post WHERE authorId = 12 OR authorId = 13;

Since the above was an OR involving the same field, Sequelize allows you to use a slightly different structure which is more readable and generates the same behavior:

MySQL.ORM.Destroy({
  where: {
    authorId: {
      ["or"]: [12, 13]
    }
  }
});
// DELETE FROM post WHERE authorId = 12 OR authorId = 13;

Operators

Sequelize provides several operators.

MySQL.ORM.FindAll({
  where: {
    ["and"]: [{ a: 5 }, { b: 6 }],            // (a = 5) AND (b = 6)
    ["or"]: [{ a: 5 }, { b: 6 }],             // (a = 5) OR (b = 6)
    someAttribute: {
      // Basics
      ["="]: 3,                              // = 3
      ["!="]: 20,                             // != 20
      ["==="]: null,                           // IS NULL
      ["!"]: true,                          // IS NOT TRUE
      ["or"]: [5, 6],                         // (someAttribute = 5) OR (someAttribute = 6)
 
      // Using dialect specific column identifiers (PG in the following example):
      ["col"]: 'user.organization_id',        // = "user"."organization_id"
 
      // Number comparisons
      [">"]: 6,                              // > 6
      [">="]: 6,                             // >= 6
      ["<"]: 10,                             // < 10
      ["<="]: 10,                            // <= 10
      ["between"]: [6, 10],                   // BETWEEN 6 AND 10
      ["notBetween"]: [11, 15],               // NOT BETWEEN 11 AND 15
 
      // Other operators
 
      ["in"]: [1, 2],                         // IN [1, 2]
      ["notIn"]: [1, 2],                      // NOT IN [1, 2]
 
      ["like"]: '%hat',                       // LIKE '%hat'
      ["notLike"]: '%hat',                    // NOT LIKE '%hat'
      ["startsWith"]: 'hat',                  // LIKE 'hat%'
      ["endsWith"]: 'hat',                    // LIKE '%hat'
      ["substring"]: 'hat',                   // LIKE '%hat%'
      ["iLike"]: '%hat',                      // ILIKE '%hat' (case insensitive) (PG only)
      ["notILike"]: '%hat',                   // NOT ILIKE '%hat'  (PG only)
      ["regexp"]: '^[h|a|t]',                 // REGEXP/~ '^[h|a|t]' (MySQL/PG only)
      ["notRegexp"]: '^[h|a|t]',              // NOT REGEXP/!~ '^[h|a|t]' (MySQL/PG only)
      ["iRegexp"]: '^[h|a|t]',                // ~* '^[h|a|t]' (PG only)
      ["notIRegexp"]: '^[h|a|t]',             // !~* '^[h|a|t]' (PG only)
 
      ["any"]: [2, 3],                        // ANY (ARRAY[2, 3]::INTEGER[]) (PG only)
 
      // In Postgres, Op.like/Op.iLike/Op.notLike can be combined to Op.any:
      ["like"]: { ["any"]: ['cat', 'hat'] }  // LIKE ANY (ARRAY['cat', 'hat'])
 
      // There are more postgres-only range operators, see below
    }
  }
});

Shorthand syntax for in

Passing an array directly to the where option will implicitly use the IN operator:

MySQL.ORM.FindAll({
  where: {
    id: [1,2,3] // Same as using `id: { ["in"]: [1,2,3] }`
  }
});
// SELECT ... FROM "posts" AS "post" WHERE "post"."id" IN (1, 2, 3);

Logical combinations with operators

The operators and, or and not can be used to create arbitrarily complex nested logical comparisons.

Examples with and and or

MySQL.ORM.FindAll({
  where: {
    rank: {
      ["or"]: {
        ["<"]: 1000,
        ["="]: null
      }
    },
    // rank < 1000 OR rank IS NULL
 
    {
      createdAt: {
        ["<"]: new Date(),
        [">]: new Date(new Date() - 24 * 60 * 60 * 1000)
      }
    },
    // createdAt < [timestamp] AND createdAt > [timestamp]
 
    {
      ["or"]: [
        {
          title: {
            ["like"]: 'Boat%'
          }
        },
        {
          description: {
            ["like"]: '%boat%'
          }
        }
      ]
    }
    // title LIKE 'Boat%' OR description LIKE '%boat%'
  }
});

Examples with not

MySQL.ORM.FindAll({
  where: {
    name: 'Some Project',
    ["!"]: [
      { id: [1,2,3] },
      {
        description: {
          ["like"]: 'Hello%'
        }
      }
    ]
  }
});

The above will generate:

SELECT *
FROM `Projects`
WHERE (
  `Projects`.`name` = 'Some Project'
  AND NOT (
    `Projects`.`id` IN (1,2,3)
    AND
    `Projects`.`description` LIKE 'Hello%'
  )
)

Postgres-only Range Operators

Range types can be queried with all supported operators.

Keep in mind, the provided range value can define the bound inclusion/exclusion as well.

["contains"]: 2,            // @> '2'::integer  (PG range contains element operator)
["contains"]: [1, 2],       // @> [1, 2)        (PG range contains range operator)
["contained"]: [1, 2],      // <@ [1, 2)        (PG range is contained by operator)
["overlap"]: [1, 2],        // && [1, 2)        (PG range overlap (have points in common) operator)
["adjacent"]: [1, 2],       // -|- [1, 2)       (PG range is adjacent to operator)
["strictLeft"]: [1, 2],     // << [1, 2)        (PG range strictly left of operator)
["strictRight"]: [1, 2],    // >> [1, 2)        (PG range strictly right of operator)
["noExtendRight"]: [1, 2],  // &< [1, 2)        (PG range does not extend to the right of operator)
["noExtendLeft"]: [1, 2],   // &> [1, 2)        (PG range does not extend to the left of operator)

Simple UPDATE queries

Update queries also accept the where option, just like the read queries shown above.

// Change everyone without a last name to "Doe"
await MySQL.ORM.Modify({ lastName: "Doe" }, {
  where: {
    lastName: null
  }
});

Simple DELETE queries

Delete queries also accept the where option, just like the read queries shown above.

// Delete everyone named "Jane"
await MySQL.ORM.Destroy({
  where: {
    firstName: "Jane"
  }
});

To destroy everything the TRUNCATE SQL can be used:

// Truncate the table
await MySQL.ORM.Destroy({
  truncate: true
});

Creating in bulk

Sequelize provides the Model.bulkCreate method to allow creating multiple records at once, with only one query.

The usage of Model.bulkCreate is very similar to Model.create, by receiving an array of objects instead of a single object.

const captains = await MySQL.ORM.BulkCreate([
  { name: 'Jack Sparrow' },
  { name: 'Davy Jones' }
]);
console.log(captains.length); // 2
console.log(captains[0] instanceof Captain); // true
console.log(captains[0].name); // 'Jack Sparrow'
console.log(captains[0].id); // 1 // (or another auto-generated value)

However, by default, bulkCreate does not run validations on each object that is going to be created (which create does). To make bulkCreate run these validations as well, you must pass the validate: true option. This will decrease performance. Usage example:

// This will not throw an error, both instances will be created
await MySQL.ORM.BulkCreate([
  { name: 'abc123' },
  { name: 'name too long' }
]);
 
// This will throw an error, nothing will be created
await MySQL.ORM.BulkCreate([
  { name: 'abc123' },
  { name: 'name too long' }
], { validate: true });

If you are accepting values directly from the user, it might be beneficial to limit the columns that you want to actually insert. To support this, bulkCreate() accepts a fields option, an array defining which fields must be considered (the rest will be ignored).

await MySQL.ORM.BulkCreate([
  { username: 'foo' },
  { username: 'bar', admin: true }
], { fields: ['username'] });
// Neither foo nor bar are admins.

Ordering and Grouping

Sequelize provides the order and group options to work with ORDER BY and GROUP BY.

Ordering

The order option takes an array of items to order the query by or a sequelize method. These items are themselves arrays in the form [column, direction]. The column will be escaped correctly and the direction will be checked in a whitelist of valid directions (such as ASC, DESC, NULLS FIRST, etc).

MySQL.ORM.FindAll({
  order: [
    // Will escape title and validate DESC against a list of valid direction parameters
    ['title', 'DESC'],
 
    // Will order an associated model's createdAt using the model name as the association's name.
    [Task, 'createdAt', 'DESC'],
 
    // Will order through an associated model's createdAt using the model names as the associations' names.
    [Task, Project, 'createdAt', 'DESC'],
 
    // Will order by an associated model's createdAt using the name of the association.
    ['Task', 'createdAt', 'DESC'],
 
    // Will order by a nested associated model's createdAt using the names of the associations.
    ['Task', 'Project', 'createdAt', 'DESC'],
 
    // Will order by an associated model's createdAt using an association object. (preferred method)
    [Subtask.associations.Task, 'createdAt', 'DESC'],
 
    // Will order by a nested associated model's createdAt using association objects. (preferred method)
    [Subtask.associations.Task, Task.associations.Project, 'createdAt', 'DESC'],
 
    // Will order by an associated model's createdAt using a simple association object.
    [{model: Task, as: 'Task'}, 'createdAt', 'DESC'],
 
    // Will order by a nested associated model's createdAt simple association objects.
    [{model: Task, as: 'Task'}, {model: Project, as: 'Project'}, 'createdAt', 'DESC']
  ],
});
 
MySQL.ORM.FindOne({
  order: [
    // will return `name`
    ['name'],
    // will return `username` DESC
    ['username', 'DESC'],
  ]
});

To recap, the elements of the order array can be the following:

  • A string (which will be automatically quoted)
  • An array, whose first element will be quoted, second will be appended verbatim
  • An object with a raw field:
    • The content of raw will be added verbatim without quoting
    • Everything else is ignored, and if raw is not set, the query will fail

Grouping

The syntax for grouping and ordering are equal, except that grouping does not accept a direction as last argument of the array (there is no ASC, DESC, NULLS FIRST, etc).

You can also pass a string directly to group, which will be included directly (verbatim) into the generated SQL. Use with caution and don't use with user generated content.

MySQL.ORM.FindAll({ group: 'name' });
// yields 'GROUP BY name'

Limits and Pagination

The limit and offset options allow you to work with limiting / pagination:

// Fetch 10 instances/rows
MySQL.ORM.FindAll({ limit: 10 });
 
// Skip 8 instances/rows
MySQL.ORM.FindAll({ offset: 8 });
 
// Skip 5 instances and fetch the 5 after that
MySQL.ORM.FindAll({ offset: 5, limit: 5 });

Usually these are used alongside the order option.

Utility methods

Sequelize also provides a few utility methods.

count

The count method simply counts the occurrences of elements in the database.

console.log(`There are ${await MySQL.ORM.Count()} projects`);
 
const amount = await MySQL.ORM.Count({
  where: {
    id: {
      [">"]: 25
    }
  }
});
console.log(`There are ${amount} projects with an id greater than 25`);

max, min and sum

Sequelize also provides the max, min and sum convenience methods.

Let's assume we have three users, whose ages are 10, 5, and 40.

await MySQL.ORM.Max('age'); // 40
await MySQL.ORM.Max('age', { where: { age: { ["<"]: 20 } } }); // 10
await MySQL.ORM.Min('age'); // 5
await MySQL.ORM.Min('age', { where: { age: { [">"]: 5 } } }); // 10
await MySQL.ORM.Sum('age'); // 55
await MySQL.ORM.Sum('age', { where: { age: { [">"]: 5 } } }); // 50

increment, decrement

Sequelize also provides the increment convenience method.

Let's assume we have a user, whose age is 10.

await MySQL.ORM.Increment({age: 5}, { where: { id: 1 } }) // Will increase age to 15
await MySQL.ORM.Increment({age: -5}, { where: { id: 1 } }) // Will decrease age to 5
UpdateFinders