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 :-(
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.
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.
Nice! However I would actually advocate for fixtures in application code. I’ve seen too much drift otherwise. And creating “scale” is also easy, just add a for loop :). No programming in yaml needed. As an added benefit you can use the same fixtures for your end to end tests!
So it would be nice if RegreSQL would support fixture hooks for those who like this route.
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?
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!
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.
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!
Wonder if the YAML fixtures drew inspiration from dbt’s unit tests: https://docs.getdbt.com/docs/build/unit-tests#unit-testing-a...
This looks great, any plans for MySQL support (or a similar project), the legacy system I'm working on could really do with this!
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...
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.
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.
Nice! However I would actually advocate for fixtures in application code. I’ve seen too much drift otherwise. And creating “scale” is also easy, just add a for loop :). No programming in yaml needed. As an added benefit you can use the same fixtures for your end to end tests!
So it would be nice if RegreSQL would support fixture hooks for those who like this route.
It's not unreasonable view - noted, will add to my list. Thank you!
I have nothing to add but this looks cool! Will definitely check it out :)
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?
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!
Yeah my question exactly, another one from me would be will the best practice be to run it periodically?
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.
[0] https://pglite.dev/
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
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!
Just found out about pglite, this library is insanely cool. You can even run Postgres right in the browser.