Comment by formerly_proven

This is the fault/price of backwards compatibility. Most users of SQLite should just fire off a few pragmas on each connection:

    PRAGMA journal_mode = WAL
    PRAGMA foreign_keys = ON
    # Something non-null
    PRAGMA busy_timeout = 1000
    # This is fine for most applications, but see the manual
    PRAGMA synchronous = NORMAL
    # If you use it as a file format
    PRAGMA trusted_schema = OFF

You might need additional options, depending on the binding. E.g. Python applications should not use the defaults of the sqlite3 module, which are simply wrong (with no alternative except out-of-stdlib bindings pre-3.12): https://docs.python.org/3/library/sqlite3.html#transaction-c...

Also use strict tables. https://www.sqlite.org/stricttables.html

While it has bad ergonomics, you can also use CHECK constraints. For example, using sqlite's built in date support, it's possible but awkward:

    CHECK (
      date(my_date_col) IS NOT NULL
      AND my_date_col = date(my_date_col)
    )

The IS NOT NULL is needed because date returns NULL for invalid dates; the other check because it also accepts Julian days (date('2026') is sometime during year 4707 BC).