Incoming links: Upsert
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:
Update multiple rows with different values in a single SQL query Updated 2024-12-15 +Created 1970-01-01
This happens when you calculate a bunch of values on your program, and then want to save the to SQL.