The MySQL connector enables LoopBack applications to connect to MySQL data sources.
Page Contents

Installation

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

$ npm install loopback-connector-mysql --save

This will install the module from npm and add it as a dependency to the application’s package.json file.

Creating a MySQL data source

Use the Data source generator to add a MySQL data source to your application. The entry in the application’s /server/datasources.json will look like this:

/server/datasources.json

"mydb": {
    "name": "mydb",
    "connector": "mysql",
}

Edit datasources.json to add other properties that enable you to connect the data source to a MySQL database.

Properties

Property Type Description
connector String

Connector name, either "loopback-connector-mysql" or "mysql"

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
username String Username to connect to database

In addition to these properties, you can use additional parameters supported by node-mysql, for example password and collationCollation currently defaults to utf8_general_ci. The collation value will also be used to derive the connection charset.

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
VARCHAR
TINYTEXT
MEDIUMTEXT
LONGTEXT
TEXT
ENUM
SET
String
TINYBLOB
MEDIUMBLOB
LONGBLOB
BLOB
BINARY
VARBINARY
BIT
Node.js Buffer object
TINYINT
SMALLINT
INT
MEDIUMINT
YEAR
FLOAT
DOUBLE
NUMERIC
DECIMAL

Number
For FLOAT and DOUBLE, See Floating-point types

For NUMERIC and DECIMAL, see Fixed-point exact value types

DATE
TIMESTAMP
DATETIME
Date

Using the datatype field/column option with MySQL

loopback-connector-mysql allows mapping of LoopBack model properties to MySQL columns using the ‘mysql’ property of the property definition.

For example:

/common/models/model.json

"locationId":{
    "type":"String",
    "required":true,
    "length":20,
    "mysql":
    {
        "columnName":"LOCATION_ID",
        "dataType":"VARCHAR2",
        "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:

{ count : { type: Number, dataType: 'smallInt' }}

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 methods

LoopBack provides a unified API to create models based on schema and tables in relational databases. The same discovery API is available when using connectors for Oracle, MySQL, PostgreSQL, and SQL Server. For more information, see Discovering models from relational databases and  Database discovery API.

Tags: connectors