4 ms·
I'd like to see STRICT as the default. That's pretty much the only disagreement with the SQLite developer, who is an amazing guy that wrote an amazing tool!
by jll29 3mo ago
I'd like to see STRICT as the default.
That's pretty much the only disagreement with the SQLite developer, who is an amazing guy that wrote an amazing tool!
- mort96 3mo agoYeah it's a really weird design decision. Why would I want the database to let me accidentally insert the wrong type? SQLite is mostly great but its philosophy towards type safety leaves something to be desired. I once had to clean up in a project where someone had accidentally stored the strings '1' and '0' in a Boolean column in code deployed to thousands of devices; not fun. Another thing I dislike is the lack of timestamp types. Instead, you're expected to just use a text column and store a textual timestamp. Even worse, instead of using ISO, the standard date time functions produce strings on the form "yyyy-mm-dd HH:MM:SS" which you're just supposed to assume are in UTC. Why not at least give us "yyyy-mm-ddTHH:MM:SSZ"? Or, you know, a proper space efficient timestamp data type. A truly great project, with some truly baffling design decisions.
- masklinn 3mo ago> Instead, you're expected to just use a text column and store a textual timestamp. You can actually use an integer column and store Unix timestamps (or floats for subsecond accuracy). But yes, sqlite has very little types support and its default behaviour is very much unityped / dynamically typed which I also dislike. Same with having to enable foreign keys every time you open a connection.
- ncruces 3mo agoOnly if you're not compiling yourself, otherwise, there's SQLITE_DEFAULT_FOREIGN_KEYS=1
- mort96 3mo agoOf course you can store timestamps as integers, but then you lose sqlite's built-in date and time functions.
- masklinn 3mo agoYou do not. SQLite date/time functions work with time-values which can be ISO timestamp strings, Julian fractional day numbers, or Unix timestamps. See https://sqlite.org/lang_datefunc.html https://sqlite.org/lang_datefunc.html for the details.
- formerly_proven 3mo agoIf every time the SQLite team added a better default for something they changed it, we'd be at SQLite 11 by now and every application would contain at least six incompatible versions of SQLite.
- 3eb7988a1663 3mo ago'0' and '1', while not ideal, seems fine to maintain? There is not even a true SQLite boolean type. Then again, I have been subjected to Oracle nonsense for too long and have had to accept all of the boolean alternatives: 0,1,'0','1',Y,N,y,n,YES,NO,T,F, etc
- chasil 3mo agoWhen you need many booleans on a table, use an integer with bitwise and/or to record them as powers of 2. I have done this many times. Function-based indexes are necessary if they must be searched.
- echoangle 3mo agoWhy would you do that? To save some bytes in storage?
- chasil 3mo agoThe largest table where I've done this had over 50 of these booleans. That's one 64-bit number, or 50 different columns.
- echoangle 3mo agoBut if you do the function based index, you actually increase the stored size, right? Seems like a microoptimization to me that I would only do if I really have to.
- chasil 3mo agoFor the searched column only? If you are greatly concerned, SQL Server and the Sybase database from which it emerged have a native boolean data type. Another benefit of this scheme is that adding another boolean means using the next power of 2 in the existing integer, assuming room remains. No new column necessary.
- 3mo ago
- win311fwg 3mo agoIf you are using SQLite as an embedded database, which seems to be SQLite's primary use-case, why wouldn't you prove statically that you are not accidentally inserting the wrong type? Runtime checks are unnecessary overhead. Runtime validation is there to enable when using SQLite in other ways.
- bbkane 3mo agothe SQLite team explains their preference for dynamic types here: https://sqlite.org/flextypegood.html https://sqlite.org/flextypegood.html (please note that I personally strongly prefer static types, but I still found this an interesting read).
- cowboylowrez 3mo ago"SQLite began as a TCL extension that later escaped into the wild." case closed, everything else is a rationalization but who doesn't like a good rationalization every now and then?
- simonw 3mo agoSQLite very rarely changes defaults because of their commitment to backwards compatibility. They don't want software written against SQLite 3.53 to start throwing errors when upgraded to 3.54 because suddenly `CREATE TABLE` is creating strict tables and the rest of the software breaks as a result.
- ezst 3mo agoWhich seems reasonable. And those who care deeply will have no problem configuring it the specific way they want on their own project. Win-win.
- sinfulprogeny 3mo agoUnless you don't know it exists of course
- walthamstow 3mo agoEven a pure AI naysayer can use it to find obscure SQLite options
- alwa 3mo agoIs it better to learn that it exists through surprise, by way of an engine upgrade suddenly changing the behavior of the software you wrote while in the bliss of your ignorance?
- esterna 3mo ago... than by surprise, when you find your integer column contains "[object Object]"? Jokes aside, breaking backwards compatibility is obviously a non-starter.
- dwattttt 3mo agoWhich is the worse surprise, when you update your dependency during development and get a fault, or when a user somehow ends up with a fault because of the previous default?
- ahartmetz 3mo agoThere are more similar issues, like disabling foreign key constraints by default "for compatibility reasons". Makes me wonder if there was a time when SQLite supported foreign key syntax, but didn't actually implement the functionality.
- formerly_proven 3mo ago> This document describes the support for SQL foreign key constraints introduced in SQLite version 3.6.19 (2009-10-14).
- ahartmetz 3mo agoThat quote leaves open whether SQLite "pretended" to support foreign keys by allowing to create tables with them, but didn't implement them. Otherwise, I don't see the compatibility problem.
- azornathogron 3mo agoYes. From the release notes: 2002-06-17 (2.5.0), "Parse (but do not implement) foreign keys." At one point there was also a tool which would generate trigger rules to enforce foreign key constraints. (2008 Oct 15 (3.6.4), Added the source code and documentation for the genfkey program for automatically generating triggers to enforce foreign key constraints)
- ahartmetz 3mo agoThank you. Strange decisions, but not completely baffling then.
- neverartful 3mo agoI agree with you. I'd go one step further and let it be the only mode available starting with new versions of the library.
- panzi 3mo agoWell, I would also like a proper datetime/timestamp datatype that isn't just a string.
- echelon 3mo agoSomeone should fork it in Rust using Claude and rename it SQRite. Strict type all the things.
- edoceo 3mo agoWhy not ~Zoidberg~ Zig?
- redman25 3mo agoA large portion of the tests are closed source unfortunately which would make it tough to create a port.
- echelon 3mo agoWhat the hell? This is the first I'm learning of this. Why did they do that? Is it owned by a private company?
- mjmas 3mo agohttps://www.hwaci.com/ https://www.hwaci.com/ See https://news.ycombinator.com/item?id=23511151 https://news.ycombinator.com/item?id=23511151
- inigyou 3mo agoThat's one way they make money. That's the reason sqlite exists.
- mburns 3mo agoIn a 2021 podcast interview[0], Dr. Hipp noted they've sold zero copies of the TH3 (extensive, proprietary) test suite in SQLite's history. By the time TH3 was added in 2008, SQLite had gained a fair bit of traction across multiple industries. Though I totally agree that the comprehensive coverage is a leading reason why they've been so stable over the last ~20 years. So indirectly, TH3 is why they (continue to) exist and (are able to) make money, but it isn't a direct line as one might assume. [0] https://corecursive.com/066-sqlite-with-richard-hipp/ https://corecursive.com/066-sqlite-with-richard-hipp/ [1] https://youtu.be/5zQdYx-fqJg?t=300 https://youtu.be/5zQdYx-fqJg?t=300
- Rendello 3mo agoEven foreign keys aren't enabled by default, you have to use `PRAGMA foreign_keys = ON;` [1]. The bigger issue with strict tables is that there is no equivalent pragma, and you're forced to use the non-standard STRICT on each CREATE TABLE. A global STRICT pragma was considered but not implemented, see this forum thread [2]. 1. https://sqlite.org/foreignkeys.html https://sqlite.org/foreignkeys.html 2. https://sqlite.org/forum/forumpost/1b9d073a37ca5998 https://sqlite.org/forum/forumpost/1b9d073a37ca5998
- Animats 3mo agoYes. I always considered that a downside of SQLite. You have to validate numeric fields on the read side or risk the application blowing up on bad data.
- m0nacle 3mo agolmao default okay buddy
- lenkite 3mo agoSQLite has a LOT of footguns that one only discovers over time. Dynamically typed by default, Off-by-Default foreign keys, ID re-use in AUTOINCREMENT, WAL Mode needing explicit enabling to ensure readers are not blocked, double-quote/single-quote issues, positional placeholders & named parameters issues, no TIMESTAMP type in 2026 despite being a CORE feature in SQL-92 standard, etc.
- bhaak 3mo agoI knew most of he issues that you mentioned ... but 'ID re-use in AUTOINCREMENT'? What the ...?
- cowboylowrez 3mo ago"Enforce authoritarian type-checking when inserting new content into tables" lol you can really get into the mindset by reading these threads. I think having the strict defaultable at the database level seems like a good option, maybe they think everyone will pile into the new default and leave the old databases in a state of decaying disrepair? My only experience with versionitus type things is with microsoft sql and other "enterprisey" things that used it and its hard enough without huge upgrade blockers like text in an integer field lol still they're willing to add the default to the table create statement. I'm thinking that sqlite folks feel that things like type safety, database consistency are disliked authoritarian attributes, and I'm sure thats a consideration in letting old code touch databases that old code doesn't understand. Its wierd that they didn't like having a pragma for any new table creation even at the database level but they allow this problem factory: "Because of a quirk in the SQL language parser, versions of SQLite prior to 3.37.0 can still read and write STRICT tables if they set "PRAGMA writable_schema=ON" immediately after opening the database file, prior to doing anything else that requires knowing the schema." https://github.com/simonw/sqlite-utils/issues/344#issuecomment-981997973 https://github.com/simonw/sqlite-utils/issues/344#issuecomme... I guess for ID reuse a rationalization could be that theres only so many integer values that can fit into a certain number of bytes haha