mio-mysql

MySQL storage plugin for Mio.

Installation

npm install mio-mysql

API

See the Mio documentation for query methods like find, save, etc.

exports(settings, options)

Create a new Mio mysql plugin with the given database settings and options.

settings same as settings for node-mysql

options

• tableName The table for this model. Defaults to singularized model name.
• maxLimit The maximum number of records to select at once. Default is 200.

var User = require('mio').createModel('User');

User.server(require('mio-mysql', {
  database: 'mydb',
  user: 'root'
}));

Queries

The query syntax is a subset of mongo-sql. The type, columns, and table properties are handled by mio-mysql.

Pagination

Both offset and limit, and page and pageSize query parameters are supported.

The collection returned by Model.findAll() has pagination properties on the array:

User.findAll()
  .where({ created_at: { $lt: '2014-01-01' }})
  .sort({ created_at: 'desc' })
  .limit(25)
  .exec(function(err, users) {
    console.log(users);          // => [user1, user2, user3, ...]
    console.log(users.total);    // => 73
    console.log(users.pages);    // => 3
    console.log(users.page);     // => 1
    console.log(users.pageSize); // => 25
    console.log(users.offset);   // => 0
    console.log(users.limit);    // => 25
});

Custom table names

Custom table names are specified using the tableName option. For example:

User.use(require('mio-mysql')({
  database: 'mydb',
  user: 'root'
}, {
  tableName: 'users'
}));

Custom column names

Custom field names are provided by a columnName property in the attribute definition. For example:

User
  .attr('id')
  .attr('firstName', {
    type: 'string',
    length: 255,
    columnName: 'first_name'
  })
  .attr('lastName', {
    type: 'string',
    length: 255,
    columnName: 'last_name'
  });

Date types

Attributes with type: "date" will be handled based on the columnType property. This property can either be "datetime", "timestamp", or "integer", corresponding to MySQL column type. If not specified, mio-mysql will assume "integer".

Data formatters

If you need to control exactly how a data-type is determined, set the attribute definition's dataFormatter function:

var Event = mio.createModel('Event');

Event.attr('date', {
  dataFormatter: function(value, Event) {
    value = Math.floor(value.getTime() / 1000);
    return value;
  }
});

Query timeout

Queries have a default timeout of 60000 milliseconds. If the query takes longer, the connection will be destroyed and the callback executed with Error("Connection ended due to query time-out.").

To change the query timeout, set settings.queryTimeout to the desired value.

Database connection

mio-mysql utilizes node-mysql's connection pool.

Models that share a settings object will share a connection pool, exposed via settings.pool.

var mysql = require('mio-mysql');

var settings = {
  database: 'mydb',
  user: 'root'
};

// Both User and Post models will share the same connection.
User.server(mysql(settings));
Post.server(mysql(settings));

console.log(settings.pool);
// => node-mysql connection pool object...

exports.mysql

MySQL module.

Tests

Tests are written with mocha and should using BDD-style assertions.

Run the tests with npm:

npm test

MIT Licensed