Prefer strict tables in SQLite

Post by hamburglar » Sun, Jul 12, 2026, 4:36 PM UTC

throwlifeaway wrote: post #169

simonw wrote: post #95

> Unfortunately, I don’t think there’s a way to ALTER a table to make it strict. I think you have to copy the data out of the non-strict table into the strict one. This inspired me to add a feature to my sqlite-utils Python library and CLI tool, so you can now use it to transform non-strict tables to strict (and vice-versa) like this: uvx sqlite-utils transform data.db mytable --strict Or in Python: import sqlite_uti…

> This inspired me to add a feature to my sqlite-utils Python library "me" == "ChatGPT", apparently: > Can the .transform() internal Python method turn a non-strict table into a strict table? > No. Table.transform() preserves the table’s existing strictness; it cannot change it. Its signature has no strict= parameter > add an optional strict= boolean parameter to the transform() method - if it is None (the default) t…

Post by mburns » Sun, Jul 12, 2026, 9:02 PM UTC

inigyou wrote: post #150

echelon wrote: post #110

Earlier quoted context omitted.

What the hell? This is the first I'm learning of this. Why did they do that? Is it owned by a private company?

That's one way they make money. That's the reason sqlite exists.

Post by frollogaston » Sun, Jul 12, 2026, 9:35 PM UTC

win311fwg wrote: post #120

frollogaston wrote: post #101

Earlier quoted context omitted.

But one DB one app is fairly common with Postgres too, particularly if you're adhering to a services deliniation. Guess if your code enforces types at DB insert time, the DB doesn't need to, but one of those is more likely to change than the other.

> But one DB one app is fairly common with Postgres too That is common today, but remember that Postgres is now 40 years old. It is so old that it was originally based on QUEL rather than SQL. Back then database servers were designed to be what we now think of as the "API server". That necessitates runtime input validation same as your "API server" needs input validation today. If you were designing Postgres from scr…

Post by inigyou » Sun, Jul 12, 2026, 11:04 PM UTC

mburns wrote: post #174

inigyou wrote: post #150

Earlier quoted context omitted.

That's one way they make money. That's the reason sqlite exists.

In a 2021 podcast interview[0], Dr. Hipp noted they've sold zero copies of the TH3 (extensive, proprietary) test suite in SQLite's history. By the time TH3 was added in 2008, SQLite had gained a fair bit of traction across multiple industries. Though I totally agree that the comprehensive coverage is a leading reason why they've been so stable over the last ~20 years. So indirectly, TH3 is why they (continue to) exis…

Post by win311fwg » Mon, Jul 13, 2026, 2:33 AM UTC

frollogaston wrote: post #176

win311fwg wrote: post #120

Earlier quoted context omitted.

> But one DB one app is fairly common with Postgres too That is common today, but remember that Postgres is now 40 years old. It is so old that it was originally based on QUEL rather than SQL. Back then database servers were designed to be what we now think of as the "API server". That necessitates runtime input validation same as your "API server" needs input validation today. If you were designing Postgres from scr…

Even with a statically typed language, two separate codepaths or code versions could write to the same tables and disagree on the types.

    CREATE TABLE foo (
        bar INTEGER
    );

    fn main() {
        stmt := "INSERT INTO foo (bar) VALUES (?)"
        if (now() % 2 == 0) {
            db.exec(stmt, [1]); 
        } else {
            db.exec(stmt, ["Baz"]); // Static analysis fails here. Input is not an integer.
        }
    }

    CREATE TABLE foo (
        bar TEXT
    );

    fn main() {
        stmt := "INSERT INTO foo (bar) VALUES (?)"
        if (now() % 2 == 0) {
            db.exec(stmt, [1]); // Static analysis fails here. Input is not text.
        } else {
            db.exec(stmt, ["Baz"]);
        }
    }