5 ms·

SQLite is the most underrated database of all time. I know it's popular, but nowhere near to the level it should be.

by Lhiw 5y ago

jerodsanto 5y ago
"SQLite is likely used more than all other database engines combined" So, like, even more popular than that?! https://www.sqlite.org/mostdeployed.html https://www.sqlite.org/mostdeployed.html
rmbyrro 5y ago
Why so? I have the impression every developer relies on it for a lot of local stuff, and many libraries also use it locally in users' machines for persistence needs. What do you think it "should be" and is missing?
- chaxor 5y ago
  I think they have this perspective because SQLite is almost never mentioned when DBs are discussed. Almost everyone pushes postgres, which I think is often a bit insane. Postgres or (often rarely) MySQL is given as the answer to most DB questions, even when coming from clearly new devs. It's not often that the dev needs any of the large amount of setup/permissions required for postgres/MySQL. The fact that SQLite comes as part of python's standard library should be used as second nature for most devs. I feel the same way - that SQLite should be the default answer, and one should reach towards postgres or MySQL unless you really *have* to.
  - cortesoft 5y ago
    What? SQLite is rarely the answer to replace Postgres or MySQL... it is not designed for network access nor high availability.
    - chaxor 5y ago
      Most of the answers I was referring to are miles away from those requirements. Postgres fanbois love to suggest it for everything - for which ahuge percentage of the time is simply 'This csv is too big, how do I load part of it?'. The suggestion of Postgres is very often not required, because there isn't a need for multiple users, different permissions, password protection, etc.
  - rmbyrro 5y ago
    SQLite is not meant to compete directly with MySQL or Postgres. It's meant for local usage only, where you don't need high concurrency, managing multiple connections, replication, etc. I mean, you could probably use SQLite in many simple cases which currently use Postgres (personal servers, small apps), but advocating it as default solution is inadequate at best, if not misleading to people starting out in the field.
    - Lhiw 5y ago
      This view is the view I was talking about in the op. Inadequate for what? Most persistence layers I've ever seen did not need MySQL, postgres or MongoDB. > high concurrency, managing multiple connections, replication In the grand scheme of things, basically nobody needs these things. Sorry, your app is overengineered. Your app actually suffered in areas such as performance, security, portability, complexity and cost. Developers love to prematurely optimise everything. Most SaaS products today don't need a dedicated database server. Most apps that require a database server backend would be better with sqlite, most Dev/test/staging/prod pipelines would be better served with sqlite files.
wenc 5y ago
SQLite is a file format with a SQL engine which makes it a great embedded database. It is not an RDBMS in the true sense so in my opinion it is correctly rated. I tried using SQLite to solve problems which normally would require an RDBMS and ran into so many issues like the lack of enforced static types, built in date or decimal types (and fast aggregations on dates), concurrent writes, etc. It’s only when you’ve gone through this process that you’ll realize that SQLite it not the database you think it is — and SQLite itself is up front about that: https://www.sqlite.org/aff_short.html https://www.sqlite.org/aff_short.html
- simonw 5y ago
  Enforced static types are now available, as of SQLite 3.37.0 which came out last week: https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html
dnautics 5y ago
> nowhere near to the level it should be. I get the impression that it's used exactly as it should be, in systems that need a database but don't need clustering or failover strategies.
- pletnes 5y ago
  As far as I can tell, there’s far too many file formats out there. I’m sure sqlite should be used more than it currently is.
- bob1029 5y ago
  > but don't need clustering or failover strategies. Even for those cases where you do need it, there are emerging options. You can handle replication 100% in business logic (something I personally enjoy), or you can use a path like dqlite to replicate the physical WAL log.

SQLite is the most underrated database of all time. I know it's popular, but nowhere near to the level it should be.

by Lhiw 5y ago

SQLite is the most underrated database of all time. I know it's popular, but nowhere near to the level it should be.

"SQLite is likely used more than all other database engines combined" So, like, even more popular than that?! https://www.sqlite.org/mostdeployed.html https://www.sqlite.org/mostdeployed.html

rmbyrro 5y ago

Why so? I have the impression every developer relies on it for a lot of local stuff, and many libraries also use it locally in users' machines for persistence needs. What do you think it "should be" and is missing?

chaxor 5y ago
I think they have this perspective because SQLite is almost never mentioned when DBs are discussed. Almost everyone pushes postgres, which I think is often a bit insane. Postgres or (often rarely) MySQL is given as the answer to most DB questions, even when coming from clearly new devs. It's not often that the dev needs any of the large amount of setup/permissions required for postgres/MySQL. The fact that SQLite comes as part of python's standard library should be used as second nature for most devs. I feel the same way - that SQLite should be the default answer, and one should reach towards postgres or MySQL unless you really *have* to.
- cortesoft 5y ago
  What? SQLite is rarely the answer to replace Postgres or MySQL... it is not designed for network access nor high availability.
  - chaxor 5y ago
    Most of the answers I was referring to are miles away from those requirements. Postgres fanbois love to suggest it for everything - for which ahuge percentage of the time is simply 'This csv is too big, how do I load part of it?'. The suggestion of Postgres is very often not required, because there isn't a need for multiple users, different permissions, password protection, etc.
- rmbyrro 5y ago
  SQLite is not meant to compete directly with MySQL or Postgres. It's meant for local usage only, where you don't need high concurrency, managing multiple connections, replication, etc. I mean, you could probably use SQLite in many simple cases which currently use Postgres (personal servers, small apps), but advocating it as default solution is inadequate at best, if not misleading to people starting out in the field.
  - Lhiw 5y ago
    This view is the view I was talking about in the op. Inadequate for what? Most persistence layers I've ever seen did not need MySQL, postgres or MongoDB. > high concurrency, managing multiple connections, replication In the grand scheme of things, basically nobody needs these things. Sorry, your app is overengineered. Your app actually suffered in areas such as performance, security, portability, complexity and cost. Developers love to prematurely optimise everything. Most SaaS products today don't need a dedicated database server. Most apps that require a database server backend would be better with sqlite, most Dev/test/staging/prod pipelines would be better served with sqlite files.

wenc 5y ago

SQLite is a file format with a SQL engine which makes it a great embedded database. It is not an RDBMS in the true sense so in my opinion it is correctly rated. I tried using SQLite to solve problems which normally would require an RDBMS and ran into so many issues like the lack of enforced static types, built in date or decimal types (and fast aggregations on dates), concurrent writes, etc. It’s only when you’ve gone through this process that you’ll realize that SQLite it not the database you think it is — and SQLite itself is up front about that: https://www.sqlite.org/aff_short.html https://www.sqlite.org/aff_short.html

simonw 5y ago
Enforced static types are now available, as of SQLite 3.37.0 which came out last week: https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html

dnautics 5y ago

> nowhere near to the level it should be. I get the impression that it's used exactly as it should be, in systems that need a database but don't need clustering or failover strategies.

pletnes 5y ago
As far as I can tell, there’s far too many file formats out there. I’m sure sqlite should be used more than it currently is.
bob1029 5y ago
> but don't need clustering or failover strategies. Even for those cases where you do need it, there are emerging options. You can handle replication 100% in business logic (something I personally enjoy), or you can use a path like dqlite to replicate the physical WAL log.