loopback-connector-mysql

MySQL is a popular open-source relational database management system (RDBMS). The loopback-connector-mysql module provides the MySQL connector module for the LoopBack framework.

See also LoopBack MySQL Connector in LoopBack documentation. NOTE: The MySQL connector requires MySQL 5.0+.

Installation

In your application root directory, enter this command to install the connector:

npm install loopback-connector-mysql --save

This installs the module from npm and adds it as a dependency to the application's package.json file.

If you create a MySQL data source using the data source generator as described below, you don't have to do this, since the generator will run npm install for you.

Creating a MySQL data source

Use the Data source generator to add a MySQL data source to your application.
The generator will prompt for the database server hostname, port, and other settings required to connect to a MySQL database. It will also run the npm install command above for you.

The entry in the application's /server/datasources.json will look like this:

"mydb": {
  "name": "mydb",
  "connector": "mysql",
  "host": "myserver",
  "port": 3306,
  "database": "mydb",
  "password": "mypassword",
  "user": "admin"
 }

Edit datasources.json to add any other additional properties that you require.

Properties

Property Type Description collation String Determines the charset for the connection. Default is utf8_general_ci. connector String Connector name, either “loopback-connector-mysql” or “mysql”. connectionLimit Number The maximum number of connections to create at once. Default is 10. database String Database name debug Boolean If true, turn on verbose mode to debug database queries and lifecycle. host String Database host name password String Password to connect to database port Number Database TCP port socketPath String The path to a unix domain socket to connect to. When used host and port are ignored. supportBigNumbers Boolean Enable this option to deal with big numbers (BIGINT and DECIMAL columns) in the database. Default is false. timeZone String The timezone used to store local dates. Default is ‘local’. url String Connection URL of form mysql://user:password@host/db. Overrides other connection settings. username String Username to connect to database

NOTE: In addition to these properties, you can use additional parameters supported by <code>node-mysql</code>.

Type mappings

See LoopBack types for details on LoopBack's data types.

LoopBack to MySQL types

LoopBack Type MySQL Type
String/JSON VARCHAR Text TEXT Number INT Date DATETIME Boolean TINYINT(1) GeoPoint object POINT Custom Enum type(See Enum below) ENUM

MySQL to LoopBack types

MySQL Type LoopBack Type CHAR String CHAR(1) Boolean VARCHARTINYTEXTMEDIUMTEXTLONGTEXTTEXTENUMSET String TINYBLOBMEDIUMBLOBLONGBLOBBLOBBINARYVARBINARYBIT Node.js Buffer object TINYINTSMALLINTINTMEDIUMINTYEARFLOATDOUBLENUMERICDECIMAL NumberFor FLOAT and DOUBLE, see Floating-point types. For NUMERIC and DECIMAL, see Fixed-point exact value types DATETIMESTAMPDATETIME Date

Using the datatype field/column option with MySQL

Use the mysql model property to specify additional MySQL-specific properties for a LoopBack model.

For example:

{% include code-caption.html content="/common/models/model.json" %}

"locationId":{
    "type":"String",
    "required":true,
    "length":20,
    "mysql":
    {
        "columnName":"LOCATION_ID",
        "dataType":"VARCHAR",
        "dataLength":20,
        "nullable":"N"
    }
}

You can also use the dataType column/property attribute to specify what MySQL column type to use for many loopback-datasource-juggler types.  The following type-dataType combinations are supported:

• Number
• integer
• tinyint
• smallint
• mediumint
• int
• bigint

Use the limit option to alter the display width. Example:

{ userName : {
    type: String,
    dataType: 'char',
    limit: 24
  }
}

Floating-point types

For Float and Double data types, use the precision and scale options to specify custom precision. Default is (16,8). For example:

{ average :
  { type: Number,
    dataType: 'float',
    precision: 20,
    scale: 4
  }
}

Fixed-point exact value types

For Decimal and Numeric types, use the precision and scale options to specify custom precision. Default is (9,2). These aren't likely to function as true fixed-point.

Example:

{ stdDev :
  { type: Number, 
    dataType: 'decimal',
    precision: 12,
    scale: 8
  }
}

Other types

Convert String / DataSource.Text / DataSource.JSON to the following MySQL types:

• varchar
• char
• text
• mediumtext
• tinytext
• longtext

Example: 

{ userName :
  { type: String,
    dataType: 'char',
    limit: 24
  }
}

Example: 

{ biography :
  { type: String,
  dataType: 'longtext'
  }
}

Convert JSON Date types to  datetime or timestamp

Example: 

{ startTime :
  { type: Date,
    dataType: 'timestamp'
  }
}

Enum

Enums are special. Create an Enum using Enum factory:

var MOOD = dataSource.EnumFactory('glad', 'sad', 'mad'); 
MOOD.SAD; // 'sad' 
MOOD(2); // 'sad' 
MOOD('SAD'); // 'sad' 
MOOD('sad'); // 'sad'
{ mood: { type: MOOD }}
{ choice: { type: dataSource.EnumFactory('yes', 'no', 'maybe'), null: false }}

Discovery and auto-migration

Model discovery

The MySQL connector supports model discovery that enables you to create LoopBack models based on an existing database schema using the unified database discovery API. For more information on discovery, see Discovering models from relational databases.

Auto-migratiion

The MySQL connector also supports auto-migration that enables you to create a database schema from LoopBack models using the LoopBack automigrate method.

For more information on auto-migration, see Creating a database schema from models for more information.

Destroying models may result in errors due to foreign key integrity. First delete any related models first calling delete on models with relationships.

Running tests

The tests in this repository are mainly integration tests, meaning you will need to run them using our preconfigured test server.

1. Ask a core developer for instructions on how to set up test server credentials on your machine
2. npm test