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

121–130 of 186 posts

Re: SQLite should have (Rust-style) editions

#121

Post by tialaramex »

mikepurvis wrote: post #66
tptacek wrote: post #61

Earlier quoted context omitted.

SQLite is used in a lot of unconventional settings (for SQL databases) where these settings don't make as much sense. But that's what makes the "edition" useful; it captures the use case we all mean when we're thinking of the "database" lego in an application stack.

While that's true, editions are more about leaving legacy decisions behind while keeping the backward compatibility promise. Even if you're in one of those unconventional settings (say, a bare-metal microcontroller or something), you'd probably still start from edition 2026 and mutate your settings accordingly, rather than using the defaults that are 26 years old.

Yeah, that's important. Rust's 2015 edition is worse, not just different from what you'd write today with 2024 edition. There's a clear direction of travel.

Re: SQLite should have (Rust-style) editions

#122

Post by dspillett »

usefulcat wrote: post #56
pstuart wrote: post #41

Earlier quoted context omitted.

You are probably correct, but I imagine the SQLite team's dedication to backwards compatibility has things the way they are so that existing systems can user later versions a swap without worrying about changing the SQL using it.

If they’re opt-in, how could the new defaults be a problem for backwards compatibility?

GPP's wording suggests that the defaults simply be changed to what is being discussed as more generally sensible in current times, rather than being opt-in.

Given how many projects are potentially out there effectively relying on the current settings, and SQLite's general attitude to backwards compatibility, that would likely not be considered a good idea. Opting in with an edition flag for new (or updating) projects does seem like a good solution to this to serve all of old, active, and new projects, but it would increase potential bug surface area and therefor testing requirements, and the existing setting do allow all that to be opted in/out to/from already (and it is only four settings we are talking about here).

That FKs being enforced is set per-connection rather than at the database level is something that surprised me a lot when I found out. A way of setting that at DB creation (or via ALTER DATABASE after) seems like quite an omission because if you have multiple potential routes that can update the same DB any one of them could cause serious the others will encounter.

Re: SQLite should have (Rust-style) editions

#123

Post by homebrewer »

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.

Firebird can be embedded, although neither the database itself, nor the embedded mode are as popular as they once were.

It's a fully featured database though, with everything you expect from one, including actually working ALTER TABLEs.

Re: SQLite should have (Rust-style) editions

#124

Post by xigoi »

lenkite wrote: post #112
xigoi wrote: post #103

Earlier quoted context omitted.

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.

From what I can tell, DuckDB is more focused on huge-scale data analysis than simple data persistence, so I’m not sure if it fits my use cases. Otherwise, it looks good.

Re: SQLite should have (Rust-style) editions

#125

Post by mort96 »

LtWorf wrote: post #82
Cyberdog wrote: post #69

Earlier quoted context omitted.

I don’t understand why a local app database shouldn’t still have the same basic functionality and data guarantees as the full-sized ones.

You can just run postgresql locally like akonadi does if that's what you need

Like you want me to ship postgres with my app? I mean sure I could, but that seems very over complicated when sqlite works just fine except for some unfortunate defaults

Re: SQLite should have (Rust-style) editions

#127

Post by bruce511 »

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…

In SQLite the max int is 64 bit. So a rather large number (about 9 billion billion, aka 19 digits). That's a lot of rows to add.

I would suggest if you are storing that much data, SQLite may not be the correct engine. (And you probably shouldn't be using an Int primary key.)

It's a good question to ask, but probably not a concern for most of us.

Re: SQLite should have (Rust-style) editions

#129

Post by IceDane »

It's really weird to me how the SQLite author is clearly a very smart guy and talented developer and then his argument against type safety effectively just boils down to

> But I do not recall a single instance where the bugs might have been caught by a rigid type system.

Which is a shame. Of course the author writes more than this, but this is IMO largely the gist of the argument. At this point it's beginning to feel like this is mostly a sort of stubborn sunken cost fallacy, where they've been arguing this for so long they can't take the "hit" of agreeing to change the defaults.

Re: SQLite should have (Rust-style) editions

#130

Post by IceDane »

souvlakee wrote: post #29

Sadly, the ORM layer lags here: Drizzle has no way to declare STRICT tables. The request has been open since March 2023 (issue #202, now discussion #2435) and didn't make the v1.0 beta either. The only workaround is hand-appending STRICT to generated migration SQL, which doesn't work at all if you use `drizzle-kit push`. https://github.com/drizzle-team/drizzle-orm/discussions/2435

But push is not for use in production? It's for development. You generate a single custom migration that sets strict, apply it, and then you can use push as you want (during development, not for deploying actual changes to your database)
Post reply on HN