On 2024-02-14 10:59 +0100, Simon Connah wrote:
> This project uses Python 3.12, Flask, psycopg3 and PostgreSQL 15.
>
> This is probably a stupid question so I apologies in advance.
>
> I'm building a website using PostgreSQL and since I've just been doing
> some dev work on it I've just manually played around with the database
> if I needed new tables or functions for example but I want to start
> doing automated testing and need to import a clean snapshot of the
> database with no data and then use the automated tests to test if
> things work with the tests.
>
> What I think is the best way to do this is to do a pg_dump of the
> database (using the --schema-only flag)
You create a dump from the prod database each time? Yikes.
> 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.
>
> My main question is does this sound OK? And if so is there a nice way
> to automate the dump / restore in Python?
The database schema should be defined by migration scripts that you also
check into version control with the rest of your application sources.
Some people also prefer a separate repository just for the database
schema, depending on how tightly coupled database and application are,
or if there are other applications relying on the database schema.
I use Sqitch[1] which works well if you want to track an existing
database schema. Alembic is popular in Python but I don't know how it
works with an existing schema because Alembic migrations are usually not
written in plain SQL.
This is also useful for automated deployment because it allows you to
migrate the database to a specific schema version that is necessary for
your application.
For testing, I have a Bash script that starts a Docker container with
Postgres and then runs sqitch-deploy before running pytest. That can of
course be adapted to spin up a local Postgres instance instead. I also
use pgTAP[2] with that to just test the database schema.
You can also use testcontainers[3] to start/stop a Docker container in
pytest and run sqitch with subprocess before running all tests.
[1] https://sqitch.org/
[2] https://pgtap.org/
[3] https://testcontainers-python.readthedocs.io/en/latest/postgres/README.html
--
Erik
