embedded-postgres VS postlite

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)

#. SQLite WAL mode

From https://www.sqlite.org/isolation.html https://news.ycombinator.com/item?id=32247085 :

> [sqlite] WAL mode permits simultaneous readers and writers. It can do this because changes do not overwrite the original database file, but rather go into the separate write-ahead log file. That means that readers can continue to read the old, original, unaltered content from the original database file at the same time that the writer is appending to the write-ahead log

#. superfly/litefs: aFUSE-based file system for replicating SQLite https://github.com/superfly/litefs

#. sqldiff: https://www.sqlite.org/sqldiff.html https://news.ycombinator.com/item?id=31265005

#. dolthub/dolt: https://github.com/dolthub/dolt

> Dolt can be set up as a replica of your existing MySQL or MariaDB database using standard MySQL binlog replication. Every write becomes a Dolt commit. This is a great way to get the version control benefits of Dolt and keep an existing MySQL or MariaDB database.

#. pganalyze/libpg_query: https://github.com/pganalyze/libpg_query :

> C library for accessing the PostgreSQL parser outside of the server environment

#. Ibis + Substrait [ + DuckDB ]

> ibis strives to provide a consistent interface for interacting with a multitude of different analytical execution engines, most of which (but not all) speak some dialect of SQL.

> Today, Ibis accomplishes this with a lot of help from `sqlalchemy` and `sqlglot` to handle differences in dialect, or we interact directly with available Python bindings (for instance with the pandas, datafusion, and polars backends).

> [...] `Substrait` is a new cross-language serialization format for communicating (among other things) query plans. It's still in its early days, but there is already nascent support for Substrait in Apache Arrow, DuckDB, and Velox.

#. benbjohnson/postlite: https://github.com/benbjohnson/postlite

> postlite is a network proxy to allow access to remote SQLite databases over the Postgres wire protocol. This allows GUI tools to be used on remote SQLite databases which can make administration easier.

> The proxy works by translating Postgres frontend wire messages into SQLite transactions and converting results back into Postgres response wire messages. Many Postgres clients also inspect the pg_catalog to determine system information so Postlite mirrors this catalog by using an attached in-memory database with virtual tables. The proxy also performs minor rewriting on these system queries to convert them to usable SQLite syntax.

> Note: This software is in alpha. Please report bugs. Postlite doesn't alter your database unless you issue INSERT, UPDATE, DELETE commands so it's probably safe. If anything, the Postlite process may die but it shouldn't affect your database.

#. > "Hosting SQLite Databases on GitHub Pages" (2021) re: sql.js-httpvfs, DuckDB https://news.ycombinator.com/item?id=28021766

#. awesome-db-tools https://github.com/mgramin/awesome-db-tools

Ben Johnson poked around in this space last year too https://github.com/benbjohnson/postlite

Note that this already exists on top of SQLite proper - authored by Ben Johnson (Litestream, Fly.io etc.) - https://github.com/benbjohnson/postlite

Currently, you need to SSH in and use the sqlite3 CLI on the server. There has been some work in this area but it's all still rough around the edges. I wrote a server called Postlite[1] that exposes remote SQLite databases over the Postgres wire protocol but it's very alpha. :)

I'd love to see more work in this area. Ricardo Ander-Egg wrote a remote management tool called litexplore[2] that connects over SSH to the SQLite CLI behind the scenes. I haven't used it but I think there's a lot of potential with that approach.

[1]: https://github.com/benbjohnson/postlite

[2]: https://github.com/litements/litexplore

I've not use this myself, but Ben Johnson's https://github.com/benbjohnson/postlite in front of SQlite might allow you to use PostgREST? I recall him saying on a podcast that his goal was to be able to point the large ecosystem of PG tools at SQlite.