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, <a href="https://sqlite.org/datatype3.html" rel="nofollow">https://sqlite.org/datatype3.html</a><p>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.<p>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.
I learnt this the hard way when I decided to go for SQLite for a hobby project for 'simplicity'.<p>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.<p>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.<p>I'd also love to know why it was designed this way; I don't see a single advantage to be honest...
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.<p>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.<p>I think for the intended use cases of SQLite, being as flexible as possible is the best path.<p>The way I see it, type enforcement is an application-level opt-in. Just like UDFs, et. al.
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.<p>[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").]<p>Therefore, I'm firmly in the 'strict typing'/protect me from myself camp...
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.<p>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.
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<p>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.
Kind of annoying, but it's an embedded database. Probably only one program should be writing to it, and data can be validated there.<p>If you've got multiple apps writing to it, you probably have the 'complexity budget' to use postgres.
>Lots of databases support the type conversion behavior, but SQLite can’t do the conversion it powers through and writes the bytes anyways.<p>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?<p>both are kind of horrible
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.<p>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.
It gets the #3 spot in SQLite's FAQ: <a href="https://www.sqlite.org/faq.html#q3" rel="nofollow">https://www.sqlite.org/faq.html#q3</a>
> I’d be curious to hear more about how this type system came to be<p>My understanding is that this relates to how SQLite originally came out of the Tcl world.
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).
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.<p>As someone else in the comments linked to their FAQ, they say it's not a bug but a feature.<p>But... when would that ever be a feature?<p>Also, how on earth does <i>indexing</i> 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 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 love SQLite as much as anyone here on HN, but the type system is not its strongest point.<p>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 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.<p>I could see people who are super-cereal about types getting bunged up about it in SQLite but ... I dig it.
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?