5 ms·

Postgres is awesome technology but for a lot of applications SQLite will do the job just fine. This gets you a single binary with zero external dependencies.

w-j-w 5y ago
I keep seeing this comment on HN, but I never really get it. In the world of platform as a service, launching a database instance is far easier than managing disk based storage yourself. It also has a very obvious route to horizontally scaling the web process. Deployment is easier too with an external database, there's no data on the disk of the machine running the web process that needs to be carried over.
- tekstar 5y ago
  > launching a database instance is far easier than managing disk based storage yourself Look, it comes down to what you, the developer are comfortable with. If you know and are comfortable with running something like postgres in the cloud, then go for it. Some of us prefer to not. Similarly, not all of us nuke the entire filesystem on every deploy :)
- mtlynch 5y ago
  I had the same objection to SQLite, and then I heard about Litestream, and it won me over.[0] Litestream watches your SQLite database and then streams changes to a cloud storage provider (e.g., S3, Backblaze). You get the performance and simplicity of writing SQLite to the local filesystem, but it's syncing to the cloud. And the cool part is that you don't have to change any of your application code to do it - as far as your app is concerned, it's writing to a local SQLite file. I wrote a little log uploading utility for my business that uses Litestream, and it's been fantastic.[1] It essentially carries around its data with it, so I can deploy my app to Heroku, blow away the instance and then launch it on fly.io, and it pops up with the exact same data.[2] I'm currently in the process of rewriting an open-source AppEngine app to use SQLite + Litestream instead of Google Firestore.[2] It's such a relief to get away from all the complexity of GCP and Firestore and get back to simple SQLite. [0] https://litestream.io/ https://litestream.io/ [1] https://mtlynch.io/litestream/ https://mtlynch.io/litestream/ [2] https://asciinema.org/a/I2HcYheYayeh7aHj23QSY9Vyf/embed?size=medium&speed=2.0&cols=80 https://asciinema.org/a/I2HcYheYayeh7aHj23QSY9Vyf/embed?size... [3] https://github.com/mtlynch/whatgotdone/pull/639 https://github.com/mtlynch/whatgotdone/pull/639
candiddevmike 5y ago
I would consider writable, durable disk to be an external dependency in this context.
- tekstar 5y ago
  You can run sqlite in memory if you pass `:memory:` as the connection string.
  - caseyohara 5y ago
    Does it persist data to disk in memory mode? If not, then I'm not sure I see the point.
    - tekstar 5y ago
      It's great for running tests in parallel. Or for working with data in memory. There's a whole world of software outside of saas crud
      - candiddevmike 5y ago
        An in memory database isn't the same thing as PostgreSQL in this context though.
    - rakoo 5y ago
      SQL is a very nice querying language, if you can stuff your data in it and run structured queries instead of some spaghetti code it can help a lot. This is the same domain as redis initially, ie a "database" with helpful structures and data types for simple data mangling
    - pstuart 5y ago
      If by persist to disk you mean "when you're done", it can by replicating the db to a file. Another option is to back it up over the network, e.g., https://github.com/benbjohnson/litestream https://github.com/benbjohnson/litestream
Ingon 5y ago
The main thing stopping me from going this way is that sqlite forces you to use cgo.
- benhoyt 5y ago
  You should check out https://pkg.go.dev/modernc.org/sqlite https://pkg.go.dev/modernc.org/sqlite -- it's an (automated) conversion of SQLite's C source to Go so you can use SQLite without CGo. Apparently it passes all the SQLite tests. I've used it in a (toy) project, and it seems to work well.
henvic 5y ago
Yes! This is why I made sure to comment about SQLite briefly, and link to this page: https://sqlite.org/whentouse.html https://sqlite.org/whentouse.html
Thaxll 5y ago
How do you do when you have more that one process accessing the DB?
- simonw 5y ago
  SQLite has a WAL mode you can switch on which handles that scenario: https://til.simonwillison.net/sqlite/enabling-wal-mode https://til.simonwillison.net/sqlite/enabling-wal-mode
  - exhilaration 5y ago
    I was curious what WAL mode was and this Sqlite page explains it: https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html
- rakoo 5y ago
  Digression: what's your usecase for having multiple processes share the same database ? SQLite is not an equivalent to a standard networked database, as a developer you're supposed to see it as an enhanced data structure, like an array++. You wouldn't share an array between multiple processes (unless you know exactly what you're doing) so you wouldn't do the same for SQLite. If you really need multiple processes writing the same kind of data, maybe it should be partitioned into multiple independent files ?

w-j-w 5y ago

I keep seeing this comment on HN, but I never really get it. In the world of platform as a service, launching a database instance is far easier than managing disk based storage yourself. It also has a very obvious route to horizontally scaling the web process. Deployment is easier too with an external database, there's no data on the disk of the machine running the web process that needs to be carried over.

tekstar 5y ago
> launching a database instance is far easier than managing disk based storage yourself Look, it comes down to what you, the developer are comfortable with. If you know and are comfortable with running something like postgres in the cloud, then go for it. Some of us prefer to not. Similarly, not all of us nuke the entire filesystem on every deploy :)
mtlynch 5y ago
I had the same objection to SQLite, and then I heard about Litestream, and it won me over.[0] Litestream watches your SQLite database and then streams changes to a cloud storage provider (e.g., S3, Backblaze). You get the performance and simplicity of writing SQLite to the local filesystem, but it's syncing to the cloud. And the cool part is that you don't have to change any of your application code to do it - as far as your app is concerned, it's writing to a local SQLite file. I wrote a little log uploading utility for my business that uses Litestream, and it's been fantastic.[1] It essentially carries around its data with it, so I can deploy my app to Heroku, blow away the instance and then launch it on fly.io, and it pops up with the exact same data.[2] I'm currently in the process of rewriting an open-source AppEngine app to use SQLite + Litestream instead of Google Firestore.[2] It's such a relief to get away from all the complexity of GCP and Firestore and get back to simple SQLite. [0] https://litestream.io/ https://litestream.io/ [1] https://mtlynch.io/litestream/ https://mtlynch.io/litestream/ [2] https://asciinema.org/a/I2HcYheYayeh7aHj23QSY9Vyf/embed?size=medium&speed=2.0&cols=80 https://asciinema.org/a/I2HcYheYayeh7aHj23QSY9Vyf/embed?size... [3] https://github.com/mtlynch/whatgotdone/pull/639 https://github.com/mtlynch/whatgotdone/pull/639

candiddevmike 5y ago

I would consider writable, durable disk to be an external dependency in this context.

tekstar 5y ago
You can run sqlite in memory if you pass `:memory:` as the connection string.
- caseyohara 5y ago
  Does it persist data to disk in memory mode? If not, then I'm not sure I see the point.
  - tekstar 5y ago
    It's great for running tests in parallel. Or for working with data in memory. There's a whole world of software outside of saas crud
    - candiddevmike 5y ago
      An in memory database isn't the same thing as PostgreSQL in this context though.
  - rakoo 5y ago
    SQL is a very nice querying language, if you can stuff your data in it and run structured queries instead of some spaghetti code it can help a lot. This is the same domain as redis initially, ie a "database" with helpful structures and data types for simple data mangling
  - pstuart 5y ago
    If by persist to disk you mean "when you're done", it can by replicating the db to a file. Another option is to back it up over the network, e.g., https://github.com/benbjohnson/litestream https://github.com/benbjohnson/litestream

Ingon 5y ago

The main thing stopping me from going this way is that sqlite forces you to use cgo.

benhoyt 5y ago
You should check out https://pkg.go.dev/modernc.org/sqlite https://pkg.go.dev/modernc.org/sqlite -- it's an (automated) conversion of SQLite's C source to Go so you can use SQLite without CGo. Apparently it passes all the SQLite tests. I've used it in a (toy) project, and it seems to work well.

henvic 5y ago

Yes! This is why I made sure to comment about SQLite briefly, and link to this page: https://sqlite.org/whentouse.html https://sqlite.org/whentouse.html

Thaxll 5y ago

How do you do when you have more that one process accessing the DB?

simonw 5y ago
SQLite has a WAL mode you can switch on which handles that scenario: https://til.simonwillison.net/sqlite/enabling-wal-mode https://til.simonwillison.net/sqlite/enabling-wal-mode
- exhilaration 5y ago
  I was curious what WAL mode was and this Sqlite page explains it: https://www.sqlite.org/wal.html https://www.sqlite.org/wal.html
rakoo 5y ago
Digression: what's your usecase for having multiple processes share the same database ? SQLite is not an equivalent to a standard networked database, as a developer you're supposed to see it as an enhanced data structure, like an array++. You wouldn't share an array between multiple processes (unless you know exactly what you're doing) so you wouldn't do the same for SQLite. If you really need multiple processes writing the same kind of data, maybe it should be partitioned into multiple independent files ?