Yeah 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.
The 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.
This 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.
Sure, but I think what throws people for a loop is that in SQLite, you can store text in that column:
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 |
+--------+
Not to mention, "create table example(a)" is perfectly fine, and then the '00123' survives the trip to the database and back again unchanged.
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)
This "sometimes it's text, sometimes it's a number" property of SQLite is well documented, but can lead to some impressive "what just happened to my data?" scenarios if you're not used to it.
Right, the unique (for an RMDBS) properties of sqlite is that (1) individual values rather than columns are typed and that (2) "column types" are affinities that only affect the casting rules on insertion.
But your initial example only shows standard casting behavior in common with other RMDBS implementations.
In this example, isn't the problem that you're declaring column A as an 'int' type when what you really wanted was a string? Column B would be the "correct" one for your use case I'd think, unless you also need numeric sorting behavior.
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.
Yep. To be clear, the real problem where I shot myself in the foot was a column declared as an int, and stored hex strings. No one noticed the issue, because 100% of the time SQLite left the strings alone, or the string->int->string conversions where safe. Then came along a case where the hex string was all ints, and had leading zeroes that were dropped. Those leading zeroes had meaning, so wacky things happened.
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.
This 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.
Flexible 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.
Not 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.
I built my own ORM for a project once. I needed to implement a thin application service that would speak JSON and query/return entire relational subtrees in a single request (to render an iTunes-like interface for a manual testing web app).
For one product I worked on, its core was a custom ORM. The product had to work with an unknown database schema - adapt to customer's database - and provide functionality similar to MS Access but specialized for pharma/chemical industry.
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.
I'm currently using one in a personal app because I'm learning a lot about a new language for me (Crystal). It's why, like this author[1], I also like writing useless apps.
Ah I did the same before I had to work with ORMs a couple of years ago, I actually started working on a distributed table orm but gave up once work picked up: https://github.com/DylanEHolland/datablox
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.
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.
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 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.
Couldn'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.)
SQLite 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).
But 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.
Wow, 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"?
I'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.
That 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.
Creating 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.
Courageous to post this here, but I emphatically disagree. Any aggregation operation will effectively have undefined behavior. You could make it like a spreadsheet where cells have both numeric data (either static or derived as in the case of a cell formula that sums things up, as an example) or functions, but if you want row/cell metadata per entry then that should be a new database type or some kind of extension.
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.
At the time sqlite was written, the author of sqlite was heavily involved with tcl, a dynamicly typed programming language. Sqlite also started out as a tcl extension. Having the database match the typing philosophy of the language it was supposed to be thightly integrated with probably seemed like a good idea.
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).
> 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.
> One nice thing that sqlite being dynamicly typed allows for, is having a key-value table for storing singleton objects,
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 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.
I 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...
I 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) 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.
> 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.
SQLite doesn't fit perfectly cleanly into this, but, for me, the big distinction is between schema-on-read and schema-on-write. This captures the fact that you always need to have some sort of schema constraint when you're working with data. The question is, do you apply those constraints when you're writing the data, or when you're reading it?
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.
Schema-on-read vs Schema-on-write are definitely relevant concepts.
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)
Agreed. Which kind of makes it even more complicated to talk about. Whether its approach offers the best of both worlds, or the worst of both words, depends on your application, and the dividing line doesn't cleanly follow any of the distinctions we normally make among types of application.
It's not so much that one is stuffing random/unknown types into the database without type-checking. Programming language types do not map exactly to database types even in statically typed DBs. ORMs manage this translation for you (imperfectly at times), but for those of those of us work with directly with SQL, we do typecheck, otherwise the INSERTs will fail.
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.
> 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
What I'm guessing here is that the column is text-biased and so what's being compared is "2" to 10, and the implicit cast is "2" > "10" because strings are compared char by char left to right.
In 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.
> What people should know with SQLite is that it's flexible about types
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;
But that's not the case. It behaves like the types were explicitly set as text:
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;
2
Writing 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.
Yeah 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.
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.
You 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.
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
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.
> 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.
> 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
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!)
Chicken and egg issue here: why did shared hosting providers choose MySql? (I guess because of tight integration with PHP, but then same question with PHP.)
Also while they've converged somewhat in the years since, when you were comparing MySQL 4 to whatever Postgres version was in the 00s, MySQL's tradeoff of performance over correctness vs Postgres tradeoff of correctness over performance would have been very appealing to shared providers looking to bundle the most customers on the least hardware
JavaScript had competition in the form of activx, java, shockwave / flash / actionscript, and for a very brief moment silverlight.
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
Section 3 under "quirks" gives more reasoning about this design choice, especially:
"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."
I wonder why they're not considering type modifiers, eg "TEXT" for dynamically-typed TEXT like now, and something like "STRICT TEXT" for erroring out when a non-string is inserted.
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.
My guess is that, given the nature of what SQLite is and the kinds of people who use it (IIRC, it was originally developed for military applications), they've also made a decision that mucking with such a core portion of the database's implementation has such a great potential for unseen consequences that it's just not worth the risk.
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.
> 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.
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.
> Some commentators say that SQLite is "weakly typed" and that other SQL databases are "strongly typed". We consider these terms to be inaccurate and pejorative.
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.
> 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 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.
I suppose I should mention that this was 15 years ago, so the behavior very well could have changed since then.
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.
Just for curiosity's sake, I built SQLite 3.2.7 (from 2005-09-24) and 2.8.17 (bugfix on the old 2.8 line, from 2005-12-19) and tried this in the shell in both:
sqlite> create table test_tbl ( test TEXT );
sqlite> insert into test_tbl values ( "0123" );
sqlite> select * from test_tbl;
Both give the same output: 0123. Were you using an API wrapper around SQLite that was doing its own coercions, perhaps?
Thanks for taking the trouble. Small syntax nitpick: in ANSI SQL, double quotes are used for identifiers (such as table or column names), single quotes for string literals ('01234'). Most databases (including sqlite) are a bit more permissive, but it's better to stick to the rules
(https://www.sqlite.org/lang_keywords.html)
Could it even be that the double quotes around values are interpreted as some kind of "strong quotes", where single quotes wouldn't? So, inserting '0123' gets coerced to integer 123, whereas "0123" in doubles is interpreted as "No really, I mean it, this is a string!" and therefore keeps the leading zero?
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.
What does "reasonable" mean here? Clearly, for SQLite, it is reasonable because that's how they want their database to work. But for someone who is used to MongoDB, maybe "reasonable" means that you can store documents instead.
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.
Most RDBMSs use static typing. That's what people expect from a database. It is SQL database, so people naturally expect familiar concepts to behave consistently with their previous experience. If database fails that assumption, it's fair to call this behaviour unreasonable. It's understandable that this was early design error which is impossible to correct now, but calling it reasonable just not to hurt someones feelings is unreasonable.
Most databases also allows you to remotely connect to them, does that mean that SQLite not offering that is unreasonable? No, it means that your understanding of SQLite is incorrect, as the use case for SQLite is not to offer remote connections. Same goes for dynamically/statically typed.
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.
Here's quote: "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."
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.
Your getting your panties in a bunch like this over some rather innocuous word choices would be understandable, albeit perhaps somewhat exaggerated, if you are one of the creators of SQLite. If you aren't, it's definitely exaggerated and not... Reasonable.
> basically spitting on the SQLite projects decision.
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.
Maybe the different authors of the website disagrees with each other then, as this paragraph from the website seems to disagree with your statement:
> 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
To be clear the FAQ only states it might have been a mistake to make flexible typing the default everywhere, not that support for flexible typing in itself is a bad idea.
But I don't think anyone is against support for flexible typing as an opt-in feature.
> 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.
So it'll corrupt text value "1.0000"? Sounds like a dangerous feature.
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.
I 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...
We 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.
I 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?
I 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
I'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.
I 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.
> 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.
I 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.
Just because a tool is well liked and widely used does not mean that it is well-designed or immune from criticism. "But it's popular" or "but I like it" really aren't a defense of any criticism at all. At best they merely express disagreement with it, but in this case they're more or less orthogonal to it.
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.
Okay, but how many are using it because of the type model versus those who are using it for any other reason and just putting up with it? You can't claim every use of the software is because of every feature or quirk.
I claimed "I could see people who are super-cereal about types getting bunged up about it in SQLite" ... which seems to have been borne out.
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.
125 comments
[ 3.5 ms ] story [ 135 ms ] threadI 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.
My understanding is that this relates to how SQLite originally came out of the Tcl world.
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:
My fault for a bunch of reasons, but still, surprised me.Whereas in most other RDBMS implementations this is at best a warning:
This "sometimes it's text, sometimes it's a number" property of SQLite is well documented, but can lead to some impressive "what just happened to my data?" scenarios if you're not used to it.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.
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.
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.
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:
(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, (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.
(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
So what is "text", but raw binary data?
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.
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.
[1] https://www.sqlite.org/appfileformat.html
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?
In 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:
But that's not the case. It behaves like the types were explicitly set as text: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...
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."
from https://sqlite.org/quirks.html#flexible_typing
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.
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.
[1] https://github.com/jolmg/cq
[2] https://news.ycombinator.com/item?id=28071615
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.
https://www.sqlite.org/different.html
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
https://www.sqlite.org/different.html
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.
[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...
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.
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. ?!
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.
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.
[0]: https://www.sqlite.org/datatype3.html
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.