Ciro Santilli
OurBigBook.comSource code: github.com/sequelize/sequelize
Some usage examples under: Section "Sequelize example".
As of 2021, this library is extremely painful to use. It does feel semi-mature, but there are just too much horrible things going on;
- the documentation is a bit messy and misses a lot of stuff. The examples are often too short, and it is hard to understand what specific options they are talking about do because they lack clear input/expected output pairs. Examples:
- the implementation has several inelegant/unintuitive annoyances/requirements of code repetition that drive you mad.The association API feels notably bad, it took a few days for Ciro Santilli to learn to do what he considers "basic" association operations, knowledge which he dumped to: stackoverflow.com/questions/22958683/how-to-implement-many-to-many-association-in-sequelize/67973948#67973948See also: how to decide if an ORM is good?.
- bugs are piling up. It appears that many key devs left, and current maintainers are just not being able to keep up.And they have setup a stupid bot that closes every thread automatically after a few days, what's the point... valid bugs are being closed due to this, and it is impossible to distinguish what is solved and what isn't since everything gets closed.
Some glaring issues are listed at the horrors of Sequelize.
- foreign keys are capitalized:
- you must give
foreignKeywhen using aliases, otherwise it fails subtely. That would be derived automatically. - stackoverflow.com/questions/41502699/return-flat-object-from-sequelize-with-association can't auto-flatten to reuse the database's
ORDER limitandoffsetdon't work withoutsubQuery: falsewhen doing includes! It is just too buggy. Examples of this can be found e.g. under nodejs/sequelize/many_to_many_same_model.js.- stackoverflow.com/questions/34059081/how-do-i-reference-an-association-when-creating-a-row-in-sequelize-without-assum hard to not duplicate foreign keys values everywhere
- stack traces permanently broken or requiring non-obvious configs:
- does not automatically update fields on hooks: github.com/sequelize/sequelize/issues/8586#issuecomment-422877555
- cannot change columns when other columns have constraints due to the backup table?
- you have to use
.get()forattributealiased fields, why? stackoverflow.com/questions/32649218/how-do-i-select-a-column-using-an-alias/69890944#69890944 .idgets added toSELECTno matter what, breakingGROUP BYunless you do horrible workarounds:- no simple built-in mechanism for transaction retries: Sequelize transaction retries
- impossible to do subqueries in general. Docs just tell you to use literals. This in particular prevents single query deletes with join as done at nodejs/sequelize/raw/many_to_many.js:Also, you can't get query strings either: github.com/sequelize/sequelize/issues/2325
- sequelize.org/master/manual/sub-queries.html: the docs actually just tell you to use literals, lol
- stackoverflow.com/questions/45354001/nodejs-sequelize-delete-with-nested-select-query
- migrations. Generally speaking, anything but the simplest migrations are exceedingly hard to get right, as you have to go very low level when doing migrations. Syntax can be very different from regular DB operations.
- no way to do (non-raw) queries during migrations, e.g. to update fields based on other fields in a complex way?
- github.com/sequelize/cli/issues/862
- stackoverflow.com/questions/18742962/add-data-in-sequelize-migration-script
- stackoverflow.com/questions/38671483/sequelize-migration-update-model-after-updating-column-attributes
- stackoverflow.com/questions/38998397/can-i-use-sequelize-models-in-migration-scripts
- stackoverflow.com/questions/45286429/custom-query-on-sequelize-seeder
queryInterface.sequelize.modelscontains onlySequelizeMeta. Not sure why they have this limitation.Edit: actually things will likely just work if immediately after making table changes you just instantiate a new sequelize and do any data changes. - stackoverflow.com/questions/56043246/node-js-sequelize-no-primary-keys-when-migrating/56046101#56046101
- SQLite
changeColumnmigrations do on delete cascades of other tables. SQLite does not have change column statements, so they have to drop and recreate tables, but they don't temporarily remove cascades, so you lose data: stackoverflow.com/questions/62667269/sequelize-js-how-do-we-change-column-type-in-migration/70486686#70486686 - associations require full explicit index construction: stackoverflow.com/questions/39651853/how-to-create-join-table-with-foreign-keys-with-sequelize-or-sequelize-cli
- ability to iterate over a large result without blowing up memory and without using limit + offset (which is inneficient e.g. when looping over recursive queries). This is also known as cursor or streaming interfaces:E.g. the Python SQLite interface supports this just fine: stackoverflow.com/questions/29582736/python3-is-there-a-way-to-iterate-row-by-row-over-a-very-large-sqlite-table-wi
- stack overflow
- stackoverflow.com/questions/28787889/how-can-i-set-up-sequelize-js-to-stream-data-instead-of-a-promise-callback
- stackoverflow.com/questions/43964067/how-to-implement-cursor-pagination-using-sequelize
- stackoverflow.com/questions/57164242/perform-sequelize-findall-in-a-huge-array
- stackoverflow.com/questions/55191891/how-to-loop-through-result-in-sequelize generic loop
- issue tracker
- stack overflow
- empty
attributes: []breaks some nested queries: github.com/sequelize/sequelize/issues/16436 - does not expose a iteration API that supports large arrays?E.g. Python SQLite does: stackoverflow.com/questions/29582736/python3-is-there-a-way-to-iterate-row-by-row-over-a-very-large-sqlite-table-wi
To run examples on a specific database:All examples can be tested on all databases with:
./index.jsor./index.js l: SQLite./index.js p: PostgreSQL. You must manually create a database calledtmpand ensure that peer authentication works for it
cd sequelize
./testOverview 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. inASorWHEREclauses, not just inUPDATE. - 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: truewhich does SQLiteINSERT OR IGNORE INTOor PostgreSQLON CONFLICT DO NOTHING. Closely related Upsert versions:- Upsert
- nodejs/sequelize/upsert.js:
.upsertselects the conflict column automatically by unique columns. Both SQLite and PostgreSQL doINSERT INTO ON CONFLICT. PostgreSQL usesRETURNING, which was added too recently to SQLite: www.sqlite.org/lang_returning.htmlAt nodejs/sequelize/composite_index.js we have a.upserttest with a composite index. Works just fine as you'd expect with a compositeON 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
- Upsert
- nodejs/sequelize/inc.js: demonstrate the
incrementmethod. 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: truething 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/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:
- 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:
- nodejs/sequelize/many_to_many_same_model.js: association between a model and itself: users can follow other users. Related:
- nodejs/sequelize/many_to_many_same_model_super.js
- 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
hasManywith 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
- nodejs/sequelize/common.js: common utilities used across examples, most notably:
Exampes under nodejs/sequelize/raw:
- nodejs/sequelize/raw/index.js: Sequelize raw query hello world. Ideally one should never use a raw query in a real project. We use raw examples mostly as a SQL tutorial under SQL example, and will not comment on them much further on this section.
No support:
Example with raw examples under nodejs/sequelize/raw/many_to_many.js
This example is the same as nodejs/sequelize/raw/parallel_select_and_update.js, but going through Sequelize rather than with Sequelize raw queries.
NONE is not supported for now to not have a transaction at all because lazy.The examples illustrates: stackoverflow.com/questions/55452441/for-share-and-for-update-statements-in-sequelize
Sample invocation:where:
node --unhandled-rejections=strict ./parallel_select_and_update.js p 10 100 READ_COMMITTED UPDATEREAD_COMMITTED: one of the keys documented at: sequelize.org/master/class/lib/transaction.js~Transaction.html which correspond to the standard sQL isolation levels. It not given, don't set one, defaulting to the database's/sequelize's default level.UPDATE: one of the keys documented at: sequelize.org/master/class/lib/transaction.js~Transaction.html#static-get-LOCK. Update generates aSELECT FOR UPDATEin PostgreSQL for example. If not given, don't use anyFOR xxxexplicit locking.
Other examples:
node --unhandled-rejections=strict ./parallel_select_and_update.js p 10 100 READ_COMMITTED UPDATE
Then, the outcome is exactly as described at: nodejs/sequelize/raw/parallel_select_and_update.js:
READ_COMMITTED: failsREAD_COMMITTED UPDATE: worksREPEATABLE_READ: works, but is a bit slower, as it does rollbacksThis case also illustrates Sequelize transaction retries, since in this transaction isolation level transactions may fail:
nodejs/sequelize/parallel_select_and_update.js was not rendered because it is too large (> 2000 bytes)
Transaction retries are inevitable, as some sQL isolation levels
Doesn't seem to have any simple built-in mechanism?
Example: nodejs/sequelize/trigger_count.js
There is of course no built-in support for SQL TRIGGERs in Sequelize, but we can add our own: stackoverflow.com/questions/29716346/how-to-create-a-trigger-in-sequelize-nodejs/76215728#76215728
Ancestors
Incoming links
- nodejs/sequelize/parallel_select_and_update.js
better-sqlite3Node.js package- DELETE with JOIN
- feathers-chat PostgreSQL
- FeathersJS
- How to decide if an ORM is good?
- OurBigBook.com
- Sigoden/node-express-realworld-example-app
- Further improvements to the website's base technology
- SQL example
- SQL TRIGGER
- SQL TRIGGER in Sequelize
- UPDATE with JOIN
- webpack