6 ms·
Prefer strict tables in SQLite
- tehlike 3mo agoIt really should be default, but it isn't due to backward compatibility (i assume).
- tuvix 3mo agoI’m kind of curious why the decision to have implicit casting like this was made in the first place. I can’t think of a single upside other than not having to type out cast(foo as bar)
- pstuart 3mo agoIIRC, the project started out as TCL code and it carried that vibe through to what it is today.
- notRobot 3mo agoSQLite docs: The Advantages Of Flexible Typing: https://sqlite.org/flextypegood.html https://sqlite.org/flextypegood.html
- rogerbinns 3mo agoSQLite was originally started as a local database library for use during development for times when the main networked database was not available. It used dbm as the underlying storage mechanism, with the dbm API roughly being string keys with string values. ie all underlying values were actually stored as strings. The SQLite code would automatically do conversions - eg the plus operator would convert the strings to int or float, add them, and generate a stringified number as a result. The vast majority of implementation code did not have to care about types, and very local decisions could be made such as in the addition example. TCL was used as a dev wrapper language at the time, and it functioned the same way. It was only in mid-2004 that SQLite 3 was released which used its own storage backend, and that allowed for the 5 supported storage types (int64, string, bytes, float, null). It was API compatible (with minor adjustments) with the earlier SQLite 2, so the lack of static typing continued, otherwise everyone would have to rewrite their code. You do get dynamic typing, which hasn't been a problem for the vast majority of SQLite users. Do remember that SQLite is competition for fopen, not Oracle / Postgres etc. It is trying to make things as effective as possible in that scenario. If you don't want numbers in your string column, then don't do that!
- deleted 3mo ago[deleted]
- somat 3mo agoIf I remember correctly mysql also started with a berkeleydb(dbm) storage layer. before myisam then later innodb. I used to sort of dismiss berkeleydb(why so simple?), but a disk backed b-tree indexed key value store is not trivial to get right and having a prebuilt library to do it provides a huge value.
- SoftTalker 3mo agoOpenLDAP also originally used BerkeleyDB
- mb7733 3mo agoIt's even worse than implicit casting, if the value can't be cast to the the column's type, it's just inserted without casting. Eg. into an integer column, '10' -> 10 and '1O' -> '1O'
- rogerbinns 3mo agoThat is documented behaviour - think of it as making a best effort, and not losing the value. As of January 2006 you could add CHECK constraints using the TYPEOF function to reject that at the SQL level. And it is your own code - there is no server - doing the insertions. As was common back then, protecting you from your own bugs was not a high priority for APIs!
- poidos 3mo agoIt’s a stated [0] goal of the project: > SQLite strives to be flexible regarding the datatype of the content that it stores. [0]: https://sqlite.org/stricttables.html https://sqlite.org/stricttables.html
- masklinn 3mo agoYou can be flexible with strict tables, type every column as ANY and you pretty much get back the original behaviour.
- poidos 3mo agoSure, but you lose the representation of the developer’s intention that way. I would be pretty pissed off if I inherited a project and the schema was all ANYs.
- masklinn 3mo agoThe intent of ANY is obviously that the values be flexible. That’s why it’s there if you need it.
- drdexebtjl 3mo ago“I intended this to be an integer but it could really be anything” is not very useful.
- poidos 3mo agoSure it is. If you encounter something that’s not an int, that could be a signal you have a bug in your writers. Or in the source of the data. That’s useful information compared to “oh, I have some ints and some strings, that’s ANY, everything is ok.”
- FridgeSeal 3mo ago> If you encounter something that’s not an int, that could be a signal you have a bug in your writers Which, is something you could have caught before it got written at all if you had your db enforcing your types.
- cdmckay 3mo agoI would’ve thought this was the default.
- itsthecourier 3mo agoabout the use of ANY, that's perfect for tracking changes on an audit table per field
- jll29 3mo agoI'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.
- Eswo 3mo agoreally interesting, thanks
- moron4hire 3mo agoUsing Entity Framework, this doesn't come up as a particular issue, but I still wish it were strict by default because I expect there could be some performance optimizations made for de/serialization.
- blixt 3mo agoI think I can see how dynamic data types make sense (eg flat key/value store), but my question would be: What is least surprising? That INTEGER implicity accepts 'hello world' without error, or that you can't insert such a value unless you use a keyword like NONSTRICT or a type like ANY? I would wager the vast majority of SQLite users if asked would probably not expect it to work.
- frollogaston 3mo agoIt's probably because SQLite intends to be untyped but also wants the statements to look like standard SQL. This matches their other note about wanting code designed to work with other DBMSes to accidentally work with SQLite too. Otherwise, yeah, it's very surprising to explicitly put INTEGER and still be able to insert text. It's not like the user left the type out.
- petilon 3mo agoThe downside of strict tables is that some data types are not available, such as Date. Strict should really be the default. If a database is shared by multiple applications then you should be able to rely on the declared data type. If one application stores a string into a numeric column that breaks everyone else. On the other hand, the main use case for SQLite is embedded databases. And that means only one application is using the database. In that scenario being able to evolve the schema (as opposed to creating a new database and copying the data over) can be seen as an advantage. The application's code knows what to expect in each column--including mixed data types.
- Ciantic 3mo agoSQLite has no date data type. Also SQLite has no way to call EXPLAIN on query and get the dummy type name either for arbitrary SELECT query, so you can't even infer it, if you were to use the dummy type name "DATE" or "DATETIME".
- masklinn 3mo ago> some data types are not available, such as Date. That’s not a type, you just get a numeric-affinity column.
- petilon 3mo agoRight, and that's a serious limitation when in strict mode.
- masklinn 3mo agoThe serious limitation is that you create a column as date, you don’t understand what sqlite does with it, and you start storing strings in there, at which point everything is confused. You can use comments to preserve intent in strict mode, and that’s strictly more useful than fuzzy mode: it is richer, it is clearer, and it is no less reliable.
- e2le 3mo ago> The downside of strict tables is that some data types are not available, such as Date. There are only 5 datatypes in sqlite. INTEGER, TEXT, BLOB, REAL, and NUMERIC. https://sqlite.org/datatype3.html https://sqlite.org/datatype3.html
- dzonga 3mo agothe only thing that sucks about SQLite is migrations.
- bbkane 3mo agoYes, the process at https://www.sqlite.org/lang_altertable.html https://www.sqlite.org/lang_altertable.html is super risky - 12 steps and a giant CAUTION sidebar about the data loss possible if you do it incorrectly. They DO include a nice section at the bottom about why these limitations exist, but I wish they would make the process easier.
- wmanley 3mo agoWe use this for migrations: https://david.rothlis.net/declarative-schema-migration-for-sqlite/ https://david.rothlis.net/declarative-schema-migration-for-s... Discussed here: https://news.ycombinator.com/item?id=31249823 https://news.ycombinator.com/item?id=31249823 But it would be a lot better if it were built in.
- Cyberdog 3mo agoIf you're stuck with an older version of SQLite and/or want to enforce order on an existing table without creating a new table with STRICT and then copying all your rows over and/or also want to do things like enforce signedness, int size, or char/varchar length on a field like you can in other DBs, you can use CHECK constraints. CREATE TABLE users ( user_id CHAR(36) NOT NULL PRIMARY KEY CONSTRAINT user_id_length CHECK (LENGTH(user_id) = 36), email_address VARCHAR(255) UNIQUE CONSTRAINT email_address_length CHECK (email_address IS NULL OR LENGTH(email_address) < 256), role UNSIGNED TINYINT(1) NOT NULL CONSTRAINT role_valid CHECK (role >= 0 AND role <= 9) ) Note that the column types here are just to describe to the user what the field should be doing and it's the constraints that actually enforce it. Behind the scenes SQLite still creates two "text (supposedly but whatever)" and one "integer (supposedly but whatever)" columns. It's a little frustrating that all this extra cruft is necessary to get the world's most popular RDBMS to take data correctness seriously. I hope that some SQLite fork that behaves more like other RDBMSes when it comes to this stuff catches on some day, but the fact that that hasn't happened yet makes me think that the demand isn't there, somehow, unfortunately. https://sqlite.org/lang_createtable.html#ckconst https://sqlite.org/lang_createtable.html#ckconst
- deleted 3mo ago[deleted]
- bch 3mo agoI had a UUID (partly?) mis-converted to a number if the UUID started w (from memory) something like 08123… which was parsed as octal. Confusing, annoying, fixed w “strict” and a complete table rebuild.
- gunapologist99 3mo agoThat was your language's driver "helping" you. There is no SQLite octal type.
- bch 3mo agoI'll see if i can find the case - my description was a bit hand-wavy because it was a while ago, and just drawing on memory -- not that your explanation couldn't be true, but I thought I exercised that possibility. For your part, you'll remain sceptical it wasn't the driving language when I tell you it was Tcl, which is indeed the origin of this "manifest type" we're discussing :)
- bch 3mo agoI can't replicate atm, but I now think it had to do w scientific notation, not octal - so "1e234..." into a text column. And istr "strict" solving my problem, but...
- sherburt3 3mo agoI really hate this trend of turning every piece of software into this kafkaesque monstrosity that demands you jump through 100 hurdles to do the simplest thing. I mean yeah its good for LLMs but as a human it gets kind of annoying. I honestly love that if you hand SQLite garbage it will do its best.
- m0nacle 3mo agothis 100%
- frollogaston 3mo agoYeah my DB is the one place I want strict types. Well also RPCs. But SQLite is a somewhat different set of use cases, so maybe I'd understand https://sqlite.org/flextypegood.html https://sqlite.org/flextypegood.html more if I were using it. Like there's a point about random scripts not made for SQLite happening to work with it, which isn't normally a consideration for other DBMSes.
- win311fwg 3mo ago> Yeah my DB is the one place I want strict types. Well also RPCs. Which is why in most cases you are going to show at compile time that your code adheres to the typed structure. The SQLite schema you are developing alongside provides the type information for static analysis. There is no real benefit in also double checking again at runtime. Your code isn't going to magically mutate in a way that it starts inserting integers where your static analysis showed that it inserts strings. SQLite is not like Postgres, which is designed for many different applications all sharing the same data, where you have to place trust in third-parties to also do the right thing. Runtime validation is critical in that environment. SQLite is designed for one application, one database. While it technically can support multiple applications sharing the same file, support is poor and it is not really designed for that. In the typical case, the only trust you need is your code, which you can evaluate at compile time. For the atypical cases you can enable strict tables.
- rjrjthtrjrj 3mo ago2004 called and they want their ignorant bad takes back
- coldtea 3mo agoThey also want this type of joke and misunderstanding SQLite purpose to get "macho programmer" points back.
- frollogaston 3mo agoReddit called and said they're missing a guy
- quotemstr 3mo agoIt's good advice to "Prefer strict X in Y" for almost any value of X and Y. Lax DWIM stuff always comes back and bites you in the end.
- nine_k 3mo ago"Everything should be built top down, except for the first time", as the saying goes %) The problem is often that new things are built with tools that allow for flexibility, because the builder hasn't decided on the shape of what needs to be built. In 1990s it was Perl, in 2020s it's vibe-coding, but in either case it's lax and "dwim". As the developer attains a much better understanding of the product, strictness becomes more and more beneficial, but the spectre of backwards compatibility haunts the interfaces and defaults.
- ezekiel68 3mo agoComing from the enterprise SQL world, I never took SQLite seriously for the very reason that field types were not enforced by default. (Yes, I was agog when it became the backbone for app metadata on smartphones.) Anyway, reading this reminds me of the old chestnut from networking about choosing UDP over TCP for its low-latency and simplicity and then eventually adding nearly all the reliability facilities of TCP to the app (automatic retry, etc) by hand.
- nine_k 3mo agoThe difference is that when you add all these mechanisms yourself, you can do it differently than TCP does, sometimes to a great effect: see QUIC and HTTP/3. OTOH I don't see a similar superpower arising from handcrafted data type enforcement over (non-strict) SQLite.
- astrobe_ 3mo agoYep. More generally the correct reason to prefer UDP over TCP is the fine-grained control you gain. When you want that and have to use TCP, you're in for a deadly fight against the OS and its TCP/IP stack. Datagram is also quite often more fit for applications than streams, because many applications are message oriented. The Websocket protocol acknowledges that even though over TCP. But that's more a bonus point than a strong reason to choose UDP over TCP, one can always recreate packets/messages on top of TCP. It's a bit goofy though, because TCP uses IP packets. A lot of online games with significant real-time constrains and many-to-many connections gladly use UDP - and similarly, video conference services also use it. Smaller protocols like DNS and NTP as well. There are other arguments beside real-time streaming with acceptable data loss, see [1] and the "end-to-end argument" paper it links in particular. Choosing UDP and ending up recreating some of its reliability and flow control features is not a "Uh, Oh..." moment. It's normally a deliberate choice. Sometimes you do need custom wheels [2]. [1] https://deepplum.com/post-b/ https://deepplum.com/post-b/ [2] https://en.wikipedia.org/wiki/Mecanum_wheel https://en.wikipedia.org/wiki/Mecanum_wheel
- 762236 3mo agoIf I'm interested in a Jeep or Bronco, I don't go to a car reviewer. They say it is noisy and handles poorly. They act like their use case is what matters for something obviously targeting a different use case.
- nektro 3mo agothanks! i have seen the sqlite quirks page before but just added this to my queries thanks to you
- dfabulich 3mo agohttps://sqlite.org/flextypegood.html https://sqlite.org/flextypegood.html explains why this isn't the default (and probably will never be the default). > rigid type enforcement can successfully prevent the customer name (text) from being inserted into the integer Customer.creditScore column. On the other hand, if that mistake occurs, it is very easy to spot the problem and find all affected rows. That doesn't line up with my experience. (In particular, it may not be easy to fix those corrupted rows; the data may be entirely lost.) > By suppressing easy-to-detect errors and passing through only the hard-to-detect errors, rigid type enforcement can actually make it more difficult to find and fix bugs. This doesn't line up with my experience at all.
- tyre 3mo agoThese are similar to arguments that people made about MongoDB. You can store anything! And then most people who used it realized that this is actually terrible, in most cases. It looks like this is an artifact of when SQLite was written and the strong opinion of its author, less so a rigorous engineering principle. Reading this, it sounds like the author has been criticized a lot on this, is digging in their heels no matter what, and will find any supposed justification. On the other hand, datatypes like JSON or HSTORE (in postgres) can handle what they are advocating for. But opt-in to YOLO typing is nearly always better than opt-out.
- zvrba 3mo agoI've seen a set of SQL tables designed to mimic "flexible classes". There's a table for the "class", another table defining its "fields", and two other tables defining class instances and related field values (all as varchar). Flexible, yes. You can store anything. The downside is, that I've also found "anything". Stuff attached to the wrong "class", wrong datatypes, missing "obligatory" fields, etc, etc. It's a PITA to work with. If I could design it from scratch, it'd be a single table with JSON payload.
- fauigerzigerk 3mo ago>It's a PITA to work with. If I could design it from scratch, it'd be a single table with JSON payload. Isn't plain JSON even worse? At least the design you're criticising has a dynamic schema definition separate from code. You could of course have a JSON schema somewhere, but in my experience the whole point of representing the schema as data in the database is to support (limited) end-user driven schema changes. I would use JSON to store data that complies with a schema that can be modified by third parties outside of my control.
- pettijohn 3mo agoCREATE TABLE ... STRICT WITHOUT ROWID is my default, I don't know why I'd ever do otherwise.
- eternauta3k 3mo agoWhy without Rowid? Are you using non-integer primary keys?
- pettijohn 3mo agoI always define a primary key, integer or otherwise, and prefer the be explicit and consistent.
- coldtea 3mo agoToo many people missing the point entirely and wanting to make SQLite Postgres or Oracle.
- frollogaston 3mo agoSQLite has to be one of the most stubborn software projects around, in a good way. Everything about it breaks what you learn in school and ignores trends, but it thrives. First time I used it was in high school, when I was a newbie to C and didn't know how to link in libraries, and SQLite was the only thing that offered all the code as a single .c file https://sqlite.org/amalgamation.html https://sqlite.org/amalgamation.html
- cowboylowrez 3mo agoI do understand the mindset, in college, math was pretty authoritarian in nature.
- simonw 3mo ago> Unfortunately, I don’t think there’s a way to ALTER a table to make it strict. I think you have to copy the data out of the non-strict table into the strict one. This inspired me to add a feature to my sqlite-utils Python library and CLI tool, so you can now use it to transform non-strict tables to strict (and vice-versa) like this: uvx sqlite-utils transform data.db mytable --strict Or in Python: import sqlite_utils db = sqlite_utils.Database("data.db") db.table("mytable").transform( strict=True ) Release notes for 4.1 here: https://sqlite-utils.datasette.io/en/stable/changelog.html#v4-1 https://sqlite-utils.datasette.io/en/stable/changelog.html#v... Here are the relevant docs: - Using table.transform(strict=True): https://sqlite-utils.datasette.io/en/stable/python-api.html#changing-strict-mode https://sqlite-utils.datasette.io/en/stable/python-api.html#... - The sqlite-utils transform command: https://sqlite-utils.datasette.io/en/stable/cli.html#transforming-tables https://sqlite-utils.datasette.io/en/stable/cli.html#transfo...
- OskarS 3mo agoDoes this trick work with foreign keys? Like, if you have an ON CASCADE DELETE, does it delete a bunch of rows in other tables when converting the table to strict?
- simonw 3mo agoIt uses "PRAGMA foreign_keys=0" and "PRAGMA defer_foreign_keys= ON" before running the transformation, then resets those settings afterwards: https://github.com/simonw/sqlite-utils/blob/3f0471701b5f8c7de888a467020e5dd34310ce9a/sqlite_utils/db.py#L2571 https://github.com/simonw/sqlite-utils/blob/3f0471701b5f8c7d... That's the pattern recommended by SQLite here: https://www.sqlite.org/lang_altertable.html#otheralter https://www.sqlite.org/lang_altertable.html#otheralter
- simonw 3mo agoThanks for the nudge, I just added a new test explicitly covering this: https://github.com/simonw/sqlite-utils/commit/d71420065903ff54247b5062b8c6af6165b7e638 https://github.com/simonw/sqlite-utils/commit/d71420065903ff...
- sgarland 3mo agoWait until you read about its quirks [0]. My favorite: “NUL characters (ASCII code 0x00 and Unicode \u0000) may appear in the middle of strings in SQLite. This can lead to unexpected behavior.” 0: https://sqlite.org/quirks.html https://sqlite.org/quirks.html
- wvenable 3mo agoI was going to say that really shouldn't be a problem but then I read further: https://sqlite.org/nulinstr.html https://sqlite.org/nulinstr.html
- cowboylowrez 3mo agoYeah the fact that length() can't count null containing strings accurately is interesting.
- m0nacle 3mo agoi think braindead developers (most people in this thread) have become way too typescript pilled and as such think that types are something you can’t live without. grow up. use another of the 4000 databases out there or stop fucking bitching that you can’t manage your data without going peepee in your pampers
- jrw0ng 3mo ago[dead]
- Leeann1951 3mo agoI want a better alternative for SQLite
- chrismorgan 3mo agoI don’t like strict mode because it quite unnecessarily thwarts better strict types in the application layer: by restricting the spellings of column types, it stops you from using more meaningful names and prevents code from using those names when mapping database and application types: https://hn.algolia.com/?query=chrismorgan+strict+sqlite&type=comment https://hn.algolia.com/?query=chrismorgan+strict+sqlite&type... If you’re going to work with a database through something like the Rust sqlx crate, I think you’re better to eschew strict mode.
- ncruces 3mo agoPrecisely, this is my experience as well. If you use strict tables with my Go SQLite driver, you'll get worse support for bool/date/time columns than otherwise. It's still unfortunate though that typing a column DECIMAL triggers numeric affinity, which destroys decimal numbers stored as strings.
- rackp 3mo ago[dead]
- jessinra98 3mo ago[flagged]
- Capitanai 3mo ago[flagged]
- upmostly 3mo agoI was inspired by this blog post to write "SQLite is all you need" [1] I love HN's (healthy) obsession with SQLite. It's brilliant. [1] https://www.dbpro.app/blog/sqlite-is-all-you-need https://www.dbpro.app/blog/sqlite-is-all-you-need
- guillaume_code 3mo ago[dead]