RegreSQL: Regression Testing for PostgreSQL Queries
11 comments
·November 14, 2025jumski
radimm
At this point it supports initialization through the fixtures systems (like inline SQL or SQL files). At the moment they have fixed order, which might lead to some limitations, but I'm already thinking about some pre/post test setup hooks and full schema handling as well (for full schema reloads).
Plus I have whole set of other requirements where RegreSQL suddenly seems to be a good solution.
And without sounding cliche - Thank you for the comment! This is exactly why I forced myself to go public and get this level of feedback.
mickeyp
IMO, you should not avoid triggers if it helps prevent invariants in your database. That is what they are especially good at preventing.
You can instruct postgres to raise exceptions using the same error code that constraints use: that way your clients do not need to know the difference.
mbvisti
I have nothing to add but this looks cool! Will definitely check it out :)
h1fra
Interesting. Perf regression can happen locally but they mostly happen in prod when data change in volume or in shape, can this run safely on a prod db?
radimm
The primary direction is to make RegreSQL part of CI/CD pipelines. In theory in can be run against production DB, but I believe it needs much more work to provide real value there. Thank you for the comment!
null_deref
Yeah my question exactly, another one from me would be will the best practice be to run it periodically?
WilcoKruijer
It's pretty terrible how poorly developers test their database queries. This looks like a great step in the right direction. I think how the ORM story in RegreSQL develops is crucial. The SQLAlchemy integration looks interesting, but at the same time super specific. There are a million ways to generate SQL statements and ORMs are just one of them. A question that comes to mind is how will you handle interactive transactions? I'd say most complexity in queries comes from the back-and-forth between database and server. Is that out of scope?
Would also be fun if you could support PGLite [0], that's what I've been using to write "unit" tests connected to a "real" database.
radimm
OP here - I do agree some of the problems that come with SQL/ORM queries are pretty horrendous and that's exactly where I would like RegreSQL going. For now I can't promise the particular direction, but comments like this are the reason why I pushed myself to release it and keep it beyond my own playground. Thank you!
jci
My goto for this lately has been ephemeralpg [0] and pgTAP [1]. It’s been pretty great
[0] https://github.com/eradman/ephemeralpg [1] https://github.com/theory/pgtap
mrasong
Just found out about pglite, this library is insanely cool. You can even run Postgres right in the browser.
Looks really well thought out and I will be testing it for sure!
I'm wondering how I would be able to regression-test functions in my project (pgflow [0]) - it tracks a graph of state machines modeled in few tables. State is mutated only by calling few exposed SQL functions (task queue worker does it).
Given I can't enforce everything I need with check constraints and I try to avoid triggers if possible, I opted for only using exposed SQL API [1] for setting up state in my pgTAP tests.
It is imperative and harder to maintain, like scripts you described in the article, but really my only option, as I want to have maximum confidence level.
Does RegreSQL support some kind of init scripts or I would need to wire it myself and just run RegreSQL after the proper state is set? Would lose the "run once and get report on everything" benefit then :-(
[0] https://pgflow.dev/ [1] https://github.com/pgflow-dev/pgflow/blob/main/pkgs/core/sup...