In the last video, we looked at dot commands which primarily affect the output of the statements that you're running or execute some like prepackaged queries like schema or tables, stuff like that. In this video we're gonna look at pragma statements which modify the behavior of the SQLite database. So they're a little bit different. Pragmas are the ones that we're going to tune for optimizing our database as we move forward, but I just wanna introduce a few of them to you right now. So the way that you interact with pragmas is you always start with the word pragma and then you type in the name of 1 of the pragmas.

There is a list and so we're gonna look at that first. Here are all of the pragmas that we can inspect and or modify. The ones I want to show you first are page count and page size. Remember pages? Pages will continue to come back to haunt us or rather they will continue to us.

So let's do, pragma page size and we see that the page size is 4096. And if we look at the page count, there are 2 pages in that database. Interesting. I wonder if we checked 8192, 4096 times 2 is 8192. So you can see already that, this database is made up of 2 pages of equal sized chunks of 40, 96 bytes and the file itself is actually 8192.

So this is a good way to calculate the size of your database. And we've already cracked this database open to see that it's a lot of zeros in there. So it's been 0 padded all the way out to fill up the pages. That's why there's very little data, but it's still set to 8192 because the pages are in there and full of data even if it's nonsense right now. Carrying on, let's look at a few more of these.

Let's see, what the list is. There's some that I wanna show you. And if we were to look at, let's say busy time out, where are you? Busy time out. So if we look at pragma busy time out, you see that it comes back to 0.

We're gonna talk about that later, but when we do talk about it, we'll see that we can just simply change it to 5 seconds and there you go. This, however, if we get out and we come back in and we check the busy time out again, look, it is set back to 0. So we set it to 5 seconds for that connection but when we closed out and came back in, it was reset to 0. Some of these are connection only and some of them are persistent. One that is super important and is persistent is the journal mode.

So if we check the journal mode here, you'll see that we are in delete journal mode. So this is the rollback mode and it will delete the journal when it's done. It's okay if you don't know what that means. We're gonna talk about this a whole lot. But if you were to change this journal mode to let's say wall mode, then that would be persistent across all connections until you change it back.

So some of these are persistent and some of of them or rather some are at the session level and some are at the database level. Journal mode is at the database level. Another interesting one is we can do pragma compile options, and we can see how this database was compiled. So there's a lot of stuff here. You can see full text search 3, 4, and 5 are all enabled on this one.

Let's compare this. This is my home brew version of SQLite. Let's compare this to the, the Mac specific one or the one that shipped with my Mac, test dot SQLite. And then what did we run? We ran pragma compile options.

Boom. There it is. There's the problem with the one that ships with, with standard Mac computers is omet load extension. And if we look over here we don't have omet load extension. So like I told you very early on, you can't load extensions with the SQLite that Apple ships you, which is kind of a bummer, honestly.

If you do db config, which is a dot command, you get a little bit nicer read. You get less information, but here enable load extension off. And if we switch back to the Homebrew 1, dbconfig load extension on. So that is what I was telling you earlier, and now we all know that that is the case. You can see there are a few other differences between the way that the Homebrew one was shipped and the way that the Apple one was shipped.

Another one that's gonna be very important is pragma foreign keys. If we check that, foreign key enforcement is off, but we can turn that on. And if we check it again, now it is on. However, if we exit and come back, it is off again. So this is a session level setting.

So if you're working with a web framework, you would need to, you would need to enable foreign keys every time you open And usually in web frameworks there are ways to execute statements at the beginning of a connection. This would be one that you would wanna execute if you want foreign keys on which we'll look at a lot more as we go. There are a lot of pragmas as we saw on that list. Many of them are deprecated. So I'll leave a note to the docs below and you can see which ones are deprecated.

As we move forward and start tuning SQLite for high performance, we'll start tinkering with all of them. There is a there's a set of pragmas that I think is probably ideal for web applications and we'll many of them are deprecated, but they remain for backwards compatibility. Even though the pragmas aren't covered under the backwards compatibility guarantee, they do keep them around. I think their vibe is always let's be backwards compatible if we can. So this is just an introduction to pragmas.

We're gonna look at several of them specifically as we move forward, but this is how you would configure the inner workings of the SQLite library.