These posts need to stop comparing their contrived use-cases for SQLite to Postgres. Sure, SQLite is all you need if it really is all you need. But Postgres does so much more than just act as a data dump with an SQL engine on top.
Dr. Hipp himself even said that SQLite does not, and will never, compete with the likes of Postgres. It competes with fopen.
most use cases are really capable of being satisfied by sqlite, but the "architect" imagines they need more (or is preparing for the potential).
I guess I am one of those "architects" that imagines they need an actual date/time storage class instead of some stringly-typed text column that I hope will contain a parsable ISO8601 datetime string when I try to read it back.
Hipp said that it will never be added because it will bloat the size of the embedded object. Because that is what SQLite was designed for: single-user embedded databases. Like the address book on your phone.
To be honest if you're using JSON at any point in your stack you have the same issue.
SQLite will gladly store a u64::MAX as a "unix timestamp" despite it being about 300x larger than the number of seconds that the universe and everything in it has existed.
Try reading that back in any application date/time code and your app probably crashes immediately.
SQLite does not support unsigned integers.
> Try reading that back in any application date/time code and your app probably crashes immediately.
Postgres will happily ingest and produce dates in the 280th millenium, which will also crash your application if its datetime type can’t handle that shrug.
You can add a check constraint that your field passes through SQLite’s datetime functions and it’ll be clamped between the years 0 and 9999. Or you can put in your own limits matching your application layer, or your application’s business logic. That’s what check constrains are for (amongst other things).
omg you're right. Another example of SQLite's type-flimsyness.
> You can add a check constraint
So now we're just layering on our own brittle validation and runtime checks to make up for sqlite's woeful deficiency supporting even basic domain data types.
You're just firing on all the bullshit cylinders in your little motorised gish gallop aren't you?
The only RDBMS in widespread use which supports unsigned integers is mysql. Not postgres, not oracle, not sql server, not db2, not snowflake, not databricks.
> So now we're just layering on our own brittle validation and runtime checks to make up for sqlite's woeful deficiency supporting even basic domain data types.
Ah yes, the "brittle validation" of actually validating the thing you purported to care about two comments ago. Amazing.
And if your app crashes on an input like that, you should pick a different date Library or stop and rethink your coding skills.
So I had to migrate the production db under live load from sqlite to mysql which was a quite ...intense week. I still like sqlite but I'd be wary of using it again for a usecase like mine.
Sqlite does support multi-process access correctly, but the performance is abysmal as it locks the entire file for any write transaction. Client/server databases have much smarter concurrency.
No, it actually competes with Excel.
At least in the finance world, there are still a million small processes driven by an Excel spreadsheet put together in an afternoon by an intern 20 years ago. If it is a really business critical process, then the input is usually a csv file, read by an Excel macro.
Because Excel is user friendly, and "is good enough". Mostly...
But also also, if you have higher concurrency requirements - e.g. multiple servers, one database - or a more write-heavy use case, sqlite is no longer the right choice.