SQLite does have types. And when we're talking about the traditional SQLite tables, those types are kind of declared at the column level, but that's just a suggestion. The actual type is at the data level. When we talk about strict tables, that is different. You will declare it at the column level and it will be true for the entire column unless you use the type any which allows any.

We'll talk about strict types here in a second. They don't have many types. SQLite doesn't have very many types and so you may be missing some of your favorites from either MySQL or Postgres, especially if you're coming from Postgres because Postgres has a lot more types than even MySQL which has more types than SQLite. The 5 types that SQLite does have are null, integer, real, text, and blob. Those are the 5 types.

That is all that you get. You'll notice we're missing a lot of stuff. We're missing boolean, which is probably fine. We're missing dates and datetime and timestamp. That's a little more problematic.

We're missing enums. There's just there's no enum support. And we're missing JSON along with uid and guid and and points, like geographic points, all kinds of stuff that we're missing from 1 of the other databases. But as we'll see that that may end up being okay. There are ways to work around dates and JSON and all of that stuff.

We need to talk about type affinity here. Type affinity is how SQLite determines if it should take your input and turn it into a different type. Let me show you here in a database. So if we do create table types and we say, int is integer and text is text. So if we create that table, and I'm gonna hop over to table plus because sometimes that's easier for me.

Insert into types, values of 1, 1 and 1. So if we were to insert into that table, select star from types, they look the same, but int and type of int along with text and type of text and we read that back, you'll see that they are in fact different. So we inserted 2 integers. They didn't have quotes around them. We inserted 1 and 1, but because of the type affinity of the text column, SQLite said, well, this is a text column.

I can convert this value to text without losing any data and so I'm gonna go ahead I'm gonna go ahead and actually convert it into text. Let's take a look at another 1. I'm gonna leave let's just copy this and I can back see, this is so much easier to do it over here. Now let's do this. Let's say 1.01.0.

And if I were to insert that into types and then we read that back, you'll see that it was in fact stored as 1 and it dropped the point 0 because that's not extra, information. This this is a lossless conversion storing it in the type that we declared the column and so SQLite says, sure. I can do that. No problem. However, if you start to lose data, SQLite says, I am not gonna do that and it stores it as a real instead of storing it as an integer because like we said, it is a suggestion.

The types are suggestion, mandate. You'll see that basically everything can be stored as text so you're probably fine there. In fact, as we looked at in 1 of the earlier videos, you can just put text anywhere and SQLite is going to accept it. So the interesting thing here, I think, is that as long as it doesn't lose information, it's really gonna try to put it in the type that you declared the column. Otherwise, it's gonna hang on to everything.

Now, how do you determine, how do you map between a column type and the affinity? Like, this this affinity where it says, I'm going to translate it into something, how do we get from column type to affinity? Well, there are rules. You know, databases are just rules and we love rules. So here are the rules.

When you create a column, you give it a name and optionally a column type. We're gonna be looking at this column type. If this type contains the string int, then it gets the integer affinity. If this type contains any of the following strings, car, club, text, then it is assigned a text affinity. This means when you create a varchar or varchar or veracare, it is assigned a text affinity because of this rule, because it contains the string CHAR.

If the type contains the string blob or is not specified at all, there's no type specified, Then, it gets the blob affinity. If the type contains any of the strings real, flow which is float without the t, real, flow or dub, D0UB, then it is assigned a real affinity. Otherwise, the affinity is numeric. Those are the rules, the 5 rules. Starting at the top, moving down, if it's declared int, it's an integer.

If it's car club text, then it's a text. If it's a blob or nothing, then it's blob. If it is real, flow, or dub, then it's a real affinity. Otherwise, it's numeric. That is how the affinities are decided.

So let's take a look again at SQLite and see if we can prove any of that. We're back here in table plus and I'm just gonna create a new table over here with no primary key. And we're gonna say that this is a double, and we can say that it's a type dub because as long as it includes that string, it's gonna work. In fact, let's just name these so we can remember how we declared them. So this 1 is flow and we're gonna name it just flow.

We'll do float and we'll do double as well, the real versions. So we have dub and flow and then float and double. You can also have, varchar. And so if we were to say that this is a varchar or vercare or verchair of 10, that's interesting. It has absolutely no effect whatsoever.

So, VARCHAR of 255, these are both going to be text columns. And unlike in MySQL or Postgres, declaring a link in there has absolutely no effect whatsoever. It's only going to store, it's only going to allocate space and it's only going to store the data that it needs to store. You could put, 10,000 characters in either of those columns and it would gladly accept it. But if you declared this as, you know, 255 or 25, 500 characters, it's not gonna allocate extra space.

It's not it's not a, performance disadvantage to declare it like this. In fact, it's completely moot. You might as well just declare it as a text. In fact, let's create a new 1 of text down here and we can call this 1 blob. And I think that's probably about it.

The interesting thing here is that these are all going to have the same affinity. Those are all gonna be an affinity of real because of the rules of Dub and Flow. So if we go ahead and save that and we'll insert some data. We're just gonna insert 1.0 in all of these first ones and we'll insert 1.1. We'll do 2.1 down here.

And then over here, let's put in, more than 10 characters. So we'll do, we'll just do the top row. We'll put in all of those, there, there, and those those you have to edit as a blob so I'm not gonna do that. You'll see that those affinities have worked because we've stored the point 0s, where if it were an integer affinity, those would have been lost. In fact, we can come in here and say int int and we can say int 11 which, if we could type int 11, which also has no bearing whatsoever.

It doesn't affect anything. It's just an integer. And big int, it's nothing. It doesn't matter. It's not going it's not going to change anything about how the data is stored.

And so if we were to hop back down here and over here we could do 1.0, 1.01.0. Save those. You drop the point 0 because it has that int in there. And same story for down here. It will gladly accept those because it can't convert it losslessly and so the affinity doesn't matter.

It can't be applied. So it just puts in whatever you gave it. SQLite is a bit strange in this regard, I will admit. This is for the non strict tables. We'll cover strict tables in a minute.

But for the traditional tables, the columns data type, the only purpose that it serves is to figure out what the affinity is. So char 10varchar255, those things don't exist in SQLite. They just don't exist. But if SQLite sees the string CHAR, it says, that's a text column. I'm gonna make it a text affinity.

And anytime you put data in there, I'm gonna try to turn it into text. Big int, medium int, small int, tiny int, int 11. None of those things exist in SQLite. But when you see the string int, SQLite says, yes, that is an integer column. I'm gonna do my best to convert it to integers.

That is what type affinity is. Let's look at strict tables.