Okay. It's time in this module to start building out some tables in SQLite. We looked at some of the history and the internals and the way it works under the hood, which I think is very interesting but I know is very helpful as we move forward. But now we finally get to start playing with SQLite itself. Now, if you are coming from a MySQL or Postgres background, some of these things are going to be very surprising.

Namely, that SQLite is a little bit strange when it comes to data types. Historically, they have not been strongly typed. You might say, well then they're weakly typed. SQLite is is weakly typed. They don't super love that.

In the documentation they like to carve out a spot known as flexible typing. And it is, honestly, it is a little bit different than weak typing. So we'll cover what flexible typing is and then there is a way to do strict typing. But flexible typing is a benefit of SQLite, and so we're gonna take a look at it. So in this video, I'll show you a few things and then we'll move on to the different data types and then strict types and then we'll keep going from there.

Let's take a look at the database. The first thing that might surprise you if you're coming from MySQL or Postgres is you can create a table with no data types whatsoever. You can just create a table with some columns and it'll hold anything. So insert into ex values. We'll say 1, asdf, and 3.

And if we were to do it again and change that one to asdf, and let's do it again and change that one to asdf and this one to 2 and then we select star from ex, you'll see all those values were completely stored safely. We haven't lost any data, but the data types are intermingled inside of the columns. The creators of SQLite view this as a great strength of SQLite. You're not gonna lose any data. It will gladly accept whatever you get it and hang on to it.

This can be really valuable in certain situations, like when you have a CSV that might have dirty data in it or when you're storing, some sort of eav pattern, entity attribute value, and the value is of mixed type or you're using SQLite as a key value store and the key could be an integer or a string and the value could be anything, integer, string, null, JSON, whatever. There are some times where it's super annoying and that's where the strict tables will come in or you could enforce this on your application side using type safety over there. But let's keep going and see what happens when we declare a column as a type and then put something wacky in it. Let's keep going. Let's create a table ex2 with a as an integer and b as, let's say text.

So if we select star from ex2, we should see nothing. I think box mode, let's make sure box mode is on. And now let's insert some data. Insert into e x two values, one for a and hello for b. So now if we read that back, we'll see, yeah, that was stored.

That's reasonable. That's what we expect. But this is not what we expect. If we insert, hello into the integer column and 2 into the text column and then read that back, it also just works. So even if you declare a column type, you can still put anything you want in there.

We're gonna talk about type affinity here in a second which is their way of trying to juggle some types. So let's take a look at this. Let's do select type of a from ex 2 where a equals 1. So if we look at that, we see that we do have an integer there. So it was stored as an integer, but if we take a look at the next one where a equals hello, you'll see that was stored as text.

So the real takeaway here is that for default SQLite tables, the data type is applied at the cell level. So row, column, at the cell level and not at the column level. So you can say, hey, this column should be an integer but each individual piece of data in there might actually be an integer or text or float or null or blob. Right? So we don't have column level data types when you're using a traditional SQLite table.

Like I said, that can be a really good thing. That can be super annoying. Let me show you one more thing. If we were to insert into ex 2 values of string 3 and string 3 and we were to read that back, select star from ex 2, kinda hard to say, right? Select a type of a and b type of b from e x 2.

Now, well, well, well, isn't that interesting? For this last row we inserted 2 strings with a value of 3. However, in column a it was turned into an integer, but in column b it was not turned into an integer. That is super interesting and that is because column a was declared as an integer, therefore it has an affinity of integer, which means SQLite is going to try to turn it into an integer if it can without losing data. And we're gonna look at that in the next video.