MySQL storage plugin for Mio.


MySQL storage plugin for Mio.

npm install mio-mysql

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

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

settings same as settings for node-mysql


  • 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'

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

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

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

  .where({ created_at: { $lt: '2014-01-01' }})
  .sort({ created_at: 'desc' })
  .exec(function(errusers) {
    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 are specified using the tableName option. For example:

  database: 'mydb',
  user: 'root'
}, {
  tableName: 'users'

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

  .attr('firstName', {
    type: 'string',
    length: 255,
    columnName: 'first_name'
  .attr('lastName', {
    type: 'string',
    length: 255,
    columnName: 'last_name'

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".

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', {
  dataFormatterfunction(valueEvent) {
    value = Math.floor(value.getTime() / 1000);
    return value;

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.

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. 
// => node-mysql connection pool object... 

MySQL module.

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

Run the tests with npm:

npm test