INSERT & DELETE

[00:00:00]
>> Brian Holt: So we're gonna start doing inserting things into the database.
>> Brian Holt: So I'm gonna say INSERT INTO whatever table that you wanna insert into, INSERT INTO Artist, and then you're going to give it a list of values of that you're going to give it. We're only going to give it one value because there's only two values and one of them is automatically assigned which is the ID.

[00:00:26]
So I'm gonna say, I want to put in a name but as you might imagine if I had like genre, year, number_of_band_members, right? Like you would just do that here, we'll only have name, because that's the only column that exists. And we'll say values, and whatever you want to insert into there.

[00:00:51]
I think it's a crying shame that Radiohead is not in this list, so I'm going to put in Radiohead, but feel free to put in your favorite band, okay? It didn't yell at me so that is sqlite way of saying, yeah, sure, I did it, right? Whatever you told me to do, I did it, I'm not gonna tell you any more about it than that.

[00:01:10]
What if I wanna see it? Well, as you might imagine, you're just gonna write another query, great. WHERE name = 'Radiohead';
>> Brian Holt: You can see there, row number 276 is Radiohead.
>> Brian Holt: Cool, just to show you something here cuz we're not gonna do it with a table that has multiple values, but I wrote a query like this here as well.

[00:01:41]
So INSERT INTO food (name, food_group, color). And then when you're doing VALUES, it just matters the order, that order, so the name, carrot, food_group, vegetable, color, orange. Just make sure that you follow the same pattern, right? If you switch vegetable and carrot, it thinks the name is gonna be vegetable, the food_group will be carrot, which is incorrect, right?

[00:02:03]
So, the order is important there.
>> Brian Holt: Yeah, this is just talking about quotes, we already talked about that a bit earlier. Let's talk about updates. So let's decide for a second that I didn't want to do Radiohead instead, I really actually wanted to do Daft Punk. We're gonna do UPDATE Artist SET name = 'Daft Punk' WHERE name= 'Radiohead'; okay?

[00:02:52]
>> Brian Holt: Again, by it not saying anything, that's its way of saying like, sure, cool, I did it, right? So now if I go back and say, by the all, you can just hit up, just like in a terminal, and it'll give you back old queries. So I'm just gonna go update this one to say instead of radiohead, I'll say daft punk.

[00:03:08]
276, notice it's the same one, and if I run the Radiohead one again, notice that it doesn't get anything 'cause it updated the old one. What do you think would happen if I didn't put that where clause in there? Let's see, not that, there we go. So if I do UPDATE Artist SET name = 'Daft Punk';
>> Brian Holt: SELECT FROM Artist.

[00:03:38]
>> Brian Holt: Everything is now Daft Punk. That is the only music that we now have forever. I'm not gonna complain too much. Pretty funny, right? Don't do that, by the way, you just blew up your database. There's not really a good way to recover in SQLite, because they just again, they don't include stuff like that.

[00:03:58]
So we really have to go get another copy of the database if you follow along with me. I, knowing this, can just say git checkout data, because I have it in git. And now if I do select star from artist, it knows everything's back to normal again, right?

[00:04:19]
But be very careful with SQL because it will do precisely what you tell it to do, right? It does not care, it's like you want to update literally everything to be Daft Punk? Sure man, you're the boss, right? So just be careful about that.
>> Brian Holt: We actually don't talk about upserts in here, you can also do upserts in SQLite, where basically like if this exists, then insert, if not, then create it.

[00:04:49]
Don't really need to talk about that today, but feel free to look into that yourself.
>> Brian Holt: So let's insert back our Daft Punk.
>> Brian Holt: We're going to do that and then
>> Brian Holt: All right, so now I have a
>> Brian Holt: There we go, record 276 Daft Punk. So we're gonna say UPDATE Artist SET Name to another must be French techno band.

[00:05:33]
I'm just kidding, but I happened to pick another French techno band, so we want'Justice' WHERE name = 'Daft punk'. And then let's say you're not satisfied with the fact that it doesn't return anything. You can say RETURNING whatever you want that select statement to be. So I'm just going to say RETURNING star and now, I updated it and now it gives me back the new record as well.

[00:06:02]
Which can be helpful sometimes, if you wanted to just do it like one go, you don't want to do two queries to get that. That can be helpful as well. I frequently will do that just when I'm, you know, interacting with the CLI.
>> Brian Holt: RETURNING star and you can just do like RETURNING name, right?

[00:06:24]
Or something like that. That's
>> Brian Holt: And again, I did this look, It didn't complain cuz it was valid query. Nothing would match this, right? Because there's no Daft Punk in the database, right? And so I did RETURNING name. It didn't do anything, because nothing was updated, right? So we returned the entire set which was a set of zero, right?

[00:06:45]
Nothing in there.
>> Brian Holt: So that's something to be aware of, you do wanna understand sometimes if your queries did anything or not. Because if they're valid, they will run and it won't complain.
>> Brian Holt: Okay, and then we can do a DELETE FROM Artist WHERE name = 'Justice'. And you can just end that query there and again if it does anything, it won't tell you, or you can do RETURNING star here as well.

[00:07:23]
Returning works on deletes, but you don't have to have it as well.
>> Brian Holt: And this will give you back everything that it deleted. And select star WHERE 'Justice'. Not in there anymore. So that is deletes.
>> Student 1: Where is sqlite storing these data updates?
>> Brian Holt: That's a good question.

[00:07:48]
So if I get out of my database here, you can see I'm modifying this file called data.db. And if I just do an ls here, you can see I have this data.db file right here. If I do a git status? Yeah, you can see it's modified, because we've been working on this, right?

[00:08:11]
Since the original check into which was just the original Chinook data. Sqlite is a library for editing files in a particular way that's stored in a certain way that's compatible with other sqlite databases. It's not a server, right? It's not doing of those things, it's just modifying this file, it's a library for modifying these sqlite database files.

[00:08:38]
You can see here I'm actually tracking my database data in git. I'm gonna say that this is not a normal pattern for the most part. It depends totally on what your data is, right? This Chinook database is not very dynamic, right? It's example, it totally makes sense that I would track this in my database or my git report because it's not meant really to change.

[00:09:07]
But imagine if you have like a Shopify storefront or just like a normal storefront or something like that. And you're reading and writing to a database in production, that would mean that you would have data in production. Production that would mean your production servers would have to check intogGit and then push back into your git repos.

[00:09:25]
That feels really weird, and I'm gonna say bad. To me I wouldn't suggest doing that. That it just kinda depends on your tolerance for those kind of things, and for pushing back into your git repo and the potential for data loss, right? Cuz imagine like your server goes down, how long ago was your last check-in?

[00:09:49]
Was it a day? Was it an hour? Was it two months, right? You would lose all that data. So normally I wouldn't do that. You might do something like where you're running in like Kubernetes or Docker or something like that. And there's a shared volume maybe or something like that.

[00:10:05]
That feels weird too. That's where you get into these like weird problems with sqlite of like when, how do you get all this into production? Actually, the best way and I'll show you here at the end, is this thing called light FS backups here. Every time that you write to the sqlite file, it will stream off to another location.

[00:10:31]
So it's just basically like, right to your database, and then this gets streamed to an S3 bucket somewhere. That's how I would end up choosing to do it.