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.

Installation

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

``sh
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:

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

$3

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

Type mappings

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

$3

$3

NOTE as of v3.0.0 of MySQL Connector, the following flags were introduced:

* treatCHAR1AsString
default false - treats CHAR(1) as a String instead of a Boolean
* treatBIT1AsBit
default true - treats BIT(1) as a Boolean instead of a Binary
* treatTINYINT1AsTinyInt
default true - treats TINYINT(1) as a Boolean instead of a Number

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" %}
`javascript
"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:

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

$3

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

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

$3

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:

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

$3

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

* varchar
* char
* text
* mediumtext
* tinytext
* longtext

Example: 

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

Example: 

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

Convert JSON Date types to  datetime or timestamp

Example: 

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

$3

Enums are special. Create an Enum using Enum factory:

`javascript
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

$3

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.

$3

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.

#### Auto-migrate/Auto-update models with foreign keys

MySQL handles the foreign key integrity of the related models upon auto-migrate or auto-update operation. It first deletes any related models before calling delete on the models with the relationship.

Example:

model-definiton.json
`json
{
"name": "Book",
"base": "PersistedModel",
"idInjection": false,
"properties": {
"bId": {
"type": "number",
"id": true,
"required": true
},
"name": {
"type": "string"
},
"isbn": {
"type": "string"
}
},
"validations": [],
"relations": {
"author": {
"type": "belongsTo",
"model": "Author",
"foreignKey": "authorId"
}
},
"acls": [],
"methods": {},
"foreignKeys": {
"authorId": {
"name": "authorId",
"foreignKey": "authorId",
"entityKey": "aId",
"entity": "Author",
"onUpdate": "restrict",
"onDelete": "restrict"
}
}
}
`

`json
{
"name": "Author",
"base": "PersistedModel",
"idInjection": false,
"properties": {
"aId": {
"type": "number",
"id": true,
"required": true
},
"name": {
"type": "string"
},
"dob": {
"type": "date"
}
},
"validations": [],
"relations": {},
"acls": [],
"methods": {}
}
`

boot-script.js
`js
module.exports = function(app) {
var mysqlDs = app.dataSources.mysqlDS;
var Book = app.models.Book;
var Author = app.models.Author;

// first autoupdate the Author model to avoid foreign key constraint failure
mysqlDs.autoupdate('Author', function(err) {
if (err) throw err;
console.log('\nAutoupdated table Author.');

mysqlDs.autoupdate('Book', function(err) {
if (err) throw err;
console.log('\nAutoupdated table Book.');
// at this point the database table Book should have one foreign key authorId integrated
});
});
};
`
#### Breaking Changes with GeoPoint since 5.x
Prior to loopback-connector-mysql@5.x, MySQL connector was saving and loading GeoPoint properties from the MySQL database in reverse.
MySQL expects values to be POINT(X, Y) or POINT(lng, lat), but the connector was saving them in the opposite order(i.e. POINT(lat,lng)).
If you have an application with a model that has a GeoPoint property using previous versions of this connector, you can migrate your models
using the following programmatic approach:
NOTE Please back up the database tables that have your application data before performing any of the steps.
1. Create a boot script under server/boot/ directory with the following:
`js
'use strict';
module.exports = function(app) {
function findAndUpdate() {
var teashop = app.models.teashop;
//find all instances of the model we'd like to migrate
teashop.find({}, function(err, teashops) {
teashops.forEach(function(teashopInstance) {
//what we fetch back from the db is wrong, so need to revert it here
var newLocation = {lng: teashopInstance.location.lat, lat: teashopInstance.location.lng};
//only update the GeoPoint property for the model
teashopInstance.updateAttribute('location', newLocation, function(err, inst) {
if (err)
console.log('update attribute failed ', err);
else
console.log('updateAttribute successful');
});
});
});
}

findAndUpdate();
};
`
2. Run the boot script by simply running your application or node .

For the above example, the model definition is as follows:
`json
{
"name": "teashop",
"base": "PersistedModel",
"idInjection": true,
"options": {
"validateUpsert": true
},
"properties": {
"name": {
"type": "string",
"default": "storename"
},
"location": {
"type": "geopoint"
}
},
"validations": [],
"relations": {},
"acls": [],
"methods": {}
}
`

Running tests

$3