pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Peter J. Holzer <hjp-pgsql@hjp.at>
To: pgsql-general@lists.postgresql.org
Subject: Re: PostgreSQL DB in prod, test, debug
Date: Fri, 16 Feb 2024 00:51:52 +0100
Message-ID: <20240215235152.va7wohn5ftcxi5a4@hjp.at> (raw)
In-Reply-To: <7304F877-D043-4395-867E-014F48EB71ED@yesql.se>
References: <NaMscvF8PaDKNcHJGR28wV7Kljc6kw4y0agCt2E3qI8hkUYUSUyj_beL825S4GfK2SjTuNVaHmRkW30ihuYmobAitd1uuLNis8nCkFCZT-0=@protonmail.com>
	<7304F877-D043-4395-867E-014F48EB71ED@yesql.se>

On 2024-02-14 11:25:48 +0100, Daniel Gustafsson wrote:
> > On 14 Feb 2024, at 10:59, Simon Connah <simon.n.connah@protonmail.com> wrote:
> 
> > This is probably a stupid question so I apologies in advance.
> 
> There is no such thing.
> 
> > What I think is the best way to do this is to do a pg_dump of the
> > database (using the --schema-only flag) and then load it into a test
> > only database that gets created at the start of the unit tests and
> > destroyed at the end. The automated tests will insert, update,
> > delete and select data to test if it all still works.
> 
> If the source of truth for your schema is the database, then sure.  If the
> source of truth is a .sql file in your source code repository then you should
> use that.

I sort of do both for one of my projects:

I originally created the SQL script by running pg_dump on a manually
constructed test database (I do have code to create the empty schema,
but I had to insert the test data manually). That script went into the
git repo.

The test files all contain a fixture which drops and recreates the test
database using that sql file.

When I add a new test case I try to make do with the existing test data.

When I need additional data for a new test case, I create a new pristine
test database using the sql file, add the new data, and then create a
new sql file using sql_dump which is then committed with the test cases.

Same for migrations: If I need to migrate the schema, I run the
migration on the test database, then dump and commit it.

This project is small enough (86 tests in 10 files) that all test cases
can use the same test data. However, I could easily use different test
data for different tests.

        hp

-- 
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

Attachments:

  signature.asc (application/pgp-signature, 832B) download

view thread (8+ messages)  latest in thread

Message-ID: <20240215235152.va7wohn5ftcxi5a4@hjp.at>
Permalink:  ../20240215235152.va7wohn5ftcxi5a4@hjp.at/
Also on:    postgresql.org/message-id/20240215235152.va7wohn5ftcxi5a4@hjp.at

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: hjp-pgsql@hjp.at
  Subject: Re: PostgreSQL DB in prod, test, debug
  In-Reply-To: <20240215235152.va7wohn5ftcxi5a4@hjp.at>
  References: <NaMscvF8PaDKNcHJGR28wV7Kljc6kw4y0agCt2E3qI8hkUYUSUyj_beL825S4GfK2SjTuNVaHmRkW30ihuYmobAitd1uuLNis8nCkFCZT-0=@protonmail.com> <7304F877-D043-4395-867E-014F48EB71ED@yesql.se> <20240215235152.va7wohn5ftcxi5a4@hjp.at>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox