Earlier quoted context omitted.
A large portion of the tests are closed source unfortunately which would make it tough to create a port.
What the hell? This is the first I'm learning of this. Why did they do that? Is it owned by a private company?
Prefer strict tables in SQLite
121–130 of 188 posts
Re: Prefer strict tables in SQLite
#122Earlier quoted context omitted.
The need ‘default sets’ so that as the very first command I can say ‘use 2026.1 defaults’
Or even better just SET STRICT_TABLES, or whatever other opt-in feature you want. A couple of such statements in the beginning of your migration script (refactored in a way to be reused across all migration scripts) and you are done. I wouldn't want to learn WTF "2026.1 defaults" are.
I just do it in my code like
def create_table(name, cols): return f"CREATE TABLE {name} ({cols}) STRICT;"
Re: Prefer strict tables in SQLite
#123I want a better alternative for SQLite
Re: Prefer strict tables in SQLite
#124Earlier quoted context omitted.
> The downside of strict tables is that some data types are not available, such as Date. There are only 5 datatypes in sqlite. INTEGER, TEXT, BLOB, REAL, and NUMERIC. https://sqlite.org/datatype3.html
Right, and that's the problem. There should be DATE and BOOL as well, especially in strict mode.
Yeah the parse rules for strict tables are annoying but it doesn't change the underlying semantics:
sqlite> create table test (
id integer primary key,
created_at text default current_timestamp,
flag integer not null default 0 check (flag in (0, 1))
) strict;
sqlite> insert into test (flag) values (1);
sqlite> select * from test;
╭────┬─────────────────────┬──────╮
│ id │ created_at │ flag │
╞════╪═════════════════════╪══════╡
│ 1 │ 2026-07-12 04:03:22 │ 1 │
╰────┴─────────────────────┴──────╯Re: Prefer strict tables in SQLite
#125I don’t like strict mode because it quite unnecessarily thwarts better strict types in the application layer: by restricting the spellings of column types, it stops you from using more meaningful names and prevents code from using those names when mapping database and application types:
https://hn.algolia.com/?query=chrismorgan+strict+sqlite&type...
If you’re going to work with a database through something like the Rust sqlx crate, I think you’re better to eschew strict mode.
Re: Prefer strict tables in SQLite
#126Coming from the enterprise SQL world, I never took SQLite seriously for the very reason that field types were not enforced by default. (Yes, I was agog when it became the backbone for app metadata on smartphones.) Anyway, reading this reminds me of the old chestnut from networking about choosing UDP over TCP for its low-latency and simplicity and then eventually adding nearly all the reliability facilities of TCP to…
> Anyway, reading this reminds me of the old chestnut from networking about choosing UDP over TCP for its low-latency and simplicity and then eventually adding nearly all the reliability facilities of TCP to the app (automatic retry Sometimes you can build a successful business out of doing so: https://aeron.io/
For sure -- but these are the 'exceptions that prove the rule'. Someone else mentioned HTTP3 and QUIC. For all of these, there are thousands of projects (including multiplayer games and even enterprises) that didn't quite get it right and suffered for it.
Or, at least, so says the lore.
Re: Prefer strict tables in SQLite
#127https://sqlite.org/flextypegood.html explains why this isn't the default (and probably will never be the default). > rigid type enforcement can successfully prevent the customer name (text) from being inserted into the integer Customer.creditScore column. On the other hand, if that mistake occurs, it is very easy to spot the problem and find all affected rows. That doesn't line up with my experience. (In particular,…
These are similar to arguments that people made about MongoDB. You can store anything! And then most people who used it realized that this is actually terrible, in most cases. It looks like this is an artifact of when SQLite was written and the strong opinion of its author, less so a rigorous engineering principle. Reading this, it sounds like the author has been criticized a lot on this, is digging in their heels no…
I've seen a set of SQL tables designed to mimic "flexible classes". There's a table for the "class", another table defining its "fields", and two other tables defining class instances and related field values (all as varchar).
Flexible, yes. You can store anything. The downside is, that I've also found "anything". Stuff attached to the wrong "class", wrong datatypes, missing "obligatory" fields, etc, etc.
It's a PITA to work with. If I could design it from scratch, it'd be a single table with JSON payload.
Re: Prefer strict tables in SQLite
#128It really should be default, but it isn't due to backward compatibility (i assume).
It’s a stated [0] goal of the project: > SQLite strives to be flexible regarding the datatype of the content that it stores. [0]: https://sqlite.org/stricttables.html
Postgres is flexible, too. Sometimes design choices are just bad, and it's ok.
Re: Prefer strict tables in SQLite
#129[dead]
Re: Prefer strict tables in SQLite
#130Earlier quoted context omitted.
Well, I would also like a proper datetime/timestamp datatype that isn't just a string.
Why not store them as integers? Representing dates is a UI responsibility
Forcing all dates to be cast to a single time zone (UTC) complicates workflows when you’re on the other side of the globe. Many DBs have added a timestamp offset type for this to keep the data in the offset it occurred in.