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 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:
"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 collation
. Collation
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 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:
"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.