loopback-connector-mysql2
v6.0.0
Published
MySQL connector for loopback-datasource-juggler. Using mysql2 instead of mysqljs
Downloads
231
Readme
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:
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.
Creating a MySQL data source
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',
};
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.
Properties
NOTE: In addition to these properties, you can use additional parameters
supported by node-mysql
.
Type mappings
See LoopBack 4 types (or LoopBack 3 types) for details on LoopBack's data types.
LoopBack to MySQL types
MySQL to LoopBack types
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
Data mapping properties
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.
Table/Column Names
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;
{
"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",
}
},
}
}
Numeric Types
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.
Floating-point types
For Float and Double data types, use the precision
and scale
options to
specify custom precision. Default is (16,8).
@property({
type: 'Number',
mysql: {
dataType: 'float',
precision: 20,
scale: 4
}
})
price: Number;
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.
@property({
type: 'Number',
mysql: {
dataType: 'decimal',
precision: 12,
scale: 8
}
})
price: Number;
Text types
Convert String / DataSource.Text / DataSource.JSON to the following MySQL types:
- varchar
- char
- text
- mediumtext
- tinytext
- longtext
@property({
type: 'String',
mysql: {
dataType: 'char',
dataLength: 24 // limits the property length
},
})
userName: String;
Dat types
Convert JSON Date types to datetime or timestamp.
@property({
type: 'Date',
mysql: {
dataType: 'timestamp',
},
})
startTime: Date;
Enum
See the Model ENUM property for details.
Default Clause/Constant
Use the default
and dataType
properties to have MySQL handle setting column DEFAULT
value.
@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
.
@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
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. 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)
Auto-migration
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.
Auto-generated ids
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;
Auto-migrate/Auto-update models with foreign keys
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;
}
({
"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 {
...
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
});
});
};
Breaking Changes with GeoPoint since 5.x
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:
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": {}
}
Running tests
DO Remember to set the OS Timezone as UTC for the MYSQL DB and Unittest running environment. And DO NOT pass the MYSQL_DATABASE
Own instance
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
Docker
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