SQLite Is Dynamically Typed (2020)
blog.zachwf.com
blog.zachwf.com
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.
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.
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!)
MySQL, mongo, JavaScript.
They all are incorrect in their behavior, but because of their defaults and ease of getting started, they got big.
It was really the iPhone that killed everything else off- flash / flex was still in heavy use right up until it seemed smartphones were here to stay and iPhones wouldnt support any plugins
"In retrospect, perhaps it would have been better if SQLite had merely implemented an ANY datatype so that developers could explicitly state when they wanted to use flexible typing, rather than making flexible typing the default. But that is not something that can be changed now without breaking the millions of applications and trillions of database files that already use SQLite's flexible typing feature."
Or a "use strict" style pragma / parameter to sqlite3_open or sqlite3_exec, which errors out for that session (resp query) if something doesn't match the expected type.
A little bit like how, at least according to rumor, Python hasn't done anything about its famous global interpreter lock because, when they looked into it, they found that, while it would be a big performance boost in some areas, it would also cause a large performance degradation for the silent majority of people who aren't writing multithreaded Python code.
It might be fun to fork it and try implementing a feature like this just to see, though. But I personally would rather see it happen as a (perhaps temporary) fork rather than a change to the core engine, so that people who are happy with SQLite's current type discipline don't have to be taken along for the ride.
OCaml is going through something like that. It currently has a GIL, and they are working to remove it and allow multithreaded code. However, they're also constantly benchmarking single core performance because it's still the main use case, and they don't want too much regression.
I would personally say that they're accurate, and not necessarily pejorative. And that the distinction describes a real thing about SQLite's behavior that is distinct from, if related to, its dynamic typing.
(On the other hand, their use of the word word "judgmental" to describe strong static typing is unambiguously pejorative. I realize a lot of people can be judgmental about type disciplines, but leave the poor type disciplines. They're not judging you, they're just obeying their designs.)
I realize that the term is very fuzzy and can mean all sorts of things, but, to me, "weakly typed" means that data of one type can be treated as if it were another type, without explicit type conversions. And, while that kind of behavior is rather unfashionable nowadays, it does introduce some behaviors that at least some people find desirable. As the section goes on to describe, not everyone likes their tools to be pedantic.
That said, I am one of those people who would prefer more rigid typing. I'm cautious about using SQLite at work specifically because I've been burned by that first example it gives. I had a situation where a client needed to use all-numeric IDs where the leading zeros were significant. (For example, "1234" and "01234" were two different IDs.) It was insisting on converting the string "01234" to the integer 1234. So when I tried to get it back out, it would give me the (non-equivalent) string "1234." I had a bugger of a time dealing with this - I eventually had to implement a workaround where all of these IDs were prefixed with a "#" character simply to force SQLite to stop trying to be helpful.
I agree with them that this feature should not be changed now, but the fear of having a bug like this, which proved to be very costly for us, prevents me from using SQLite for similar purposes in the future. It's fine if you have tight control over what data goes into the database, and are aware of this behavior, and can design accordingly. For the vast majority of cases where I need a database, it's the one I'd prefer to use.
But if you're in a situation where you might just be the custodian of someone else's data and you have little ability to curate it, the defensive coding that this feature entails can be burdensome. Not really because there's a lot of it. My sense is that the flexible typing is actually a net gain on this front. More because it's so different from the kind of defensive coding that you have to do with other SQL databases, so people tend not to have as good of an instinct for it, or as much explicit training on the subject, and are therefore maybe more likely to get it wrong. And also because it tends to lead to silent failures instead of noisy ones, so it's less likely that you'll discover your mistakes before they've created a big mess to clean up.
I think you're misremembering some crucial details. Sqlite only attempts to convert '01234' to the integer 1234 on insertion in columns defined with numeric type affinity. If it did so succesfully, it will return the value as the integer 1234, not as a string. The only way to preserve leading zeros is to store it as a string, and sqlite will do exactly that if the column was defined with text affinity. Moreover, this is the same behavior as in other databases: in mysql if you try to insert the value '0123' in a INT column, both with and without quotes, it will silently cast to the integer 123 and return that on retrieval.
I just tested all of this on both sqlite3 and mysql, but from memory, postgresql behaves the same.
At the time, though, this was happening even though I had set the columns' type affinity to TEXT. That was the very first thing I checked when the bug rolled in.
sqlite> create table test_tbl ( test TEXT );
sqlite> insert into test_tbl values ( "0123" );
sqlite> select * from test_tbl;
Just a WAG.
I have a light wrapper around SQLite called cq[1], to make querying CSVs with SQL more convenient. The point of the tool is to facilitate making ad-hoc, one-off queries on CSV data. CSVs don't specify the types of their columns, and they're either text or numeric. Having to be explicit on what the columns are would be a downside for me. Many CSVs have a ton of columns and I often care only about a few columns for one particular query I'll write and run once and never again.
I've fallen into the trap[2] where imported columns compare alphabetically despite them being numbers. I've been casting in my queries and was considering adding argument syntax for specifying the types of certain columns, but now that I see this, I think the behavior on specifying everything as NUMERIC is more desirable. It's more like Excel/LibreOfficeCalc, where you can just use numbers and they behave like numbers without having to be super-explicit.
Actually, your comment almost strikes me as mean. Instead of asking "can I make it not dynamically typed?", you're asking for "reasonable" behavior, implying the existing behavior is not reasonable, basically spitting on the SQLite projects decision.
Your comment about this being a "design error" is also in bad taste. Have you tried looking up where this decision comes from and why it is like that? According to the SQLite folks, this is on purpose, not by accident.
> As far as we can tell, the SQL language specification allows the use of manifest typing. Nevertheless, most other SQL database engines are statically typed and so some people feel that the use of manifest typing is a bug in SQLite. But the authors of SQLite feel very strongly that this is a feature. The use of manifest typing in SQLite is a deliberate design decision which has proven in practice to make SQLite more reliable and easier to use, especially when used in combination with dynamically typed programming languages such as Tcl and Python.
For me it means that SQLite developers admit it was a mistake and should have been implemented differently.
I don't understand why this mistake can't be corrected now, with some opt-in "strict types" pragma, but whatever.
That is a very emotional response given the SQLite FAQ itself basically admit the design is unfortunate. They just state it is too late to change due to backwards compatibility. So it is not crazy to suggest an opt-in flag to change the default behavior, although I can understand if the SQLite team does not bother.
> Nevertheless, most other SQL database engines are statically typed and so some people feel that the use of manifest typing is a bug in SQLite. But the authors of SQLite feel very strongly that this is a feature. The use of manifest typing in SQLite is a deliberate design decision which has proven in practice to make SQLite more reliable and easier to use
But I don't think anyone is against support for flexible typing as an opt-in feature.
So it'll corrupt text value "1.0000"? Sounds like a dangerous feature.
https://sqlite.org/quirks.html#flexible_typing
It is probably not as big a problem in an embedded database as it would be in a server-based database. Nevertheless "flexible typing" should have been an opt-in feature per column, not the default.
That‘s exactly the thing that always comes back to bite you in Excel.
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...
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) 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.
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.
Schema-on-write is useful when you know exactly what the schema should be ahead of time, and it's static. That happens quite often in OLTP and business intelligence applications, but that's not always what you're doing.
Schema-on-read is very useful in those situations where the schema constraints you need can't be known ahead of time, or might vary from situation to situation. At that point you're kind of stuck delaying the schema bits (including making sure everything is an appropriate type) until the last minute. This comes up in, for example, big data applications such as data lakes.
I think SQLite sort of straddles the two -- it's sort of a loose schema-on-write (with type affinity to its base types), and schema-on-read (with respect to more complex types which is inferred from what is stored in base types)
It's more that static database types provide a standard contractual interface that is enforced (agnostic of application and programming language) and has reproducible behavior upon retrieval.
The advantage of static types is the guarantee that if a data point is successfully INSERTed, it can be successfully retrieved in the future.
In a dynamically typed DB you have a problem of standardization across codebases -- every new program/microservice that is written will have to use the exact same typechecking code, or else risk future retrieval issues. Those that do type coercion on the other end are essentially guessing and hoping that the original type was successfully reproduced upon retrieval. Plus if you work with different programming languages, that same code has to be ported to all the different languages. You also find yourselves having to reinvent the wheel a lot -- for instance the SQL DECIMAL type. In SQLite you can either store it as an INTEGER or REAL, and then either store metadata in another field or create a specific function to retrieve the INTEGER and recreate the specific DECIMAL type with the right number of digits (say DECIMAL(18,0)).
On the other hand you can rely on SQL types and get all this for free and have the assurance that it will work impeccably.
Yeah, well, one good way of finding that out in practice is that reasonable RDBMS systems tell you that when you try to stuff the stuff in.
Can you elaborate?
Can you specify what weird results you got?
sqlite> .import /dev/stdin t
x
1
2
sqlite> select * from t where x < 10;
1
sqlite> select * from t where x > 10;
2In schemas, when you define a column as integer or float, input is interpreted this way WHEN POSSIBLE (i.e. with 1 and 2 it's possible, but if you pass "foo" it'll be stored as a text attribute, obviously).
What people should know with SQLite is that it's flexible about types, and stores everything you pass, but it still cares what the column type is and it affects how it interprets input.
and flexible how. What caught me off-guard is that I thought that since the types aren't specified with .import, sqlite would behave like how it does when you create a table without specifying the types:
sqlite> create table t(x);
sqlite> insert into t values (1), (2);
sqlite> select * from t where x < 10;
1
2
sqlite> select * from t where x > 10;
sqlite> create table t(x text);
sqlite> insert into t values (1), (2);
sqlite> select * from t where x < 10;
1
sqlite> select * from t where x > 10;
2We 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.
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.
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.
There was no ORM flexible enough for this use case. Then, jOOQ came out and it could be usable with a lot of extending for the chemistry stuff. But at that point, we already had the custom ORM.
[1] https://web.eecs.utk.edu/~azh/blog/makinguselessstuff.html
I’m a fan of rebuilding things to understand, just didn’t know if there was a good reason for a production app to use its own.
[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...
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.
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
[b'a', 0xffu8]
(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.
(Probably should tag the custom function SQLITE_DETERMINISTIC|SQLITE_INNOCUOUS to make sure you can use it in a CHECK constraint.)
So what is "text", but raw binary data?
I don't think that's cool - I think it's horrible.
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.
Of course, SQL is horrible, so...
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".
The opposite of dynamically typed is statically typed. SQL is not statically typed.
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.
If you've got multiple apps writing to it, you probably have the 'complexity budget' to use postgres.
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 or include your own postgres binary.
Firebird Embedded: https://www.firebirdsql.org/pdfmanual/html/ufb-cs-embedded.h...
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
"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"
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
sqlite> create table example(a int);
sqlite> insert into example values('00123');
sqlite> insert into example values('abcdef');
sqlite> select * from example;
+--------+
| a |
+--------+
| 123 |
| abcdef |
+--------+
Whereas in most other RDBMS implementations this is at best a warning:
mysql> create table example(test int);
Query OK, 0 rows affected (0.06 sec)
mysql> insert into example values ('00123');
Query OK, 1 row affected (0.03 sec)
mysql> insert into example values ('abcdef');
Query OK, 1 row affected, 1 warning (0.03 sec)
mysql> select * from example;
+------+
| test |
+------+
| 123 |
| 0 |
+------+
3 rows in set (0.02 sec)
But your initial example only shows standard casting behavior in common with other RMDBS implementations.
Is there a type in e.g. Postgresql that would let you store '000123', give you numeric sorting (000123 > 1), and still return the leading zero characters? AFAIK that doesn't exist but I'm not that familiar with DB types.
And yep, it's all my fault for the column definition. Still an annoying surprise, esp since in this specific case, the surprise happened years after I screwed up.
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.
My understanding is that this relates to how SQLite originally came out of the Tcl world.
Proper shocked now?
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"?
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.
If I have a column that's defined (lol... hinted) as an INT then if I see a STRING or TEXT in one of the rows I'm gonna be pretty pissed.
But it's ok, at least this is an exciting novelty to observe and shake one's head at.
One nice thing that sqlite being dynamicly typed allows for, is having a key-value table for storing singleton objects, like project config (as opposed to having a well defined table with a single row).
If you need to store singleton objects, wouldn't you just serialize manually to a string representation and store them as TEXT? A fancier solution would be something like the JSON type in Postgres.
I fail to see how sqlite's dynamic types are helping at all in this scenario.
(edit) And deserialize again on read.
(edit) Storing project config in Postgres, even though i like it very much, sounds like overkill, excuse the strong words.
OK I can see how this can be convenient. But I would prefer a special-case "variant" column type for such cases and otherwise default to strongly typed columns.
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. ?!
I could see people who are super-cereal about types getting bunged up about it in SQLite but ... I dig it.
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.
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.
I have and do use SQLite. That doesn't mean I'm incapable of recognizing and understanding a design flaw when I see one, nor that I'm unwilling to point one out for fear of upsetting those who simply haven't experienced it.
If you don't know what your data looks like because you're just wading into filthy data, being loose about your types is not the worst thing in the world. That's all.
You know the progression. You get this file, claims were made about what was in the file and its structure, its data, but then you realize that these claims are ... aspirational at best. You open it up in a notepad or cat it or whatever. Yup, it's awful.
Can you maybe look at it in the spreadsheet? How bad are the line endings anyway?
And the next step from that is trying to pull it into SQLite, because it won't fail when I start trying to cram junk into it. I am in no way claiming that I want to use SQLite as my final database.