On November 27, 2021, SQLite released version 3.37.0. No performance doubling, no flashy new features — just the ability to add a single keyword at the end of a CREATE TABLE statement: STRICT.
What does it mean? In plain English: from that day forward, SQLite finally learned to do one thing — refuse to store a name in a phone-number column.
By this point, SQLite was already 21 years old. And it’s the invisible database inside virtually every app on your phone.
The Hidden Foundation in Your Phone
First, let’s clear up a common misconception: SQLite is not an app you can download from an app store. There’s no “SQLite” icon on your phone. It’s a database engine — hiding inside apps, silently managing data storage.
WeChat chat history, Alipay transaction records, Douyin video cache, your phone contacts, browser-saved passwords, offline map packages — they all run on SQLite.
By reliable estimates, over a trillion SQLite databases are running simultaneously worldwide. No other database comes close. It’s the undisputed global champion.
But this champion has an almost unbelievable quirk: it doesn’t check the types of data you store in it, at all.
”Age = ‘Zhang San’? No problem, come on in.”
What does “doesn’t check types” mean? Let me use a real-world analogy.
You walk into a bank to open an account. The teller hands you a form. One field says “Age,” another says “Name.” You write “Zhang San” in the age field and “42” in the name field. In any normal database, the teller would push the form back: “Sir, age must be a number, name must be text.”
SQLite in default mode is the teller who glances at it and says flatly: “Sure, whatever. Age is ‘Zhang San’? Stored. Name is ‘42’? OK. Your choice.”
In code: you create a table declaring the “age” column as INTEGER and the “name” column as TEXT. Then you run:
INSERT INTO Users (age) VALUES ('I am not a number');In MySQL or PostgreSQL, this statement throws an error immediately. In SQLite? It succeeds. No warning. Your “age” column now peacefully hosts a text value that says “I am not a number.”
This isn’t an edge case. On July 11, 2026, developer Evan Hahn published a blog post titled “Prefer STRICT tables in SQLite” that scored nearly 200 points and 89 comments on Hacker News. The comments section was full of developers sharing their own horror stories of falling into this trap.
Table 1: STRICT vs. non-STRICT behavior comparison
| Operation | Non-STRICT (default) | STRICT mode |
|---|---|---|
Insert 'abc' into INTEGER column (text into numeric column) | ✅ Accepted | ❌ Error |
Insert '123' into INTEGER column (numeric text, losslessly convertible) | ✅ Accepted | ✅ Accepted |
Column type declared as GARBAGE (typo / nonexistent type) | ✅ Accepted | ❌ Error |
Insert any type into ANY column | ✅ Accepted | ✅ Accepted |
CREATE TABLE without column types | ✅ Accepted | ❌ Error |
| Allowed type names | Unlimited | INT, INTEGER, REAL, TEXT, BLOB, ANY |
A Philosophical War That Lasted 20 Years
This wasn’t an oversight, or laziness. It was a deliberate design choice by SQLite’s creator, D. Richard Hipp. The SQLite website has an entire page called “The Advantages of Flexible Typing” defending the no-type-checking approach.
To understand the root of this choice, you have to go back to 2000. Hipp was working for a Navy contractor, needing a lightweight database for shipboard systems. The options on the market were either too heavy or required a server — completely impractical on a warship. So he wrote his own.
One key influence was TCL — Hipp’s favorite programming language. TCL is dynamically typed: programmers don’t need to declare variable types upfront; everything can be treated as a string. Hipp brought that philosophy into SQLite: you declared column types? Fine, but that’s just a suggestion. You decide what actually goes in.
For the next 20 years, the database community was locked in a debate over whether flexible typing was a feature or a bug.
The pro-flexible-typing camp (Hipp and his team) had three core arguments:
First, “I’ve written software for 35 years and never seen a single bug caught by type checking.” Hipp wrote in the official docs that in decades of developing TCL and SQLite, he couldn’t recall a single program failure caused by the lack of type constraints. His conclusion: type checking is useful in low-level languages like C and C++ that deal with hardware directly — in a SQL engine where all data is passed as “value objects,” type checking doesn’t help.
Second, “Type checks only catch trivial errors that are easy to find anyway.” This is a sharp argument: putting a name in an age field is indeed caught — but it’s so blatant it would be exposed by the most casual test. The bugs that cost you three days of debugging are swapping first and last names — both text, type checking won’t see it. Hipp argues that type checking gives developers a false sense that “the data is clean.”
Third, “Flexibility lets you do things other databases can’t.” Things like using a single table as a key-value store for any type, repurposing abandoned columns for multiple uses, or loading dirty CSV exports from Excel directly into the database and cleaning them later.
The opposition’s rebuttals were equally strong:
“It’s precisely those ‘trivial’ errors that become the needle you can’t find in a million-row haystack. Type checking was never meant to catch bugs you’d find during debugging — it’s meant to prevent that 3 AM production incident where there’s no error in the log but user data is systematically corrupt.”
“You say you’ve written 35 years of code without seeing a type bug? SQLite itself is written in C — you’re enjoying C’s type checking every time you compile it. You rely on strict type systems to keep SQLite itself error-free, but tell the rest of us that type checking doesn’t matter?”
One HN comment that got cited repeatedly: “This is like replacing TCP with UDP — dropping data validation for speed and simplicity, then manually adding retransmission, ordering, and verification at the application layer. When you’re done, you’ve just built a worse TCP.”
Another commenter put it more bluntly: “Tweaking defaults for performance — acceptable. Tweaking defaults for correctness — unsettling.”
What STRICT Mode Actually Does
Back to November 2021. The STRICT keyword does three things:
1. Rejects type-mismatched writes. Inserting text into an integer column? Error. Inserting a number into a text column? Accepted — because numbers can be losslessly converted to text. Inserting the string '123' into an integer column? Also accepted — because '123' converts perfectly to the integer 123. STRICT cares about whether the value can be losslessly converted, not just surface-level types. In this regard, it’s actually smarter than many strictly-typed databases.
2. Rejects fictional column types. In non-STRICT mode, if you declare a column type as GARBAGE, DATETIME, JSON, UUID, or BLOBB (typo of BLOB), SQLite silently accepts them all and treats them as generic types. In STRICT mode, only six types are recognized: INT, INTEGER, REAL, TEXT, BLOB, ANY. Accidentally type BLOBB instead of BLOB? Caught on the spot.
3. Use ANY when you need flexibility. STRICT isn’t all-or-nothing. Declare a column as ANY, and it accepts any data — just like default mode. The difference: flexibility is opt-in where you need it, not the default everywhere.
Why Did It Take 21 Years?
From 2000 to 2021 — 21 years. Why did such a basic validation mechanism take two generations of engineers’ careers to arrive?
The answer lies in SQLite’s core promise: backward compatibility.
SQLite’s developers have an almost obsessive rule — any SQLite code you write today must run 100% correctly ten years later after an upgrade. This means default behavior can never change. Change it, and a trillion active SQLite instances around the globe could break.
Figure 2: SQLite type safety evolution timeline
2000 ─ SQLite 1.0 released, flexible typing as core philosophy
│
│ "Column types are hints, not constraints"
│
2009 ─ SQLite 3.6.19: foreign key constraint syntax supported
│ But disabled by default — must manually PRAGMA foreign_keys = ON
│
│ Next 12 years: STRICT mode proposed and debated repeatedly
│ But always blocked by the "backward compatibility" iron rule
│
2021 ─ SQLite 3.37.0: STRICT table support
│ Add STRICT keyword at end of CREATE TABLE — per-table opt-in
│ No global toggle — still "you choose if you want enforcement"
│
2026 ─ Evan Hahn posts: "Prefer STRICT tables in SQLite"
│ HN 199 points, 89 comments — debate continuesThree milestones spanning 21 years, each following the same principle: new features are fine, but default behavior never changes.
This isn’t an isolated case. Foreign key constraints — preventing you from deleting a user while leaving a thousand “orphan orders” in the orders table — SQLite supported the syntax back in 2009, but it’s still off by default. Every time you open a database connection, you have to manually run:
PRAGMA foreign_keys = ON;to activate foreign key checks. Same reason: changing defaults would break backward compatibility.
One HN commenter proposed a middle ground: like browsers, declare COMPAT_MODE=2026 when creating a database, and new versions automatically enable recommended settings for that era. So far, not adopted.
Another comment captured the dilemma perfectly: “SQLite very, very rarely changes defaults because their backward compatibility promise is nearly sacred. They don’t want someone’s software written for SQLite 3.53 to explode after upgrading to 3.54 because CREATE TABLE suddenly became STRICT.”
This perfectly sums up SQLite’s tension: the evolutionary drive to “keep getting better” versus the sacred vow to “never change.”
SQLite’s Success Came From Not Caring
At this point, a counterintuitive question naturally arises: if SQLite has so many “default-unsafe” designs, why is it the most popular database in the world?
The answer is in its design philosophy. SQLite’s success comes largely from not caring about things other databases insist on.
No installation. No server. No configuration file. A few-hundred-KB library embedded in an app and it just works. Don’t check your data types — store whatever. Don’t enforce foreign keys — that’s your problem. Don’t worry about transaction isolation levels — just get it running.
The payoff for this minimalism: you can embed SQLite in phones, browsers, IoT sensors, routers, smart TVs, car infotainment systems, airplane entertainment systems — and it never complains about the environment, never demands resources, never fails to start.
It’s a universal power outlet — any plug fits. Whether it shorts out? Not my problem.
The arrival of STRICT mode means this database that “didn’t care” for 21 years finally acknowledged a reality: when your user base grows from a few dozen professional C programmers to millions of app developers of wildly varying skill levels, default “freedom” becomes default “risk.”
Epilogue
SQLite’s history, viewed in the larger arc of software engineering, is a microcosm of an entire industry gradually maturing.
Early software was built for a small number of professional users. The design philosophy: “maximum freedom, and if something breaks it’s your problem.” Today’s software serves billions of ordinary people, and the design focus has shifted from “freedom” to “safety” and “fool-proofing.”
STRICT mode isn’t an exciting technical breakthrough — it does something MySQL and PostgreSQL have done since day one. But the fact that it arrived 21 years late silently speaks to a deeper truth: many of the “basic features” we take for today were earned through decades of industry accumulation, debate, painful mistakes, and retrospection — bit by bit.
Next time your phone app quietly stores data into SQLite in the background, think about this: the invisible champion that has faithfully worked thousands of days and nights inside your device took 21 years to learn a skill that a human child masters in kindergarten —
Don’t put shoes in the cereal bowl.
References:
