6 ms·

Yeah. I've gotten into discussions on HN before about this. sqlite is awesome when you run it in one instance on a VM or something, but solving migration betwee

Yeah. I've gotten into discussions on HN before about this. sqlite is awesome when you run it in one instance on a VM or something, but solving migration between hosts or backup via the filesystem seems like a mess. Sure LiteFS helps, but is it better than a managed networked database? > One huge benefit to SQLite is the fact that it runs as an embedded part of your application But all production workloads I've been involved with the last ≈5 years have either been containerised apps offloading state to databases/caches/blob storage etc, or have strived towards that. What I do think would be awesome would be an embeddable Postgres library/binary that could use a single state file on your local filesystem for development and use a networked database in production. Getting the benefits I like most about sqlite locally, and not having to deal with files in production.

eduction 3y ago
> What I do think would be awesome would be an embeddable Postgres library/binary that could use a single state file on your local filesystem for development and use a networked database in production. Absolutely! And also, short of that but incrementally building toward it, some sort of simplification of initial db setup and configuration, including really solid, thoughtful defaults (many of them are but I am guessing they could be substantially improved). I feel like half of people’s attraction to SQLite is the easy setup and first deploy story. Postgres will never match that but could get closer.
- jimbokun 3y ago
  > I feel like half of people’s attraction to SQLite is the easy setup and first deploy story. That is an incredibly important part of the story! Spending a lot of time on configuring and monitoring and operationalizing your database can kill a business iterating on its minimum viable project.
  - icedchai 3y ago
    In production, use a managed service (RDS, Cloud SQL, tons of others...) In dev, set up a Docker container. You won't spend a lot of time on either of these things.
_yy01 3y ago
You can get very close to this with docker compose. You can delete and recreate the volume when you want with `docker compose down --volumes` flag, which is great for testing. Shameless plug: https://mahesh-hegde.github.io/posts/postgres_local_using_docker/ https://mahesh-hegde.github.io/posts/postgres_local_using_do...
sa46 3y ago
> What I do think would be awesome would be an embeddable Postgres library/binary that could use a single state file on your local filesystem for development I wired Postgres up for our local dev. I don't believe in mocking the database, so all our tests and local dev run against a real Postgres instance. The main tricks: - Store the Postgres installation in a known location. - Set the dynamic loader path to the installation lib dir, e.g., LD_PRELOAD. - Don't run CI as root (or patch out Postgres' check for root) - Create and cache the data directory based on the source code migrations. - Use clone file to duplicate the cached data directory to give to individual tests. One thing I'd like to pursue is to store the Postgres data dir in SQLite [1]. Then, I can reset the "file system" using SQL after each test instead of copying the entire datadir. [1]: https://github.com/guardianproject/libsqlfs https://github.com/guardianproject/libsqlfs
- thom 3y ago
  Little use if you’re not on the JVM but I’ve had great success with Embedded Postgres: https://github.com/zonkyio/embedded-postgres https://github.com/zonkyio/embedded-postgres Each test just copies a template database so it’s ultra fast and avoids the need for complicated reset logic.
  - sa46 3y ago
    Yep, I used that trick as well. The evolution we went through: 1. Each test runs all migrations on a fresh database. Each test spends 2.1 seconds on db setup. 2. Each test suite runs all migrations. Each test copies the template database from the test suite. Each suite takes 2.1 seconds to run migrations, but cloning a template database takes 300 ms. 3. Bazel caches the data dir and rebuilds it once for all test suites. Reduces the initial test suite setup from 2.1 seconds to 160 ms (to copy the datadir). 4. Each test in a suite uses clonefile to copy the datadir. Reduces db setup overhead from 300 ms (to copy a template database) to 80 ms (to clonefile a datadir). Currently, most of our testing overhead is clonefile and cleaning up the datadir. I'm interested in a single file sqlite FS because I could be clever and use LD_PRELOAD to replace Postgres's file system operations with sqlite, avoiding most syscalls altogether.
Aurornis 3y ago
> sqlite is awesome when you run it in one instance on a VM or something, This article really needs to be taken in the context of the author’s target audience: He sells courses that primarily target web dev juniors doing learning projects and small projects. That’s why he includes the “most of you reading this” disclaimer in the article: > So, can you use SQLite? For the vast majority of you reading this, the answer is “yes.” Should you use SQLite? I’d say that still for the majority of you reading this, the answer is also “yes.” Pushing strong contrarian opinions is part of his social media marketing style. He’s also pushing a “why I won’t use next.js” article and opinion on his social media, while conveniently omitting the obvious conflict of interest that he sells courses that don’t happen to cover Next.js.
- yawaramin 3y ago
  So if I like apples more than oranges, I write a blog post saying I like apples and won't eat oranges, and I also have a business selling apples...it's a conflict of interest?
  - Aurornis 3y ago
    If a company sells apples and they start pushing social media campaigns about why oranges are bad and they think you should be buying apples instead, then that’s a conflict of interest. I think some people are missing the conflict of interest because the author blurs the lines between his personal opinion and his business. He turned his personal brand into his business and uses his name as his brand. EDIT: I think people are missing the meaning of "conflict of interest". Having a conflict of interest doesn't mean something is wrong. You can be right and have a conflict of interest. The issue is that conflicts of interest exist whenever someone's paycheck depends on an opinion being true. If someone on Twitter was alternating between trying to sell you vitamin supplements and then posting articles about why those vitamin supplements are good for you, HN would have no problem with pointing out the conflict of interest and taking it into consideration in the context of evaluating the claims. If someone's entire job and personal brand are built around selling courses for particular stacks, that's important context to bring up when they start writing about why other technologies are bad. Agree or disagree with the conclusion, but you have to acknowledge that the writings should be read with the conflict of interest taken into account.
appplication 3y ago
> What I do think would be awesome would be an embeddable Postgres library/binary that could use a single state file on your local filesystem for development and use a networked database in production. I mean this is more of less what using a local container for Postgres is. It’s not exactly one file but it’s hidden in a volume somewhere where you don’t need to go file digging, so for all intents and purposes sure, it’s one file. Most ORMs also will let you swap out your RDBMS so you can run sqlite locally and Postgres in prod via the same code. I think this is a bit of an anti pattern though. While the two are similar in most standard use cases, it’s not always apparent when you veer off that standard use case and into something that’s not supported by sqlite. And then all of the sudden you either have a divergence between what works locally vs in production or you start to intentionally limit your app’s capability to only what works with sqlite because you can’t locally validate it.
- geraldwhen 3y ago
  You will run into compatibility issues almost immediately. Running a local Postgres container is really the only option.
- time0ut 3y ago
  My general pattern these days is a compose file with postgres and another container to run my migrations (along with anything else I need). Then I have an integration test suite that hits all the code paths that touch the database. The whole workflow gets run again in CI but with the new image via a profile in my compose file. Its so fast and easy to do at this point.
- captn3m0 3y ago
  Managing Postgres, even via containers is not so simple. Upgrades between major versions will still need a backup+restore for eg. I run a single container Postgres for my homeserver, and have to deal with occasional issues that wouldn’t apply to SQLite (which I run as well).
thaumasiotes 3y ago
> but solving migration between hosts or backup via the filesystem seems like a mess. I don't understand this. How would you solve backups without using the filesystem?
- casselc 3y ago
  https://docs.oracle.com/en-us/iaas/dbcs/doc/back-database-object-storage-using-rman.html https://docs.oracle.com/en-us/iaas/dbcs/doc/back-database-ob... and https://learn.microsoft.com/en-us/sql/relational-databases/tutorial-sql-server-backup-and-restore-to-azure-blob-storage-service?view=sql-server-ver16&tabs=SSMS https://learn.microsoft.com/en-us/sql/relational-databases/t... are a couple examples.
  - thaumasiotes 3y ago
    Why is this considered "not using the filesystem"? What's the conceptual difference between backing up your Oracle database to an Oracle Cloud Infrastructure bucket and backing up your sqlite database to an S3 bucket?
- filleokus 3y ago
  I mean the filesystem is going to be used somewhere of course, and everything depends on your setup / the scale of things. My point was more that with a networked database, you can easily centralise your backing up of databases. If you are in the cloud with managed databases, the backing up is kinda solved out of the box. If you are doing it yourself or on-prem, you probably have a database cluster of some kind where backing up can be managed (which of course involves a filesystem at some point). But if you are using sqlite, every sqlite file across your organisation needs to be backed up. You'll probably have some scripts creating the actual snapshot and then some other scripts shipping that away somewhere. Those scripts probably need to be quite bespoke for different hosting solutions. Are you going to ssh into the vm's running the apps and do the backup that way, or will you be running the backing up scripts on each vm? Of course if you have a single VM hosting a few apps with one sqlite database each, none of this is an issue, and it might be easier in the short term than a networked database. Or even if you are operating at "scale" and treating your VM's more like pets than cattle, it's probably not going to be an issue either.

eduction 3y ago

> What I do think would be awesome would be an embeddable Postgres library/binary that could use a single state file on your local filesystem for development and use a networked database in production. Absolutely! And also, short of that but incrementally building toward it, some sort of simplification of initial db setup and configuration, including really solid, thoughtful defaults (many of them are but I am guessing they could be substantially improved). I feel like half of people’s attraction to SQLite is the easy setup and first deploy story. Postgres will never match that but could get closer.

jimbokun 3y ago
> I feel like half of people’s attraction to SQLite is the easy setup and first deploy story. That is an incredibly important part of the story! Spending a lot of time on configuring and monitoring and operationalizing your database can kill a business iterating on its minimum viable project.
- icedchai 3y ago
  In production, use a managed service (RDS, Cloud SQL, tons of others...) In dev, set up a Docker container. You won't spend a lot of time on either of these things.

_yy01 3y ago

You can get very close to this with docker compose. You can delete and recreate the volume when you want with `docker compose down --volumes` flag, which is great for testing. Shameless plug: https://mahesh-hegde.github.io/posts/postgres_local_using_docker/ https://mahesh-hegde.github.io/posts/postgres_local_using_do...

sa46 3y ago

> What I do think would be awesome would be an embeddable Postgres library/binary that could use a single state file on your local filesystem for development I wired Postgres up for our local dev. I don't believe in mocking the database, so all our tests and local dev run against a real Postgres instance. The main tricks: - Store the Postgres installation in a known location. - Set the dynamic loader path to the installation lib dir, e.g., LD_PRELOAD. - Don't run CI as root (or patch out Postgres' check for root) - Create and cache the data directory based on the source code migrations. - Use clone file to duplicate the cached data directory to give to individual tests. One thing I'd like to pursue is to store the Postgres data dir in SQLite [1]. Then, I can reset the "file system" using SQL after each test instead of copying the entire datadir. [1]: https://github.com/guardianproject/libsqlfs https://github.com/guardianproject/libsqlfs

thom 3y ago
Little use if you’re not on the JVM but I’ve had great success with Embedded Postgres: https://github.com/zonkyio/embedded-postgres https://github.com/zonkyio/embedded-postgres Each test just copies a template database so it’s ultra fast and avoids the need for complicated reset logic.
- sa46 3y ago
  Yep, I used that trick as well. The evolution we went through: 1. Each test runs all migrations on a fresh database. Each test spends 2.1 seconds on db setup. 2. Each test suite runs all migrations. Each test copies the template database from the test suite. Each suite takes 2.1 seconds to run migrations, but cloning a template database takes 300 ms. 3. Bazel caches the data dir and rebuilds it once for all test suites. Reduces the initial test suite setup from 2.1 seconds to 160 ms (to copy the datadir). 4. Each test in a suite uses clonefile to copy the datadir. Reduces db setup overhead from 300 ms (to copy a template database) to 80 ms (to clonefile a datadir). Currently, most of our testing overhead is clonefile and cleaning up the datadir. I'm interested in a single file sqlite FS because I could be clever and use LD_PRELOAD to replace Postgres's file system operations with sqlite, avoiding most syscalls altogether.

Aurornis 3y ago

> sqlite is awesome when you run it in one instance on a VM or something, This article really needs to be taken in the context of the author’s target audience: He sells courses that primarily target web dev juniors doing learning projects and small projects. That’s why he includes the “most of you reading this” disclaimer in the article: > So, can you use SQLite? For the vast majority of you reading this, the answer is “yes.” Should you use SQLite? I’d say that still for the majority of you reading this, the answer is also “yes.” Pushing strong contrarian opinions is part of his social media marketing style. He’s also pushing a “why I won’t use next.js” article and opinion on his social media, while conveniently omitting the obvious conflict of interest that he sells courses that don’t happen to cover Next.js.

yawaramin 3y ago
So if I like apples more than oranges, I write a blog post saying I like apples and won't eat oranges, and I also have a business selling apples...it's a conflict of interest?
- Aurornis 3y ago
  If a company sells apples and they start pushing social media campaigns about why oranges are bad and they think you should be buying apples instead, then that’s a conflict of interest. I think some people are missing the conflict of interest because the author blurs the lines between his personal opinion and his business. He turned his personal brand into his business and uses his name as his brand. EDIT: I think people are missing the meaning of "conflict of interest". Having a conflict of interest doesn't mean something is wrong. You can be right and have a conflict of interest. The issue is that conflicts of interest exist whenever someone's paycheck depends on an opinion being true. If someone on Twitter was alternating between trying to sell you vitamin supplements and then posting articles about why those vitamin supplements are good for you, HN would have no problem with pointing out the conflict of interest and taking it into consideration in the context of evaluating the claims. If someone's entire job and personal brand are built around selling courses for particular stacks, that's important context to bring up when they start writing about why other technologies are bad. Agree or disagree with the conclusion, but you have to acknowledge that the writings should be read with the conflict of interest taken into account.

appplication 3y ago

> What I do think would be awesome would be an embeddable Postgres library/binary that could use a single state file on your local filesystem for development and use a networked database in production. I mean this is more of less what using a local container for Postgres is. It’s not exactly one file but it’s hidden in a volume somewhere where you don’t need to go file digging, so for all intents and purposes sure, it’s one file. Most ORMs also will let you swap out your RDBMS so you can run sqlite locally and Postgres in prod via the same code. I think this is a bit of an anti pattern though. While the two are similar in most standard use cases, it’s not always apparent when you veer off that standard use case and into something that’s not supported by sqlite. And then all of the sudden you either have a divergence between what works locally vs in production or you start to intentionally limit your app’s capability to only what works with sqlite because you can’t locally validate it.

geraldwhen 3y ago
You will run into compatibility issues almost immediately. Running a local Postgres container is really the only option.
time0ut 3y ago
My general pattern these days is a compose file with postgres and another container to run my migrations (along with anything else I need). Then I have an integration test suite that hits all the code paths that touch the database. The whole workflow gets run again in CI but with the new image via a profile in my compose file. Its so fast and easy to do at this point.
captn3m0 3y ago
Managing Postgres, even via containers is not so simple. Upgrades between major versions will still need a backup+restore for eg. I run a single container Postgres for my homeserver, and have to deal with occasional issues that wouldn’t apply to SQLite (which I run as well).

thaumasiotes 3y ago

> but solving migration between hosts or backup via the filesystem seems like a mess. I don't understand this. How would you solve backups without using the filesystem?

casselc 3y ago
https://docs.oracle.com/en-us/iaas/dbcs/doc/back-database-object-storage-using-rman.html https://docs.oracle.com/en-us/iaas/dbcs/doc/back-database-ob... and https://learn.microsoft.com/en-us/sql/relational-databases/tutorial-sql-server-backup-and-restore-to-azure-blob-storage-service?view=sql-server-ver16&tabs=SSMS https://learn.microsoft.com/en-us/sql/relational-databases/t... are a couple examples.
- thaumasiotes 3y ago
  Why is this considered "not using the filesystem"? What's the conceptual difference between backing up your Oracle database to an Oracle Cloud Infrastructure bucket and backing up your sqlite database to an S3 bucket?
filleokus 3y ago
I mean the filesystem is going to be used somewhere of course, and everything depends on your setup / the scale of things. My point was more that with a networked database, you can easily centralise your backing up of databases. If you are in the cloud with managed databases, the backing up is kinda solved out of the box. If you are doing it yourself or on-prem, you probably have a database cluster of some kind where backing up can be managed (which of course involves a filesystem at some point). But if you are using sqlite, every sqlite file across your organisation needs to be backed up. You'll probably have some scripts creating the actual snapshot and then some other scripts shipping that away somewhere. Those scripts probably need to be quite bespoke for different hosting solutions. Are you going to ssh into the vm's running the apps and do the backup that way, or will you be running the backing up scripts on each vm? Of course if you have a single VM hosting a few apps with one sqlite database each, none of this is an issue, and it might be easier in the short term than a networked database. Or even if you are operating at "scale" and treating your VM's more like pets than cattle, it's probably not going to be an issue either.