sequelize-auto
sequelize-auto copied to clipboard
sequelize
Relationship missing in some particular case
In some conditions, the generated model miss some of the relationship between classes.
Ok, this bug is really strange and hard to isolate, so I'm not able to give a minimal reproduction case. I will try to explain it as best as I can. On a SQL Server database, if I create a new DB and schema, then create tables, then create indexes, then create foreign keys, when I generate the model, some of the relationship are missing. I found a workaround by creating the tables, then the foreign keys, then the indexes. That way the model is correctly generated. However, the database looks exactly the same, so I would have trouble giving a pointer so a solution to this problem. I notice that if I explicitly name all the index (Including autogenerated ones such as PK), there is less relationship missing (I would have said no dependency missing, but I just had a case). The really puzzling part is that only some relationship are missing, not all.
This bug is not blocker since I found a workaround, but the generated model need to be check carefully to ensure all link are generated. I have had this issue since 0.8.5 at least, and it is still present in 0.8.7
That is really strange. Thanks for the detailed explanation of the problem.
Sequelize-auto gets the foreign key relationships from a query in mssql.ts. It looks like this:
SELECT ccu.TABLE_NAME AS source_table,
ccu.CONSTRAINT_NAME AS constraint_name,
ccu.CONSTRAINT_SCHEMA AS source_schema,
ccu.COLUMN_NAME AS source_column,
kcu.TABLE_NAME AS target_table,
kcu.TABLE_SCHEMA AS target_schema,
kcu.COLUMN_NAME AS target_column,
tc.CONSTRAINT_TYPE AS constraint_type,
c.IS_IDENTITY AS is_identity
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
ON ccu.CONSTRAINT_NAME = tc.CONSTRAINT_NAME
LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
ON ccu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
ON kcu.CONSTRAINT_NAME = rc.UNIQUE_CONSTRAINT_NAME
AND tc.CONSTRAINT_TYPE = 'FOREIGN KEY'
INNER JOIN sys.columns c
ON c.name = ccu.COLUMN_NAME
AND c.object_id = OBJECT_ID(ccu.TABLE_SCHEMA + '.' + ccu.TABLE_NAME)
WHERE tc.CONSTRAINT_TYPE != 'CHECK'
AND ccu.TABLE_NAME = 'Customer'
AND ccu.TABLE_SCHEMA = 'dbo'
Maybe you could run this query on some of your tables for the case that works correctly (create foreign keys, then indexes) and the case that does not work (create indexes, then foreign keys). Then compare the output, and see if there is any difference.
Thanks for finding and diagnosing this problem.
I tried to play the given query, and I have noticed something. The result does not include the target in the failling case. I'm adding a file with the raw results. Sequelize_auto_relationship_error.txt The only difference between the 2 schema is the order of the index / foreign key creation. I'm not able to give you the full DB creation script, as it is not mine, but I will try to create a reproductible example.
Thanks for you analysis on this error, and sorry for not being able to propose a PR fixing this.
Thanks. It seems like in the working version, the query has the foreign key target information filled in, but in the non-working version, it does not. For example, here's the info for DOADefinition (just the first 4 rows from each query):
DOADefinition: (Fail to link to CDA)
source_table |constraint_name |source_schema|source_column |target_table|target_schema|target_column|constraint_type|is_identity|
-------------+------------------------------+-------------+---------------+------------+-------------+-------------+---------------+-----------+
DOADefinition|applies to |DOA_2 |LegalEntityId | | | |FOREIGN KEY | 0|
DOADefinition|applies to |DOA_2 |CDAId | | | |FOREIGN KEY | 0|
DOADefinition|has type |DOA_2 |DOATypeId | | | |FOREIGN KEY | 0|
DOADefinition|PK__DOADefin__978278B6503EA1BE|DOA_2 |DOADefinitionId| | | |PRIMARY KEY | 1|
DOADefinition : (Linked to CDA OK)
source_table |constraint_name |source_schema|source_column |target_table|target_schema|target_column|constraint_type|is_identity|
-------------+------------------------------+-------------+---------------+------------+-------------+-------------+---------------+-----------+
DOADefinition|applies to |DOA_2 |LegalEntityId |LegalEntity |DOA_2 |LegalEntityId|FOREIGN KEY | 0|
DOADefinition|applies to |DOA_2 |CDAId |CDA |DOA_2 |CDAId |FOREIGN KEY | 0|
DOADefinition|has type |DOA_2 |DOATypeId |DOAType |DOA_2 |DOATypeId |FOREIGN KEY | 0|
DOADefinition|PK__DOADefin__978278B65D76995C|DOA_2 |DOADefinitionId| | | |PRIMARY KEY | 1|
In the query, the target information (target_table, target_schema, target_column) comes from INFORMATION_SCHEMA.KEY_COLUMN_USAGE, so the information is either not in that table, or it is not being joined correctly in the non-working case.
That's what I've seen in the end, but I can't really explain it. It might be a SQL Server bug (It would not be the first one). I'm not sure it is possible to handle this in sequelize-auto...
Do you want me to try and give you a DB creation script to reproduce the problem ? I'm not sure how simple I can make it...
Yes, please! A DB creation skip would definitely help.
There may be a bug in sequelize-auto's INFORMATION_SCHEMA query, or there may be a bug in MSSQL in the way that it writes metadata to INFORMATION_SCHEMA when creating the indexes and keys. If the latter is the case, I may be able to work around it by completely re-writing the query using the sys.* tables instead of INFORMATION_SCHEMA. Ugh.
BTW, What version of SQL Server are you using?
I will try to give you this tomorrow. I need to clean up a lot and test, because I don't think you need the 20+ tables schema with 40+ relationship...
I have found the problem on a dockerized SQL Server 2019, but also on an AWS RDS SQL Server 2017, so it seems pretty consistent by the time.