If you don't like the flexible typing of SQLite, that's totally fine. There is such a thing as a strict table. Let's take a look. We can create a table called, we'll just call it strict types, and we can have a column called int that is an integer and a column called, let's say text that is text. Now, this is how you would create a traditional table, but to create a strict table, you add what's called a table modifier at the end.

There are there is maybe one other table modifier that we'll cover later, but we're gonna add a table modifier here that says strict. So if we create that, we now have a strict table. Let's go ahead and select star from strict types so we can get that ready. There's nothing in there. We'll turn on box mode and we'll insert into strict types values of 1 and text of hello.

So if we insert that, that works just fine. So we read that back and we get int of 1 and text of hello. However, if we were to insert hello and 1, we would see you cannot enter a text value in an integer column. So we already have strict types. All we had to do was add that strict table modifier to the end, and now we have a strictly typed table.

There are a few other things that we need to look at. For example, if we were to create another table called strict types, I already dropped the old 1. So if we did create table strict types and we were to say that int is an int and foo is a foo, that doesn't make any sense. In fact, let's create this as a non strict table first and it just works. Foo is not a a data type, but remember, in a non strict table, the data types are simply there to declare an affinity.

And the affinity follows certain rules. It's not a 1 to 1 mapping, it just follows certain rules. And because the word food doesn't have the substring int or char or flow or dub or anything like that. It just falls all the way through and it becomes a numeric column. So when you are declaring a non strict table, you can make up your own data type as long as it matches 1 of the affinity rules.

Now if we were, let's drop that again and we were to create this again as a strict table, you can't because it's not a real data type. So you're limited when you're creating strict tables. You're limited to actual data types, which seems reasonable enough to me honestly. So you have, the data types that you have are int and integer. Both of those both of those are legal.

Both of those are allowed. You've got real text and blob, so you could have, you know, text as text or let's say, let's say notes as text and price as real. You could have those. Those are all those are all legit. But let's say you wanted a little bit of SQLite's flexible typing and a little bit of SQLite's strict typing.

You can mix and match those. You could have a flexible table and a strict table but you can also mix and match them in the same table. Let's imagine, for example, you're creating some sort of key value store in SQLite. That's actually a great idea. Create kv where key is a string that's supposed to say text, where key is text and value is well, value could be anything.

So you can just type the word any. It does have a data type for any. So we can still declare this as a strict table, but key must be text and value can be anything. So if we were to insert into kvvalues of, let's do key 1 and the value could be foo. And the value could be the the value could be 3.

And now if we select star from kv, you'll see that we have the keys as text and the values as any. Let's do something interesting here. At least I think it's interesting. In fact, I think all of this is interesting. Type of key and value, type of value, from kv.

So you can see that it retained the value or the type of the value over there and these are both texts. Now here's something interesting. Insert into kv values of 3 and baz. What do you think we're gonna get here? Yes.

It inserted 3 but it coerced it. It juggled it. It forced it to be text. So it could be converted to text without losing any information and so it went ahead and converted it into text. You can imagine where an any column might be really helpful.

Key value store is 1. User settings is another. Some settings are numeric. Some are boolean which doesn't really exist. Some are text.

Some are maybe floats or doubles, anything like that. And so having the, ability to declare some columns flexibly typed and some columns strict typed is very nice and honestly 1 of the unique features of SQLite that I happen to really like. So whichever one you prefer, neither are wrong, but likewise neither are right. It just matters what your use case is and what you're trying to do. But you can choose strict tables, strict columns, or flexible everything.