Re: PostgreSQL DB in prod, test, debug

On Wednesday, 14 February 2024 at 10:25, Daniel Gustafsson <daniel(at)yesql(dot)se> wrote:

>

>

> > On 14 Feb 2024, at 10:59, Simon Connah simon(dot)n(dot)connah(at)protonmail(dot)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. In essence, create the test database identically to how you create
> the production database to ensure that you are testing what you will run in
> production.
>

> --
> Daniel Gustafsson

Thank you. I'm a bit disorganised with things at the moment. I'm kinda developing this site in an ad hoc manner which is probably a bad idea so I'm trying to fix up a few mistakes I made early on.

In terms of the database I've just added new functions as needed which I use instead of direct SQL in my Python code. Maybe I should sit down with Visio and try and do a diagram before I go any further?

I'll just write a simple script for backup and restore and call it before each test run but have the schema only dump come from production. At this point it doesn't really matter as the website is so small.

Simon.