Article update is not working on PostgreSQL fails with "SequelizeDatabaseError: missing FROM-clause entry for table "Tags""

[patdpat]

Cannot update article in every field (title, body, tags)
I suspect false in https://github.com/cirosantilli/node-express-sequelize-nextjs-realworld-example-app/blob/next/api/articles.js

[cirosantilli]

Thanks for this! Reproduce locally on 452ca0e on PostgreSQL but not SQLite, so this some annoying inconsistncy between databases in sequelize... that library drives me nuts.

The error message from the server is:

SequelizeDatabaseError: missing FROM-clause entry for table "Tags"                                                                                                                                                                                                              
    at Query.formatError (/home/ciro/bak/git/node-express-sequelize-nextjs-realworld-example-app/node_modules/sequelize/lib/dialects/postgres/query.js:386:16)                                                                                                                      at Query.run (/home/ciro/bak/git/node-express-sequelize-nextjs-realworld-example-app/node_modules/sequelize/lib/dialects/postgres/query.js:87:18)                                                                                                                               at processTicksAndRejections (internal/process/task_queues.js:95:5)                                                                                                                                                                                                         
    at async /home/ciro/bak/git/node-express-sequelize-nextjs-realworld-example-app/node_modules/sequelize/lib/sequelize.js:619:16                                                                                                                                                  at async PostgresQueryInterface.select (/home/ciro/bak/git/node-express-sequelize-nextjs-realworld-example-app/node_modules/sequelize/lib/dialects/abstract/query-interface.js:952:12)                                                                                          at async Function.findAll (/home/ciro/bak/git/node-express-sequelize-nextjs-realworld-example-app/node_modules/sequelize/lib/model.js:1753:21)                                                                                                                              
    at async model.Article.deleteEmptyTags (/home/ciro/bak/git/node-express-sequelize-nextjs-realworld-example-app/models/article.js:65:23)                                                                                                                                         at async /home/ciro/bak/git/node-express-sequelize-nextjs-realworld-example-app/api/articles.js:304:15                                                                                                                                                                      
    at async /home/ciro/bak/git/node-express-sequelize-nextjs-realworld-example-app/node_modules/sequelize/lib/sequelize.js:1090:24                                                                                                                                                 at async /home/ciro/bak/git/node-express-sequelize-nextjs-realworld-example-app/api/articles.js:299:9

and it comes from this call:

node-express-sequelize-nextjs-realworld-example-app/models/article.js

Line 65 in 452ca0e

const emptyTags = await sequelize.models.Article.findAll({

A more or less minimized equivalent is: https://github.com/cirosantilli/cirosantilli.github.io/blob/a8d2103981507f73331c03d06ad8f06960a2163b/nodejs/sequelize/many_to_many.js#L345 but that is working, gotta figure out what's the difference now.

Local repro is easy at d6d7566 with:

DEBUG='sequelize:sql:*' npm run test-pg -- -g 'tags without articles are deleted automatically after their last article is deleted'

The generated failing query is:

SELECT
  "Tags"."id",
  COUNT("Article"."id") AS "count"
FROM
  "Article" AS "Article"
  INNER JOIN (
    "ArticleTag" AS "tags->ArticleTag"
    INNER JOIN "Tag" AS "tags" ON "tags"."id" = "tags->ArticleTag"."tagId"
  ) ON "Article"."id" = "tags->ArticleTag"."articleId"
  AND "Article"."id" = 2
  LEFT OUTER JOIN (
    "ArticleTag" AS "tags->taggedArticles->ArticleTag"
    INNER JOIN "Article" AS "tags->taggedArticles" ON "tags->taggedArticles"."id" = "tags->taggedArticles->ArticleTag"."articleId"
  ) ON "tags"."id" = "tags->taggedArticles->ArticleTag"."tagId"
GROUP BY
  "Tags"."id"
HAVING
  COUNT("Article"."id") = 1
ORDER BY
  "count" DESC;

[cirosantilli]

OK, I figured out the problem by looking at the produced query, the Tags table is named as lower case tags at: INNER JOIN "Tag" AS "tags" presumably because of the as: , so I converted my constants to "tags"."id" and it works.

The problem might not happen in SQLite due to case insentiveness or differnt case generation, I didn't bother to check.

That ORM should really validate for us this kind of thing for the user rather than just taking constants like 'Tags.id and putting them in the query.

Now also a test run on both SQLite and PostgreSQL before deploying to avoid this problem in the future. PG tests weren't working well so fixed them too.

Thanks so much for the report!