embedded-postgres VS sqlite-utils

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)

https://github.com/simonw/sqlite-utils

So, his code might not be a good place to find best patterns (for ex, I don't think they are fully typed), but his repos are very pragmatic, and his development process is super insightful (well documented PRs for personal repos!). Best part, he blogs about every non-trivial update, so you get all the context!

Sounds like your problem is with SQLAlchemy, not with SQLite.

My https://sqlite-utils.datasette.io library might be a better fit for you. It's a much thinner abstraction than SQLAlchemy.

There are a few things you can do here.

SQLite is great at JSON - so I often dump JSON structures in a TEXT column and query them using https://www.sqlite.org/json1.html

I also have plugins for running jq() functions directly in SQL queries - https://datasette.io/plugins/datasette-jq and https://github.com/simonw/sqlite-utils-jq

I've been trying to drive the cost of turning semi-structured data into structured SQL queries down as much as possible with https://sqlite-utils.datasette.io - see this tutorial for more: https://datasette.io/tutorials/clean-data

This is also an area that I'm starting to explore with LLMs. I love the idea that you could take a bunch of messy data, tell Datasette Cloud "I want this imported into a table with this schema"... and it does that.

I have a prototype of this working now, I hope to turn it into an open source plugin (and Datasette Cloud feature) pretty soon. It's using this trick: https://til.simonwillison.net/gpt3/openai-python-functions-d...

I've baked a ton of different SQLite tricks - including things like full-text indexing support and advanced alter table methods - into my sqlite-utils CLI tool and Python library: https://sqlite-utils.datasette.io

My Datasette project provides tools for exploring, analyzing and publishing SQLite databases, plus ways to expose them via a JSON API: https://datasette.io

I've also written a ton of stuff about SQLite on my two blogs:

- https://simonwillison.net/tags/sqlite/

- https://til.simonwillison.net/sqlite

This is really fun. I have an experimental branch of my sqlite-utils CLI tool (which has dozens of sub-commands) running with this now and it really did only take 4 lines of code - I'm treating Trogon as an optional dependency because people using my package as a Python library rather than a CLI tool may not want the extra installed components:

https://github.com/simonw/sqlite-utils/commit/ec12b780d5dcd6...

There's an animated GIF demo of the result here: https://github.com/simonw/sqlite-utils/issues/545#issuecomme...

My project https://datasette.io/ is ideal for this kind of thing. You can use https://sqlite-utils.datasette.io/ to load JSON data into a SQLite database, then publish it with Datasette.

I've been using this for about six months now and I absolutely love it.

Make never stuck for me - I couldn't quite get it to fit inside my head.

Just has the exact set of features I want.

Here's one example of one of my Justfiles: https://github.com/simonw/sqlite-utils/blob/fc221f9b62ed8624... - documented here: https://sqlite-utils.datasette.io/en/stable/contributing.htm...

I also wrote about using Just with Django in this TIL: https://til.simonwillison.net/django/just-with-django

SQLite with the open source toolchain I've been building over the past five years:

https://datasette.io as the interface for running queries against (and visualizing) my data.

https://sqlite-utils.datasette.io/ as a set of tools for creating and modifying my databases (inserting JSON or CSV data, enabling full text search text)

https://dogsheep.github.io as a suite of tools for importing my personal data - see also this talk I gave about that project: https://simonwillison.net/2020/Nov/14/personal-data-warehous...

Here's an example: https://github.com/simonw/sqlite-utils/pull/468

> After identifying about 7 commits (with pretty basic/useless messages, and no PR link!), I then had to find the corresponding PRs based on timestamps, and search the PR history for PRs merged around those timestamps.

Not sure if this would save any time, but it is possible to search PRs by commit. For example, say git blame led me to this commit: https://github.com/simonw/sqlite-utils/commit/129141572f249e...

I could have found PR #373 via this search: https://github.com/simonw/sqlite-utils/pulls?q=bb16f52681b6d...

> I thus treat PRs as ephemeral

I think I see what you're saying but as others have pointed out, sometimes you want to add screenshots etc to the context, and you can't capture this kind of info in commit messages. So then you have two choices: issues or PRs.

> Then any review comments are preferably not addressed directly in the PR

I would think that sometimes you really do want to have a back and forth conversation in the PR, rather than just a "make this change" -> "ok done" type of feedback loop.

I view the PR as an decent place for all of this because it's basically a commit of commits, capturing the related changes/conversation/context all in a single place at the point of merge.