1 | # loopback-connector-mysql
|
2 |
|
3 | [MySQL](https://www.mysql.com/) is a popular open-source relational database
|
4 | management system (RDBMS). The `loopback-connector-mysql` module provides the
|
5 | MySQL connector module for the LoopBack framework.
|
6 |
|
7 | ## Installation
|
8 |
|
9 | In your application root directory, enter this command to install the connector:
|
10 |
|
11 | ```sh
|
12 | npm install loopback-connector-mysql --save
|
13 | ```
|
14 |
|
15 | **Note**: Since `loopback-connector-mysql` v7.x.x, this MySQL connector has dropped support for MySQL 5.7 and requires MySQL 8.0+.
|
16 |
|
17 | This installs the module from npm and adds it as a dependency to the
|
18 | application's `package.json` file.
|
19 |
|
20 | If you create a MySQL data source using the data source generator as described
|
21 | below, you don't have to do this, since the generator will run `npm install` for
|
22 | you.
|
23 |
|
24 | ## Creating a MySQL data source
|
25 |
|
26 | For LoopBack 4 users, use the LoopBack 4
|
27 | [Command-line interface](https://loopback.io/doc/en/lb4/Command-line-interface.html)
|
28 | to generate a DataSource with MySQL connector to your LB4 application. Run
|
29 | [`lb4 datasource`](https://loopback.io/doc/en/lb4/DataSource-generator.html), it
|
30 | will prompt for configurations such as host, post, etc. that are required to
|
31 | connect to a MySQL database.
|
32 |
|
33 | After setting it up, the configuration can be found under
|
34 | `src/datasources/<DataSourceName>.datasource.ts`, which would look like this:
|
35 |
|
36 | ```ts
|
37 | const config = {
|
38 | name: 'db',
|
39 | connector: 'mysql',
|
40 | url: '',
|
41 | host: 'localhost',
|
42 | port: 3306,
|
43 | user: 'user',
|
44 | password: 'pass',
|
45 | database: 'testdb',
|
46 | };
|
47 | ```
|
48 |
|
49 | <details><summary markdown="span"><strong>For LoopBack 3 users</strong></summary>
|
50 |
|
51 | Use
|
52 | the [Data source generator](http://loopback.io/doc/en/lb3/Data-source-generator.html) to
|
53 | add a MySQL data source to your application.
|
54 | The generator will prompt for the database server hostname, port, and other
|
55 | settings required to connect to a MySQL database. It will also run the
|
56 | `npm install` command above for you.
|
57 |
|
58 | The entry in the application's `/server/datasources.json` will look like this:
|
59 |
|
60 | ```javascript
|
61 | "mydb": {
|
62 | "name": "mydb",
|
63 | "connector": "mysql",
|
64 | "host": "myserver",
|
65 | "port": 3306,
|
66 | "database": "mydb",
|
67 | "password": "mypassword",
|
68 | "user": "admin"
|
69 | }
|
70 | ```
|
71 |
|
72 | </details>
|
73 |
|
74 | Edit `<DataSourceName>.datasources.ts` to add any other additional properties
|
75 | that you require.
|
76 |
|
77 | ### Properties
|
78 |
|
79 | <table>
|
80 | <thead>
|
81 | <tr>
|
82 | <th width="150">Property</th>
|
83 | <th width="80">Type</th>
|
84 | <th>Description</th>
|
85 | </tr>
|
86 | </thead>
|
87 | <tbody>
|
88 | <tr>
|
89 | <td>collation</td>
|
90 | <td>String</td>
|
91 | <td>Determines the charset for the connection. Default is utf8_general_ci.</td>
|
92 | </tr>
|
93 | <tr>
|
94 | <td>connector</td>
|
95 | <td>String</td>
|
96 | <td>Connector name, either “loopback-connector-mysql” or “mysql”.</td>
|
97 | </tr>
|
98 | <tr>
|
99 | <td>connectionLimit</td>
|
100 | <td>Number</td>
|
101 | <td>The maximum number of connections to create at once. Default is 10.</td>
|
102 | </tr>
|
103 | <tr>
|
104 | <td>database</td>
|
105 | <td>String</td>
|
106 | <td>Database name</td>
|
107 | </tr>
|
108 | <tr>
|
109 | <td>debug</td>
|
110 | <td>Boolean</td>
|
111 | <td>If true, turn on verbose mode to debug database queries and lifecycle.</td>
|
112 | </tr>
|
113 | <tr>
|
114 | <td>host</td>
|
115 | <td>String</td>
|
116 | <td>Database host name</td>
|
117 | </tr>
|
118 | <tr>
|
119 | <td>password</td>
|
120 | <td>String</td>
|
121 | <td>Password to connect to database</td>
|
122 | </tr>
|
123 | <tr>
|
124 | <td>port</td>
|
125 | <td>Number</td>
|
126 | <td>Database TCP port</td>
|
127 | </tr>
|
128 | <tr>
|
129 | <td>socketPath</td>
|
130 | <td>String</td>
|
131 | <td>The path to a unix domain socket to connect to. When used host and port are ignored.</td>
|
132 | </tr>
|
133 | <tr>
|
134 | <td>supportBigNumbers</td>
|
135 | <td>Boolean</td>
|
136 | <td>Enable this option to deal with big numbers (BIGINT and DECIMAL columns) in the database. Default is false.</td>
|
137 | </tr>
|
138 | <tr>
|
139 | <td>timeZone</td>
|
140 | <td>String</td>
|
141 | <td>The timezone used to store local dates. Default is ‘local’.</td>
|
142 | </tr>
|
143 | <tr>
|
144 | <td>url</td>
|
145 | <td>String</td>
|
146 | <td>Connection URL of form <code>mysql://user:password@host/db</code>. Overrides other connection settings.</td>
|
147 | </tr>
|
148 | <tr>
|
149 | <td>username</td>
|
150 | <td>String</td>
|
151 | <td>Username to connect to database</td>
|
152 | </tr>
|
153 | <tr>
|
154 | <td>allowExtendedOperators</td>
|
155 | <td>Boolean</td>
|
156 | <td>Set to <code>true</code> to enable MySQL-specific operators
|
157 | such as <code>match</code>. Learn more in
|
158 | <a href="#extended-operators">Extended operators</a> below.
|
159 | </td>
|
160 | </tr>
|
161 | </tbody>
|
162 | </table>
|
163 |
|
164 | **NOTE**: In addition to these properties, you can use additional parameters
|
165 | supported by [`node-mysql`](https://github.com/felixge/node-mysql).
|
166 |
|
167 | ## Type mappings
|
168 |
|
169 | See [LoopBack 4 types](http://loopback.io/doc/en/lb4/LoopBack-types.html) (or [LoopBack 3 types](http://loopback.io/doc/en/lb3/LoopBack-types.html)) for
|
170 | details on LoopBack's data types.
|
171 |
|
172 | ### LoopBack to MySQL types
|
173 |
|
174 | <table>
|
175 | <thead>
|
176 | <tr>
|
177 | <th width="450">LoopBack Type</th>
|
178 | <th width="450">MySQL Type</th>
|
179 | </tr>
|
180 | </thead>
|
181 | <tbody>
|
182 | <tr>
|
183 | <td>String/JSON</td>
|
184 | <td>VARCHAR</td>
|
185 | </tr>
|
186 | <tr>
|
187 | <td>Text</td>
|
188 | <td>TEXT</td>
|
189 | </tr>
|
190 | <tr>
|
191 | <td>Number</td>
|
192 | <td>INT</td>
|
193 | </tr>
|
194 | <tr>
|
195 | <td>Date</td>
|
196 | <td>DATETIME</td>
|
197 | </tr>
|
198 | <tr>
|
199 | <td>Boolean</td>
|
200 | <td>TINYINT(1)</td>
|
201 | </tr>
|
202 | <tr>
|
203 | <td><a href="http://apidocs.strongloop.com/loopback-datasource-juggler/#geopoint" class="external-link">GeoPoint</a> object</td>
|
204 | <td>POINT</td>
|
205 | </tr>
|
206 | <tr>
|
207 | <td>Custom Enum type<br>(See <a href="#enum">Enum</a> below)</td>
|
208 | <td>ENUM</td>
|
209 | </tr>
|
210 | </tbody>
|
211 | </table>
|
212 |
|
213 | ### MySQL to LoopBack types
|
214 |
|
215 | <table>
|
216 | <thead>
|
217 | <tr>
|
218 | <th width="450">MySQL Type</th>
|
219 | <th width="450">LoopBack Type</th>
|
220 | </tr>
|
221 | </thead>
|
222 | <tbody>
|
223 | <tr>
|
224 | <td>CHAR</td>
|
225 | <td>String</td>
|
226 | </tr>
|
227 | <tr>
|
228 | <td>BIT(1)<br>CHAR(1)<br>TINYINT(1)</td>
|
229 | <td>Boolean</td>
|
230 | </tr>
|
231 | <tr>
|
232 | <td>VARCHAR<br>TINYTEXT<br>MEDIUMTEXT<br>LONGTEXT<br>TEXT<br>ENUM<br>SET</td>
|
233 | <td>String</td>
|
234 | </tr>
|
235 | <tr>
|
236 | <td>TINYBLOB<br>MEDIUMBLOB<br>LONGBLOB<br>BLOB<br>BINARY<br>VARBINARY<br>BIT</td>
|
237 | <td>Node.js <a href="http://nodejs.org/api/buffer.html">Buffer object</a></td>
|
238 | </tr>
|
239 | <tr>
|
240 | <td>TINYINT<br>SMALLINT<br>INT<br>MEDIUMINT<br>YEAR<br>FLOAT<br>DOUBLE<br>NUMERIC<br>DECIMAL</td>
|
241 | <td>
|
242 | <p>Number<br>For FLOAT and DOUBLE, see <a href="#floating-point-types">Floating-point types</a>. </p>
|
243 | <p>For NUMERIC and DECIMAL, see <a href="MySQL-connector.html">Fixed-point exact value types</a></p>
|
244 | </td>
|
245 | </tr>
|
246 | <tr>
|
247 | <td>DATE<br>TIMESTAMP<br>DATETIME</td>
|
248 | <td>Date</td>
|
249 | </tr>
|
250 | </tbody>
|
251 | </table>
|
252 |
|
253 | _NOTE_ as of v3.0.0 of MySQL Connector, the following flags were introduced:
|
254 |
|
255 | - `treatCHAR1AsString` default `false` - treats CHAR(1) as a String instead of a
|
256 | Boolean
|
257 | - `treatBIT1AsBit` default `true` - treats BIT(1) as a Boolean instead of a
|
258 | Binary
|
259 | - `treatTINYINT1AsTinyInt` default `true` - treats TINYINT(1) as a Boolean
|
260 | instead of a Number
|
261 |
|
262 | ## Data mapping properties
|
263 |
|
264 | Except the common database-specific properties we introduce in [How LoopBack Models Map To Database Tables/Collections](https://loopback.io/doc/en/lb4/Model.html#how-loopback-models-map-to-database-tablescollections), the following are more detailed examples and MySQL-specific settings.
|
265 |
|
266 | ### Table/Column Names
|
267 |
|
268 | Besides the basic LoopBack types, as we introduced above, you can also specify
|
269 | additional MySQL-specific properties for a LoopBack model. It would be mapped to
|
270 | the database.
|
271 |
|
272 | Use the `mysql.<property>` in the model definition or the property definition to
|
273 | configure the table/column definition.
|
274 |
|
275 | For example, the following settings would allow you to have custom table name
|
276 | (`Custom_User`) and column name (`custom_id` and `custom_name`). Such mapping is
|
277 | useful when you'd like to have different table/column names from the model:
|
278 |
|
279 | {% include code-caption.html content="user.model.ts" %}
|
280 |
|
281 | ```ts
|
282 | @model({
|
283 | settings: { mysql: { schema: 'testdb', table: 'Custom_User'} },
|
284 | })
|
285 | export class User extends Entity {
|
286 | @property({
|
287 | type: 'number',
|
288 | required: true,
|
289 | id: true,
|
290 | mysql: {
|
291 | columnName: 'custom_id',
|
292 | },
|
293 | })
|
294 | id: number;
|
295 |
|
296 | @property({
|
297 | type: 'string',
|
298 | mysql: {
|
299 | columnName: 'custom_name',
|
300 | },
|
301 | })
|
302 | name?: string;
|
303 | ```
|
304 |
|
305 | <details><summary markdown="span"><strong>For LoopBack 3 users</strong></summary>
|
306 |
|
307 | ```javascript
|
308 | {
|
309 | "name": "User",
|
310 | "options": {
|
311 | "mysql": {
|
312 | "schema": "testdb",
|
313 | "table": "Custom_User"
|
314 | }
|
315 | },
|
316 | "properties": {
|
317 | "id": {
|
318 | "type": "Number",
|
319 | "required": true,
|
320 | "mysql": {
|
321 | "columnName": "custom_id",
|
322 | }
|
323 | },
|
324 | "name": {
|
325 | "type": "String",
|
326 | "mysql": {
|
327 | "columnName": "custom_name",
|
328 | }
|
329 | },
|
330 | }
|
331 | }
|
332 | ```
|
333 |
|
334 | </details>
|
335 |
|
336 | ### Numeric Types
|
337 |
|
338 | Except the names, you can also use the dataType column/property attribute to
|
339 | specify what MySQL column type to use. The following MySQL type-dataType
|
340 | combinations are supported:
|
341 |
|
342 | - number
|
343 | - integer
|
344 | - tinyint
|
345 | - smallint
|
346 | - mediumint
|
347 | - int
|
348 | - bigint
|
349 | - float
|
350 | - double
|
351 | - decimal
|
352 |
|
353 | The following examples will be in LoopBack 4 style, but it's the same if you
|
354 | provide `mysql.<property>` to the LB3 property definition.
|
355 |
|
356 | #### Floating-point types
|
357 |
|
358 | For Float and Double data types, use the `precision` and `scale` options to
|
359 | specify custom precision. Default is (16,8).
|
360 |
|
361 | <details><summary markdown="span"><strong>Example</strong></summary>
|
362 |
|
363 | ```ts
|
364 | @property({
|
365 | type: 'Number',
|
366 | mysql: {
|
367 | dataType: 'float',
|
368 | precision: 20,
|
369 | scale: 4
|
370 | }
|
371 | })
|
372 | price: Number;
|
373 | ```
|
374 |
|
375 | </details>
|
376 |
|
377 | #### Fixed-point exact value types
|
378 |
|
379 | For Decimal and Numeric types, use the `precision` and `scale` options to
|
380 | specify custom precision. Default is (9,2). These aren't likely to function as
|
381 | true fixed-point.
|
382 |
|
383 | <details><summary markdown="span"><strong>Example</strong></summary>
|
384 |
|
385 | ```ts
|
386 | @property({
|
387 | type: 'Number',
|
388 | mysql: {
|
389 | dataType: 'decimal',
|
390 | precision: 12,
|
391 | scale: 8
|
392 | }
|
393 | })
|
394 | price: Number;
|
395 | ```
|
396 |
|
397 | </details>
|
398 |
|
399 | ### Text types
|
400 |
|
401 | Convert String / DataSource.Text / DataSource.JSON to the following MySQL types:
|
402 |
|
403 | - varchar
|
404 | - char
|
405 | - text
|
406 | - mediumtext
|
407 | - tinytext
|
408 | - longtext
|
409 |
|
410 | <details><summary markdown="span"><strong>Example</strong></summary>
|
411 |
|
412 | ```ts
|
413 | @property({
|
414 | type: 'String',
|
415 | mysql: {
|
416 | dataType: 'char',
|
417 | dataLength: 24 // limits the property length
|
418 | },
|
419 | })
|
420 | userName: String;
|
421 | ```
|
422 |
|
423 | </details>
|
424 |
|
425 | ### Dat types
|
426 |
|
427 | Convert JSON Date types to datetime or timestamp.
|
428 |
|
429 | <details><summary markdown="span"><strong>Example</strong></summary>
|
430 |
|
431 | ```ts
|
432 | @property({
|
433 | type: 'Date',
|
434 | mysql: {
|
435 | dataType: 'timestamp',
|
436 | },
|
437 | })
|
438 | startTime: Date;
|
439 | ```
|
440 |
|
441 | </details>
|
442 |
|
443 | ### Enum
|
444 |
|
445 | See the [Model ENUM property](https://loopback.io/doc/en/lb4/Model.html#enum-property) for details.
|
446 |
|
447 | ### Default Clause/Constant
|
448 |
|
449 | Use the `default` and `dataType` properties to have MySQL handle **setting column `DEFAULT` value**.
|
450 |
|
451 | <details><summary markdown="span"><strong>Example</strong></summary>
|
452 |
|
453 | ```ts
|
454 | @property({
|
455 | type: 'String',
|
456 | mysql: {
|
457 | dataType: 'varchar',
|
458 | default: 'pending'
|
459 | }
|
460 | })
|
461 | status: String;
|
462 |
|
463 | @property({
|
464 | type: 'Number',
|
465 | mysql: {
|
466 | dataType: 'int',
|
467 | default: 42
|
468 | }
|
469 | })
|
470 | maxDays: Number;
|
471 |
|
472 | @property({
|
473 | type: 'boolean',
|
474 | mysql: {
|
475 | dataType: 'tinyint',
|
476 | default: 1
|
477 | }
|
478 | })
|
479 | isDone: Boolean;
|
480 | ```
|
481 |
|
482 | </details>
|
483 |
|
484 | For the date or timestamp types use `CURRENT_TIMESTAMP` or `now`.
|
485 |
|
486 | <details><summary markdown="span"><strong>Example</strong></summary>
|
487 |
|
488 | ```ts
|
489 | @property({
|
490 | type: 'Date',
|
491 | mysql: {
|
492 | dataType: 'datetime',
|
493 | default: 'CURRENT_TIMESTAMP'
|
494 | }
|
495 | })
|
496 | last_modified: Date;
|
497 | ```
|
498 |
|
499 | </details>
|
500 |
|
501 | **NOTE**: The following column types do **NOT** supported
|
502 | [MySQL Default Values](https://dev.mysql.com/doc/refman/5.7/en/data-type-defaults.html):
|
503 |
|
504 | - BLOB
|
505 | - TEXT
|
506 | - GEOMETRY
|
507 | - JSON
|
508 |
|
509 | ## Extended operators
|
510 | MySQL connector supports the following MySQL-specific operators:
|
511 | - [`match`](#operator-match)
|
512 | Please note extended operators are disabled by default, you must enable
|
513 | them at datasource level or model level by setting `allowExtendedOperators` to
|
514 | `true`.
|
515 | ### Operator `match`
|
516 | The `match` operator allows you to perform a full text search using the [MATCH() .. AGAINST()](https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html) operator in MySQL.
|
517 |
|
518 | Three different modes of the `MATCH` clause are also available in the form of operators -
|
519 |
|
520 | - `matchbool` for [Boolean Full Text Search](https://dev.mysql.com/doc/refman/8.0/en/fulltext-boolean.html)
|
521 | - `matchnl` for [Natural Language Full Text Search](https://dev.mysql.com/doc/refman/8.0/en/fulltext-natural-language.html)
|
522 | - `matchqe` for [Full-Text Searches with Query Expansion](https://dev.mysql.com/doc/refman/8.0/en/fulltext-query-expansion.html)
|
523 | - `matchnlqe` for [Full-Text Searches with Query Expansion](https://dev.mysql.com/doc/refman/8.0/en/fulltext-query-expansion.html) with the `IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION` modifier.
|
524 |
|
525 | By default, the `match` operator works in Natural Language mode.
|
526 |
|
527 | **Note** The fields you are querying must be setup with a `FULLTEXT` index to perform full text search on them.
|
528 | Assuming a model such as this:
|
529 | ```ts
|
530 | @model({
|
531 | settings: {
|
532 | allowExtendedOperators: true,
|
533 | }
|
534 | })
|
535 | class Post {
|
536 | @property({
|
537 | type: 'string',
|
538 | mysql: {
|
539 | index: {
|
540 | kind: 'FULLTEXT'
|
541 | }
|
542 | },
|
543 | })
|
544 | content: string;
|
545 | }
|
546 | ```
|
547 | You can query the content field as follows:
|
548 | ```ts
|
549 | const posts = await postRepository.find({
|
550 | where: {
|
551 | {
|
552 | content: {match: 'someString'},
|
553 | }
|
554 | }
|
555 | });
|
556 | ```
|
557 |
|
558 | ## Discovery and auto-migration
|
559 |
|
560 | ### Model discovery
|
561 |
|
562 | The MySQL connector supports _model discovery_ that enables you to create
|
563 | LoopBack models based on an existing database schema. Once you defined your
|
564 | datasource:
|
565 |
|
566 | - LoopBack 4 users could use the commend
|
567 | [`lb4 discover`](https://loopback.io/doc/en/lb4/Discovering-models.html) to
|
568 | discover models.
|
569 | - For LB3 users, please check
|
570 | [Discovering models from relational databases](https://loopback.io/doc/en/lb3/Discovering-models-from-relational-databases.html).
|
571 | (See
|
572 | [database discovery API](http://apidocs.strongloop.com/loopback-datasource-juggler/#datasource-prototype-discoverandbuildmodels)
|
573 | for related APIs information)
|
574 |
|
575 | ### Auto-migration
|
576 |
|
577 | The MySQL connector also supports _auto-migration_ that enables you to create a
|
578 | database schema from LoopBack models. For example, based on the following model,
|
579 | the auto-migration method would create/alter existing `Customer` table in the
|
580 | database. Table `Customer` would have two columns: `name` and `id`, where `id`
|
581 | is also the primary key that has `auto_increment` set as it has definition of
|
582 | `type: 'Number'` and `generated: true`:
|
583 |
|
584 | ```ts
|
585 | @model()
|
586 | export class Customer extends Entity {
|
587 | @property({
|
588 | id: true,
|
589 | type: 'Number',
|
590 | generated: true,
|
591 | })
|
592 | id: number;
|
593 |
|
594 | @property({
|
595 | type: 'string',
|
596 | })
|
597 | name: string;
|
598 | }
|
599 | ```
|
600 |
|
601 | Moreover, additional MySQL-specific properties mentioned in the
|
602 | [Data mapping properties](#data-mapping-properties) section work with
|
603 | auto-migration as well.
|
604 |
|
605 | #### Auto-generated ids
|
606 |
|
607 | For now LoopBack MySQL connector only supports auto-generated id
|
608 | (`generated: true`) for integer type as for MySQL, the default id type is
|
609 | _integer_. If you'd like to use other types such as string (uuid) as the id
|
610 | type, you can:
|
611 |
|
612 | - use uuid that is **generated by your LB application** by setting
|
613 | [`defaultFn: uuid`](https://loopback.io/doc/en/lb4/Model.html#property-decorator).
|
614 |
|
615 | ```ts
|
616 | @property({
|
617 | id: true,
|
618 | type: 'string'
|
619 | defaultFn: 'uuidv4',
|
620 | // generated: true, -> not needed
|
621 | })
|
622 | id: string;
|
623 | ```
|
624 |
|
625 | - Alter the table in your database to use a certain function if you prefer
|
626 | having **the database to generate the value**.
|
627 |
|
628 | ```ts
|
629 | @property({
|
630 | id: true,
|
631 | type: 'string'
|
632 | generated: true, // to indicate the value generates by the db
|
633 | useDefaultIdType: false, // needed
|
634 | })
|
635 | id: string;
|
636 | ```
|
637 |
|
638 | #### Auto-migrate/Auto-update models with foreign keys
|
639 |
|
640 | Foreign key constraints can be defined in the model definition.
|
641 |
|
642 | **Note**: The order of table creation is important. A referenced table must
|
643 | exist before creating a foreign key constraint. The order can be specified
|
644 | using the optional <a href="https://loopback.io/doc/en/lb4/apidocs.repository.schemamigrationoptions.html">`SchemaMigrationOptions`</a> argument of `migrateSchema`:
|
645 |
|
646 | ```
|
647 | await app.migrateSchema({
|
648 | models: [ 'Customer', 'Order' ]
|
649 | });
|
650 | ```
|
651 |
|
652 | Define your models and the foreign key constraints as follows:
|
653 |
|
654 | {% include code-caption.html content="customer.model.ts" %}
|
655 |
|
656 | ```ts
|
657 | @model()
|
658 | export class Customer extends Entity {
|
659 | @property({
|
660 | id: true,
|
661 | type: 'Number',
|
662 | generated: true,
|
663 | })
|
664 | id: number;
|
665 |
|
666 | @property({
|
667 | type: 'string',
|
668 | })
|
669 | name: string;
|
670 | }
|
671 | ```
|
672 |
|
673 | `order.model.ts`:
|
674 |
|
675 | ```ts
|
676 | @model({
|
677 | settings: {
|
678 | foreignKeys: {
|
679 | fk_order_customerId: {
|
680 | name: 'fk_order_customerId',
|
681 | entity: 'Customer',
|
682 | entityKey: 'id',
|
683 | foreignKey: 'customerId',
|
684 | },
|
685 | },
|
686 | })
|
687 | export class Order extends Entity {
|
688 | @property({
|
689 | id: true,
|
690 | type: 'Number',
|
691 | generated: true
|
692 | })
|
693 | id: number;
|
694 |
|
695 | @property({
|
696 | type: 'string'
|
697 | })
|
698 | name: string;
|
699 |
|
700 | @property({
|
701 | type: 'Number'
|
702 | })
|
703 | customerId: number;
|
704 | }
|
705 | ```
|
706 |
|
707 | <details><summary markdown="span"><strong>For LoopBack 3 users</strong></summary>
|
708 |
|
709 | ```json
|
710 | ({
|
711 | "name": "Customer",
|
712 | "options": {
|
713 | "idInjection": false
|
714 | },
|
715 | "properties": {
|
716 | "id": {
|
717 | "type": "Number",
|
718 | "id": 1
|
719 | },
|
720 | "name": {
|
721 | "type": "String",
|
722 | "required": false
|
723 | }
|
724 | }
|
725 | },
|
726 | {
|
727 | "name": "Order",
|
728 | "options": {
|
729 | "idInjection": false,
|
730 | "foreignKeys": {
|
731 | "fk_order_customerId": {
|
732 | "name": "fk_order_customerId",
|
733 | "entity": "Customer",
|
734 | "entityKey": "id",
|
735 | "foreignKey": "customerId"
|
736 | }
|
737 | }
|
738 | },
|
739 | "properties": {
|
740 | "id": {
|
741 | "type": "Number"
|
742 | "id": 1
|
743 | },
|
744 | "customerId": {
|
745 | "type": "Number"
|
746 | },
|
747 | "description": {
|
748 | "type": "String",
|
749 | "required": false
|
750 | }
|
751 | }
|
752 | })
|
753 | ```
|
754 |
|
755 | </details>
|
756 |
|
757 | MySQL handles the foreign key integrity by the referential action specified by
|
758 | `ON UPDATE` and `ON DELETE`. You can specify which referential actions the
|
759 | foreign key follows in the model definition upon auto-migrate or auto-update
|
760 | operation. Both `onDelete` and `onUpdate` default to `restrict`.
|
761 |
|
762 | Take the example we showed above, let's add the referential action to the
|
763 | foreign key `customerId`:
|
764 |
|
765 | ```ts
|
766 | @model({
|
767 | settings: {
|
768 | foreignKeys: {
|
769 | fk_order_customerId: {
|
770 | name: 'fk_order_customerId',
|
771 | entity: 'Customer',
|
772 | entityKey: 'id',
|
773 | foreignKey: 'customerId',
|
774 | onUpdate: 'restrict', // restrict|cascade|set null|no action|set default
|
775 | onDelete: 'cascade' // restrict|cascade|set null|no action|set default
|
776 | },
|
777 | },
|
778 | })
|
779 | export class Order extends Entity {
|
780 | ...
|
781 | ```
|
782 |
|
783 | <details><summary markdown="span"><strong>For LoopBack 3 users</strong></summary>
|
784 |
|
785 | **model-definiton.json**
|
786 |
|
787 | ```json
|
788 | {
|
789 | "name": "Customer",
|
790 | "options": {
|
791 | "idInjection": false
|
792 | },
|
793 | "properties": {
|
794 | "id": {
|
795 | "type": "Number",
|
796 | "id": 1
|
797 | },
|
798 | "name": {
|
799 | "type": "String",
|
800 | "required": false
|
801 | }
|
802 | }
|
803 | },
|
804 | {
|
805 | "name": "Order",
|
806 | "options": {
|
807 | "idInjection": false,
|
808 | "foreignKeys": {
|
809 | "fk_order_customerId": {
|
810 | "name": "fk_order_customerId",
|
811 | "entity": "Customer",
|
812 | "entityKey": "id",
|
813 | "foreignKey": "customerId",
|
814 | "onUpdate": "restrict",
|
815 | "onDelete": "cascade"
|
816 | }
|
817 | }
|
818 | },
|
819 | "properties": {
|
820 | "id": {
|
821 | "type": "Number"
|
822 | "id": 1
|
823 | },
|
824 | "customerId": {
|
825 | "type": "Number"
|
826 | },
|
827 | "description": {
|
828 | "type": "String",
|
829 | "required": false
|
830 | }
|
831 | }
|
832 | }
|
833 | ```
|
834 |
|
835 | **boot-script.js**
|
836 |
|
837 | ```js
|
838 | module.exports = function (app) {
|
839 | var mysqlDs = app.dataSources.mysqlDS;
|
840 | var Book = app.models.Order;
|
841 | var Author = app.models.Customer;
|
842 |
|
843 | // first autoupdate the `Customer` model to avoid foreign key constraint failure
|
844 | mysqlDs.autoupdate('Customer', function (err) {
|
845 | if (err) throw err;
|
846 | console.log('\nAutoupdated table `Customer`.');
|
847 |
|
848 | mysqlDs.autoupdate('Order', function (err) {
|
849 | if (err) throw err;
|
850 | console.log('\nAutoupdated table `Order`.');
|
851 | // at this point the database table `Order` should have one foreign key `customerId` integrated
|
852 | });
|
853 | });
|
854 | };
|
855 | ```
|
856 |
|
857 | </details>
|
858 |
|
859 | #### Breaking Changes with GeoPoint since 5.x
|
860 |
|
861 | Prior to `loopback-connector-mysql@5.x`, MySQL connector was saving and loading
|
862 | GeoPoint properties from the MySQL database in reverse. MySQL expects values to
|
863 | be `POINT(X, Y)` or `POINT(lng, lat)`, but the connector was saving them in the
|
864 | opposite order(i.e. `POINT(lat,lng)`).
|
865 |
|
866 | Use the `geopoint` type to achieve so:
|
867 |
|
868 | ```ts
|
869 | @property({
|
870 | type: 'geopoint'
|
871 | })
|
872 | name: GeoPoint;
|
873 | ```
|
874 |
|
875 | If you have an application with a model that has a GeoPoint property using
|
876 | previous versions of this connector, you can migrate your models using the
|
877 | following programmatic approach:
|
878 |
|
879 | <details><summary markdown="span"><strong>Click here to expand</strong></summary>
|
880 |
|
881 | **NOTE** Please back up the database tables that have your application data
|
882 | before performing any of the steps.
|
883 |
|
884 | 1. Create a boot script under `server/boot/` directory with the following:
|
885 |
|
886 | ```js
|
887 | 'use strict';
|
888 | module.exports = function (app) {
|
889 | function findAndUpdate() {
|
890 | var teashop = app.models.teashop;
|
891 | //find all instances of the model we'd like to migrate
|
892 | teashop.find({}, function (err, teashops) {
|
893 | teashops.forEach(function (teashopInstance) {
|
894 | //what we fetch back from the db is wrong, so need to revert it here
|
895 | var newLocation = {
|
896 | lng: teashopInstance.location.lat,
|
897 | lat: teashopInstance.location.lng,
|
898 | };
|
899 | //only update the GeoPoint property for the model
|
900 | teashopInstance.updateAttribute('location', newLocation, function (
|
901 | err,
|
902 | inst,
|
903 | ) {
|
904 | if (err) console.log('update attribute failed', err);
|
905 | else console.log('updateAttribute successful');
|
906 | });
|
907 | });
|
908 | });
|
909 | }
|
910 |
|
911 | findAndUpdate();
|
912 | };
|
913 | ```
|
914 |
|
915 | 2. Run the boot script by simply running your application or `node .`
|
916 |
|
917 | For the above example, the model definition is as follows:
|
918 |
|
919 | ```json
|
920 | {
|
921 | "name": "teashop",
|
922 | "base": "PersistedModel",
|
923 | "idInjection": true,
|
924 | "options": {
|
925 | "validateUpsert": true
|
926 | },
|
927 | "properties": {
|
928 | "name": {
|
929 | "type": "string",
|
930 | "default": "storename"
|
931 | },
|
932 | "location": {
|
933 | "type": "geopoint"
|
934 | }
|
935 | },
|
936 | "validations": [],
|
937 | "relations": {},
|
938 | "acls": [],
|
939 | "methods": {}
|
940 | }
|
941 | ```
|
942 |
|
943 | </details>
|
944 |
|
945 | ## Running tests
|
946 |
|
947 | ### Own instance
|
948 |
|
949 | If you have a local or remote MySQL instance and would like to use that to run
|
950 | the test suite, use the following command:
|
951 |
|
952 | - Linux
|
953 |
|
954 | ```bash
|
955 | MYSQL_HOST=<HOST> MYSQL_PORT=<PORT> MYSQL_USER=<USER> MYSQL_PASSWORD=<PASSWORD> MYSQL_DATABASE=<DATABASE> CI=true npm test
|
956 | ```
|
957 |
|
958 | - Windows
|
959 |
|
960 | ```bash
|
961 | 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
|
962 | ```
|
963 |
|
964 | ### Docker
|
965 |
|
966 | If you do not have a local MySQL instance, you can also run the test suite with
|
967 | very minimal requirements.
|
968 |
|
969 | - Assuming you have [Docker](https://docs.docker.com/engine/installation/)
|
970 | installed, run the following script which would spawn a MySQL instance on your
|
971 | local:
|
972 |
|
973 | ```bash
|
974 | source setup.sh <HOST> <PORT> <USER> <PASSWORD> <DATABASE>
|
975 | ```
|
976 |
|
977 | where `<HOST>`, `<PORT>`, `<USER>`, `<PASSWORD>` and `<DATABASE>` are optional
|
978 | parameters. The default values are `localhost`, `3306`, `root`, `pass` and
|
979 | `testdb` respectively.
|
980 |
|
981 | - Run the test:
|
982 |
|
983 | ```bash
|
984 | npm test
|
985 | ```
|