Ciro Santilli @cirosantilli 35

 Incoming links: JOIN (SQL)

Sequelize example  Updated 2025-01-10  +Created 1970-01-01

To run examples on a specific database:

./index.js or ./index.js l: SQLite
./index.js p: PostgreSQL. You must manually create a database called tmp and ensure that peer authentication works for it

All examples can be tested on all databases with:

cd sequelize
./test

Overview of the examples:

nodejs/sequelize/index.js: a bunch of basic examples
nodejs/sequelize/update.js: This file is also where we are storing our expression-foo for now, e.g. how to do stuff like col1 + col2. Such knowledge can however be used basically anywhere else however, e.g. in AS or WHERE clauses, not just in UPDATE.
- stackoverflow.com/questions/54898994/bulkupdate-in-sequelize-orm/69044138#69044138
- stackoverflow.com/questions/55646233/updating-with-calculated-values-in-sequelize
nodejs/sequelize/count.js: a simplified single-table count example. In practice, this will be usually done together with JOIN queries across multiple tables. Answers: stackoverflow.com/questions/22627258/how-does-group-by-works-in-sequelize/69896449#69896449
nodejs/sequelize/date.js: automatic date typecasts
nodejs/sequelize/like.js: LIKE
nodejs/sequelize/camel_case.js: trying to get everything in the database camel cased, columns starting with lowercase, and tables starting with uppercase. The defaults documented on getting started documentation do uppercase foreign keys, and lowercase non-foreign keys. It's a mess.
nodejs/sequelize/ignore_duplicates.js: ignore query on unique violation with ignoreDuplicates: true which does SQLite INSERT OR IGNORE INTO or PostgreSQL ON CONFLICT DO NOTHING. Closely related Upsert versions:
- Upsert
  - nodejs/sequelize/upsert.js: .upsert selects the conflict column automatically by unique columns. Both SQLite and PostgreSQL do INSERT INTO ON CONFLICT. PostgreSQL uses RETURNING, which was added too recently to SQLite: www.sqlite.org/lang_returning.html
    At nodejs/sequelize/composite_index.js we have a .upsert test with a composite index. Works just fine as you'd expect with a composite ON CONFLICT. Well done.
  - nodejs/sequelize/update_on_duplicate.js: .bulkCreate({}, { updateOnDuplicate: ['col1', col2'] }. Produces queries analogous to .upsert. This method is cool because it can upsert multiple columns at once. But it is annoying that you have to specify all fields to be updated one by one manually.
  - stackoverflow.com/questions/29063232/how-to-get-the-id-of-an-inserted-or-updated-record-in-sequelize-upsert/72092277#72092277
  - stackoverflow.com/questions/55531860/sequelize-bulkcreate-updateonduplicate-for-postgresql

nodejs/sequelize/inc.js: demonstrate the increment method. In SQLite, it produces a statement of type:

UPDATE `IntegerNames` SET `value`=`value`+ 1,`updatedAt`='2021-11-03 10:23:45.409 +00:00' WHERE `id` = 3

nodejs/sequelize/sync_alter.js: illustrates Model.sync({alter: true}) to modify a table definition, answers: stackoverflow.com/questions/54898994/bulkupdate-in-sequelize-orm/69044138#69044138
nodejs/sequelize/truncate_key.js
nodejs/sequelize/validation.js: is handled by a third-party library: github.com/validatorjs/validator.js. They then add a few extra validators on top of that.
The args: true thing is explained at: stackoverflow.com/questions/58522387/unhandled-rejection-sequelizevalidationerror-validation-error-cannot-create-pr/70263032#70263032
nodejs/sequelize/composite_index.js: stackoverflow.com/questions/34664853/sequelize-composite-unique-constraint
nodejs/sequelize/indent_log.js: stackoverflow.com/questions/34664853/sequelize-composite-unique-constraint
association examples:
- nodejs/sequelize/one_to_many.js: basic one-to-many examples.
  - nodejs/sequelize/one_to_many_custom_column_name.js:
    stackoverflow.com/questions/43025861/sequelize-association-is-referencing-to-wrong-foreignkey-column-name
    stackoverflow.com/questions/49818406/sequelize-targetkey-not-working/59953948#59953948
- nodejs/sequelize/many_to_many.js: basic many-to-many examples, each user can like multiple posts. Answers: stackoverflow.com/questions/22958683/how-to-implement-many-to-many-association-in-sequelize/67973948#67973948
  - ORDER BY include:
    github.com/sequelize/sequelize/issues/4553
    stackoverflow.com/questions/66984410/why-the-sequelize-include-order-does-not-work
  - nodejs/sequelize/many_to_many_custom_table.js: many-to-many example, but where we craft our own table which can hold extra data. In our case, users can like posts, but likes have a integer weight associated with them. Related threads:
    stackoverflow.com/questions/22958683/how-to-implement-many-to-many-association-in-sequelize/67973948#67973948
    stackoverflow.com/questions/30308217/sequelize-find-by-association-through-manually-defined-join-table/69899876#69899876
  - nodejs/sequelize/many_to_many_same_model.js: association between a model and itself: users can follow other users. Related:
    stackoverflow.com/questions/43152180/node-sequelize-followering-following-relationship
    stackoverflow.com/questions/27065154/how-to-get-all-children-or-parents-in-a-many-to-many-association-if-one-model-re/72951602#72951602
    github.com/sequelize/sequelize/issues/8263
  - nodejs/sequelize/many_to_many_same_model_super.js
    nodejs/sequelize/find_duplicates.js: stackoverflow.com/questions/71235548/how-to-find-all-rows-that-have-certain-columns-duplicated-in-sequelize/71235550#71235550
  - nodejs/sequelize/many_to_many_super.js: "Super many to many": sequelize.org/master/manual/advanced-many-to-many.html This should not exist and shows how bad this library is for associations, you need all that boilerplate in order to expose certain relationships that aren't otherwise exposed by a direct hasMany with implicit join table.
- nested includes to produce queries with multiple JOIN:
  - nodejs/sequelize/nested_include.js: find all posts by users that a given user follows. Answers: stackoverflow.com/questions/42632943/sequelize-multiple-where-clause/68018083#68018083
  - nodejs/sequelize/nested_include_super.js: like nodejs/sequelize/nested_include.js but with a super many to many. We should move this to nodejs/sequelize/many_to_many_super.js.
- two relationships between two specific tables: we need to use as: to disambiguate them
  - nodejs/sequelize/many_to_many_double.js: users can both follow and like posts
  - nodejs/sequelize/one_to_many_double.js: posts have the author and a mandatory reviewer
hooks
internals:
- nodejs/sequelize/common.js: common utilities used across examples, most notably:
  - to easily setup different DBRM
- nodejs/sequelize/min_nocommon.js: to copy paste to Stack Overflow
- nodejs/sequelize/min.js: template for new exapmles in the folder

 Read the full article

Link to an image or video in another file that has an \x on title from another file  Updated 2025-01-10  +Created 1970-01-01

 View more

= Programming language

\Image[https://raw.githubusercontent.com/cirosantilli/media/master/python-logo.jpg]
{title=The \x[python-programming-language] logo}

== Python
{c}
{disambiguate=programming-language}

Now, when rendering \x[image-the-python-logo], we would need to fetch two IDs:

But after group all SQL queries together was done, there was no way to know that rendering image-the-python-logo would imply also fetching python-programming-language.

This was solved by adding a new database entry type, REFS_TABLE_X_TITLE_TITLE to the existing References table, which tracks dependencies between IDs.

After this was setup, we can now know that image-the-python-logo depends on image-the-python-logo, and then fetch both of them together in a single JOIN.

 Read the full article

SQL example  Updated 2025-01-10  +Created 1970-01-01

 View more

We have some runnable SQL examples with assertion under the sequelize/raw directory.

These examples are written in the Sequelize library using raw queries.

Sequelize is used minimally, just to feed raw queries in transparently to any underlying database, and get minimally parsed results out for us, which we then assert with standard JavaScript. The queries themselves are all written by hand.

By default the examples run on SQLite. Just like the examples from sequelize example, you can set the database at runtime as:

./index.js or ./index.js l: SQLite
./index.js p: PostgreSQL. You must manually create a database called tmp and ensure that peer authentication works for it

Here we list only examples which we believe are standard SQL, and should therefore work across different SQL implementations:

nodejs/sequelize/raw/index.js: basic hello world to demonstrate the setup and very simple functionality
nodejs/sequelize/raw/many_to_many.js: illustrates many-to-many relations with JOIN. Contains:
SQL transaction examples:
- nodejs/sequelize/raw/commit_error.js: stackoverflow.com/questions/27245101/why-should-we-use-rollback-in-sql-explicitly/27245234#27245234 and stackoverflow.com/questions/48277519/how-to-use-commit-and-rollback-in-a-postgresql-function/48277708#48277708 suggest that on PostgreSQL, once something fails inside a transaction, all queries in the current transaction are ignored, and COMMIT simply does a ROLLBACK. Let's check. Yup, true for Postgres, but false for SQLite, SQLite just happily runs anything it can, you really need ROLLBACK for it.
- SQL isolation level example
GROUP BY and SQL aggregate functions:
- nodejs/sequelize/raw/group_by_extra_column.js: let's see if it blows up or not on different DB systems, sqlite3 Node.js package allows it:
  - github.com/sequelize/sequelize/issues/5481#issuecomment-964387232
  - dba.stackexchange.com/questions/141594/how-select-column-does-not-list-in-group-by-clause/141600 says that it was allowed in SQL:1999 when there are no ambiguities due to constraints, e.g. when grouping by unique columns
  - github.com/postgres/postgres/blob/REL_13_5/src/test/regress/sql/functional_deps.sql#L27 shows that PostgreSQL wants it to work for UNIQUE NOT NULL, but they just haven't implemented it as of 13.5, where it only works if you group by PRIMARY KEY
  - dba.stackexchange.com/questions/158015/why-can-i-select-all-fields-when-grouping-by-primary-key-but-not-when-grouping-b also says that UNIQUE NOT NULL doesn't work. Dan Lenski then points to rationale mailing list thread:
- nodejs/sequelize/raw/group_by_max_full_row.js: here we try to get the full row of each group at which a given column reaches the max of the group
  - Postgres: has SELECT DISCINTCT ON which works perfectly if you only want one row in case of multiple rows attaining the max. ON is an extension to the standard unfortunately: www.postgresql.org/docs/9.3/sql-select.html#SQL-DISTINCT Docs specify that it always respects ORDER BY when selecting the row.
    stackoverflow.com/questions/586781/postgresql-fetch-the-row-which-has-the-max-value-for-a-column asks it without the multiple matches use case
    stackoverflow.com/questions/586781/postgresql-fetch-the-rows-which-have-the-max-value-for-a-column-in-each-group/587209#587209 also present in simpler form at stackoverflow.com/questions/121387/fetch-the-rows-which-have-the-max-value-for-a-column-for-each-distinct-value-of/123481#123481 gives a very nice OUTER JOIN only solution! Incredible, very elegant.
    dba.stackexchange.com/questions/171938/get-only-rows-with-max-group-value asks specifically the case of multiple matches to the max
  - SQLite:
    stackoverflow.com/questions/48326957/row-with-max-value-per-group-sqlite
    stackoverflow.com/questions/48326957/row-with-max-value-per-group-sqlite/48328243#48328243 teaches us that in SQLite min and max are magic and guarantee that the matching row is returned
    stackoverflow.com/questions/48326957/row-with-max-value-per-group-sqlite/72996649#72996649 Ciro Santilli uses the magic of ROW_NUMBER
    stackoverflow.com/questions/17277152/sqlite-select-distinct-of-one-column-and-get-the-others/71924314#71924314 get any full row without specifying which, we teach how to specify
    code.djangoproject.com/ticket/22696 WONTFIXed DISTINCT ON
    stackoverflow.com/questions/50846722/what-is-the-difference-between-postgres-distinct-vs-distinct-on/72997494#72997494 DISTINCT vs DISTINCT ON, somewhat related question
  - stackoverflow.com/questions/5803032/group-by-to-return-entire-row asks how to take the top N with distinct after order limit. I don't know how to do it in Postgres
- nodejs/sequelize/raw/most_frequent.js: illustrates a few variants of findind the mode, including across GROUP
  - stackoverflow.com/questions/12235595/find-most-frequent-value-in-sql-column/72979899#72979899
- nodejs/sequelize/raw/group_by_max_n.js: get the top N in each group
order results in the same order as IN:
- MysQL: stackoverflow.com/questions/396748/ordering-by-the-order-of-values-in-a-sql-in-clause
- PostgreSQL:
  - stackoverflow.com/questions/866465/order-by-the-in-value-list
  - dba.stackexchange.com/questions/59394/order-of-returned-rows-with-in-statement
LIMIT by a running total: TODO links

 Read the full article