8 ms·

SQLite Query Language: upsert

248 points by coleifer 8y ago

Normal_gaussian 8y ago
The diagram used on this page [0], and the ones on the JSON site [1] are pretty awesome diagrams. What are they called? Is there a good programmatic tool to generate them? [0] - https://www.sqlite.org/draft/images/syntax/upsert-clause.gif https://www.sqlite.org/draft/images/syntax/upsert-clause.gif [1] - https://www.json.org/ https://www.json.org/
- wyldfire 8y ago
  Often called "railroad diagram" [1] [2] [1] https://en.wikipedia.org/wiki/Syntax_diagram https://en.wikipedia.org/wiki/Syntax_diagram [2] https://stackoverflow.com/a/7143467/489590 https://stackoverflow.com/a/7143467/489590
  - easytiger 8y ago
    I thought these are simple EBNF diagrams. As taught in British schools to 16 year olds
- cpburns2009 8y ago
  From the FAQ [1], > (25) How are the syntax diagrams (a.k.a. "railroad" diagrams) for SQLite generated? > The process is explained at http://wiki.tcl-lang.org/21708 http://wiki.tcl-lang.org/21708. I looks like a TCL script is used [2]. [1]: https://www.sqlite.org/faq.html#q25 https://www.sqlite.org/faq.html#q25 [2]: https://www.sqlite.org/docsrc/doc/tip/art/syntax/bubble-generator.tcl https://www.sqlite.org/docsrc/doc/tip/art/syntax/bubble-gene...
- deleted 8y ago
  [deleted]
- politips 8y ago
  I don't know if they used it but mermaid is pretty great: https://mermaidjs.github.io/ https://mermaidjs.github.io/
jedberg 8y ago
This is great! Given that SQLite is generally used for small projects and not in any sort of cluster or HA environment, pushing this kind of work (the if/then/else block that you don't have to write in your code anymore) makes a ton of sense. I actually like this feature more in SQLite than in Postgres. In Postgres I worry about pushing that kind of work into the database in clustered situations, as there are places where it can go wrong and it would be more likely that the code wouldn't be able to handle that failure properly (as opposed to having to write the if/then/else yourself, which if you're a good coder would also involve some try/catch and proper fallback behavior in case of an error).
- anarazel 8y ago
  > which if you're a good coder would also involve some try/catch and proper fallback behavior in case of an error It's actually really hard to do so correctly in the face of concurrency. Do not attempt to do so unless you've a really good reason.
  - jedberg 8y ago
    I would say in this day of high scalability and microservices, a good engineer should be skilled at dealing with these types of errors, as they would be one of the most common types. Edit: I find it interesting that this comment has fluctuated between -2 and +2 points. Apparently saying that engineers should be able to handle concurrency errors is controversial?
    - anarazel 8y ago
      I bet that at least 9/10 experienced engineers in the space will come up with a solution that has at least one of a) incorrect behaviour under concurrency b) unnecessary deadlocks c) extreme slowness d) break w/ triggers, row level security etc.
    - anarazel 8y ago
      > Apparently saying that engineers should be able to handle concurrency errors is controversial? I didn't vote either way. But perhaps it's less the fact that they should be able to handle concurrency, and more that replicating the necessary non-trivial logic in several applications isn't a good plan.
Grollicus 8y ago
I hope they build this in a way that returns the correct changed row count. Currently upserts in sqlite are a bit complicated because there is no feedback
- Apocryphon 8y ago
  Agreed that it should return the updated count automatically, but it's possible to get that amount by pairing it with a query for the rowid of that new record: https://www.sqlite.org/c3ref/last_insert_rowid.html https://www.sqlite.org/c3ref/last_insert_rowid.html And if it's a table with autoincrement primary key, try: https://stackoverflow.com/a/2217015/223327 https://stackoverflow.com/a/2217015/223327
- coleifer 8y ago
  From a quick test SQLite seems to be doing the right thing. I tested inserting single rows, multiple rows, and restricting the conflict-resolution with a WHERE clause -- the "row count" always came out correct.
binarymax 8y ago
I'm torn on whether always forcing an explicit SET for ON CONFLICT UPDATE is good practice. It would make for much cleaner code to just update every column with the supplied insert values (as an option). Or maybe I'm just lazy (heck that's why I like UPSERTs in the first place!)
- konsumer 8y ago
  INSERT OR REPLACE does this. http://www.sqlitetutorial.net/sqlite-replace-statement/ http://www.sqlitetutorial.net/sqlite-replace-statement/
coleifer 8y ago
Just a note: this feature is scheduled to be included in the next official release of SQLite (3.24.0). Additionally, for some time SQLite has supported INSERT OR REPLACE which offers a simpler, all-or-nothing upsert query.
cyberferret 8y ago
It's been a while since I've used SQLite (over a decade ago), but didn't they already have this sort of functionality in there with an 'INSERT OR UPDATE' modification to the standard SQL INSERT command? In fact, it always made me wonder why other systems (at the time) didn't support this critical and useful feature? I was getting tired of writing a SELECT, comparing the returned row against the one I was about to update, then branching to an UPDATE or INSERT command depending. Doing it all in one command was a breeze.
- anorwell 8y ago
  If I understand, the difference is that this allows insertion of only some columns, including the primary key. If there is an existing row, the union of all columns will be the result. On the other hand, `INSERT OR REPLACE` completely replaces the existing row, if one exists.
  - cyberferret 8y ago
    Thanks for the quick and informative explanation. I'll admit that I didn't have time to read the entire article right now, but I will go back and look at it in more detail.
igtztorrero 8y ago
MySql have: INSERT INTO t1 (a,b,c) VALUES (1,2,3)ON DUPLICATE KEY UPDATE b=2,c=3;
deleted 8y ago
[deleted]
jcrites 8y ago
For those who may be interested: I was curious about the difference between MERGE [1] and UPSERT, and why database engines seem to support a custom (nonstandard) UPSERT instead of the MERGE introduced in standard SQL in 2003, so I did some web research and came across a reasonable-sounding answer in a comment on the following article. https://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/ https://www.depesz.com/2012/06/10/why-is-upsert-so-complicat... > As noted, MERGE is complicated and slow. If two different database servers implement it, then it is going to be implemented with different schematics [sic]. Oracle and MySQL already have different implementation’s, and to nobody’s surprise, MySQL’s implementation allows for risky behavior that does not protect the data. PostgreSQL users expect that any data put into the database will reliably come out of the database. So MERGE is slow and inconsistently implemented. The correct thing to do is make the application aware of if a record is new or existing. Any application making use of MERGE should open a bug to replace it with more predictable and faster logic using INSERT and UPDATE. > If an application does use MERGE, it has to account for implementation specific behavior and not will not be portable, in a safe way, to other database servers. So it is not suitable for ORM’s or database independent applications. > So what is a legitimate use case for MERGE where not knowing if a record is new or existing is not possible prior to the transaction? They only thing that I can think of is one-off scripts that do not have any concurrency. But if you design for that, then some misguided ORM for web applications is going to use your MERGE function and users are going to wonder about what happened to their data when concurrency concerns were ignored or not understood. (I believe this comment is by Michael McLaughlin, the author of books on Oracle PL/SQL programming.) Does anyone happen to know if this is still accurate today, or whether there's been any attempt to incorporate UPSERT support into standard SQL? I could imagine the SQL standard maintainers not wanting to offer UPSERT if MERGE is already a superset of its functionality; but a case could be made that, since MERGE's complexity has apparently discouraged adoption by implementations and users, it may be sensible to support UPSERT nonetheless. Is this a reasonable way to think about it? I don't mean to imply that standardizing UPSERT into SQL is an especially important concern for anyone; this is merely a trail of thought that originated from wondering about why the UPSERT statement is nonstandard. [1] https://en.wikipedia.org/wiki/Merge_(SQL) https://en.wikipedia.org/wiki/Merge_(SQL)
- viraptor 8y ago
bmpafa 8y ago
I'd love it the SQLite page wrapped content in a div of a reasonable max-width. Text running edge-to-edge is hard to read. Anyone know if the site is hosted on GitHub?
- ratboy666 8y ago
  Since sqlite uses fossil for its scm, I thought that fossil drove the web site. It does for fossil. https://www.fossil-scm.org/index.html/doc/trunk/www/index.wiki https://www.fossil-scm.org/index.html/doc/trunk/www/index.wi...
- lmm 8y ago
  I've never understood this position - if you want sites to be no wider than a particular width, can't you just make your browser that width?
  - Sean1708 8y ago
    I usually have things set up such that my browser only takes up half of one of my monitors and the amount of websites that don't handle this properly is ridiculous, even Hacker News has a scroll bar at the bottom because the bar at the top is too wide. That was somewhat tangential, sorry, but it's been really bugging me recently and I wanted to vent.
    - bmpafa 8y ago
      I do a lot of front end work and this is a pet peeve of mine. And I _still_ screw it up sometimes. For whatever reason, layout is still really hard to get bug-free. CSS Grid will help a lot as it gains adoption, but if FlexBox's adoption is any indication, that won't be for some time.
- spiralx 8y ago
  I agree, although I also dislike text that's wrapped in a too narrow div with tiny text :) There are a couple of user styles here that look like what you're after if you've got the Stylish/Stylus extension installed: https://userstyles.org/styles/browse?search_terms=sqlite https://userstyles.org/styles/browse?search_terms=sqlite
dzonga 8y ago
I actually started using Sqlite as my go to SQL db. Logic being, the products I make will never have more than a thousand people that pay me $1/day. For that reason, Sqlite suits me well and will serve my users too reliably.
- tomkinson 8y ago
  At what level would you say SQLite doesn't scale?
  - ioddly 8y ago
    I'd say it depends more on what you're doing -- if you're not doing a whole lot of writes, you could get really far. See the "websites" section of the sqlite When to Use article: https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html
  - dzonga 8y ago
    @levelsio website hood maps was able to handle hundred thousands users on sqlite. For stuff you're charging for scale is not issue, as in you won't face that problem.
twodave 8y ago
At an old job we had a mammoth awful database running on MSSQLSERVER and we at one point ran into concurrency using the MERGE statement in a high-traffic area of the database. It turns out MERGE isn’t transactional so in between the check to see if a row exists and the insert, a concurrent call would sometimes create the row and we would get a constraint violation (it was a unique index basically). Tracking down the source of the error was only the first of many horrors on the way to fixing the problem for good. Fun times.
- sixdimensional 8y ago
  Did you put the merge in a transaction to resolve the issue?
anonu 8y ago
Cool. Just as a side note - I was first introduced to the upsert concept in kdb. Similar behaviour to the SQLite one..
zenmollusc 8y ago
Excellent news that they are finally including this. I was using SQLite3 heavily a few years ago, and one of the only complaints I had was the lack of this feature.
z92 8y ago
MySQL named this action as "REPLACE" instead of UPSERT which isn't an english word.
- willvarfar 8y ago
  MySQL has REPLACE INTO. SQLite has REPLACE INTO too. MySQL has INSERT INTO ... ON DUPLICATE KEY UPDATE. This is the functionality that SQLite now has. The difference between REPLACE and ON DUPLICATE KEY UPDATE is subtle but useful.

248 points by coleifer 8y ago

The diagram used on this page [0], and the ones on the JSON site [1] are pretty awesome diagrams. What are they called? Is there a good programmatic tool to generate them? [0] - https://www.sqlite.org/draft/images/syntax/upsert-clause.gif https://www.sqlite.org/draft/images/syntax/upsert-clause.gif [1] - https://www.json.org/ https://www.json.org/

wyldfire 8y ago
Often called "railroad diagram" [1] [2] [1] https://en.wikipedia.org/wiki/Syntax_diagram https://en.wikipedia.org/wiki/Syntax_diagram [2] https://stackoverflow.com/a/7143467/489590 https://stackoverflow.com/a/7143467/489590
- easytiger 8y ago
  I thought these are simple EBNF diagrams. As taught in British schools to 16 year olds
cpburns2009 8y ago
From the FAQ [1], > (25) How are the syntax diagrams (a.k.a. "railroad" diagrams) for SQLite generated? > The process is explained at http://wiki.tcl-lang.org/21708 http://wiki.tcl-lang.org/21708. I looks like a TCL script is used [2]. [1]: https://www.sqlite.org/faq.html#q25 https://www.sqlite.org/faq.html#q25 [2]: https://www.sqlite.org/docsrc/doc/tip/art/syntax/bubble-generator.tcl https://www.sqlite.org/docsrc/doc/tip/art/syntax/bubble-gene...
deleted 8y ago
[deleted]
politips 8y ago
I don't know if they used it but mermaid is pretty great: https://mermaidjs.github.io/ https://mermaidjs.github.io/

jedberg 8y ago

This is great! Given that SQLite is generally used for small projects and not in any sort of cluster or HA environment, pushing this kind of work (the if/then/else block that you don't have to write in your code anymore) makes a ton of sense. I actually like this feature more in SQLite than in Postgres. In Postgres I worry about pushing that kind of work into the database in clustered situations, as there are places where it can go wrong and it would be more likely that the code wouldn't be able to handle that failure properly (as opposed to having to write the if/then/else yourself, which if you're a good coder would also involve some try/catch and proper fallback behavior in case of an error).

anarazel 8y ago
> which if you're a good coder would also involve some try/catch and proper fallback behavior in case of an error It's actually really hard to do so correctly in the face of concurrency. Do not attempt to do so unless you've a really good reason.
- jedberg 8y ago
  I would say in this day of high scalability and microservices, a good engineer should be skilled at dealing with these types of errors, as they would be one of the most common types. Edit: I find it interesting that this comment has fluctuated between -2 and +2 points. Apparently saying that engineers should be able to handle concurrency errors is controversial?
  - anarazel 8y ago
    I bet that at least 9/10 experienced engineers in the space will come up with a solution that has at least one of a) incorrect behaviour under concurrency b) unnecessary deadlocks c) extreme slowness d) break w/ triggers, row level security etc.
  - anarazel 8y ago
    > Apparently saying that engineers should be able to handle concurrency errors is controversial? I didn't vote either way. But perhaps it's less the fact that they should be able to handle concurrency, and more that replicating the necessary non-trivial logic in several applications isn't a good plan.

Grollicus 8y ago

I hope they build this in a way that returns the correct changed row count. Currently upserts in sqlite are a bit complicated because there is no feedback

Apocryphon 8y ago
Agreed that it should return the updated count automatically, but it's possible to get that amount by pairing it with a query for the rowid of that new record: https://www.sqlite.org/c3ref/last_insert_rowid.html https://www.sqlite.org/c3ref/last_insert_rowid.html And if it's a table with autoincrement primary key, try: https://stackoverflow.com/a/2217015/223327 https://stackoverflow.com/a/2217015/223327
coleifer 8y ago
From a quick test SQLite seems to be doing the right thing. I tested inserting single rows, multiple rows, and restricting the conflict-resolution with a WHERE clause -- the "row count" always came out correct.

binarymax 8y ago

I'm torn on whether always forcing an explicit SET for ON CONFLICT UPDATE is good practice. It would make for much cleaner code to just update every column with the supplied insert values (as an option). Or maybe I'm just lazy (heck that's why I like UPSERTs in the first place!)

konsumer 8y ago
INSERT OR REPLACE does this. http://www.sqlitetutorial.net/sqlite-replace-statement/ http://www.sqlitetutorial.net/sqlite-replace-statement/

coleifer 8y ago

Just a note: this feature is scheduled to be included in the next official release of SQLite (3.24.0). Additionally, for some time SQLite has supported INSERT OR REPLACE which offers a simpler, all-or-nothing upsert query.

cyberferret 8y ago

It's been a while since I've used SQLite (over a decade ago), but didn't they already have this sort of functionality in there with an 'INSERT OR UPDATE' modification to the standard SQL INSERT command? In fact, it always made me wonder why other systems (at the time) didn't support this critical and useful feature? I was getting tired of writing a SELECT, comparing the returned row against the one I was about to update, then branching to an UPDATE or INSERT command depending. Doing it all in one command was a breeze.

anorwell 8y ago
If I understand, the difference is that this allows insertion of only some columns, including the primary key. If there is an existing row, the union of all columns will be the result. On the other hand, `INSERT OR REPLACE` completely replaces the existing row, if one exists.
- cyberferret 8y ago
  Thanks for the quick and informative explanation. I'll admit that I didn't have time to read the entire article right now, but I will go back and look at it in more detail.

igtztorrero 8y ago

MySql have: INSERT INTO t1 (a,b,c) VALUES (1,2,3)ON DUPLICATE KEY UPDATE b=2,c=3;

deleted 8y ago

[deleted]

jcrites 8y ago

For those who may be interested: I was curious about the difference between MERGE [1] and UPSERT, and why database engines seem to support a custom (nonstandard) UPSERT instead of the MERGE introduced in standard SQL in 2003, so I did some web research and came across a reasonable-sounding answer in a comment on the following article. https://www.depesz.com/2012/06/10/why-is-upsert-so-complicated/ https://www.depesz.com/2012/06/10/why-is-upsert-so-complicat... > As noted, MERGE is complicated and slow. If two different database servers implement it, then it is going to be implemented with different schematics [sic]. Oracle and MySQL already have different implementation’s, and to nobody’s surprise, MySQL’s implementation allows for risky behavior that does not protect the data. PostgreSQL users expect that any data put into the database will reliably come out of the database. So MERGE is slow and inconsistently implemented. The correct thing to do is make the application aware of if a record is new or existing. Any application making use of MERGE should open a bug to replace it with more predictable and faster logic using INSERT and UPDATE. > If an application does use MERGE, it has to account for implementation specific behavior and not will not be portable, in a safe way, to other database servers. So it is not suitable for ORM’s or database independent applications. > So what is a legitimate use case for MERGE where not knowing if a record is new or existing is not possible prior to the transaction? They only thing that I can think of is one-off scripts that do not have any concurrency. But if you design for that, then some misguided ORM for web applications is going to use your MERGE function and users are going to wonder about what happened to their data when concurrency concerns were ignored or not understood. (I believe this comment is by Michael McLaughlin, the author of books on Oracle PL/SQL programming.) Does anyone happen to know if this is still accurate today, or whether there's been any attempt to incorporate UPSERT support into standard SQL? I could imagine the SQL standard maintainers not wanting to offer UPSERT if MERGE is already a superset of its functionality; but a case could be made that, since MERGE's complexity has apparently discouraged adoption by implementations and users, it may be sensible to support UPSERT nonetheless. Is this a reasonable way to think about it? I don't mean to imply that standardizing UPSERT into SQL is an especially important concern for anyone; this is merely a trail of thought that originated from wondering about why the UPSERT statement is nonstandard. [1] https://en.wikipedia.org/wiki/Merge_(SQL) https://en.wikipedia.org/wiki/Merge_(SQL)

viraptor 8y ago

bmpafa 8y ago

I'd love it the SQLite page wrapped content in a div of a reasonable max-width. Text running edge-to-edge is hard to read. Anyone know if the site is hosted on GitHub?

ratboy666 8y ago
Since sqlite uses fossil for its scm, I thought that fossil drove the web site. It does for fossil. https://www.fossil-scm.org/index.html/doc/trunk/www/index.wiki https://www.fossil-scm.org/index.html/doc/trunk/www/index.wi...
lmm 8y ago
I've never understood this position - if you want sites to be no wider than a particular width, can't you just make your browser that width?
- Sean1708 8y ago
  I usually have things set up such that my browser only takes up half of one of my monitors and the amount of websites that don't handle this properly is ridiculous, even Hacker News has a scroll bar at the bottom because the bar at the top is too wide. That was somewhat tangential, sorry, but it's been really bugging me recently and I wanted to vent.
  - bmpafa 8y ago
    I do a lot of front end work and this is a pet peeve of mine. And I _still_ screw it up sometimes. For whatever reason, layout is still really hard to get bug-free. CSS Grid will help a lot as it gains adoption, but if FlexBox's adoption is any indication, that won't be for some time.
spiralx 8y ago
I agree, although I also dislike text that's wrapped in a too narrow div with tiny text :) There are a couple of user styles here that look like what you're after if you've got the Stylish/Stylus extension installed: https://userstyles.org/styles/browse?search_terms=sqlite https://userstyles.org/styles/browse?search_terms=sqlite

dzonga 8y ago

I actually started using Sqlite as my go to SQL db. Logic being, the products I make will never have more than a thousand people that pay me $1/day. For that reason, Sqlite suits me well and will serve my users too reliably.

tomkinson 8y ago
At what level would you say SQLite doesn't scale?
- ioddly 8y ago
  I'd say it depends more on what you're doing -- if you're not doing a whole lot of writes, you could get really far. See the "websites" section of the sqlite When to Use article: https://www.sqlite.org/whentouse.html https://www.sqlite.org/whentouse.html
- dzonga 8y ago
  @levelsio website hood maps was able to handle hundred thousands users on sqlite. For stuff you're charging for scale is not issue, as in you won't face that problem.

twodave 8y ago

At an old job we had a mammoth awful database running on MSSQLSERVER and we at one point ran into concurrency using the MERGE statement in a high-traffic area of the database. It turns out MERGE isn’t transactional so in between the check to see if a row exists and the insert, a concurrent call would sometimes create the row and we would get a constraint violation (it was a unique index basically). Tracking down the source of the error was only the first of many horrors on the way to fixing the problem for good. Fun times.

sixdimensional 8y ago
Did you put the merge in a transaction to resolve the issue?

anonu 8y ago

Cool. Just as a side note - I was first introduced to the upsert concept in kdb. Similar behaviour to the SQLite one..

zenmollusc 8y ago

Excellent news that they are finally including this. I was using SQLite3 heavily a few years ago, and one of the only complaints I had was the lack of this feature.

z92 8y ago

MySQL named this action as "REPLACE" instead of UPSERT which isn't an english word.

willvarfar 8y ago
MySQL has REPLACE INTO. SQLite has REPLACE INTO too. MySQL has INSERT INTO ... ON DUPLICATE KEY UPDATE. This is the functionality that SQLite now has. The difference between REPLACE and ON DUPLICATE KEY UPDATE is subtle but useful.