= Sequelize {c} Source code: https://github.com/sequelize/sequelize Some usage examples under: {full}. 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: * https://github.com/sequelize/sequelize/issues/5385#issuecomment-324479607 * https://github.com/sequelize/sequelize/issues/1775#issuecomment-360028396 * 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 to learn to do what he considers "basic" association operations, knowledge which he dumped to: https://stackoverflow.com/questions/22958683/how-to-implement-many-to-many-association-in-sequelize/67973948#67973948 See also: . * 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 {parent=Sequelize} * foreign keys are capitalized: * https://stackoverflow.com/questions/55273091/why-use-the-uppercase-key-when-creating-association-model-in-sequelize * https://github.com/sequelize/sequelize/issues/5828 * you must give `foreignKey` when using aliases, otherwise it fails subtely. That would be derived automatically. * https://stackoverflow.com/questions/41502699/return-flat-object-from-sequelize-with-association can't auto-flatten to reuse the database's `ORDER` * `limit` and `offset` don't work without `subQuery: false` when doing includes! It is just too buggy. Examples of this can be found e.g. under \a[nodejs/sequelize/many_to_many_same_model.js]. * https://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: * https://github.com/sequelize/sequelize/issues/8199#issuecomment-863943835 * https://github.com/sequelize/sequelize/issues/12044 * https://stackoverflow.com/questions/28231970/bluebird-shows-broken-stacktrace-when-using-with-sequelize-mysql * does not automatically update fields on hooks: https://github.com/sequelize/sequelize/issues/8586#issuecomment-422877555 * cannot change columns when other columns have constraints due to the backup table? * https://stackoverflow.com/questions/64533732/process-of-changing-a-table-with-sequelize-migration-if-foreign-key-constraint-i * you have to use `.get()` for `attribute` aliased fields, why? https://stackoverflow.com/questions/32649218/how-do-i-select-a-column-using-an-alias/69890944#69890944 * `.id` gets added to `SELECT` no matter what, breaking `GROUP BY` unless you do horrible workarounds: * https://github.com/sequelize/sequelize/issues/3256 * https://github.com/sequelize/sequelize/issues/5481 * no simple built-in mechanism for 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 \a[nodejs/sequelize/raw/many_to_many.js]: * https://sequelize.org/master/manual/sub-queries.html[]: the docs actually just tell you to use literals, lol * https://stackoverflow.com/questions/45354001/nodejs-sequelize-delete-with-nested-select-query Also, you can't get query strings either: https://github.com/sequelize/sequelize/issues/2325 * 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? * https://github.com/sequelize/cli/issues/862 * https://stackoverflow.com/questions/18742962/add-data-in-sequelize-migration-script * https://stackoverflow.com/questions/38671483/sequelize-migration-update-model-after-updating-column-attributes * https://stackoverflow.com/questions/38998397/can-i-use-sequelize-models-in-migration-scripts * https://stackoverflow.com/questions/45286429/custom-query-on-sequelize-seeder `queryInterface.sequelize.models` contains only `SequelizeMeta`. 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. * https://stackoverflow.com/questions/56043246/node-js-sequelize-no-primary-keys-when-migrating/56046101#56046101 * `changeColumn` migrations 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: https://stackoverflow.com/questions/62667269/sequelize-js-how-do-we-change-column-type-in-migration/70486686#70486686 * associations require full explicit index contruction: https://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: * stack overflow * https://stackoverflow.com/questions/28787889/how-can-i-set-up-sequelize-js-to-stream-data-instead-of-a-promise-callback * https://stackoverflow.com/questions/43964067/how-to-implement-cursor-pagination-using-sequelize * https://stackoverflow.com/questions/57164242/perform-sequelize-findall-in-a-huge-array * https://stackoverflow.com/questions/55191891/how-to-loop-through-result-in-sequelize generic loop * issue tracker * https://github.com/sequelize/sequelize/issues/15827 * https://github.com/sequelize/sequelize/issues/10347 * https://github.com/sequelize/sequelize/issues/4286 * https://github.com/sequelize/sequelize/issues/2454 E.g. the interface supports this just fine: https://stackoverflow.com/questions/29582736/python3-is-there-a-way-to-iterate-row-by-row-over-a-very-large-sqlite-table-wi = Sequelize example {parent=Sequelize} To run examples on a specific database: * `./index.js` or `./index.js l`: * `./index.js p`: . 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: * \a[nodejs/sequelize/index.js]: a bunch of basic examples * \a[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`. * https://stackoverflow.com/questions/54898994/bulkupdate-in-sequelize-orm/69044138#69044138[] * https://stackoverflow.com/questions/55646233/updating-with-calculated-values-in-sequelize * \a[nodejs/sequelize/count.js]: a simplified single-table count example. In practice, this will be usually done together with queries across multiple tables. Answers: https://stackoverflow.com/questions/22627258/how-does-group-by-works-in-sequelize/69896449#69896449 * \a[nodejs/sequelize/date.js]: automatic date typecasts * \a[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. * \a[nodejs/sequelize/ignore_duplicates.js]: ignore query on unique violation with `ignoreDuplicates: true` which does `INSERT OR IGNORE INTO` or `ON CONFLICT DO NOTHING`. Closely related versions: * * \a[nodejs/sequelize/upsert.js]: `.upsert` selects the conflict column automatically by unique columns. Both and do `INSERT INTO ON CONFLICT`. PostgreSQL uses `RETURNING`, which was added too recently to SQLite: https://www.sqlite.org/lang_returning.html At \a[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. * \a[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. * https://stackoverflow.com/questions/29063232/how-to-get-the-id-of-an-inserted-or-updated-record-in-sequelize-upsert/72092277#72092277 * https://stackoverflow.com/questions/55531860/sequelize-bulkcreate-updateonduplicate-for-postgresql * \a[nodejs/sequelize/inc.js]: demonstrate the `increment` method. In , 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 `` * \a[nodejs/sequelize/sync_alter.js]: illustrates `Model.sync({alter: true})` to modify a table definition, answers: https://stackoverflow.com/questions/54898994/bulkupdate-in-sequelize-orm/69044138#69044138 * \a[nodejs/sequelize/truncate_key.js] * \a[nodejs/sequelize/validation.js]: is handled by a third-party library: https://github.com/validatorjs/validator.js[]. They then add a few extra validators on top of that. The `args: true` thing is explained at: https://stackoverflow.com/questions/58522387/unhandled-rejection-sequelizevalidationerror-validation-error-cannot-create-pr/70263032#70263032 * \a[nodejs/sequelize/composite_index.js]: https://stackoverflow.com/questions/34664853/sequelize-composite-unique-constraint * \a[nodejs/sequelize/indent_log.js]: https://stackoverflow.com/questions/34664853/sequelize-composite-unique-constraint * association examples: * \a[nodejs/sequelize/one_to_many.js]: basic examples. * \a[nodejs/sequelize/one_to_many_custom_column_name.js]: * https://stackoverflow.com/questions/43025861/sequelize-association-is-referencing-to-wrong-foreignkey-column-name * https://stackoverflow.com/questions/49818406/sequelize-targetkey-not-working/59953948#59953948 * \a[nodejs/sequelize/many_to_many.js]: basic examples, each user can like multiple posts. Answers: https://stackoverflow.com/questions/22958683/how-to-implement-many-to-many-association-in-sequelize/67973948#67973948 * ORDER BY include: * https://github.com/sequelize/sequelize/issues/4553 * https://stackoverflow.com/questions/66984410/why-the-sequelize-include-order-does-not-work * \a[nodejs/sequelize/many_to_many_custom_table.js]: 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: * https://stackoverflow.com/questions/22958683/how-to-implement-many-to-many-association-in-sequelize/67973948#67973948 * https://stackoverflow.com/questions/30308217/sequelize-find-by-association-through-manually-defined-join-table/69899876#69899876 * \a[nodejs/sequelize/many_to_many_same_model.js]: association between a model and itself: users can follow other users. Related: * https://stackoverflow.com/questions/43152180/node-sequelize-followering-following-relationship * https://stackoverflow.com/questions/27065154/how-to-get-all-children-or-parents-in-a-many-to-many-association-if-one-model-re/72951602#72951602 * https://github.com/sequelize/sequelize/issues/8263 * \a[nodejs/sequelize/many_to_many_same_model_super.js] * \a[nodejs/sequelize/find_duplicates.js]: https://stackoverflow.com/questions/71235548/how-to-find-all-rows-that-have-certain-columns-duplicated-in-sequelize/71235550#71235550 * \a[nodejs/sequelize/many_to_many_super.js]: "Super many to many": https://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 : * \a[nodejs/sequelize/nested_include.js]: find all posts by users that a given user follows. Answers: https://stackoverflow.com/questions/42632943/sequelize-multiple-where-clause/68018083#68018083 * \a[nodejs/sequelize/nested_include_super.js]: like \a[nodejs/sequelize/nested_include.js] but with a super many to many. We should move this to \a[nodejs/sequelize/many_to_many_super.js]. * two relationships between two specific tables: we need to use `as:` to disambiguate them * \a[nodejs/sequelize/many_to_many_double.js]: users can both follow and like posts * \a[nodejs/sequelize/one_to_many_double.js]: posts have the author and a mandatory reviewer * hooks * \a[nodejs/sequelize/before_validate_modify.js]: * https://github.com/sequelize/sequelize/issues/3534 * https://github.com/sequelize/sequelize/issues/8586 * \a[nodejs/sequelize/hook_in_transaction.js]: * https://stackoverflow.com/questions/35014679/sequelize-hook-depending-on-transaction * \a[nodejs/sequelize/hook_abort.js]: aborting updates from hooks: https://stackoverflow.com/questions/64362298/how-to-abort-an-update-operation-with-beforeupdate-hook-sequelize/71308632#71308632 * internals: * \a[nodejs/sequelize/common.js]: common utilities used across examples, most notably: * to easily setup different DBRM * \a[nodejs/sequelize/min_nocommon.js]: to copy paste to * \a[nodejs/sequelize/min.js]: template for new exapmles in the folder = Sequelize raw query {parent=Sequelize example} Exampes under \a[nodejs/sequelize/raw]: * \a[nodejs/sequelize/raw/index.js]: . Ideally one should never use a raw query in a real project. We use raw examples mostly as a tutorial under , and will not comment on them much further on this section. = UPDATE with JOIN in Sequelize {c} {tag=UPDATE with JOIN (SQL)} {parent=Sequelize example} No support: * https://stackoverflow.com/questions/51275802/sequelize-bulk-update-with-inner-join * https://github.com/sequelize/sequelize/issues/3957 Example with raw examples under \a[nodejs/sequelize/raw/many_to_many.js] = Sequelize parallel example {c} {parent=Sequelize example} = nodejs/sequelize/parallel_select_and_update.js {file} {parent=Sequelize parallel example} This example is the same as {file}, but going through rather than with . `NONE` is not supported for now to not have a transaction at all because lazy. The examples illustrates: https://stackoverflow.com/questions/55452441/for-share-and-for-update-statements-in-sequelize Sample invocation: `` node --unhandled-rejections=strict ./parallel_select_and_update.js p 10 100 READ_COMMITTED UPDATE `` where: * `READ_COMMITTED`: one of the keys documented at: https://sequelize.org/master/class/lib/transaction.js~Transaction.html which correspond to the standard . It not given, don't set one, defaulting to the database's/sequelize's default level. * `UPDATE`: one of the keys documented at: https://sequelize.org/master/class/lib/transaction.js~Transaction.html#static-get-LOCK[]. Update generates a `SELECT FOR UPDATE` in for example. If not given, don't use any `FOR xxx` explicit 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: {file}: * `READ_COMMITTED`: fails * `READ_COMMITTED UPDATE`: works * `REPEATABLE_READ`: works, but is a bit slower, as it does rollbacks This case also illustrates , since in this transaction isolation level transactions may fail: * https://stackoverflow.com/questions/68427796/sequelize-transaction-retry-doenst-work-as-expected * https://github.com/sequelize/sequelize/issues/1478 * https://github.com/sequelize/sequelize/issues/8294 = Sequelize transaction retry {c} {parent=Sequelize} Transaction retries are inevitable, as some Doesn't seem to have any simple built-in mechanism? * https://stackoverflow.com/questions/68427796/sequelize-transaction-retry-doenst-work-as-expected * https://github.com/sequelize/sequelize/issues/1478 * https://github.com/sequelize/sequelize/issues/8294 = SQL TRIGGER in Sequelize {c} {parent=Sequelize} Example: \a[nodejs/sequelize/trigger_count.js] There is of course no built-in support for in , but we can add our own: https://stackoverflow.com/questions/29716346/how-to-create-a-trigger-in-sequelize-nodejs/76215728#76215728