embedded-postgres VS litestream

Little use if you’re not on the JVM but I’ve had great success with 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.

Outside of differences between assertion-based unit tests and property-based tests (both of which are worth doing), I don't think framework makes much difference. But your approach to testing definitely does.

I think every language having its own testing framework is good, even for things like functional tests which can often be externalised. Tests are an essential part of every project and should be well integrated with the rest of the codebase and the team creating it. Often, the tests are the only good place to go and see what an app actually _does_ and so they form an essential part of the documentation.

In my experience it's very rare that you can effectively create and maintain something like Cucumber tests owned by anyone but the team implementing the code so there's little benefit to translating from a text DSL like that. But the language used is definitely useful, so what I like to see is code in the implementation language that matches the Given/When/Then structure of those tests, but instead of reusable text steps you just have reusable functions which take parameters. This means you can easily refactor, and use the full functionality of your IDE to suggest and go to definitions etc. No matter what, you should treat your test code the same way you do everything else - abstractions matter, so functional tests at the top level should rarely just be about clicking on things and asserting other things, they should be in the language of the domain.

Functional tests are worth much more than unit tests. No only do they test the only things of actual business value, they are also more robust in the face of implementation refactorings and so require less rework (unless you're being overly specific with CSS selectors etc). Unit tests are often highly coupled to specific implementations and can be a poor investment, especially early in a project. I believe a good balance is functional and integration tests that explore the various paths through your app and prove everything's hooked up, coupled with property based unit tests for gnarly or repetitive logic that isn't worth endlessly iterating via the UI. All other unit tests are optional and at the discretion of the implementer.

You should be able to mock out every major articulation point in your code, but it's generally preferable if you can mock _real_ dependencies. That is, instead of mocking out a 'repository' abstraction that looks stuff up and returns canned data, have a real test database against which you look up real data (created by steps in your functional tests). This reduces risk and cognitive overhead (you're not having to encode too many assumptions in your test suite) and doesn't have to be as slow as people like to make out - Embedded Postgres is quite fast, for example:

https://github.com/zonkyio/embedded-postgres

Same with network services - it's not slow to chat to localhost and you'll find more issues testing proper round-trips. I have not found "assert that you called X" style testing with mocks useful - you care about outcomes, not implementation details.

Beyond all that, as long as you can make assertions that generate clear error messages, you're fine.

I use an embedded postgres testing library for the JVM that does something along those lines.

Well no actually it just unpacks the tar file in a temp dir and runs the full postgres, but it mostly feels like what you describe (minus the single file part) and starts surprisingly fast. That would totally work for a little proof of concept (https://github.com/zonkyio/embedded-postgres)

I have not, but I keep meaning to collate everything I've learned into a set of useful defaults just to remind myself what settings I should be enabling and why.

Regarding Litestream, I learned pretty much all I know from their documentation: https://litestream.io/

This presentation is focused on the use-case of vertically scaling a single server and driving everything through that app server, which is running SQLite embedded within your application process.

This is the sweet-spot for SQLite applications, but there have been explorations and advances to running SQLite across a network of app servers. LiteFS (https://fly.io/docs/litefs/), the sibling to Litestream for backups (https://litestream.io), is aimed at precisely this use-case. Similarly, Turso (https://turso.tech) is a new-ish managed database company for running SQLite in a more traditional client-server distribution.

This post intends to help you setup replication for SQLite using Litestream.

I've been messing around with litestream. It is so cool. And, I either found a bug in the -timestamp switch or don't understand it correctly.

What I want to do is time travel into my sqlite database. I'm trying to do some forensics on why my web service returned the wrong data during a production event. Unfortunately, after the event, someone deleted records from the database and I'm unsure what the data looked like and am having trouble recreating the production issue.

Litestream has this great switch: -timestamp. If you use it (AFAICT) you can time travel into your database and go back to the database state at that moment. However, it does not seem to work as I expect it to:

https://github.com/benbjohnson/litestream/issues/564

I have the entirety of the sqlite database from the production event as well. Is there a way I could cycle through the WAL files and restore the database to the point in time before the records I need were deleted?

Will someone take sqlite and compile it into the browser using WASM so I can drag a sqlite database and WAL files into it and then using a timeline slider see all the states of the database over time? :)

We're using SQLite in production very heavily with millions of databases and fairly high operations throughput.

But we did run into some scariness around trying to use Litestream that put me off it for the time being. Litestream is really cool but it is also very much a cool hack and the risk of database corruption issues feels very real.

The scariness I ran into was related to this issue https://github.com/benbjohnson/litestream/issues/510

Litestream is a library that allows you to easily create backups. You can probably just do analytic queries on the backup data and reduce load on your server.

https://litestream.io/

One possible strategy is to have one directory/file per customer which is one SQLite file. But then as the user logs in, you have to look up first what database they should be connected to.

OR somehow derive it from the user ID/username. Keeping all the customer databases in a single directory/disk and then constantly "lite streaming" to S3.

Because each user is isolated, they'll be writing to their own database. But migrations would be a pain. They will have to be rolled out to each database separately.

One upside is, you can give users the ability to take their data with them, any time. It is just a single file.

[0]. https://litestream.io/

Upstream Kuma uses a local SQLite database to store account data, configuration for services to monitor, notification settings, and more. To make sure that our data is available across redeploys, we will bundle Uptime Kuma with Litestream, a project that implements streaming replication for SQLite databases to a remote object storage provider. Effectively, this allows us to treat the local SQLite database as if it were securely stored in a remote database.