Strict vs Weak types in SQLite
When you create a table in SQLite, by default it’s loosely typed contrary to traditional databases like PostgreSQL or MySQL. Now, allow me to explain what that means.
Shall we?
Weak types by default
Let’s say you’ve created a table called users with the following schema.
CREATE TABLE users (
id INTEGER,
name TEXT
);
Now, let’s add a new user to the table.
INSERT INTO users VALUES (1, 'John Doe');
This will work just fine and if you fetch the record back, you’ll get the following result.
SELECT * FROM users;
/*
┌────┬──────────┐
│ id │ name │
├────┼──────────┤
│ 1 │ John Doe │
└────┴──────────┘
*/
Let’s try to add a new user but this time we’ll feed a string (“two”) to the id column.
INSERT INTO users VALUES ('two', 'Jane Doe');
You might be thinking SQLite won’t let you do that but SURPRISE! SURPRISE! The insertion will be successful without any error or warning. Now, let’s fetch the records back.
SELECT * FROM users;
/*
┌─────┬──────────┐
│ id │ name │
├─────┼──────────┤
│ 1 │ John Doe │
│ two │ Jane Doe │
└─────┴──────────┘
*/
This is because tables in SQLite are not strict by default to maintain flexibility and compatibility with a wide range of applications and use cases. This can be useful in various scenarios, such as when dealing with semi-structured data or when the schema is expected to evolve over time.
So, what would you do if you want to enforce strict typing in your SQLite tables?
We can make “strict” tables.
Strict tables
To create a strict table, you can use the STRICT keyword when creating the table.
CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT
) STRICT;
Now, let’s try to add a new user with a string value for the id column.
INSERT INTO users VALUES ('one', 'Jon Doe');
/*
Runtime error: cannot store TEXT value
in INTEGER column users.id (19)
*/
As you can tell, this will result in a runtime error and the record will not be inserted. And this is all you need if you want to enforce strict typing in your SQLite tables.
But what if you want a strict table but still want some columns to be weakly typed?
Enter “any” datatype.
The ANY datatype
SQLite lets you define a column with the ANY datatype. The ANY datatype enables you to store any values from integer, float, text, blob, and their datatypes will be preserved exactly as they are inserted.
CREATE TABLE users (
id INTEGER,
name ANY
) STRICT;
Now that the users is strict and has a name column with the ANY datatype, you can insert any value into the name column while still enjoying strict typing on the rest of the columns.
I learned about these concepts courtesy of this video by Aaron Francis!
Like this article?
Buy me a coffee
👋 Hi there! I'm Amit. I write articles about all things web development. You can become a sponsor on my blog to help me continue my writing journey and get your brand in front of thousands of eyes.
