Perfect summary. I'll add: insane defaults that'll catch you unaware if you're not careful! Like foreign keys being opt-in; sure, it'll create 'em, but it won't enforce them by default!
The fact that they didn’t make STRICT default is really a shame.
I understand maintaining backwards compatibility, but the non-strict behavior is just so insane I have a hard time imagine it doesn’t bite most developers who use SQLite at some point.
Isn't SQLite a de facto standard? Seems like it to me. If I want an embedded SQL engine, it is the "nobody got fired for selecting" choice. A competitor needs to offer something very compelling to unseat it.
I haven't investigated this so I might be behind the times, but last I checked remotely managing an SQLite database, or having some sort of dashboarding tool run management reporting queries and the likes, or make a Retool app for it, was very messy. The benefit of not being networked becomes a downside.
Maybe this has been solved though? Anybody here running a serious backend-heavy app with SQLite in production and can share? How do you remotely edit data, do analytics queries etc on production data?
It is for use cases like local application storage, but it doesn't do well in (or isn't designed for) concurrent use cases like any networked services. SQLite is not like the other databases.
No network, no write concurrency, no types to speak of... Where those things aren't needed, sqlite is the de facto standard. It's everywhere.
Perfect summary. I'll add: insane defaults that'll catch you unaware if you're not careful! Like foreign keys being opt-in; sure, it'll create 'em, but it won't enforce them by default!
Is it possible to fix some of these limitations by building DBMSes on top of SQLite, which might fix the sloppiness around types and foreign keys?
1 reply →
SQLite did add 'STRICT' tables for type enforcement.
Still doesn't have a huge variety of types though.
The fact that they didn’t make STRICT default is really a shame.
I understand maintaining backwards compatibility, but the non-strict behavior is just so insane I have a hard time imagine it doesn’t bite most developers who use SQLite at some point.
Isn't SQLite a de facto standard? Seems like it to me. If I want an embedded SQL engine, it is the "nobody got fired for selecting" choice. A competitor needs to offer something very compelling to unseat it.
I mean as in: Most web stacks do not default to sqlite over MySQL or postgres. Why not? Best default for most users, apparently.
I think in the past it was more obvious. Rails switched to SQLite as the default somewhat recently
1 reply →
I haven't investigated this so I might be behind the times, but last I checked remotely managing an SQLite database, or having some sort of dashboarding tool run management reporting queries and the likes, or make a Retool app for it, was very messy. The benefit of not being networked becomes a downside.
Maybe this has been solved though? Anybody here running a serious backend-heavy app with SQLite in production and can share? How do you remotely edit data, do analytics queries etc on production data?
My best answer so far is ssh and sqlite3 cli.
It is for use cases like local application storage, but it doesn't do well in (or isn't designed for) concurrent use cases like any networked services. SQLite is not like the other databases.
Partly for the same reason it’s fast for small sites. In their words: “SQLite is not client/server”