The fact that SQLite has upsurts is hopefully another point in favor of SQLite being a real database and not a toy. Upserts are not a core standard part of SQL. So these upserts are actually modeled after the way upserts are done in postgres, but this is not common SQL. This is very specific to SQLite and it's very awesome. Let me show you how it works.

Select star from kvlimit10. So you see we're back here at this kv table that we used a little bit for benchmarking and if we look at the structure, you see we do have a unique index on the key column which is exactly what we would want in a key value store. So if we were to do insert into kv, we'll say the key and the value with values of the first key there and we're just gonna insert fu. We do get that error. Exactly what we want.

We don't want 2 key values to end up in our kvstore. We just want 1. But what if you want to update something in the key value store or if it doesn't exist, you want to insert it. What if you want to insert it or update it or update or insert? That's where the upsert comes in.

You can update it or it will insert it. In this case, it did not insert it. So let's see how we can potentially update it. We add this on conflict clause here. This this clause is what begins the upsert.

If you check the docs, you'll see a railroad diagram that calls this the upsert clause. That's exactly what it is which is different than when you declare a foreign key constraint and you say on conflict rollback or abort or something like that. This is for the insert statement only. So there are 2 on conflicts. This one is for the insert which makes it an upsert.

The next thing you need to do is say on conflict what? You have to declare a a conflict target. In our case, the conflict target is the key. So when there is a conflict on the key, do this. If you had another unique index, you could declare a different on conflict.

But since we just have the one, we are going to declare this on conflict here. So on conflict key do, that's the next keyword, and you have the option to do nothing. So if you do nothing, what that does is that makes the error go away, which might be which might be important. If your business logic dictates that when there is a conflict, that's totally fine. It doesn't matter.

Just leave the row in the database alone and carry on. Do nothing is great because it silences that error. You don't have to you don't have to trap it or try catch it or anything like that. It's just, yeah, yeah, yeah, we know. Insert it if it's there.

Otherwise, I don't want to hear about it. Most of the time you want to do something else though. Most of the time instead of do nothing you wanna do update. You wanna do update and then you want to set the value equal to something. And what do you want to set the value equal to?

Potentially a few different things. You could want to set it equal to the value that was excluded by the conflict which in this case would be fu. And so you're able to chain off of this, magical keyword excluded. So we can say excluded dot value. You could, it would be very silly, but you could say excluded dot key.

I don't know that that makes a lot of sense, but excluded dot value will accomplish the up cert. So now if we run this and we do put a semicolon here, select star from kvlimit10 and we run that we see it was updated. You can you can do anything you want. So if we run that again you'll see there was a conflict. That's that's kind of nice.

So you can set any value you want in here. You have access to excluded. That's likely what you want And then there is one more thing that you can do. You can add a where clause on the end. Now, the where clause is pretty specific, but you can accomplish an upsert where certain things are true and otherwise do not do the upsert.

You can do that by chaining a where on here. So you can say where and then you can access the original table or you can access the excluded value or you can do both. So let's access the original table first. So we'll say where kv.valueequalsfu, we want to update it to let's say, foo 2. That's a good that's a good one.

So if we run that, we'll see it's set to foo 2. But now if we were to say foo 3, this is not going to update because it's still it's, excluded by that it's excluded by that where clause. So if we run that again with foo 4, you'll see it's still set to foo 2 because kv.value is not foo. This is a silly little example but you can imagine something like, kv. Expiredat is less than or equal to date now or whatever, right.

So in this case, you can check, you can inspect the row that is about to be upserted and say, hey, if this value has expired, yeah, that's fine. Let's go ahead and update it. If it hasn't expired, well, let's just ignore any updates. That seems fairly reasonable to me. You could also say, hey, if somehow, let's say somehow that kv.value is null, let's go ahead and insert foo 4.

Otherwise, leave whatever value is present there. You can also do, you can also access both of them. So you could say something like kv. Kv.let's see, last updated which doesn't exist on this table. Where kv last updated is less than excluded dot last updated or or whatever.

But you can imagine, you have a time stamp in the table and you have a time stamp coming in and you want the newest one to win. And so, you can use the existing value and the excluded value to make the correct decision based on your particular data, which I think is really interesting. The last thing I wanna show you is using an upsert as a way to increment or potentially decrement, but increment a counter. So if we have this table here, which we do have this table, and we were to change this to 1, So instead of keeping track of a string, now we're just incrementing a counter. We can change this to say insert into kv key value.

The key is gonna stay the same and the value is going to be 1. So on the first hit, it's always going to be 1. On conflict, do update, we don't wanna set it back to 1. That doesn't make any sense. What we wanna do is say value plus 1.

We can drop that where and now we have a counter. So if we run this over and over, you'll see that it counts up. Let's pick a key that is going to be before that. So we'll just say, all zeros. So if we run this one, it's going to insert all zeros as a one and then we run it again and we get back 2.

So this is a nice way to implement a counter without having to run a query to see does the key exist. If the key exists, let's get the value back and then increment it by 1 and then send that update query back. Otherwise, send an insert. What are we even doing here? That's where an upsert can come in handy.

One query, throw it over the wall, let the database take care of it. Upserts are very, very cool.