15 ms·
SQLite Is Dynamically Typed (2020)
- hans_castorp 5y ago> A small, cool fact about SQLite is that its columns are flexibly typed I don't think that's cool - I think it's horrible.
- nooorofe 5y agofrom my experience, mixed values in a column silently kills index.
- IshKebab 5y agoYeah I think they meant to write "A subtle braindead footgun about SQLite..." I love SQLite but that's easily the most broken thing about it. Also foreign keys are not enforced by default which I'm sure has caused lots of people unexpected pain.
- randomdata 5y agoSQL is dynamically typed, so it stands to reason that the storage engine behind it be too. Of course, SQL is horrible, so...
- hans_castorp 5y ago> SQL is dynamically typed, No, it's not. It's strongly type. The SQL standard clearly states that you can't store 'abc' in a column defined as integer (or date or timestamp or boolean). It has some well defined implicit casting rules if you compare values of different types. But that has nothing to do with "dynamically typed".
- randomdata 5y agoThe opposite of strongly typed is weakly typed, not dynamically typed. A language can be both dynamically typed and strongly typed. Python, for example. The opposite of dynamically typed is statically typed. SQL is not statically typed.
- simonw 5y ago> I’d be curious to hear more about how this type system came to be My understanding is that this relates to how SQLite originally came out of the Tcl world.
- asddubs 5y ago>Lots of databases support the type conversion behavior, but SQLite can’t do the conversion it powers through and writes the bytes anyways. oh god. So if I write "a" to an int column it turns into 97? or does it just return a text instead next time, which the first example seems to imply? both are kind of horrible
- usmannk 5y agoFrom the (linked) sqlite docs: "In SQLite, the datatype of a value is associated with the value itself, not with its container." And on the topic of specifically inserting "a" into an INTEGER column: "If the TEXT value is not a well-formed integer or real literal, then the value is stored as TEXT"
- banana_giraffe 5y agoBasically, if it doesn't look like an INT, it won't be coerced into it. The edge case I hit was when it did look like an integer, but had leading zeroes that were important: sqlite> create table example(a int, b text); sqlite> insert into example values ('00123', '00123'); sqlite> select * from example; 123|00123 My fault for a bunch of reasons, but still, surprised me.
- usmannk 5y agoThis is a classic mixup in Excel/spreadsheet tools too, as they'll often assume any pasted numerical IDs are ints and "helpfully" strip the leading zeroes.
- asddubs 5y agowell, it sure is one hell of a footgun
- em500 5y agoI'm surprised that sqlite is singled out for this, AFAIK this is standard RDBMS behavior. Your example gives exactly the same results in mysql.
- 5y ago
- bob1029 5y agoThis doesn't bother us. We use a hand-rolled ORM which is exclusively used for accessing tables, so type enforcement is effectively maintained throughout. We could go either way on strictly-typed SQLite to be honest. All of our schema declares reasonable types for columns - TEXT, INTEGER, BINARY, etc. I think for the intended use cases of SQLite, being as flexible as possible is the best path. The way I see it, type enforcement is an application-level opt-in. Just like UDFs, et. al.
- anyfoo 5y agoFlexible would be offering some kind of "anytype" column or similar. I'd still be wary of it, but there you could properly claim that one does not have to use it, and it would mean correctness is still enforced on all other columns. This doesn't bother you because you have your own schema layer. Imagine your ORM would stop enforcing types, and return a number when a string property was requested.
- edgyquant 5y agoWhy hand roll an ORM?
- progre 5y agoNot op, but this has happened in every major project I have seen: Typically, someone started out thinking "This is the simple, I don't need the overhead of [orm]". Then as time goes by, helper methods for data mapping between the DB and the domain objects crops up and one day you realize that you now have your own half decent ORM. You don't even need to start from scratch, in the project I'm currently working this happend on top of Dapper sometime on the past.
- coldacid 5y agoI'm now looking at replacing the half-baked ORM I built inside a Xamarin app with EFCore because it does a smarter job of handling SQLite than I do.
- TimTheTinker 5y ago
- favorited 5y agoIt gets the #3 spot in SQLite's FAQ: https://www.sqlite.org/faq.html#q3 https://www.sqlite.org/faq.html#q3
- skissane 5y agoYou can use a CHECK constraint with typeof() to enforce static typing. If your SQL DDL is being generated somehow (as opposed to manually written), you could even autogenerate the CHECK constraints. I wonder why they don't have static typing as an option built-in. It could auto-generate the CHECK constraints necessary if some table-level option is set.
- deathanatos 5y ago> You can use a CHECK constraint with typeof() to enforce static typing. If your SQL DDL is being generated somehow (as opposed to manually written), you could even autogenerate the CHECK constraints. You can't enforce that text values are actually text: sqlite> create table foo ( a text, check (typeof(a) == 'text') ); sqlite> insert into foo (a) values ( 'a' || x'ff' ); sqlite> select a, typeof(a) from foo; a typeof(a) -- --------- a� text (The Unicode replacement character in this comment is the output of sqlite/sane terminals, here.) The value in the column purports to be "text", but it's raw binary data. If you retrieve this in, say, Rust, you'll get, [b'a', 0xffu8] (This is with PRAGMA encoding set to "UTF-8". Not that this isn't a blob: for those, typeof() is "blob".) (I believe this to be a bug with SQLite. It's docs state: > TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE). The developers do not see it as a bug.) Realistically, it's not much of a problem. In Rust, I already have to run-time assert that it is a string and not an integer, so also asserting "it's a string that's actually a string" is just another assertion and doesn't change much. Program goes boom if column unexpectedly has the integer 4 in it, program goes boom if it has b"a\xff" in a text column.
- skissane 5y agoCouldn't your issue be addressed by writing an application-defined function [0] called something like "utf8_is_valid()" and invoking it in the CHECK constraint? (Probably should tag the custom function SQLITE_DETERMINISTIC|SQLITE_INNOCUOUS to make sure you can use it in a CHECK constraint.) [0] https://www.sqlite.org/appfunc.html https://www.sqlite.org/appfunc.html
- CRConrad 5y ago
- zinekeller 5y agoSQLite is also unbounded in its columns (as long as there are enough disk space), even if you put a limit into them. This is both wonderful (every data is intact unless deliberately deleted) and a curse (you can't move into more strict DBs).
- neolog 5y agoThis is not dynamic types. In a dynamic type system, runtime values have types. This is weak typing.
- anyfoo 5y agoBut it is dynamic typing. The type in sqlite is associated with the value. For example you can have a number column, but actually store a string in it. Proper shocked now?
- goto11 5y agoColumns are like lists in Python - they can contain values of any type. The type is associated with the individual value.
- crazygringo 5y agoWow, it never even occurred to me a database's columns might not have fixed types, but that each value could have its own type. As someone else in the comments linked to their FAQ, they say it's not a bug but a feature. But... when would that ever be a feature? Also, how on earth does indexing on a column work, or heck even a sort? However they define it must be a gigantic headache... so I'm curious what possibly motivated this "feature"?
- tedunangst 5y agoNumeric data may be missing for any number of reasons. Storing the reason, as opposed to just null, could be useful.
- tinalumfoil 5y agoI'm skeptical of using different types in a dynamically typed language (or db) as a tagged union where each type has a different meaning. IMHO youre better off with two columns with a constraint that exactly one of them must be Null. The issue is someone at some point is going to write code reasonably assuming the value must be an integer, not a string, and when the value turns out to be a string you'll get unpredictable behavior.
- jimktrains2 5y agoThat sounds like a nightmare to deal with. You can't use any aggregate as it's either not well defined (e.g. sum) or will be wrong (e.g. count). Kust put the reason in another column if it's needed.
- anyfoo 5y agoCreating a mapping between primitive data type and "kind of content" is ad-hoc, arbitrary, and icky. What if you want your reasons to be numeric as well, then the scheme falls apart? What about aggregates, as a sibling comment mentioned? Copy of another comment of mine: I dealt with a large sqlite database once where a past bug had entered data of a different data type. Instead of blowing up when the rows were created or modified, it blowed up in a much harder to debug way much, much later.
- anyfoo 5y agoI found that out the very hard way. I dealt with a large sqlite database once where a past bug had entered data of a different data type. Instead of blowing up when the rows were created or modified, it blowed up in a much harder to debug way much, much later.
- koyote 5y agoI learnt this the hard way when I decided to go for SQLite for a hobby project for 'simplicity'. Depending on how you store your values you could have a column with both floats and ints where a simple query like 'number < 10' will result in weird results. On the performance side I wonder how much of an effect this has as the DB can't guarantee the size of any given column/row. I'd also love to know why it was designed this way; I don't see a single advantage to be honest...
- wenc 5y agoI also found out the hard way -- I was initially excited to adopt SQLite everywhere because I had heard so many good things about it (which are in fact true), but its dynamic typing/type-affinity based design made it difficult to use as a data store especially for analytics work. I mean, you can still use it, but you have to do your own type checking/coercion in code -- basically you have to re-implement a subset of a type handling system -- which you'd get for free in a full SQL db. You also have to standardize certain types like DATEs because SQLite has no native date type -- the 3 ways to implement dates in SQLite are ISO 8601 strings, Julian and UNIX epochs, and you can convert between them, but you lose some performance on joins and aggregations. It's best to think of SQLite as its own data structure/file format which happens to have a SQL engine on top of it, rather than an actual database format. In fact the docs makes a case for SQLite as an application file format [1]. For analytics work however, I'm finding DuckDB (https://duckdb.org https://duckdb.org) to be quite amazing. It is SQLite-ish (i.e. also an embedded database), but has a columnar query engine, can read parquet files, and has native types (including date!). It also runs in-process in Python and queries Pandas dataframes using a more optimized query engine than Pandas itself. Side: Many of the main contributors work at CWI Amsterdam, the Dutch institute where Guido van Rossum conceived Python. [1] https://www.sqlite.org/appfileformat.html https://www.sqlite.org/appfileformat.html
- clnhlzmn 5y ago> I mean, you can still use it, but you have to do your own type checking/coercion in code Can you explain why you wouldn't have to check what you're putting into the database? If you're just stuffing values of unknown type into a statically typed DB you're going to get errors up front and if you stuff values of unknown type into SQLite you might get errors down the road. Either way you probably should know what you're putting into the DB in the first place.
- LAC-Tech 5y agoKind of annoying, but it's an embedded database. Probably only one program should be writing to it, and data can be validated there. If you've got multiple apps writing to it, you probably have the 'complexity budget' to use postgres.
- cirrus3 5y agoWriting additional code to check things that you'd reasonably expect the DB to handle is a slippery slope. It can work well if you are going into it with an application geared towards that mindset and behavior, it works pretty bad introducing it into an existing codebase that historically works a more strict way.
- LAC-Tech 5y agoYeah don't get me wrong, I'd prefer it if SQLite took care of it. But I don't know of any embedded DBs that do loads of type-checking. Apart from SQLite they all seem to be key value stores that operate on sequences of bytes.
- john-shaffer 5y agoIt's pretty easy to run embedded postgres on the JVM: https://github.com/opentable/otj-pg-embedded https://github.com/opentable/otj-pg-embedded The defaults create a temporary DB which is useful for dev & tests, but a pair of calls to .setCleanDataDirectory(false) and .setDataDirectory("...") will change that. If you don't like the default postgres version, you may select one from https://search.maven.org/search?q=io.zonky.test.postgres https://search.maven.org/search?q=io.zonky.test.postgres or include your own postgres binary.
- formerly_proven 5y agoYou can put check constraints into your schema to make sure no one puts strings in your integer columns (for example). If you have an ORM you likely can automatically make it generate check constraints for each column based on the declared properties.
- 5y ago
- combatentropy 5y agoBefore rendering judgment, please read SQLite's own page on the subject. I think you will see that the choice wasn't sloppy but thoughtfully designed, https://sqlite.org/datatype3.html https://sqlite.org/datatype3.html My takeaway from that is: (1) for many people, SQLite is not your database, but you already knew that, (2) it is nice there exists a database out there with this flexibility, if you want it, and (3) if you choose it, you might consider declaring columns NUMERIC. From the documentation: "A column with NUMERIC affinity may contain values using all five storage classes. When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if the text is a well-formed integer or real literal . . . If the TEXT value is not a well-formed integer or real literal, then the value is stored as TEXT." So NUMERIC is kind of like TEXT except when the text is a pure number. Some ask, why would you want a database like this? I don't know, perhaps to match your application language if it is also typed dynamically. For example, PHP.
- cirrus3 5y ago> why would you want a database like this? I don't know Yea, I hope anyone who takes advantage of this does know, and intentionally uses it to their advantage in a scenario that outweighs the long-term downsides.
- piaste 5y ago> Before rendering judgment, please read SQLite's own page on the subject. I think you will see that the choice wasn't sloppy but thoughtfully designed, https://sqlite.org/datatype3.html https://sqlite.org/datatype3.html I've read the whole page. It's an excellent piece of documentation and reference, but it says nothing about the reasoning behind that design choice, nor show that it was a design choice at all (rather than being e.g. a TCL legacy as some other commenters have hypothesized). The introduction says "However, the dynamic typing in SQLite allows it to do things which are not possible in traditional rigidly typed databases", but it doesn't say what those things are, or explain why they're considered a good trade-off against the well-known footguns of implicit type coercion. (Indeed, if SQLite had rigid static typing, all the detailed rules on that page wouldn't need to exist!)
- cirrus3 5y agoI really don't need or want this flexibility in a production DB. One less cause of bugs without it IMO.
- jll29 5y agoI love everything about SQLite except its lack of strictness about types; it reminds me of when Excel is trying to be helpful/"intelligent". The Numeric type leads to three types of different behaviour for the same row of data, so any downstream processing will need to cover all these cases. [And read the rest of this thread for some examples of how people got hurt by unexpected behavior resulting from it (e.g. the case of HN user koyote who got the wrong result for "where x > 10").] Therefore, I'm firmly in the 'strict typing'/protect me from myself camp...
- Asmod4n 5y agoAnd C only has one Datatype. It’s up to you how to treat it.
- vector_spaces 5y agoWe love this aspect of SQLite and leverage it to great advantage. One of our projects is a tool for customers to validate that data files submitted at regular intervals comply with hundreds of data and business rules before submitting them to a lengthy ETL process, which will need to be repeated if there are data issues The tool loads the data into a SQLite database, and runs a battery of SQL queries against it which represent the rule checks. The queries return data that fail our checks. Since SQLite is dynamically typed, we can load the data in just fine even if it is incorrectly typed, but still leverage the awesomeness of a relational database along with the particular awesomeness of SQLite which allows us to define ephemeral, connection-specific UDFs with Python code.
- nlitened 5y agoI wonder if in this thread engineers crapping all over the design choices of one of the most reliable and wide-spread DB engines in the world are the same ones who spin up Kubernetes clusters for todo-list SPAs at work?
- kungito 5y agoI haven't done it so far but if you know how to set it up that fast, is it really that bad to also set up a kube cluset together with everything else? It's not that resource demanding and for a simple app you can probably set it up in 5 minutes
- petters 5y agoI love SQLite as much as anyone here on HN, but the type system is not its strongest point. I can declare a column of type POINT and that will work fine. The result will be a column of integer affinity because it contains the string INT. ?!
- hiccuphippo 5y agoIs there a way to make it return an error on type mismatch? At least during development to catch bugs early.
- BuckRogers 5y agoI've never used SQLite but always had it on my list of things to look into for smaller projects. I guess I would still use it since I only use statically typed programming languages, and can just closely watch over my DA layer changes, but I'd greatly prefer this were done right in the DB. A 'SQLiteNext' with design corrections like this would be a good project to live alongside SQLite. His Tcl story is strange to me, I think dynamically typed languages like JS need these sorts of changes more than anything. Some layer of your stack has to come in and keep things in order. And if you only have one layer doing things right, it should definitely be your data storage.
- at_a_remove 5y agoI have used SQLite to great advantage when prototyping (when you don't have a great idea of what your data is going to look like anyway) or when dealing with filthy (beyond dirty) data, where your input types are nothing but lies from the tongue of Satan himself. I could see people who are super-cereal about types getting bunged up about it in SQLite but ... I dig it.
- da_chicken 5y ago> I could see people who are super-cereal about types getting bunged up about it in SQLite As soon as you have more than one client authored by different people talking to the same data store, you, too, will care about types. In a purely embedded system where the database is simply long-term storage for itself, this may rarely become a problem. Once you're using SQLite as a microservice's data store, however, you'll quickly find that what you think is predictable and what everyone else thinks is predictable don't really match all that much. There is a reason that most SQL RDBMSs have features like NOT NULL, UNIQUE, or support foreign key constraints. It's to prevent some other fool's application from breaking your application because they think 1.5 of something makes sense when you think it's got to be a whole number. Can you seriously tell me that you don't see a problem with this notation in the documentation[0]: > Note that a declared type of "FLOATING POINT" would give INTEGER affinity, not REAL affinity, due to the "INT" at the end of "POINT". And the declared type of "STRING" has an affinity of NUMERIC, not TEXT. I really like SQLite, but I think I'd quit a project that asked me to document that kind of behavior as an acceptable consequence of the overall design. There is a very good reason that nearly everyone prefers a data store that will emit errors instead of silently making guesses about what the user wants. Returning an error is not a crime. [0]: https://www.sqlite.org/datatype3.html https://www.sqlite.org/datatype3.html
- at_a_remove 5y agoI mean, it appears that there are a lot of people who use SQLite and they therefore disagree with you. Each tool has appropriate uses. I am reminded of the people who say that software development cannot happen without Agile -- as if it never took place prior to 2000! It may not be your tool for your projects but tons of people here will attest to it being very useful for them in some places, and I am among them. Make of that what you will.