Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

SQLite should have (Rust-style) editions

mort.coffee

Post reply on HN
Print view View original

111–120 of 186 posts

Re: SQLite should have (Rust-style) editions

#111

Post by ncruces »

This changes one default that "everyone agrees about" and which you can change with a compile time option: SQLITE_DEFAULT_FOREIGN_KEYS

Then it argues for STRICT tables, recognizing that there are drawbacks without introducing a new feature (custom type aliases, CREATE TYPE alias = base).

If also doesn't even considering what it means for existing data to make tables strict, which is precisely why “there is no pragma to globally make all tables strict”.

Then it argues for setting a busy timeout, and picks 5s. Why? Why 5s and not 1 or 60s? SQLite doesn't decide, which makes perfect sense. Your OS or programming language also doesn't offer you locks with a default timeout: it's either indefinite, or an instant "try lock".

Finally: WAL mode is a different file format, unsupported on many platforms, in more danger of silent corruption. Why should it be the default?

Re: SQLite should have (Rust-style) editions

#112

Post by lenkite »

xigoi wrote: post #103
lenkite wrote: post #77

Earlier quoted context omitted.

I agree with you. There are 2 dozen foot-guns to be kept in mind. And discovered a new footgun regarding multi-byte strings and NUL handling today on HN. SQLite became popular because it was the only free and open-source choice 2 decades ago. Now there are other type-safe and robust choices.

What are other choices for FOSS serverless relational databases? I’ve been looking everywhere and couldn’t find anything.

DuckDB ? Strict by default and excellent for logs, telemetry, dashboard apps, etc.

Re: SQLite should have (Rust-style) editions

#114

Post by Rygian »

ncruces wrote: post #111

This changes one default that "everyone agrees about" and which you can change with a compile time option: SQLITE_DEFAULT_FOREIGN_KEYS Then it argues for STRICT tables, recognizing that there are drawbacks without introducing a new feature (custom type aliases, CREATE TYPE alias = base). If also doesn't even considering what it means for existing data to make tables strict, which is precisely why “there is no pragma…

The section "The solution: editions?" in the article addresses directly the point of existing data.

The way I read it, this article does not advocate at any point to change the defaults for existing databases, but rather to start with better defaults for new databases.

Also, regarding the timeout of 5 seconds, I disagree with your premise "SQLite doesn't decide, which makes perfect sense". As the article explains, SQLite decides on the value zero (ie. instant error), which is arguably an inconvenient default.

Re: SQLite should have (Rust-style) editions

#115

Post by tialaramex »

Animats wrote: post #67
tptacek wrote: post #5

Earlier quoted context omitted.

This isn't so much a list of pet peeves as it is the almost universal way people that work seriously with SQLite configure the database. It's reasonable to suggest that the alternative settings for each of these suggestions is probably the wrong default for 2026.

> It's reasonable to suggest that the alternative settings for each of these suggestions is probably the wrong default for 2026. That's the key concept here. When tightening up the defaults, an "edition" mechanism is a good solution. Now we need this for C/C++, which have much legacy stuff which ought to go away for new code. This is more feasible than it used to be, because "Convert this Edition 4 code to Edition 5"…

> Now we need this for C/C++

P1881 Epochs proposed to WG21 (the C++ standards committee) in 2019 by Vittorio Romeo

The committee found plenty of problems with this, and made it clear that if Vittorio did all the hard work to resolve those problems they would find more, P1881 was abandoned.

There was a Reddit thread https://www.reddit.com/r/cpp/comments/1tja9zr/c_profiles_a_c... which suggested that the "Profiles" idea Bjarne is pushing for C++ 29 could be used to deliver this.

So, you're not the first person to notice that this is a good idea, P1881 was written after Rust's 2018 Edition, but before 2021 Edition with its even more significant improvements. I firmly believe Rust's Editions unlock not only technical possibilities (though it does certainly do that) it unlocks an appetite from users which is good for your ecosystem.

Re: SQLite should have (Rust-style) editions

#116

Post by [deleted] »

LtWorf wrote: post #106
echoangle wrote: post #87

Earlier quoted context omitted.

> The normal ROWID selection algorithm described above will generate monotonically increasing unique ROWIDs as long as you never use the maximum ROWID value and you never delete the entry in the table with the largest ROWID. If you ever delete rows or if you ever create a row with the maximum possible ROWID, then ROWIDs from previously deleted rows might be reused when creating new rows and newly created ROWIDs might…

What should it do once you hit MAXINT? Honest question…

[deleted]

Re: SQLite should have (Rust-style) editions

#117

Post by mort96 »

IshKebab wrote: post #91
Polizeiposaune wrote: post #19

Earlier quoted context omitted.

The Postfix mailer has allowed recommended default behavior to evolve using its "compatibility_level" parameter: https://www.postfix.org/postconf.5.html#compatibility_level https://www.postfix.org/COMPATIBILITY_README.html You get a warning whenever you depend on the deprecated old default until you either move forward or specifically commit to the old behavior.

I think CMake actually has the best default evolution system out there (a bit surprising give how awful the actual language is). Each "policy" they change can be manually set to old or new, and there's a global config to set them all at once based on the version of CMake. https://cmake.org/cmake/help/latest/command/cmake_minimum_re...

I scarcely go a week without encountering issues caused by their huge CMake 4 backward compatibility break. I think CMake has one of the worst solutions out there.

Re: SQLite should have (Rust-style) editions

#118

Post by groundzeros2015 »

afiori wrote: post #98
groundzeros2015 wrote: post #33

Earlier quoted context omitted.

Yep. The whole locking database thing is this persistent myth about SQLite. All databases lock on write, it’s a question of the granularity of the lock. Multiple writers simply take turns.

yes but no... most database allow for at least as many parallel and concurrent writes as there are tables at a minimum. The "lock on write" problem is that in MySQL i could run a OLAP pipeline for a few hours and have a fully functioning database with degraded perfomance, on SQLite the same pipeline would lock the database for the full hour. (there are surely ways to solve this (eg using the main db as read-only and…

The “myth” is you can’t use it for a website because if you have multiple requests that need to write they can’t do it concurrently. (They just take turns of course for the milliseconds of write time.)

If you run a transaction with writes for an hour on any database, the data you update will literally be locked. So your example only works if results are independent of the data other programs want to use.

Of course more granularity of locking is better and enables more designs that would not otherwise work. But somewhere you run into the same problem of writers taking turns.

Re: SQLite should have (Rust-style) editions

#119

Post by mort96 »

PunchyHamster wrote: post #104

I think the solution is for author to use PostgreSQL Those choices were made for specific reasons that make sense in embedded environment and when backward compatibility is no.1 concern. But I wouldn't mind feature-sets. Editions are too wide of a concept and tell you nothing at glance what a given code is doing, "enable 2026 set of features" tells me nothing on what is actually enabled.

What are the specific reasons for why it makes sense in embedded systems to not enforce foreign key constraints or to let me insert a blob into an integer column? Because I work with embedded systems and have never found those defaults to make sense.

My proposal does not harm backwards compatibility in the slightest.

Re: SQLite should have (Rust-style) editions

#120

Post by Certhas »

afiori wrote: post #100
appplication wrote: post #20

Earlier quoted context omitted.

I think my silly and unserious naive response would be linear scaling bundled binaries with number of platforms doesn’t seem to be that materially different in terms of total size. But I see your point, didn’t consider the architectures

it would be sorta enough to bundle the wasm binary

That's the idea behind the future file format:

https://github.com/future-file-format/F3

Bundle a wasm decoder as a fallback when native decoder isn't available.

Post reply on HN