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 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;
2Can you elaborate?