8 ms·
SQLite should have (Rust-style) editions
- SnehRJoshi 3mo ago[flagged]
- KomorKomor 3mo ago[flagged]
- mort96 3mo agoOh hi, author here. Fun to see this make it to HN.
- simonw 3mo agoSuggestion: post this on https://sqlite.org/forum/forum https://sqlite.org/forum/forum - the SQLite team monitor that forum closely and I've had some really great answers from them to questions or suggestions in the past.
- quadhome 3mo agohttps://lobste.rs/c/kzln1c https://lobste.rs/c/kzln1c
- mort96 3mo agoI haven't really ever participated in that forum before, but it's an interesting idea. I don't know if I'm going to do it, I might. Though I also wouldn't mind if someone else posted about it there. I might even make a forum account and participate in the discussion.
- rmunn 3mo agoFound what appears to be a minor mistake you may want to fix: "This means that a dangling reference easily results in a reference to the wrong column" should probably be "... a reference to the wrong row". (In the paragraph about SQLite's tendency to re-use ROWIDs).
- mort96 3mo agoYou're right, thanks. Fixed
- kccqzy 3mo agoSQLite is slightly different from Rust in that it is a data container. It’s somewhat more common for people to move SQLite database files from one machine to another and then inspect using the command line tool. And it is often the case that the embedded SQLite version in your app is a newer version than whatever version /usr/bin/sqlite3 happens to be. Adding editions to your SQLite file will probably break this use case of using an older version to read a database written by a newer version because it does not know what has changed in a new edition. Not a big deal though. Probably just need better ops to bundle the command-line utility that’s the same version as what’s used in your app.
- tptacek 3mo agoFor some of these pragmas you have the same issue with or without "editions", right? Busy timeout is per-connection. And then: if you're running in WAL mode, you, the user, have to know that, or risk messing up the database by copying just the .db file rather than vacuuming-into.
- kccqzy 3mo agoEditions make the problem worse by requiring the version not only to support the underlying pragmas but also to understand the edition mapping. Example: PRAGMA foo=1 is introduced in 2027. PRAGMA edition=2030 implies this foo pragma. Now you unnecessarily lock out three years worth of releases.
- sethev 3mo agoInteresting idea - I like seeing a list of pet-peeves followed by a proposal for a straightforward way to have a set of 'alternative defaults' that remains backwards compatible. If you don't want to opt in, don't run the new PRAGMA edition = 2026. Too often it's just a list of issues and a wish that everyone else will change. In (mild) defense of SQLITE_BUSY - busy_timeout just tells sqlite to sleep and retry up to the timeout when it receives SQLITE_BUSY. It seems like a sensible default for a library to leave that up the calling code - which may have something else it could do while it waits. However, that logic often gets missed!
- tptacek 3mo agoThis 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.
- sethev 3mo agoYes, agree. These are very sane defaults and match what I use..
- doc_ick 3mo agoI’d say these are reasonable settings for most uses. Though do you know of surveys that back this up? I don’t mean to nit pick too much, I’d just like to see common uses and the data.
- tptacek 3mo agoSQLite 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.
- mikepurvis 3mo ago
- Rendello 3mo agoIt might be worth bringing this up on the forum [1]. The developers are quite active there, and it's possible they've never considered this option, or they have considered it and have reasons to not go for it. The original design followed Postel's Law (see my comment from the other say [2]), it would (theoretically) be nice if that mess could be avoided by specifying an edition. Today I noticed I could do `pragma foreign_key = ON`, and despite the pragma being wrong (it should be foreign_keys, plural), it reported nothing. In fact, it reports nothing with the correct pragma either. So check your pragmas! 1. https://sqlite.org/forum/forum https://sqlite.org/forum/forum 2. https://news.ycombinator.com/item?id=48900625 https://news.ycombinator.com/item?id=48900625
- Polizeiposaune 3mo agoThe 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/postconf.5.html#compatibility_level https://www.postfix.org/COMPATIBILITY_README.html 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.
- IshKebab 3mo agoI 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_required.html https://cmake.org/cmake/help/latest/command/cmake_minimum_re...
- mort96 3mo agoI 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.
- andai 3mo agoIn the first example, there's a a second thing that surprised me: you delete an entity and it's unique ID gets reused? Is that a good idea? I guess if foreign keys are handled properly then that's not a problem by definition? But it sounds wrong somehow.
- deepsun 3mo agoI think that's a security vulnerability. If a parent table ID gets reused, then it's a potential to expose data to a wrong user -- security broked.
- zarzavat 3mo agoThat's correct but SQLite was never designed to be a production database in the first place. It can be used as a production database but only if you know what you're doing, and presumably anyone who knows what they're doing knows about the AUTOINCREMENT keyword because it's one of the first things you learn about SQLite.
- nick__m 3mo agoI disagree, SQLite is a production embedded database, the extensive test¹ suite is a testament to that. It's just has different default and behavior than a database designed for being served to multiples simultaneous writers. 1- https://sqlite.org/testing.html https://sqlite.org/testing.html
- bruce511 3mo ago>> you delete an entity and it's unique ID gets reused? Is that a good idea? That's default behavior, but it can be altered when creating a table. See; https://sqlite.org/autoinc.html https://sqlite.org/autoinc.html
- LtWorf 3mo agoWhere do you see that they get reused from that link?
- andai 3mo agoThe "use strict" thing is interesting. I often hear people say, well we can't fix absurd behavior in JS because backwards compatibility! Well, we already did, and we can do it again!
- ShinyLeftPad 3mo ago"use stricter"
- rmunn 3mo ago"use loose; footloose; kick off your Sunday shoes"
- test6554 3mo ago“hold my beverage”;
- crabmusket 3mo ago"use strong" was a proposal from Google https://docs.google.com/document/d/1Qk0qC4s_XNCLemj42FqfsRLp49nDQMZ1y7fwf5YjaI4/view https://docs.google.com/document/d/1Qk0qC4s_XNCLemj42FqfsRLp...
- andai 3mo agoI became hopeful for a moment, then saw it's from 2015. Ouch! (Also, I love the name. I had a similar idea I called "use sane", but obviously that one wouldn't have gotten far...) The strong typing thing is really interesting. After using JavaScript for a while, I developed PTSD around dynamic types. I became convinced that static typing was the only way to avoid hell. Then I used Python for a while, and... experienced approximately none of my previous pain. I found that quite odd. Turns out what I was actually after was a sane type system, not a static one. In other words, strong types rather than weak ones. I do think there are additional benefits to static typing, especially for larger projects and serious work. But I was surprised that most of the pain-delta was in this first jump: Weak -> Strong -> Static
- postepowanieadm 3mo ago
- Thaxll 3mo agoSQLite gets so much praise here but when you start using it, you realize quickly how bad it is, the type system is by default very limited and dangerous. It's like comparing old php with a strongly typed language. There is not even a date type...
- kccqzy 3mo agoIt’s not as bad since you can always use a powerful programming language with a good type system that avoids type errors at the SQL level. You can build good abstractions in your programming language.
- groundzeros2015 3mo agoSQLite competes with fopen. Not Postgres
- wang_li 3mo agoIt’s curious how many people don’t understand what SQLite is and its intended feature set. They get huffy that it’s not a full client server model with multimaster clustering across 8 data centers on 12 continents plus New Zealand with realtime synchronous replication. It’s a product that allows you to do sql like things without a database server. If you need to have database server behavior, you’re using the wrong product.
- lbourdages 3mo agoWell, it goes both ways. You'll see articles saying essentially "you don't need Postgres or any other fancy database, SQlite is enough" while ignoring the fact that some use-cases warrant a more conventional DB server. Different tools for different situations!
- groundzeros2015 3mo agoI think this critique was traditionally about the LAMP stack. Imagine how many engineering years would have been saved if Wordpress ran on SQLite, - no db user configuration - no installing multiple tenants in the same db - no phpmyadmin (ftp db files) - no remote database hacks - no backup tools
- souvlakee 3mo agoSadly, 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 https://github.com/drizzle-team/drizzle-orm/discussions/2435
- deleted 3mo ago[deleted]
- IceDane 3mo agoBut 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)
- chillfox 3mo agoyou can also set those defaults with compile time flags, that's what I have been doing.
- Retr0id 3mo agoAn alternative is to use wrapper libraries that set sane defaults, e.g. https://rogerbinns.github.io/apsw/bestpractice.html https://rogerbinns.github.io/apsw/bestpractice.html But I suppose it would be nice to have a standard way to refer to those defaults, in a cross-runtime way.
- andrewchambers 3mo agoIt is sqlite3. Emphasis on the 3 - it already has 'editions'.
- Retr0id 3mo agoThe "3" refers to the file format (or rather, represents a breaking file format change vs 2), which the devs have committed to keep backward-compatible until 2050. https://sqlite.org/lts.html https://sqlite.org/lts.html
- andrewchambers 3mo agoIt also refers to what the binary on my PATH is called, also what the library name I need to pass to link against it. They even had an sqlite4: https://sqlite.org/src4/doc/trunk/www/design.wiki https://sqlite.org/src4/doc/trunk/www/design.wiki
- anitil 3mo agoOooh I'd forgotten about that! I'm keen on the real (non-rowid) primary keys and covering indexes. I'm not sure about defaulting to decimal math, but I suppose the reasoning makes sense
- GianFabien 3mo agoNah, all those defaults are features. Of course, there are contexts where those defaults are unsuitable which means: Use a Different RDBMS!
- Rohansi 3mo agoChange RDBMS instead of changing configs from their default value? They're configurable for a reason.
- Cyberdog 3mo agoAre there any other serverless SQL DBMSes?
- chuckadams 3mo agoLots. Firebird and DuckDB come immediately to mind. Wikipedia lists several more, though not all of them are relational: https://en.wikipedia.org/wiki/Embedded_database https://en.wikipedia.org/wiki/Embedded_database
- Cyberdog 3mo agoThanks for the suggestions. I did some brief research on those two and DuckDB in particular looks enticing. I'll have to remember to give it a try on my next side project.
- linncharm 3mo ago[flagged]
- redsocksfan45 3mo ago[dead]
- eduction 3mo agoHN… for the love of god… please please stop trying to make SQLite be something it isn’t. Leave this poor project alone. It’s a great tool if you want to give a local app its own database. If you need concurrent writes and full ACID guarantees of an industrial strength database, use an industrial strength database. Yes, other databases will require you to read more manual pages and configure a service. Higher up front cost. Not “lightweight.” But given enough operating time there is a certain unarguable lightness to using the right tool for the job.
- yomismoaqui 3mo agoJust by its testing and its number of installations (real world testing) you could consider SQLite being more "industrial strength" than any other DB on the market. - https://sqlite.org/testing.html https://sqlite.org/testing.html - https://sqlite.org/mostdeployed.html https://sqlite.org/mostdeployed.html EDIT: added links
- eduction 3mo agoExactly. There is no reason to complain about it. It is successful. People who find it lacking can use something else, there are many options.
- Cyberdog 3mo agoI don’t understand why a local app database shouldn’t still have the same basic functionality and data guarantees as the full-sized ones.
- deleted 3mo ago[deleted]
- tmpfile 3mo agoWhy not a .conf file like everything in /etc or postgresql.conf?
- hmry 3mo agoThese proposed editions are per connection. A system-wide config file in /etc that changes defaults for every program would break any program that assumes the old defaults. It also wouldn't solve the problem of having to manually find out what the current recommended defaults are. With editions, you can simply enable the latest one and know you've got the right defaults.
- tmpfile 3mo agoI should have been more clear, I meant a sqlite.conf can configure a program rather than have it apply globally. For example, a config file placed in same directory as its .wal file to tune it for specific instances. That way you don’t need to lookup what “editions” apply which pragmas or settings. With sqlite.conf you can tune your specific database connections by uncommenting the default settings to enable current features/best practices
- IshKebab 3mo agoSQLite isn't typically a global one-per-system database, and even if it was how would that solve this problem? The problem isn't that you can't set all these settings to the right values - it's that they don't have the right values by default.
- mgc_blackbox 3mo ago[flagged]
- lasjk7 3mo ago[flagged]
- vanyaland 3mo agoOn iOS most apps link Apple's prebuilt libsqlite3, so compile-time defaults are out of reach.
- henryoman 3mo agobeen working on a new implementation entirely of a local sqlite like database. It's from scratch in rust and data is made up of TSV's so the data is human (and agent) readable. sql querying is more expensive for an llm
- azeirah 3mo agoOh I really like this! The one counterargument that comes to mind is when I think about the likes of C++, where there are many editions and they can be confusing to keep track of. I'm not sure if you'd want to set one edition in stone every year. Perhaps every 3 years? Or 5 years? Especially for a long-term project like SQLite, that sounds perfectly acceptable!
- spwa4 3mo agoYou know, 10 years ago one might have remarked ... changing these defaults means C programmers would have to correctly implement error handling and retries. The reaction they're likely to have to that can probably be best described in megatons, like any other nuclear explosion ...
- bambax 3mo ago> I don't think I need to explain why it's a bad idea for a database to be so careless about data validation. Well, loose typing can be extremely useful, and having a type of "ANY" would not replace it. I have built recently an accounting reconciliation system to find discrepancies in data coming from a large variety of sources: some from proper database engines (MySQL MariaDB), but most from proprietary systems that export to CSV. It's amazing how corrupt data can become: dates that are invalid, numbers that aren't numbers, strings strings strings everywhere. Being able to store the data into tables that have types, but can accept anything, is simply great.
- poly2it 3mo ago> loose typing can be extremely useful "Loose typing" enforced in a strict typing system can be useful in certain scenarios, but it is regrettable that it instead replaced the strict typing discipline for some time in software. Strict typing should be the default, because it is the most accurate description of data in the vast majority of cases.
- scadge 3mo ago> I don't think I need to explain why it's a bad idea for a database to be so careless about data validation. ...Meanwhile MongoDB being successful for years with no sign of decline.
- mickeyp 3mo agoYour... solution to bad ETL data is to go "let's keep it this way"? You can already "store whatever you want" in a serious database that respects types by default. It's called a blob or if you must, a text/varchar.
- bambax 3mo agoYes! keeping it that way helps with traceability. The point is not to fix the data, it is to understand where corruption happens.
- amluto 3mo agoAre you saying that you have an application where you want the loose-typing-with-integer-affinity semantics for a column (or some other particular affinity)? It would be entirely reasonable to have a specific type for each loose-with-affinity variant. But I don’t think those should be the default.
- PunchyHamster 3mo agoI 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.
- mort96 3mo agoWhat 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.
- ncruces 3mo agoThis 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?
- Rygian 3mo agoThe 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.
- ncruces 3mo ago> The section "The solution: editions?" in the article addresses directly the point of existing data. I'm sorry, where? SQLite schema is stored as text. If you change the default interpretation of CREATE TABLE with a PRAGMA, your existing tables become STRICT, but (1) they might now have columns with invalid types (which means you have an invalid schema, and your database fails to open), (2) they may have invalid data for their strict types (which you can only figure out with a full table scan, PRAGMA integrity_check will complain). This was discussed previously on the SQLite forum, you can read the team's position there: https://sqlite.org/forum/forumpost/0248dcf7f0ece9fb https://sqlite.org/forum/forumpost/0248dcf7f0ece9fb Regarding busy_timeout, why is 5s specifically a better default? You did not engage with my argument: that 5s is no different from 1s or 60s. How do you decide? Also discussed in the forum, with the team laying out the rational; https://sqlite.org/forum/forumpost/f0da30efa661bd9c https://sqlite.org/forum/forumpost/f0da30efa661bd9c I think the minimum is considering the arguments by the people who promise to maintain the software for the next 25 years. PS: I actually really like the idea of `CREATE TYPE alias = type` for use with STRICT tables. I would champion that feature request on the forum. Given how schema is saved, I disagree with making it the default. Having to mark your tables STRICT is not such a burden, IMO.
- vugar82 3mo ago[flagged]
- ak39 3mo agoOff-topic: TIL wal2 mode in the works. Wow. That's a no-brainer feature, IMHO.
- latexr 3mo agoAssuming the author is here (they link to the HN submission from the post), you have a typo: “laudbile” instead of “laudable”.
- IceDane 3mo agoIt'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.
- WhereIsTheTruth 3mo agoWho are these tourists? D will get caught into that edition trap too All it does is fragment a ecosystem, bloat a source tree and makes maintenance a painful task only to please people who think maintainers should cater to their poor tech hygiene
- daynthelife 3mo agoEditions do not fragment the ecosystem at all. A crate written in rust 2015 can depend on a crate written in rust 2024 and vice versa. There are no forced upgrades. The only maintainers it causes a burden for are the compiler developers (and tech debt within the compiler). But this is pretty much unavoidable if the language is to evolve while remaining backwards compatible -- e.g. it's not like the c++ compiler has a lower rate of tech debt accrual.
- red_admiral 3mo agoI think the OP wants duckdb. The first two points are deliberate SQLite design decisions so they're unlikely to change.
- deskamess 3mo agoWhat are the defaults for duckdb? Is it the pragma 2026 equivalent?
- red_admiral 3mo agoI can't speak for speed, but foreign keys and data type checking are on by default.
- mort96 3mo agoNo, I actually like SQLite with the settings tweaked a tiny bit. Disagreement with the defaults isn't enough to make me switch databases completely
- weberer 3mo agoDuckDB is a OLAP database. Its optimized for batch analytics, not for individual transactions. Its great for data science work, but I wouldn't use it for a production system.
- dolmen 3mo agoCounterpoint: each new edition will add bloat to SQLite as future edition will need to keep each past edition pragma set for backward compatibility. As SQLite is often used embedded, bloat matters. So I suggest that "PRAGMA edition" to be only be a shortcut to a list of PRAGMA commands, that would be expanded at the library level: PRAGMA edition would never appear in the DB file. As such, the build of the library would just support a limited set of editions, with a removal policy in default builds. Maybe editions could even be defined at runtime (a system table?) as a way to load them dynamically if old editions are needed beyond builtin support (think about the state of SQLite in 2046).
- Hendrikto 3mo agoIsn’t that exactly what the author suggests? Editions as a set of config options that are already there. The config options are already implemented, so the heavy lifting is done. Editions would amount to a few hundred bytes.
- ncruces 3mo agoThat's certainly not the case for making STRICT tables the default. But figuring that out requires understanding how they are implemented, or reading the forum, which the author admittedly didn't do. The rest are assumptions about best practices (also not shared by the developers of SQLite).
- Bratmon 3mo agoIt's always really funny to me when commenters don't read the article and then phrase the article's point like they invented it.
- tzot 3mo ago> I once had to clean up a project where some code had accidentally been writing the strings '1' and '0' to a column which was intended to store booleans (1 and 0). That was not a fun debugging story. So this was a write to a column that did not have INTEGER affinity. If it was intended to be used as a boolean, then it should have INTEGER affinity. I know because I've tried hard to enter integer- and float-like strings as strings in INTEGER affinity columns, and I haven't managed to; I could only insert them as BLOBs, or prefix the string with say '\' and check/remove at the application level. (That was for an ontology-like database, where table EAttribute.eatvalue could have any type.)
- jmull 3mo agoI actually think this wouldn’t be that helpful. The developer still needs to ensure they apply their set of pragmas, whether that’s a single edition pragma or a set of pragmas. And they still need to understand and carefully choose the pragmas/options they use (an edition really makes this a little harder by abstracting/hiding something that needs to be directly understood and visible). And these proposed new default pragmas are more incremental improvements rather than complete solutions, more convenience than critical. E.g., while the default affinity is goofy, strict tables don’t come close to a comprehensive validation mechanism — so if you need strong validation you’re likely going to need to implement that at a higher level anyway. (Also, there’s a decent separation-of-concerns argument that you should handle it separately.) Likewise the busy timeout. The pragma is convenient but you still need to handle busy timeouts. (The author’s problem, “I didn’t realize busy timeouts could happen so the app didn’t handle them correctly”, is not solved by the pragma.)
- tiaisliar 3mo ago[flagged]
- SaturnIC 3mo agoRust is a cancer
- ian-g 3mo agoIt sounds like you're also advocating for some form of permanent pragma that gets stored in the SQLite file and that the CLI will read and apply on startup? That way somebody can snag a copy of your data and be subject to the same constraints by default.
- throwaway2037 3mo ago> Bad default #3: SQLITE_BUSY errors with concurrent writers This is a weird complaint to me. When I use SQLite, I always make sure there is a single writer thread. Any thread can submit a write request in a thread-safe queue. If you follow this pattern, you never need to worry about SQLITE_BUSY errors.
- mort96 3mo agoAnd your scripts for rare tasks you haven't gotten around to making a GUI for also go through that queue? And your manual interactions with the database to fix problems? Or do you just risk crashing the program when you do those things?
- hommelix 3mo agoThe oldest `editions` idea I know of, is with Perl. For 20 years it was already common to write `use 5.18` to activate multiple options. https://perldoc.perl.org/functions/use https://perldoc.perl.org/functions/use
- alexwennerberg 3mo agoI don't have a problem with setting a few relevant flags for any new SQLite database. It's a good encouragement to familiarize yourself with the settings.