loopbackio / loopbackio/loopback-next
[@loopback/sequelize] Duplicate column names in relation queries
Nobody has claimed this yet.
- Dominant language
- TypeScript
- Stars
- 5.1k
- Forks
- 1.1k
- Avg merge
- 2d 21h
- Merged PRs (30d)
- 27
Description
### Describe the bug
Repository queries with relations that do not explicitly set all the keyTo / keyFrom / through properties will lead to broken SQL queries that contain duplicated title case column names mixed in with the expected camelcase columns.
Example:
```sql
SELECT bookid as bookId, reader.id as readerId, ReaderId …
```
Relates to:
https://github.com/sequelize/sequelize/issues/9328
https://github.com/loopbackio/loopback-next/issues/9591
https://github.com/sourcefuse/loopback4-sequelize/issues/35
### Logs
_No response_
### Additional information
Workaround seems to be to go through all the Entity relations and explicitly set all the relation key names but that can be time-consuming and error-prone with a larger project. It seems like it might be possible to mitigate this at the loopback model to Sequelize relation layer.
### Reproduction
With these changes, running the tests for the Sequelize extension will replicate the error:
https://github.com/KalleV/loopback-next/commit/3c29d852b46f19f4805d556ae32aee49c79471f1
Turns out it's necessary to define additional "belongsTo" relations in other entities before this happens. The extra relation is added to the "Patient" entity in this case. With this set up, I am seeing the following happen:
- The default relation properties are set by Loopback (i.e. I can see the "keyFrom" is populated as todoListId) but the "keyTo" is undefined:

- This leads to undefined being passed as the foreign key to sequelize:

- And then that causes Sequelize to assign a Title Case property through it's own default relation column logic leading to a duplicate column name in the database query:

Contributor guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
Research direction
Start with the Sequelize extension tests and the reproduction changes in commit 3c29d852b46f19f4805d556ae32aee49c79471f1. Inspect the Patient relation setup and handling of keyFrom, keyTo, and through before the query reaches Sequelize. Done means relations without every property explicitly set no longer produce duplicate columns, with regression coverage in the extension tests.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- node.js, typescript
- Domain
- api, backend, database
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100