Changing SQLite's defaults

[Wukuyon]

The announcement for Limbo last December discussed SQLite’s accumulation of tunables, such as the choice between WAL mode and journal mode. It mentioned that Limbo eliminates less-important SQLite features while maintaining compatibility with SQLite’s bytecode and file format.

While SQLite has many warts to keep backward compatibility with old query code, if Turso is willing to modify old query code, as long as bytecode/file compatibility is maintained, these warts could be removed to enhance the user experience with Turso Limbo. Here’s a non-exhaustive list of possible changes.

1. Defaulting to wal mode (or wal2 mode when it stabilizes) instead of journal mode.
2. Enabling foreign key constraints by default. They are currently disabled for backward compatibility with old code.
3. Enforcing NOT NULL constraints on primary keys by default in ordinary tables, not just tables WITHOUT ROWID. Early versions of SQLite, due to a bug, did not enforce the NOT NULL constraint on PRIMARY KEY columns. By the time this bug was discovered, SQLite could not fix it without compromising backward compatibility. Fixing this might pose a challenge for Turso to modify unless SQLite introduces a flag in the bytecode for ordinary tables to opt into enforcing NOT NULL on their primary keys.
4. Turning on PRAGMA recursive_triggers by default. SQLite currently disables recursive triggers for backward compatibility with old code, and it may enable them in a future version.
5. Disabling PRAGMA trusted schema by default. SQLite enables this for backward compatibility with old code, but “all applications are encouraged to switch this setting off on every database connection as soon as that connection is opened.”
6. Making columns with INT PRIMARY KEY, BIGINT PRIMARY KEY, etc. an alias for the rowid: right now only an INTEGER PRIMARY KEY column is an alias for the rowid.
7. Most controversially: making tables STRICT tables by default.

Except for change 3 and maybe change 6, I don't think any of these changes would break compatibility with SQLite’s bytecode or file format. Of course, you would have to decide about each potential improvement on a case by case basis. But do you have an overall strategy for these kinds of improvements? And would it be better to make a thread about each of these potential changes?

[penberg]

@Wukuyon I don't know if there can be an overall strategy because I think it's all very situational. What we want is drop-in replacement level compatibility for existing SQLite databases, so that's one factor. Of course, for new databases, we can certainly use different defaults, but even then we need to balance between developer experience and performance, for example. I think each of the suggestions you bring up make sense as individual issues that we can discuss and decide on individually.