SQLite is not weakly typed!

One of the things you’ll often hear about SQLite is that it’s "weakly typed."

But let me tell you: SQLite isn’t weakly typed—it’s flexibly typed. Big difference!

In SQLite, the column types you define are more like suggestions. You can say a column is for integers, but SQLite will allow other types to slip in there unless you’ve set it to be strict.

(Prefer to watch instead of read? Check out the video version of this article on YouTube: SQLite is not weakly typed!)

Storing multiple types in a column

Let’s take a look at a non-strict table in action. (NB: non-strict is the default table type.)

Say we create a table, insert an integer into it, and then check the type of that column. SQLite will store and return it as an integer:

CREATE TABLE types (n INTEGER);
INSERT INTO types VALUES (1);
 
SELECT n, TYPEOF(n) FROM types;
 
-- | n  | typeof(n) |
-- | -- | --------- |
-- | 1  | integer   |

What happens if we insert a string into that same column? Let’s give it a try:

INSERT INTO types VALUES ('hello');
 
SELECT n, TYPEOF(n) FROM types;
 
-- | n      | typeof(n) |
-- | ------ | --------- |
-- | 1      | integer   |
-- | hello  | text      | 

We’ve just added a text value into a column we suggested was for integers! SQLite’s flexible typing lets you put anything in a traditional table.

The interesting thing is that when you insert a value, if SQLite can convert the input that you give it into the type suggested for that column (i.e. INTEGER in this case) without losing data, then it will do so. If it can't, it will store the value as is and will not convert it to the suggested type. Look at what happens when we insert a floating point number into the column:

INSERT INTO types VALUES (1.0);
INSERT INTO types VALUES (1.1);
 
SELECT n, TYPEOF(n) FROM types;
 
-- | n     | typeof(n) |
-- | ----- | --------- |
-- | 1     | integer   |
-- | hello | text      |
-- | 1     | integer   | 
-- | 1.1   | real      |

1.0 is stored as an integer because it can be converted to an integer without losing data. 1.1 is stored as a real because it can't be converted to an integer without losing data.

Why flexible typing can be a good thing

Is this behavior good or bad? Well... it depends. If flexibility is what you want, it’s fantastic. If you’re not expecting it, it might catch you off guard. But there are some situations where SQLite’s flexibility really shines.

Imagine you’re building a key-value store where the value could be a number, a string, or even a blob. SQLite’s flexible typing makes that easy. Or say you’re working with CSV files—people don’t always follow strict database rules when they create CSVs, so having a system that’s forgiving can save you a lot of headaches.

It’s also great for scenarios like user settings, where some settings are integers and others are strings. Flexible typing gives you the ability to store mixed data types in the same column, no problem.

What if you want strict typing?

What if you hate the idea of flexible typing? You want rules! Structure! Don’t worry—SQLite can do that too.

Let’s create a strict table and try entering a non-integer into column n:

CREATE TABLE types_strict (n INTEGER) STRICT;
INSERT INTO types_strict VALUES ('hello');

Error! You can’t store a text value in an integer column in a strict table. SQLite will enforce the types you declare.

The best of both worlds: strict and flexible

Here’s where it gets even cooler. What if you want some columns to be strict but others to be flexible? You can do that too! Here we have one column to strictly hold integers but another column to accept any type:

CREATE TABLE types_both (n INTEGER, value ANY) STRICT;
INSERT INTO types_both VALUES (1, 1);
INSERT INTO types_both VALUES (1, 'hello');

The n column is strictly integers, but the value column can be anything. You can only insert an integer into n and anything (a string, a float, a blob) into value. Flexibility and structure—best of both worlds!

SQLite is flexibly typed! And if you want strict typing? SQLite can do that too. It's up to you!

YouTube video

Check out the video version of this article on YouTube!