6 ms·

It being so obvious, why is sqlite not the de facto standard?

skrebbel 9mo ago
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?
- Sammi 9mo ago
  My best answer so far is ssh and sqlite3 cli.
chuckadams 9mo ago
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.
- mickeyp 9mo ago
  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!
  - ogogmad 9mo ago
    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?
    - Polizeiposaune 9mo ago
      Using the API with discipline goes a long way. Always send "pragma foreign_keys=on" first thing after opening the db. Some of the types sloppiness can be worked around by declaring tables to be STRICT. You can also add CHECK constraints that a column value is consistent with the underlying representation of the type -- for instance, if you're storing ip addresses in a column of type BLOB, you can add a CHECK that the blob is either 4 or 16 bytes.
- BenjiWiebe 9mo ago
  SQLite did add 'STRICT' tables for type enforcement. Still doesn't have a huge variety of types though.
  - mikeocool 9mo ago
    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.
    - sethops1 9mo ago
      Nearly every default setting in sqlite is "wrong" from the outset, for typical use cases. I'm surprised packages that offer a sane configuration out of the box aren't more popular.
    - sgbeal 9mo ago
      > The fact that they didn’t make STRICT default is really a shame. SQLite makes strong backwards-compatibility guarantees. How many apps would be broken if an Android update suddenly defaulted its internal copy of SQLite to STRICT? Or if it decided to turn on foreign keys by default? Those are rhetorical questions. Any non-0 percentage of affected applications adds up to a big number for software with SQLite's footprint. Software pulling the proverbial rug out from under downstream developers by making incompatible changes is one of the unfortunate evils of software development, but the SQLite project makes every effort to ensure that SQLite doesn't do any rug-tugging.
  - andersmurphy 9mo ago
    I mean it has blob types. Which basically means you can implement any type you want. You can also trivially implement custom application functions to work on these blob types in your queries. [1] - [1] https://sqlite.org/appfunc.html https://sqlite.org/appfunc.html
Cthulhu_ 9mo ago
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.
dahart 9mo ago
Partly for the same reason it’s fast for small sites. In their words: “SQLite is not client/server”
jerf 9mo ago
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.
- jstummbillig 9mo ago
  I mean as in: Most web stacks do not default to sqlite over MySQL or postgres. Why not? Best default for most users, apparently.
  - conradkay 9mo ago
    I think in the past it was more obvious. Rails switched to SQLite as the default somewhat recently
    - jstummbillig 9mo ago
      Yeah, that's the one prominent example but, like you said, also just rather recently. Since "the network is slow, duh" has always been true, I wonder why.
      - Kerrick 9mo ago
        It took a lot: https://fractaledmind.com/2023/12/23/rubyconftw/ https://fractaledmind.com/2023/12/23/rubyconftw/ and https://news.ycombinator.com/item?id=39835496 https://news.ycombinator.com/item?id=39835496
      - conradkay 9mo ago
        My guess would be that performance improvements (mostly hardware from Moore's law and the proliferation of SSDs, but also SQLite itself) have led to far fewer websites needing to run on more than 1 computer, and most are fine on a $5/month VPS And stuff like https://litestream.io/ https://litestream.io/ or SQLite adding STRICT mode
Kerrick 9mo ago
It's becoming so! Rails devs are starting to ship SQLite to production. It's not just for their main database either... it's replacing Redis for them, too.

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?

Sammi 9mo ago
My best answer so far is ssh and sqlite3 cli.

chuckadams 9mo ago

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.

mickeyp 9mo ago
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!
- ogogmad 9mo ago
  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?
  - Polizeiposaune 9mo ago
    Using the API with discipline goes a long way. Always send "pragma foreign_keys=on" first thing after opening the db. Some of the types sloppiness can be worked around by declaring tables to be STRICT. You can also add CHECK constraints that a column value is consistent with the underlying representation of the type -- for instance, if you're storing ip addresses in a column of type BLOB, you can add a CHECK that the blob is either 4 or 16 bytes.
BenjiWiebe 9mo ago
SQLite did add 'STRICT' tables for type enforcement. Still doesn't have a huge variety of types though.
- mikeocool 9mo ago
  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.
  - sethops1 9mo ago
    Nearly every default setting in sqlite is "wrong" from the outset, for typical use cases. I'm surprised packages that offer a sane configuration out of the box aren't more popular.
  - sgbeal 9mo ago
    > The fact that they didn’t make STRICT default is really a shame. SQLite makes strong backwards-compatibility guarantees. How many apps would be broken if an Android update suddenly defaulted its internal copy of SQLite to STRICT? Or if it decided to turn on foreign keys by default? Those are rhetorical questions. Any non-0 percentage of affected applications adds up to a big number for software with SQLite's footprint. Software pulling the proverbial rug out from under downstream developers by making incompatible changes is one of the unfortunate evils of software development, but the SQLite project makes every effort to ensure that SQLite doesn't do any rug-tugging.
- andersmurphy 9mo ago
  I mean it has blob types. Which basically means you can implement any type you want. You can also trivially implement custom application functions to work on these blob types in your queries. [1] - [1] https://sqlite.org/appfunc.html https://sqlite.org/appfunc.html

Cthulhu_ 9mo ago

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.

dahart 9mo ago

Partly for the same reason it’s fast for small sites. In their words: “SQLite is not client/server”

jerf 9mo ago

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.

jstummbillig 9mo ago
I mean as in: Most web stacks do not default to sqlite over MySQL or postgres. Why not? Best default for most users, apparently.
- conradkay 9mo ago
  I think in the past it was more obvious. Rails switched to SQLite as the default somewhat recently
  - jstummbillig 9mo ago
    Yeah, that's the one prominent example but, like you said, also just rather recently. Since "the network is slow, duh" has always been true, I wonder why.
    - Kerrick 9mo ago
      It took a lot: https://fractaledmind.com/2023/12/23/rubyconftw/ https://fractaledmind.com/2023/12/23/rubyconftw/ and https://news.ycombinator.com/item?id=39835496 https://news.ycombinator.com/item?id=39835496
    - conradkay 9mo ago
      My guess would be that performance improvements (mostly hardware from Moore's law and the proliferation of SSDs, but also SQLite itself) have led to far fewer websites needing to run on more than 1 computer, and most are fine on a $5/month VPS And stuff like https://litestream.io/ https://litestream.io/ or SQLite adding STRICT mode

Kerrick 9mo ago

It's becoming so! Rails devs are starting to ship SQLite to production. It's not just for their main database either... it's replacing Redis for them, too.