mysl-orm

1.1Installation

Install from npm:

npm install --save loke-mysql-orm

1.2Example

var db = require('loke-mysql-orm').create('mysql://root@localhost/demo');
var petRepository = db.table('Pets', {
  name: { type: String, defaultValue: () => 'Untitled' },
  description: db.Text
});
var userRepository = db.table('Users', {
  firstName: db.String,
  lastName: db.String,
  pets: [petRepository]
});

userRepository.find({firstName: 'Testing'})
.then(function (users) {
  users[0].pets[0].description = 'Hello World!';
  return userRepository.persist(users[0]);
});

3.1Defining Schemas

The db.table method allows creating repositories with a schema description.

/**
 * Create a repository
 * @param  {String} tableName
 * @param  {} schemaDescription describe keys
 * @return {Repository} model constructor
 */
Connection.prototype.table = function (tableName, schemaDescription) {
  var schema = new Schema(schemaDescription);
  return new SequelizeRepository(sequelize, tableName, schema);
};

Example Usage:

var db = require('loke-mysql-orm').create('mysql://root@localhost/demo');

var userRepo = db.table('Users', {
  firstName: {type: db.String},
  birthdate: {type: db.Date}
});

3.2Relations

Relations are defined by using another repository instance as a type value.

Example Usage:

var db = require('loke-mysql-orm').create('mysql://root@localhost/demo');

var address  = db.table('Addresses', { suburb : String });
var petsRepo = db.table('Pets',      { name   : String });

var userRepo = db.table('Users', {
  address  : { type: address },   // Defines a `HasOne` relation
  pets     : { type: [petsRepo] } // Defines a `HasMany` relation
});

3.3Types:

List of all supported types and their MYSQL translation:

• db.Id - INT(11) UNSIGNED
• db.String - VARCHAR(255)
• db.Number - DOUBLE
• db.Boolean - TINYINT(1)
• db.Date - DATETIME
• db.Text - TEXT
• db.Decimal - DECIMAL(10, 2)
• db.Enum - ENUM

4.1find

Query documents: find(q, opts).

The first argument is the query description object which is passed to Sequelize as the where parameter. As such, it can contain operators like as $or and $lte. You can find a full list of operators in the Sequelize documentation.

Example:

userRepo.find({added: {$lt: new Date('2014')});

Options:

4.2stream

Query documents using a streaming interface. Same function signature as find(), but returns a Readable stream.

userRepo.find({added: {$lt: new Date('2014')})
.on('data', user => console.log(user.firstName))
.on('end', () => console.log('END'));

Options:

4.3findOne

Find the first document for a query.

Repository.prototype.findOne = function (q, opts) {
  opts = opts || {};
  opts.limit = 1;
  return this.find(q, opts)
  .then(function (results) {
    return results[0] || null;
  });
};

4.4findById

Find a document by its primary key.

Repository.prototype.findById = function (id, opts) {
  var q = {};
  q[this.schema.primaryField.name] = id;
  return this.findOne(q, opts);
};

4.5new

Create a document without yet persisting it to the database.

Example:

var user = userRepository.new({firstName: 'Testing'});
console.log(user.firstName);

4.6persist

Save a document. Depending on whether the object is already in the database, this will trigger either an INSERT or an UPDATE query. Persisting a document will also persist all of its related child documents (HasOne and HasMany).

var user = userRepository.new({firstName: 'Testing'});
userRepository.persist(user);

4.7update

Update a document by using its primary key.

userRepository.update(user, {firstName: 'ReplacementValue'});

4.8create

Shorthand to build a document and persist it:

Repository.prototype.create = function (o) {
  return this.persist(this.new(o));
};

4.9insert

You would usually not use this directly, but use persist() instead. This inserts values into a table.

Example:

userRepo.insert({x: 3, y: 5});
// INSERT INTO `Users` (`ID`, `x`, `y`)
// VALUES (DEFAULT, 3, 5);

4.10reload

Reload the entire document.

exampleRepo.reload(document);

4.11remove

Delete a document. Creates a DELETE query with the primary key.

Example:

userRepo.remove(user);

4.12delete

Delete a document. By default, this will call .remove, but can be overridden to implement soft‐deleting.

Example:

userRepo.delete(user);

4.13count

Count the number of rows matching a query. Uses the COUNT(*) SQL method.

Example:

userRepo.count({gender: 'FEMALE'})
.then(n => console.log(`There are ${n} female users.`));

4.14updateWhere

Creates an UPDATE query. Returns a promise that resolves to the number of rows affected.

// UPDATE Users SET y = 5 WHERE X = 3
userRepo.updateWhere({x: 3}, {y: 5});

4.15removeWhere

Create a DELETE query. Example

// DELETE FROM Users WHERE X = 3
userRepo.deleteWhere({x: 3});

4.16literal

Create SQL code object Literal for use in attributes parameters. To avoid SQL injections the string should not contain user input.

Example:

exampleRepo.find({}, {
  raw: true,
  attributes: [exampleRepo.literal('COUNT(*) AS n')]
})
.then(row => {
  // { n: 5 }
  console.log(row);
});

4.17rawInsert

Create a custom INSERT query. To avoid SQL injections the string should not contain user input.

Example:

rawSQLString:

INSERT INTO example (a,b,c) VALUES (1,2,0)
  ON DUPLICATE KEY UPDATE c=c+:value;

code:

exampleRepo.rawInsert(rawSQLString, {value: 1});

4.18rawSelect

Create a custom SELECT query. To avoid SQL injections the string should not contain user input.

Example:

rawSQLString:

SELECT * FROM example WHERE a + b < :value

code:

exampleRepo.rawSelect(rawSQLString, {value: 300});

4.19rawUpdate

Create a custom UPDATE query. Returns the number of affected rows. To avoid SQL injections the string should not contain user input.

Example:

rawIncrementString:

UPDATE example SET c = c + 1 WHERE a = :a LIMIT 1

code:

exampleRepo.rawUpdate(rawIncrementString, {a: 239});

4.20didApplyChanges

When you use the repo.update(document, {...changes}) method the change tracking system will update the local document object. However, with custom SQL queries this cannot be done automatically.

Although it is possible to update the in‐memory object simply by setting document.c = document.c + 1, that would cause problems if you were to persist that object later on.

Hence, to update the local value on the document, without triggering the change tracker to overwrite anything, there is a didApplyChanges(document, fieldValues, relationValues) method for this purpose.

Example:

rawIncrementString:

UPDATE example SET c = c + 1 WHERE id = :id LIMIT 1

code:

exampleRepo.rawUpdate(rawIncrementString, {id: document.id})
.then(nUpdated => {
  if (nUpdated) exampleRepo.didApplyChanges(document, {c: document.c + 1});
});