Let's talk about backing up your SQLite database. If you're using a hosted service like Terso, they handle that for you so you don't have to. But if you're not, there are a couple different ways we can backup your SQLite database. The first one that I'm going to show you is just using the dot backup. So we're gonna say SQLite database dot SQLite.

And then instead of like actually opening up the database and going in there, we're just gonna pass a command through and that command is the dot backup command. So this is a built in way to make a backup. It is rock solid, because it's built in to SQLite. Writes can continue while this is being backed up but the rights won't be reflected in the backup. So this isn't going to this isn't going to lock your database, but it is going to take a backup at the time that you issue this command.

So you can say backup and then you can do backup dot SQLite or whatever you wanna call it and that's gonna take a second. And this is going to make an exact copy. So if we do a list here, you'll see we've got a lot of databases. We've got database SQLite that starts with 91, ends with 9 2 and we've got backup SQLite, starts with 91, ends with 92. So those are identical.

Now, that may not be what you're looking for. You could potentially want to compress the database as you're backing it up. So remember when data is deleted, it's not actually deleted, it's just marked as gone and it creates these free pages. And so in the scenario where you have a lot of deleted data, your database could be quite large but quite sparse. It could be very empty.

And for forensic purposes, you might wanna overwrite everything that was deleted. I don't know what your application is, but if you don't want deleted data showing up in your backups, you kinda wanna compress it all and you can do that. So you can do that by saying SQLite 3 database. And then instead of using the, dot backup, we're gonna use something called vacuum. And we're gonna vacuum into and then we'll just call this one vacuumed dot SQLite.

And what this is gonna do, this is a little bit more CPU intensive because it's not just a straight copy, but it is going to compress that database, as it is doing the backup. So if we look at database SQLite, starts with 91, ends with 92, and vacuumed starts with 73. So it is in fact quite a bit smaller than the original. So this also will not block writes. Like I said, it is a little bit more, CPU intensive because it's doing more work.

And do remember, SQLite is just a file, but it could potentially be 2 files and sometimes 3. If you have a SQLite file and a SQLite dash wall file, then you've got your database split across 2 files. You could copy both of them, but odds are pretty good. That means somebody is writing to the database and you don't you don't wanna copy while somebody is writing to the database. I would use one of the prescribed backup methods.

I would use the dot backup or the vacuum into. Those are going to handle this use case where if you go outside of SQLite and just start copying files around, you have to make sure that no one is writing to the database. And you could do that in a bunch of different ways, but you have to be very, very, very sure about it. And I'm just not ever that sure really about anything. And so I'm gonna use SQLite's built in methods.

If you don't like that and you're not using, one of the hosted providers, you can use something in the ecosystem. You can use something like, Lightstream or something like that that basically, opens a transaction and just like streams the wall file out. And so it's kind of it's kind of a nice system. It it does require a little bit of setup. So I would first default to the dot backup or the vacuum into.

I would try to stay away from copying the files manually because that feels risky to me. And if you really want to, you could look at a third party tool that streams backups out to somewhere like s 3.