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.
In your application root directory, enter this command to install the connector:
npm install loopback-connector-mysql --save
Note: The MySQL connector requires MySQL 5.0+.
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.
For LoopBack 4 users, use the LoopBack 4
Command-line interface
to generate a DataSource with MySQL connector to your LB4 application. Run
lb4 datasource
, it
will prompt for configurations such as host, post, etc. that are required to
connect to a MySQL database.
After setting it up, the configuration can be found under
src/datasources/<DataSourceName>.datasource.ts
, which would look like this:
const config = {
name: 'db',
connector: 'mysql',
url: '',
host: 'localhost',
port: 3306,
user: 'user',
password: 'pass',
database: 'testdb',
};
For LoopBack 3 users
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 <DataSourceName>.datasources.ts
to add any other additional properties
that you require.
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 |
allowExtendedOperators | Boolean | Set to true to enable MySQL-specific operators
such as match . Learn more in
Extended operators below.
|
NOTE: In addition to these properties, you can use additional parameters
supported by node-mysql
.
See LoopBack 4 types (or LoopBack 3 types) for details on LoopBack's data 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 Type | LoopBack Type |
---|---|
CHAR | String |
BIT(1) CHAR(1) TINYINT(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 |
NOTE as of v3.0.0 of MySQL Connector, the following flags were introduced:
treatCHAR1AsString
defaultfalse
- treats CHAR(1) as a String instead of a BooleantreatBIT1AsBit
defaulttrue
- treats BIT(1) as a Boolean instead of a BinarytreatTINYINT1AsTinyInt
defaulttrue
- treats TINYINT(1) as a Boolean instead of a Number
Except the common database-specific properties we introduce in How LoopBack Models Map To Database Tables/Collections, the following are more detailed examples and MySQL-specific settings.
Besides the basic LoopBack types, as we introduced above, you can also specify additional MySQL-specific properties for a LoopBack model. It would be mapped to the database.
Use the mysql.<property>
in the model definition or the property definition to
configure the table/column definition.
For example, the following settings would allow you to have custom table name
(Custom_User
) and column name (custom_id
and custom_name
). Such mapping is
useful when you'd like to have different table/column names from the model:
{% include code-caption.html content="user.model.ts" %}
@model({
settings: { mysql: { schema: 'testdb', table: 'Custom_User'} },
})
export class User extends Entity {
@property({
type: 'number',
required: true,
id: true,
mysql: {
columnName: 'custom_id',
},
})
id: number;
@property({
type: 'string',
mysql: {
columnName: 'custom_name',
},
})
name?: string;
For LoopBack 3 users
{
"name": "User",
"options": {
"mysql": {
"schema": "testdb",
"table": "Custom_User"
}
},
"properties": {
"id": {
"type": "Number",
"required": true,
"mysql": {
"columnName": "custom_id",
}
},
"name": {
"type": "String",
"mysql": {
"columnName": "custom_name",
}
},
}
}
Except the names, you can also use the dataType column/property attribute to specify what MySQL column type to use. The following MySQL type-dataType combinations are supported:
- number
- integer
- tinyint
- smallint
- mediumint
- int
- bigint
- float
- double
- decimal
The following examples will be in LoopBack 4 style, but it's the same if you
provide mysql.<property>
to the LB3 property definition.
For Float and Double data types, use the precision
and scale
options to
specify custom precision. Default is (16,8).
Example
@property({
type: 'Number',
mysql: {
dataType: 'float',
precision: 20,
scale: 4
}
})
price: Number;
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
@property({
type: 'Number',
mysql: {
dataType: 'decimal',
precision: 12,
scale: 8
}
})
price: Number;
Convert String / DataSource.Text / DataSource.JSON to the following MySQL types:
- varchar
- char
- text
- mediumtext
- tinytext
- longtext
Example
@property({
type: 'String',
mysql: {
dataType: 'char',
dataLength: 24 // limits the property length
},
})
userName: String;
Convert JSON Date types to datetime or timestamp.
Example
@property({
type: 'Date',
mysql: {
dataType: 'timestamp',
},
})
startTime: Date;
See the Model ENUM property for details.
Use the default
and dataType
properties to have MySQL handle setting column DEFAULT
value.
Example
@property({
type: 'String',
mysql: {
dataType: 'varchar',
default: 'pending'
}
})
status: String;
@property({
type: 'Number',
mysql: {
dataType: 'int',
default: 42
}
})
maxDays: Number;
@property({
type: 'boolean',
mysql: {
dataType: 'tinyint',
default: 1
}
})
isDone: Boolean;
For the date or timestamp types use CURRENT_TIMESTAMP
or now
.
Example
@property({
type: 'Date',
mysql: {
dataType: 'datetime',
default: 'CURRENT_TIMESTAMP'
}
})
last_modified: Date;
NOTE: The following column types do NOT supported MySQL Default Values:
- BLOB
- TEXT
- GEOMETRY
- JSON
MySQL connector supports the following MySQL-specific operators:
match
Please note extended operators are disabled by default, you must enable them at datasource level or model level by settingallowExtendedOperators
totrue
.
The match
operator allows you to perform a full text search using the MATCH() .. AGAINST() operator in MySQL.
Three different modes of the MATCH
clause are also available in the form of operators -
matchbool
for Boolean Full Text Searchmatchnl
for Natural Language Full Text Searchmatchqe
for Full-Text Searches with Query Expansionmatchnlqe
for Full-Text Searches with Query Expansion with theIN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
modifier.
By default, the match
operator works in Natural Language mode.
Note The fields you are querying must be setup with a FULLTEXT
index to perform full text search on them.
Assuming a model such as this:
@model({
settings: {
allowExtendedOperators: true,
}
})
class Post {
@property({
type: 'string',
mysql: {
index: {
kind: 'FULLTEXT'
}
},
})
content: string;
}
You can query the content field as follows:
const posts = await postRepository.find({
where: {
{
content: {match: 'someString'},
}
}
});
The MySQL connector supports model discovery that enables you to create LoopBack models based on an existing database schema. Once you defined your datasource:
- LoopBack 4 users could use the commend
lb4 discover
to discover models. - For LB3 users, please check Discovering models from relational databases. (See database discovery API for related APIs information)
The MySQL connector also supports auto-migration that enables you to create a
database schema from LoopBack models. For example, based on the following model,
the auto-migration method would create/alter existing Customer
table in the
database. Table Customer
would have two columns: name
and id
, where id
is also the primary key that has auto_increment
set as it has definition of
type: 'Number'
and generated: true
:
@model()
export class Customer extends Entity {
@property({
id: true,
type: 'Number',
generated: true,
})
id: number;
@property({
type: 'string',
})
name: string;
}
Moreover, additional MySQL-specific properties mentioned in the Data mapping properties section work with auto-migration as well.
For now LoopBack MySQL connector only supports auto-generated id
(generated: true
) for integer type as for MySQL, the default id type is
integer. If you'd like to use other types such as string (uuid) as the id
type, you can:
- use uuid that is generated by your LB application by setting
defaultFn: uuid
.
@property({
id: true,
type: 'string'
defaultFn: 'uuidv4',
// generated: true, -> not needed
})
id: string;
- Alter the table in your database to use a certain function if you prefer having the database to generate the value.
@property({
id: true,
type: 'string'
generated: true, // to indicate the value generates by the db
useDefaultIdType: false, // needed
})
id: string;
Foreign key constraints can be defined in the model definition.
Note: The order of table creation is important. A referenced table must
exist before creating a foreign key constraint. The order can be specified
using the optional SchemaMigrationOptions
argument of migrateSchema
:
await app.migrateSchema({
models: [ 'Customer', 'Order' ]
});
Define your models and the foreign key constraints as follows:
{% include code-caption.html content="customer.model.ts" %}
@model()
export class Customer extends Entity {
@property({
id: true,
type: 'Number',
generated: true,
})
id: number;
@property({
type: 'string',
})
name: string;
}
order.model.ts
:
@model({
settings: {
foreignKeys: {
fk_order_customerId: {
name: 'fk_order_customerId',
entity: 'Customer',
entityKey: 'id',
foreignKey: 'customerId',
},
},
})
export class Order extends Entity {
@property({
id: true,
type: 'Number',
generated: true
})
id: number;
@property({
type: 'string'
})
name: string;
@property({
type: 'Number'
})
customerId: number;
}
For LoopBack 3 users
({
"name": "Customer",
"options": {
"idInjection": false
},
"properties": {
"id": {
"type": "Number",
"id": 1
},
"name": {
"type": "String",
"required": false
}
}
},
{
"name": "Order",
"options": {
"idInjection": false,
"foreignKeys": {
"fk_order_customerId": {
"name": "fk_order_customerId",
"entity": "Customer",
"entityKey": "id",
"foreignKey": "customerId"
}
}
},
"properties": {
"id": {
"type": "Number"
"id": 1
},
"customerId": {
"type": "Number"
},
"description": {
"type": "String",
"required": false
}
}
})
MySQL handles the foreign key integrity by the referential action specified by
ON UPDATE
and ON DELETE
. You can specify which referential actions the
foreign key follows in the model definition upon auto-migrate or auto-update
operation. Both onDelete
and onUpdate
default to restrict
.
Take the example we showed above, let's add the referential action to the
foreign key customerId
:
@model({
settings: {
foreignKeys: {
fk_order_customerId: {
name: 'fk_order_customerId',
entity: 'Customer',
entityKey: 'id',
foreignKey: 'customerId',
onUpdate: 'restrict', // restrict|cascade|set null|no action|set default
onDelete: 'cascade' // restrict|cascade|set null|no action|set default
},
},
})
export class Order extends Entity {
...
For LoopBack 3 users
model-definiton.json
{
"name": "Customer",
"options": {
"idInjection": false
},
"properties": {
"id": {
"type": "Number",
"id": 1
},
"name": {
"type": "String",
"required": false
}
}
},
{
"name": "Order",
"options": {
"idInjection": false,
"foreignKeys": {
"fk_order_customerId": {
"name": "fk_order_customerId",
"entity": "Customer",
"entityKey": "id",
"foreignKey": "customerId",
"onUpdate": "restrict",
"onDelete": "cascade"
}
}
},
"properties": {
"id": {
"type": "Number"
"id": 1
},
"customerId": {
"type": "Number"
},
"description": {
"type": "String",
"required": false
}
}
}
boot-script.js
module.exports = function (app) {
var mysqlDs = app.dataSources.mysqlDS;
var Book = app.models.Order;
var Author = app.models.Customer;
// first autoupdate the `Customer` model to avoid foreign key constraint failure
mysqlDs.autoupdate('Customer', function (err) {
if (err) throw err;
console.log('\nAutoupdated table `Customer`.');
mysqlDs.autoupdate('Order', function (err) {
if (err) throw err;
console.log('\nAutoupdated table `Order`.');
// at this point the database table `Order` should have one foreign key `customerId` integrated
});
});
};
Prior to [email protected]
, 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)
).
Use the geopoint
type to achieve so:
@property({
type: 'geopoint'
})
name: GeoPoint;
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:
Click here to expand
NOTE Please back up the database tables that have your application data before performing any of the steps.
- Create a boot script under
server/boot/
directory with the following:
'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();
};
- Run the boot script by simply running your application or
node .
For the above example, the model definition is as follows:
{
"name": "teashop",
"base": "PersistedModel",
"idInjection": true,
"options": {
"validateUpsert": true
},
"properties": {
"name": {
"type": "string",
"default": "storename"
},
"location": {
"type": "geopoint"
}
},
"validations": [],
"relations": {},
"acls": [],
"methods": {}
}
If you have a local or remote MySQL instance and would like to use that to run the test suite, use the following command:
- Linux
MYSQL_HOST=<HOST> MYSQL_PORT=<PORT> MYSQL_USER=<USER> MYSQL_PASSWORD=<PASSWORD> MYSQL_DATABASE=<DATABASE> CI=true npm test
- Windows
SET MYSQL_HOST=<HOST> SET MYSQL_PORT=<PORT> SET MYSQL_USER=<USER> SET MYSQL_PASSWORD=<PASSWORD> SET MYSQL_DATABASE=<DATABASE> SET CI=true npm test
If you do not have a local MySQL instance, you can also run the test suite with very minimal requirements.
- Assuming you have Docker installed, run the following script which would spawn a MySQL instance on your local:
source setup.sh <HOST> <PORT> <USER> <PASSWORD> <DATABASE>
where <HOST>
, <PORT>
, <USER>
, <PASSWORD>
and <DATABASE>
are optional
parameters. The default values are localhost
, 3306
, root
, pass
and
testdb
respectively.
- Run the test:
npm test