.svg)
Datatypes in SQLite are handled differently than pretty much every other popular relational database. How is it different? Well, SQLite uses a flexible, dynamic type system, whereas the other popular relational databases use a strict, static type system. What does this mean to developers? Watch this video to learn more.
"Datatypes in SQLite are handled differently than pretty much every other popular relational database.
For example, look at the following SQL statement for creating a table.
Now if you've used other relational databases, odds are, you're probably making some assumptions about this statement, that are wrong.
What do I mean by this?
Well, what do you think will happen with the following insert SQL statement:
So, will this insert work?
I bet most SQLite newbies would say no, this insert won't work and it should error out because we're trying to insert a string, into an integer column, and that should fail, right?
Let's see exactly what happens with this insert statement, by trying to run it in sqlite.
So, I'll enter sqlite3, the command line utility for sqlite, and I'll pass along a new database name.
Next, I'll create the contacts table, and I'll try an insert this new contact, and well, we didn't see an error, so did it work?
Well, to check, I'll run a query on the contacts table, and we see the inserted record, so obviously, it worked.
Ok, so what's going on here? Why didn't we get an error?
Well, by default, SQLite uses a more flexible, dynamic type system, whereas pretty much every other popular relational database, uses a more strict, or rigid type system.
So how do you feel about this flexible, dynamic type system, in SQLite?
I bet a lot of you don't like the behavior you just saw, for reasons like:
Ok, if you really hate what you just saw, well, don't bail on me just yet, because in a moment here, I'll show you how you can make SQLite behave more like the other popular databases, if that's important to you, so hang with me.
So, is there a point to specifying a column datatype, given the fact that, SQLite doesn't really seem to enforce it, as you'd expect?
I mean, why not just do something like this...
where you drop the datatypes altogether.
By the way, do you think this statement is valid in SQLite?
Well, let's go try this out and see.
So, first I'll drop the contacts table, then I'll recreate it, without specifying column datatypes, and it didn't throw an error, so it must have worked, right?
Now just to verify, I'll insert a new record, and I'll query for it, and as you can see, this is confirmation that the insert worked.
So, the question we're left with, after this small experiment, is, what's the point of datatypes in SQLite, if they're optional, and even when you do use them, they don't seem to do what you would expect?
Well, datatypes in SQLite, do have a function, it's just that, they don't work like you probably expect them to.
So, to demonstrate what the datatypes do in SQLite, I'll drop the contacts table again, then I'll recreate it, with column datatypes.
Now I'll insert a new record, but notice what I'm doing here. This time, I'm still using a string for the age, but the content of the string is a valid number.
Ok, I'll go ahead and insert this record, then I'll query the contacts table again, and we see the record we inserted, but the question I'll ask you, is what datatype is this value here. Is it a string, is it an integer, what is it?
Well, you can't really tell what datatype this is, from what we see right now, but I'll run another query, where I'll use the typeof function, and check this out.
SQLite, ended up coercing the string 48, to the number 48, and it did this because, specifying the age as an integer type, created what's called a type affinity for the column.
So, what does this type affinity concept do for you?
Well, basically, it tells SQLite, to coerce values if possible, to the columns datatype. So, since the string 48, can be losslessly coerced to the integer 48, SQLite did the type coercion for us.
And by the way, this idea of a type affinity and coercion, also exists in the other popular relational databases, the difference being, that other databases will try to coerce values to the columns datatype, and if the coercion works, then the insert or update will succeed, but if the coercion fails, then the entire statement fails. Whereas with SQLite, the insert will work, even if the coercion fails.
So, what do you think will happen with the following insert statement?
In other words, what datatype will this value be, when it's inserted?
Well, let's see. So, I'll insert the record, and I'll query the table again, and as you can see, the 48.5 wasn't coerced to an integer, it was inserted as a real number because coercing it, would end up losing the decimal value, so SQLite inserted the column in a lossless way, which means the value is a real value.
So, why does SQLite's datatypes work this way, in other words, why does SQLite use a flexible, dynamic type system?
Well, according to the authors of SQLite, they prefer SQLites flexible dynamic type system, because you can do things with it, that can't be done in other databases.
For example, consider the following statement.
So, do you see what's going on here, with this new store table?
Basically, what we did here, is we created a key-value store out of SQLite, at least in part.
Now, if you wanted to create a key/value store with the other popular databases, it would be a bit more complicated than the SQLite solution, because you'd need to add value columns for each datatype you want to store.
Ok, so as you can see, SQLites flexible dynamic datatypes do offer some features you don't get in other databases.
Alright, but even though there's some benefits to SQLite datatypes, I'm sure some of you, still don't like what you're seeing here, but what I'll ask of you is to keep an open mind, because, well, SQLite seems to work well for a lot of people, and it will likely work well for you too.
I mean, it's estimated that there are more than 1 million different applications using SQLite, and there's over 1 trillion instances of SQLite, so odds are, SQLite will work well in your applications too.
Now, if for some reason, SQLites dynamic datatypes are a no-go for you, then there is a relatively new feature in SQLite that makes it behave more like the other popular database engines.
So, what's this new feature?
Well, it's SQLite's new Strict data typing mode, which is a feature that was added in November of 2021.
So, what's strict mode?
Well, as the name suggests, strict mode causes SQLite to behave more like the other popular relational databases.
Now, to be clear, SQLite's strict mode doesn't function exactly like the other relational databases, but you'll see what I mean by this in a moment.
The next question you probably have is, how do you use strict mode.
Well, to use strict mode, you simply include the STRICT keyword, just before the end of your create table statements, like this.
Let me walk you through an example here.
So, first I'll drop the existing contacts table, then I'll create a new contacts table, using strict mode, then I'll try and insert this questionable record.
So, what do you think will happen this time, in other words, do you think we'll get an error, or do you think this insert will work?
Let's run it and see.
Ok, we see the error 'cannot store a TEXT value in the INTEGER column age', and an error like this, is what you'd see in the other popular relational databases.
Let's add another column to the contacts table, for a phone number.
So, first I'll drop the contacts table, then I'll create the table again, but this time I'll include the phone number column, now let me ask you this, what datatype should we use for this number?
We could use text, but what if we wanted to limit the number of characters, to say 15 characters?
Well, for most databases, you might use something like a varchar 15.
Let's see if this works in SQLite, using strict mode, and we get an error that we're using an unknown datatype.
So, this begs the question, what datatypes are valid here?
Well, while in strict mode, the datatype you use must be one of the following:
So, does this mean you can't limit the size of a string in SQLite?
Well, not the way you might with other databases, in other words, you can't use char(15) or varchar(15) or any variant like this, you have to use the TEXT type, but if it's important to you, you could limit the size of a field with a database check constraint.
For example, I'll recreate the contacts table, but this time I'll add a check constraint, that limits the length of the phone column to less than 16.
Now, if I insert a new contact with a well-formed phone number, it works, but if I try and insert a contact with a phone number that's too long, then we get an error.
So, in summary, SQLite's data types are very different from the other popular relational databases, and this works out fine for most use cases, I mean, SQLite is the most used database in the world, and the vast majority of these SQLite powered applications use SQLite's flexible, dynamic datatypes, and it works just fine.
But, keep in mind, you can use SQLite in STRICT mode, if you feel it's appropriate for your application.
Hey, here at Mycelial, we're big fans of SQLite, and we're building tools around SQLite that will allow you to synchronize an SQLite database to multiple computers without conflict.
If you are interested in SQLite and what we're doing with it, please subscribe to our channel, and consider joining our newsletter to learn more."
Sign up for our newsletter to get updates delivered
