I'm fairly confident this is AI generated, but it makes me think regardless: Whenever I see these kind of articles, I'm left wondering if they've actually used SQLite in production because I always see points about how to optimize performance, like using the WAL, but never about annoyances/issues you'd run into before even needing to worry about that. I guess it's the zeitgeist to use it in a production setting, and…
A handy trick for column types is check constraints.
You can define constraints on a column that ensure it is text that's valid JSON for example:
CREATE TABLE documents (
id INTEGER PRIMARY KEY,
data TEXT NOT NULL
CHECK (
json_valid(data)
json_type(data) = 'object'
)
);
CREATE TABLE documents (
id INTEGER PRIMARY KEY,
data TEXT NOT NULL CHECK (
json_valid(data)
AND json_type(data) = 'object'
AND json_type(data, '$.name') = 'text'
AND json_type(data, '$.age') = 'integer'
)
);
CREATE TABLE events (
id INTEGER PRIMARY KEY,
occurred_on TEXT NOT NULL CHECK (
length(occurred_on) = 10
AND occurred_on GLOB
'[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'
AND date(occurred_on, '+0 days') = occurred_on
)
);