This is acknowledged as a likely mistake, but one that will never be fixed due to backward compatibility:
> Flexible typing is considered a feature of SQLite, not a bug. Nevertheless, we recognize that this feature does sometimes cause confusion and pain for developers who are acustomed to working with other databases that are more judgmental with regard to data types. 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.
But IMO that would just warrant an entirely separate software package. Shoving it inside sqlite3 is likely to make it much more complex.
Having a tool like this would made my life whole lot easier, well, one can dream.
I wonder if a fork/"new version" could address this. Like, sqlite2 (v1.0, etc.).
Think of SQLie as having a weird dialect where `Col1 INTEGER` is spelled `Col1 INTEGER CHECK (typeof(Col1) IN ('integer', 'null'))`. Ideal? No, but also, not a showstopper.
There are a few good reasons not to use SQLite. Your point 2 is one of them, running the client on one machine and accessing the database file via a network file system is another. Although I've pushed write-heavy workloads pretty hard with some care, it's easy to create a situation where contention becomes untenable. You can really pound on it with one client, but with several it gets dicey.
Other language bindings do things like this also, and makes it pretty idiot proof. you `create table test (mydict dict);` so your tables know their types, and then at bind time you say a sqlite column type of dict == a python dictionary.
Obviously python is sort of a terrible example, because python typing is somewhat non-existent in many ways, but you see the point here.
2: There are definitely cases where it won't work out well, high-concurrent write load is definitely it's big weak spot, but those are usually fairly rare use cases.
It's not the worst thing in the world; you're validating on data ingest anyways to prevent sqli, for example, right?
> But lesser known is that there is a branch of SQLite that has page locking, which enables for fantastic concurrent write performance. Reach out to the SQLite folks and I’m sure they’ll tell you more
If the speed of your write-only workload is limited by whole file locks rather than by raw I/O speed, you can probably consolidate your writes into fewer transactions (i.e. fewer disk accesses, amortizing lock cost over more data) and write to several databases in parallel according to any suitable sharding criteria. Which is what any RDBMS would have to to anyway.
1. SQLite doesn't really enforce column types[0], the choice is really puzzling to me. Since schema enforced type check is one of the strong suit of SQL/RDMBS based data solution.
2. Whole database lock on write, this make it unsuitable to high write usages like logging and metric recording. WAL mode will help but it will only alleviate the issue, you will need row based lock solution eventually.
Just like the offical FAQ said, SQLite competes with fopen[1] instead of RDBMS systems.
--
[0]: https://sqlite.org/datatype3.html
[1]: https://www.sqlite.org/whentouse.html