Initial documentation on .onConflict, .ignore, and .merge #260
Conversation
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
sections/builder.js
Outdated
| type: "method", | ||
| method: "onConflict", | ||
| example: "insert(..).onConflict(column) / insert(..).onConflict([column1, column2, ...])", | ||
| description: "Utlized by PostgreSQL and Sqlite databases. When chained onto an insert query, it specifies the columns to be used in `ON CONFLICT` clause. A call to .onConflict should always be followed by either .ignore or .update (otherwise it does nothing). In MySQL this method is noop, and .ignore and .merge can be used without .onConflict.", |
kibertoad
reviewed
Oct 28, 2020
sections/builder.js
Outdated
| type: "method", | ||
| method: "ignore", | ||
| example: "insert(..).onConflict(..).ignore()", | ||
| description: "Implemented for the PostgreSQL, MySQL, and Sqlite databases. Modifies an insert query, and causes it silently dropped without error if a conflict occurs. Uses INSERT IGNORE in MySQL, and adds an ON CONFLICT (columns) DO NOTHING clause to the insert statement in PostgreSQL and Sqlite.", |
Collaborator
There was a problem hiding this comment.
and causes it to be silently dropped without an error?
kibertoad
reviewed
Oct 28, 2020
sections/builder.js
Outdated
| }, | ||
| { | ||
| type: "text", | ||
| content: "It is also possible to specify data to update seperately from the data to insert. This is useful if you only want to update a subset of the columns. For example, you may want to set a 'created_at' column when inserting, but not wish to update it if the row already exists:" |
Collaborator
There was a problem hiding this comment.
but would prefer not to update it
1ecd77c to
4b03b42
Compare
4b03b42 to
58cd2c2
Compare
Contributor
Author
|
Review comments fixed. |
elhigu
reviewed
Oct 28, 2020
| type: "method", | ||
| method: "merge", | ||
| example: "insert(..).onConflict(..).merge() / insert(..).onConflict(..).merge(updates)", | ||
| description: "Implemented for the PostgreSQL, MySQL, and Sqlite databases. Modifies an insert query, to turn it into an 'upsert' operation. Uses ON DUPLICATE KEY UPDATE in MySQL, and adds an ON CONFLICT (columns) DO UPDATE clause to the insert statement in PostgreSQL and Sqlite.", |
Member
There was a problem hiding this comment.
Even that the documentation says how the feature is implemented on each DB, this should also specify explicitly how to write crossdb compatible .merge. For many people from above it still will not be clear that mysql ignores the column list parameter and that actually to have cross db compatible merge, all the columns in the table must be listed.
Collaborator
There was a problem hiding this comment.
@nicoburns Could you please address this comment?
Contributor
Author
There was a problem hiding this comment.
@kibertoad Updated with a note on cross db compatibility.
kibertoad
approved these changes
Oct 31, 2020
kibertoad
approved these changes
Oct 31, 2020
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
4 participants
Add this suggestion to a batch that can be applied as a single commit.
This suggestion is invalid because no changes were made to the code.
Suggestions cannot be applied while the pull request is closed.
Suggestions cannot be applied while viewing a subset of changes.
Only one suggestion per line can be applied in a batch.
Add this suggestion to a batch that can be applied as a single commit.
Applying suggestions on deleted lines is not supported.
You must change the existing code in this line in order to create a valid suggestion.
Outdated suggestions cannot be applied.
This suggestion has been applied or marked resolved.
Suggestions cannot be applied from pending reviews.
Suggestions cannot be applied on multi-line comments.
Suggestions cannot be applied while the pull request is queued to merge.
Suggestion cannot be applied right now. Please check back later.
Documentation for UPSERT pull request: knex/knex#3763