pgtap and sqitch - recommended test style

[Matt DeLuco]

What is the recommended test style in combination with sqitch - test scripts or xUnit style?

If I go with xUnit, is the intent for the test functions to always exist in the DB, in e.g. a “test” schema? If I’m using sqitch with this approach, do I then also write sqitch migrations for my test functions?

I originally wrote xUnit for my project, and have an SQL script that loads them all up in a transaction, calls runtests() then rolls back. Should I just leave the test functions in the DB? Or just go with scripts and the pg_prove harness?

With test scripts, I presume I would commit changes to the test scripts in git along with my sqitch migration?

I recently reviewed the sqitch Agile Database Development slide deck and down near the bottom with the rework example it got me thinking about how to reconcile migrations with pgtap tests, and which approach works better with sqitch.

Thanks!

Matt

[David E. Wheeler]

Hi Matt,

On Aug 13, 2025, at 23:37, 'Matt DeLuco' via pgTAP Users <pgtap...@googlegroups.com> wrote:

> What is the recommended test style in combination with sqitch - test scripts or xUnit style?

I’m biased, of course, as a long-time user of scripts. They’re also easy to integrate with Sqitch, including templating support:

https://justatheory.com/2014/01/templating-tests-with-sqitch/

> If I go with xUnit, is the intent for the test functions to always exist in the DB, in e.g. a “test” schema? If I’m using sqitch with this approach, do I then also write sqitch migrations for my test functions?

I don’t like to have test stuff in my production database, personally, which is another reason I prefer scripts. But I would think the expectation is for xUnit tests to live in a schema, which you could omit deploying to prod.

> I originally wrote xUnit for my project, and have an SQL script that loads them all up in a transaction, calls runtests() then rolls back. Should I just leave the test functions in the DB? Or just go with scripts and the pg_prove harness?

I mean, whatever works best for you, TBH.

> With test scripts, I presume I would commit changes to the test scripts in git along with my sqitch migration?

That’s how I would do it, yes.

> I recently reviewed the sqitch Agile Database Development slide deck and down near the bottom with the rework example it got me thinking about how to reconcile migrations with pgtap tests, and which approach works better with sqitch.

Woah, that takes me back! Yeah, the trick of course is to test the latest reworking of a change, not previous versions.

Best,

David

[Logan Grosz]

I don't mean to hijack the thread, but I have a project that uses both Sqitch and pgTAP that's I've been restructuring recently. My project structure looks something like

./deploy

./revert

./t

./verify

My pgTAP tests live in ./t and test my "current" (as in SCM) schema and I run them with `pg_prove`. My verify scripts are quite minimal and don't leverage pgTAP at all. Is this redundant? Could I just be integrating my tests in the verify scripts (removing ./t entirely) and running them through `sqitch verify` instead?

--
You received this message because you are subscribed to the Google Groups "pgTAP Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to pgtap-users...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/pgtap-users/BBDE01E6-40C3-4F2E-9A1D-4F9664DFF51D%40justatheory.com.

[David E. Wheeler]

On Aug 14, 2025, at 19:11, Logan Grosz <logan...@gmail.com> wrote:

> My pgTAP tests live in ./t and test my "current" (as in SCM) schema and I run them with `pg_prove`. My verify scripts are quite minimal and don't leverage pgTAP at all. Is this redundant? Could I just be integrating my tests in the verify scripts (removing ./t entirely) and running them through `sqitch verify` instead?

You could, and some do. I personally only use verify scripts to validate that a change succeeded, not for unit testing. I like a separate pgTAP test suite for that, personally. Pretty similar to what you have, it sounds like.

D

[Logan Grosz]

Sounds good, I'll keep it then. Currently, they wouldn't be different, but I suppose when I have more complex functions the dedicated test suite will come in handy.

[Matt DeLuco]

Yes, your approach seems inline with the “verify” tests from the sqitch documentation.

But as my database and my DB functions evolve over time, so must their unit tests (testing for current behaviours, as you describe.) So my question is more around keeping my pgTAP tests up to date, and how those tests are migrated along with the DB changes.

Thanks for the insight!

Matt

On Aug 14, 2025, at 9:47 AM, Logan Grosz <logan...@gmail.com> wrote:

This doesnt really answer your question "which is best," but I use both. Here's an anecdote...

My migration tests are usually just things like "does the table and its columns exist" and my pgTAP tests are much more specific, testing constraints, function output, etc.

I am usually looking at my pgTAP tests for current behaviors. Whereas I only run my verifies on deploy or revert.

--
You received this message because you are subscribed to the Google Groups "pgTAP Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to pgtap-users...@googlegroups.com.

To view this discussion visit https://groups.google.com/d/msgid/pgtap-users/31545f43-3d34-4fa6-a545-b3e8ca9ae963n%40googlegroups.com.

[Logan Grosz]

Ah, I see. I must've misread. I just patch the tests directly like I would code in any other codebase. Assuming a current changeset, I only have tests that run on the completely deployed plan. Perhaps that's overlooking something, but I haven't had the need for anything greater. 

[Logan Grosz]

This doesnt really answer your question "which is best," but I use both. Here's an anecdote...

My migration tests are usually just things like "does the table and its columns exist" and my pgTAP tests are much more specific, testing constraints, function output, etc.

I am usually looking at my pgTAP tests for current behaviors. Whereas I only run my verifies on deploy or revert.

On Wed, Aug 13, 2025, 9:37 PM 'Matt DeLuco' via pgTAP Users <pgtap...@googlegroups.com> wrote:

--

[Matt DeLuco]

Hey Logan,

You may find my question (and David’s answer) on the Sqitch mailing list relevant here!

https://groups.google.com/g/sqitch-users/c/DAIzwMz1O5I

Matt

[Logan Grosz]

Hey, Matt

I appreciate you digging up that conversation. That's exactly what I needed to read.

[David E. Wheeler]

On Aug 15, 2025, at 22:31, Logan Grosz <logan...@gmail.com> wrote:

> I appreciate you digging up that conversation. That's exactly what I needed to read.

Oh hey, look at that, so clear! Maybe I should turn that into a blog post :-)

D

[kevin]

On 8/14/25 08:51, 'Matt DeLuco' via pgTAP Users wrote:
> Yes, your approach seems inline with the “verify” tests from the
> sqitch documentation.
>
> But as my database and my DB functions evolve over time, so must their
> unit tests (testing for current behaviours, as you describe.) So my
> question is more around keeping my pgTAP tests up to date, and how
> those tests are migrated along with the DB changes.
>

Hi Matt,

Like Logan, I find most changes are pretty minimal once you get things
up and running, so it's easy enough to manually update the tests.

That being said, what to do when first implementing a DB or if you add
several new tables and such? Well, assuming you have a "gold standard
DB" (or a DB you *know* to be correct), you could use that DB to create
your tests to check the table, columns, types, defaults, indexes, FKs,
etc., or so the theory goes. To that end, I wrote a Perl program to go
thru the various pg_catalog & information_schema tables & views and
create the pgTap tests to verify structure. There are a few things it
doesn't do, but it does the majority of the work of writing the tests
for you. Having used it for a while, I can see ways to add to it and
perhaps I will soon (reason below). [Note, I don't use sqitch so I don't
know if this fits with what you do or not. Obviously, it can't create
your business function result tests, but just keep a separate file for
that.]

David,

I'm willing to donate the tool to help out other Pg admins/users. It
doesn't belong in the pgTap extension, but is an auxiliary tool. Would
you be willing to put it on on the pgTap website for download? Or
perhaps a sub-dir under "tools" in the github repo would be better? Of
if you'd prefer, I could create a github project for it and you could
mention it exists.

<plug class=hopeful>I was laid off this week (and I might have some
extra time to work on things like this very soon). So if anyone is
looking for a developer who knows Pg, please contact me directly. I live
in the DFW area, but remote work is fine too. My email should be in the
FROM email header.</plug>

Kevin

[David E. Wheeler]

On Aug 16, 2025, at 15:34, kevin <kbra...@pwhome.com> wrote:

> I'm willing to donate the tool to help out other Pg admins/users. It doesn't belong in the pgTap extension, but is an auxiliary tool. Would you be willing to put it on on the pgTap website for download? Or perhaps a sub-dir under "tools" in the github repo would be better? Of if you'd prefer, I could create a github project for it and you could mention it exists.

Sounds similar to pg_tapgen.

https://github.com/theory/tap-parser-sourcehandler-pgtap/blob/develop/bin/pg_tapgen

Maybe they could be merged?

> <plug class=hopeful>I was laid off this week (and I might have some extra time to work on things like this very soon). So if anyone is looking for a developer who knows Pg, please contact me directly. I live in the DFW area, but remote work is fine too. My email should be in the FROM email header.</plug>

Will keep that in mind!

Best,

David