Repository files navigation

MySQL is a popular open-source relational databasemanagement system (RDBMS). Theloopback-connector-mysql module provides theMySQL 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: Sinceloopback-connector-mysql v7.x.x, this MySQL connector has dropped support for MySQL 5.7 and requires MySQL 8.0+.

This installs the module from npm and adds it as a dependency to theapplication's package.json file.

If you create a MySQL data source using the data source generator as describedbelow, you don't have to do this, since the generator will runnpm install foryou.

For LoopBack 4 users, use the LoopBack 4Command-line interfaceto generate a DataSource with MySQL connector to your LB4 application. Runlb4 datasource, itwill prompt for configurations such as host, post, etc. that are required toconnect to a MySQL database.

After setting it up, the configuration can be found undersrc/datasources/<DataSourceName>.datasource.ts, which would look like this:

constconfig={name:'db',connector:'mysql',url:'',host:'localhost',port:3306,user:'user',password:'pass',database:'testdb',};

"mydb":{"name":"mydb","connector":"mysql","host":"myserver","port":3306,"database":"mydb","password":"mypassword","user":"admin"}

Edit<DataSourceName>.datasources.ts to add any other additional propertiesthat you require.

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

See LoopBack 4 types (orLoopBack 3 types) fordetails on LoopBack's data 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 aBoolean
• treatBIT1AsBit defaulttrue - treats BIT(1) as a Boolean instead of aBinary
• treatTINYINT1AsTinyInt defaulttrue - treats TINYINT(1) as a Booleaninstead of a Number

Except the common database-specific properties we introduce inHow 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 specifyadditional MySQL-specific properties for a LoopBack model. It would be mapped tothe database.

Use themysql.<property> in the model definition or the property definition toconfigure the table/column definition.

For example, the following settings would allow you to have custom table name(Custom_User) and column name (custom_id andcustom_name). Such mapping isuseful 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'}},})exportclassUserextendsEntity{  @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",}},}}

Except the names, you can also use the dataType column/property attribute tospecify what MySQL column type to use. The following MySQL type-dataTypecombinations 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 youprovidemysql.<property> to the LB3 property definition.

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

@property({type:'Number',mysql:{dataType:'float',precision:20,scale:4}})price:Number;

For Decimal and Numeric types, use the precision and scale options tospecify custom precision. Default is (9,2). These aren't likely to function astrue fixed-point.

@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

@property({type:'String',mysql:{dataType:'char',dataLength:24// limits the property length},})userName:String;

Convert JSON Date types to datetime or timestamp.

@property({type:'Date',mysql:{dataType:'timestamp',},})startTime:Date;

See theModel ENUM property for details.

Use thedefault anddataType properties to have MySQL handlesetting columnDEFAULT 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 useCURRENT_TIMESTAMP ornow.

@property({type:'Date',mysql:{dataType:'datetime',default:'CURRENT_TIMESTAMP'}})last_modified:Date;

NOTE: The following column types doNOT supportedMySQL Default Values:

• BLOB
• TEXT
• GEOMETRY
• JSON

MySQL connector supports the following MySQL-specific operators:

• matchPlease note extended operators are disabled by default, you must enablethem at datasource level or model level by settingallowExtendedOperators totrue.

Thematch operator allows you to perform a full text search using theMATCH() .. AGAINST() operator in MySQL.

Three different modes of theMATCH clause are also available in the form of operators -

• matchbool forBoolean Full Text Search
• matchnl forNatural Language Full Text Search
• matchqe forFull-Text Searches with Query Expansion
• matchnlqe forFull-Text Searches with Query Expansion with theIN NATURAL LANGUAGE MODE WITH QUERY EXPANSION modifier.

By default, thematch operator works in Natural Language mode.

Note The fields you are querying must be setup with aFULLTEXT index to perform full text search on them.Assuming a model such as this:

@model({settings:{allowExtendedOperators:true,}})classPost{  @property({type:'string',mysql:{index:{kind:'FULLTEXT'}},})content:string;}

You can query the content field as follows:

constposts=awaitpostRepository.find({where:{{content:{match:'someString'},}}});

The MySQL connector supportsmodel discovery that enables you to createLoopBack models based on an existing database schema. Once you defined yourdatasource:

• LoopBack 4 users could use the commendlb4 discover todiscover models.
• For LB3 users, please checkDiscovering models from relational databases.(Seedatabase discovery APIfor related APIs information)

The MySQL connector also supportsauto-migration that enables you to create adatabase schema from LoopBack models. For example, based on the following model,the auto-migration method would create/alter existingCustomer table in thedatabase. TableCustomer would have two columns:name andid, whereidis also the primary key that hasauto_increment set as it has definition oftype: 'Number' andgenerated: true:

@model()exportclassCustomerextendsEntity{  @property({id:true,type:'Number',generated:true,})id:number;  @property({type:'string',})name:string;}

Moreover, additional MySQL-specific properties mentioned in theData mapping properties section work withauto-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 isinteger. If you'd like to use other types such as string (uuid) as the idtype, you can:

• use uuid that isgenerated by your LB application by settingdefaultFn: 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 preferhavingthe database to generate the value.

  @property({id:true,type:'string'generated:true,// to indicate the value generates by the dbuseDefaultIdType: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 mustexist before creating a foreign key constraint. The order can be specifiedusing the optionalSchemaMigrationOptions argument ofmigrateSchema:

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()exportclassCustomerextendsEntity{  @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',},},})exportclassOrderextendsEntity{  @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 byON UPDATE andON DELETE. You can specify which referential actions theforeign key follows in the model definition upon auto-migrate or auto-updateoperation. BothonDelete andonUpdate default torestrict.

Take the example we showed above, let's add the referential action to theforeign keycustomerId:

@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 defaultonDelete:'cascade'// restrict|cascade|set null|no action|set default},},})exportclassOrderextendsEntity{...

{"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    }  }}

module.exports=function(app){varmysqlDs=app.dataSources.mysqlDS;varBook=app.models.Order;varAuthor=app.models.Customer;// first autoupdate the `Customer` model to avoid foreign key constraint failuremysqlDs.autoupdate('Customer',function(err){if(err)throwerr;console.log('\nAutoupdated table `Customer`.');mysqlDs.autoupdate('Order',function(err){if(err)throwerr;console.log('\nAutoupdated table `Order`.');// at this point the database table `Order` should have one foreign key `customerId` integrated});});};

Prior toloopback-connector-mysql@5.x, MySQL connector was saving and loadingGeoPoint properties from the MySQL database in reverse. MySQL expects values tobePOINT(X, Y) orPOINT(lng, lat), but the connector was saving them in theopposite order(i.e.POINT(lat,lng)).

Use thegeopoint type to achieve so:

  @property({type:'geopoint'})  name:GeoPoint;

If you have an application with a model that has a GeoPoint property usingprevious versions of this connector, you can migrate your models using thefollowing programmatic approach:

'use strict';module.exports=function(app){functionfindAndUpdate(){varteashop=app.models.teashop;//find all instances of the model we'd like to migrateteashop.find({},function(err,teashops){teashops.forEach(function(teashopInstance){//what we fetch back from the db is wrong, so need to revert it herevarnewLocation={lng:teashopInstance.location.lat,lat:teashopInstance.location.lng,};//only update the GeoPoint property for the modelteashopInstance.updateAttribute('location',newLocation,function(err,inst,){if(err)console.log('update attribute failed',err);elseconsole.log('updateAttribute successful');});});});}findAndUpdate();};

{"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 runthe test suite, use the following command:

• Linux

MYSQL_HOST=<HOST> MYSQL_PORT=<PORT> MYSQL_USER=<USER> MYSQL_PASSWORD=<PASSWORD> MYSQL_DATABASE=<DATABASE> CI=true npmtest

• Windows

SET MYSQL_HOST=<HOST> SET MYSQL_PORT=<PORT> SET MYSQL_USER=<USER> SET MYSQL_PASSWORD=<PASSWORD> SET MYSQL_DATABASE=<DATABASE> SET CI=true npmtest

If you do not have a local MySQL instance, you can also run the test suite withvery minimal requirements.

• Assuming you haveDockerinstalled, run the following script which would spawn a MySQL instance on yourlocal:

source setup.sh<HOST><PORT><USER><PASSWORD><DATABASE>

where<HOST>,<PORT>,<USER>,<PASSWORD> and<DATABASE> are optionalparameters. The default values arelocalhost,3306,root,pass andtestdb respectively.

• Run the test:

npmtest