Horton

Thread

  1. Unit tests and foreign key constraints

    Andy Chambers <achambers.home@gmail.com> — 2015-05-21T19:39:01Z

    Hey All,

I've started trying to use foreign key constraints in my schema but it
seems to make it more difficult to write unit tests that touch the database
because each test now requires more setup data to satisfy the foreign key
constraint. (I know some say your unit tests shouldn't touch the DB but the
more full stack tests I have, the better I sleep at night :-))

I wondered if anyone else has run into this problem and found a good
strategy to mitigate it. I thought I might be able to make these
constraints deferred during a test run since I have automatic rollback
after each test but even after "set constraints all deferred", I still got
a foreign key violation during my test run if the test tries to insert data
with a non-existent foreign key.

Cheers,
Andy

  2. Re: Unit tests and foreign key constraints

    Dave Owens <dave@teamunify.com> — 2015-05-21T20:33:46Z

    >
> I know some say your unit tests shouldn't touch the DB but the more full
> stack tests I have, the better I sleep at night :-))
>

Unit tests really should be about testing individual bits of logic.  Does a
single method do the desired thing, and not the undesired thing...
Ideally, your data access layer should be interchangeable, ie: use a real
database record in production, but use a mock database record for unit
tests.

I would consider database access to be an integration test, something that
you run periodically but not at every commit.

I wondered if anyone else has run into this problem and found a good
> strategy to mitigate it.
>

Shouldn't failling to insert due to FK violation be considered an
integration test failure?  You may want to beef up your tests to make sure
the necessary rows get inserted in the right order.  Another option would
be to automate the creation of a complete set of test data... using psql to
load a dump, or possibly CREATE DATABASE testdbcopy WITH TEMPLATE testdb.

Integration tests can be costly to maintain with little ROI, tread
carefully...

-Dave

  3. Re: Unit tests and foreign key constraints

    Martijn van Oosterhout <kleptog@svana.org> — 2015-05-21T20:34:54Z

    On Thu, May 21, 2015 at 12:39:01PM -0700, Andy Chambers wrote:
> Hey All,
> 
> I've started trying to use foreign key constraints in my schema but it
> seems to make it more difficult to write unit tests that touch the database
> because each test now requires more setup data to satisfy the foreign key
> constraint. (I know some say your unit tests shouldn't touch the DB but the
> more full stack tests I have, the better I sleep at night :-))
> 
> I wondered if anyone else has run into this problem and found a good
> strategy to mitigate it. I thought I might be able to make these
> constraints deferred during a test run since I have automatic rollback
> after each test but even after "set constraints all deferred", I still got
> a foreign key violation during my test run if the test tries to insert data
> with a non-existent foreign key.

Foreign keys aren't deferrable by default, you have to create them that
way...

Have a nice day,
-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> He who writes carelessly confesses thereby at the very outset that he does
> not attach much importance to his own thoughts.
   -- Arthur Schopenhauer

  4. Re: Unit tests and foreign key constraints

    Martijn van Oosterhout <kleptog@svana.org> — 2015-05-21T21:29:20Z

    On Thu, May 21, 2015 at 01:33:46PM -0700, Dave Owens wrote:
> >
> > I know some say your unit tests shouldn't touch the DB but the more full
> > stack tests I have, the better I sleep at night :-))
> >
> 
> Unit tests really should be about testing individual bits of logic.  Does a
> single method do the desired thing, and not the undesired thing...
> Ideally, your data access layer should be interchangeable, ie: use a real
> database record in production, but use a mock database record for unit
> tests.

Nice in theory. But if you use Postgres features like timestamptz
calculations and hstore, it's generally way easier to run your unit
tests on an actual PostgreSQL database.  Otherwise you're going to spend
all your time working around the fact that your mock database is not
the real thing (and running into bugs in your emulation layer).

Have a nice day,
-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> He who writes carelessly confesses thereby at the very outset that he does
> not attach much importance to his own thoughts.
   -- Arthur Schopenhauer

  5. Re: Unit tests and foreign key constraints

    Andy Chambers <achambers.home@gmail.com> — 2015-05-21T21:41:21Z

    On Thu, May 21, 2015 at 1:34 PM, Martijn van Oosterhout <kleptog@svana.org>
wrote:

> On Thu, May 21, 2015 at 12:39:01PM -0700, Andy Chambers wrote:
> > Hey All,
> >
> > I've started trying to use foreign key constraints in my schema but it
> > seems to make it more difficult to write unit tests that touch the
> database
> > because each test now requires more setup data to satisfy the foreign key
> > constraint. (I know some say your unit tests shouldn't touch the DB but
> the
> > more full stack tests I have, the better I sleep at night :-))
> >
> > I wondered if anyone else has run into this problem and found a good
> > strategy to mitigate it. I thought I might be able to make these
> > constraints deferred during a test run since I have automatic rollback
> > after each test but even after "set constraints all deferred", I still
> got
> > a foreign key violation during my test run if the test tries to insert
> data
> > with a non-existent foreign key.
>
> Foreign keys aren't deferrable by default, you have to create them that
> way...
>

Ah that's what I was missing. Thanks!

--
Andy