Loopback model definition is not adding foreign key relation in database table about loopback-connector-mysql HOT 13 CLOSED

For anyone else that needs this, you can check out my fork:
https://github.com/EmileSpecs/loopback-connector-mysql/tree/feature/foreign-key-migration

It just supports "belongsTo" relations at the moment since those are all I needed. Shouldn't be to difficult to add other relevant relationship types.

It makes sense to me that the database takes care of relational integrity of data and it saves a lot of code to not have to worry about cascading your deletions to linked models.

from loopback-connector-mysql.

Since I use automigrate a lot for my tests, I've added the following code after automigrate to add indexes after table creation

let sql = 'ALTER TABLE quote add constraint fk_book foreign key (book_id) REFERENCES book (id);'
Quote.dataSource.connector.query(sql, done);

from loopback-connector-mysql.

Hi, @fusionstrings.

The automigrate/autoupdate doesn't support relations at this moment.

from loopback-connector-mysql.

ohh ok, any work around then?

from loopback-connector-mysql.

Please note that LoopBack's relation is not tied to RDB foreign keys. It doesn't use SQL JOIN. For example, to query all transactions for a given account, say /api/accounts//transactions, LoopBack generates a query like:

select * from transaction where accountId = .

Can you elaborate why you need to set up the RDB foreign keys? Is it for referential integrity?

from loopback-connector-mysql.

Exactly @raymondfeng , I require to use foreign keys for referential integrity. I understand LoopBack uses "WHERE" query and as a work around I am adding foreign keys directly in database but while running automigrate or autoupdate, it throws some error due to that, although it works fine.

As a temporary workaround I am thinking about not to add foreign keys constraint in database until model definitions are stable. Once need to run autoupdate/automigrate is eliminated, I can manually add foreign keys. Somehow it does not feel right.

from loopback-connector-mysql.

The autoupdate/automigrate is only required if your model changes. You should be able to manually add foreign keys after autoupdate/automigrate is done and disable autoupdate/automigrate.

from loopback-connector-mysql.

This helps a lot and I am glad I was thinking in right direction. Thanks a lot.

from loopback-connector-mysql.

Hi @fusionstrings , just stumble upon this same problem and I was wondering "what am I doing wrong?" but yes, it appears that loopback is not adding the foreign key constrains in the RDB on migrate. I wanted to ask you: what approach did you finally use? did you add the constrains yourself manually? I'm finding very cumbersome having to add extra logic in the models controller to add the proper reference to the owner model id

from loopback-connector-mysql.

@josefd8 Yes it's cumbersome but that's the workaround I'd to adopt. As of not it's been ages since I'd used loopback so can't say much.

from loopback-connector-mysql.

This can help:

https://www.npmjs.com/package/loopback-mysql-referential-integrity-component

from loopback-connector-mysql.

This is supported by the newer mysql-connector. The information is buried in the documentation (specifically here). See also discussion #294.

from loopback-connector-mysql.

Does it have any impact on performance when doing complex queries ?

from loopback-connector-mysql.