The Design of SQLite4 (2014) | Hacker News Reader

The Design of SQLite4 (2014)

sqlite.org

41 pointsiamwil13 comments

> SQLite4 was an experimental rewrite of SQLite that was active from 2012 through 2014. All development work on SQLite4 has ended. Lessons learned from SQLite4 have been folded into the main SQLite3 product. SQLite4 was never released. There are no plans to revive it. You should be using SQLite3.

Oh, wow, that's right. That note is here: https://sqlite.org/src4/doc/trunk/www/index.wiki

I love this concept. Work on a rewrite and then fold lessons learned methodically into the existing, stable, proven one.

Did they fold the LSM idea? What about the single global namespace in which all records live?

I feel like that should be in a banner at the top of the page OP linked to!

I don't see why. It's on the SQLite4 Home page: https://sqlite.org/src4/doc/trunk/www/index.wiki The OP's link is to the first subpage, The Design of SQLite4. If you're putting it there, you might as well put it on every page.

EDIT: It looks like something that mostly addresses my problem, “strict tables”, was added in 2021. Thank you everyone.

It seems this rewrite would not have addressed my biggest gripe with SQLite: Surprisingly, it is not actually type safe.

    SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container.

https://www.sqlite.org/datatype3.html

That’s right, the type you give to a column when creating a table is merely a suggestion. This means at some point, TEXT may make it into a column where a NUMERIC is expected.

This actually caused a large scale problem in production once.

I still love SQLite, it’s the very best at what it does, but I’m a bit more careful now. The same documentation says SQLite uses dynamic typing so that you can “do more things” than with other databases, but for me this is akin to saying you can “do more things with assembly than with a high level language” (in both cases that’s mostly not even true).

I expect my SQL database to keep data from taking the wrong form. It looks like the SQLite 4 rewrite would at least have enabled constraints by default…

I think many would appreciate a “no surprises” strict, type safe mode in SQLite 3, for me this would complete it. Maybe that exists already?

Yes, create your table with a trailing strict statement.

> I think many would appreciate a “no surprises” strict, type safe mode in SQLite 3, for me this would complete it. Maybe that exists already?

It does, somewhat. https://www.sqlite.org/stricttables.html

You're looking for [strict tables](https://www.sqlite.org/stricttables.html) and they do already exist :-)

Added in 3.37.0 (2021-11-27), apparently :-)

Strict tables were added with 3.37.0, does that help? https://www.sqlite.org/stricttables.html

One issue I've run into while using strict tables is that since sqlite does not (yet?) have a dedicated type for timestamps, a driver like github.com/mattn/go-sqlite3 use the typename in the schema to determine when a column can be converted to a native time datatype. But STRICT tables only allow 6 specific typenames: INT, INTEGER, REAL, TEXT, BLOB, ANY

> One issue I've run into while using strict tables is that since sqlite does not (yet?) have a dedicated type for timestamps, a driver like github.com/mattn/go-sqlite3 use the typename in the schema to determine when a column can be converted to a native time datatype. But STRICT tables only allow 6 specific typenames: INT, INTEGER, REAL, TEXT, BLOB, ANY

I wish they’d implement CREATE DOMAIN, like Postgres has. Using it you can define a named custom type from a base type, optionally with associated CHECK constraints. A minimal implementation would skip the constraints (and DEFAULT and collation) and just let you do CREATE DOMAIN timestamp AS INT (or TEXT if you’d rather do it that way), and thereafter STRICT tables could have timestamp as column type

Previous comment of mine which explains this in more detail: https://news.ycombinator.com/item?id=29367517

Related. Others?

The Design of SQLite4 - https://news.ycombinator.com/item?id=31785170 - June 2022 (19 comments)

Work on SQLite4 has concluded - https://news.ycombinator.com/item?id=15648280 - Nov 2017 (157 comments)

SQLite4: The Design of SQLite4 - https://news.ycombinator.com/item?id=10104964 - Aug 2015 (2 comments)

The Design Of SQLite4 - https://news.ycombinator.com/item?id=7797025 - May 2014 (67 comments)

The Design Of SQLite4 (work in progress) - https://news.ycombinator.com/item?id=4168645 - June 2012 (49 comments)

Reply on news.ycombinator.com