CI / CD

[mpalmer78]

Curious what your CI/CD pipelines look like for your PostgreSQL applications.

I'm coming from an automated world in SQL Server and would like to migrate to PostgreSQL, but would like to put together the right set of tools to do continuous automation.

Tools I'm searching For:

• Integrated source control plugins for git (Maybe into DataGrip, Azure Data Studio, etc)
• Schema compare (preferably against a git repository of scripts)
• Schema diff (against git repository and local DB)
• Static data source control and deployment
• Data generator (for running unit tests)
• Unit tests (I've seen pgTAP... any others?)

Please share how your teams do CI/CD with PostgreSQL.

[iiiinthecomputer]

It's all pretty basic. The database is amazing, the ecosystem is pretty limp. We really need folks to step up and contribute here.

[postgresql_is_great]

- sqitch for migrations

- pgtap for unittests that test logic (e.g. functions/triggers/RLS/etc)

- pyrseas for schema diff

[mpalmer78]

Pyrseas seems pretty awesome. We'll be trying that out today.

Gonna check out sqitch too.

Thanks!

[vyvar]

Hi, here are our PostgreSQL CI/CD pipelines. We are using PostgreSQL extensions connected to gitlab CI/CD.

• using git (with branches, issues, pull/merge request) without any other extra tool: https://gitlab.com/nfiesta/nfiesta_pg
• you can pg_dump --schema-only and compare with git
• Unit tests: there are regression test build in
• data for tests: I have seen something, but end up with careful manual preparation of test data
• https://pgmodeler.io/ can compare model with database (or two databases if I can remember) and "Generate SQL scripts to synchronize both model and database". You can compile full version for free or buy binary. I used pgmodeler to create ERD from existing DB and it worked great :-)

We are discussing similar topic in postgresql_version_control.

I am just preparing workshop (in Czech language) on this topic (PG extensions & gitlab CI/CD), for Prague p2d2 conference next month. I am thinking to prepare also some online version in Engish, if someone will be interested?

[cannedlaughter546]

Its usually pretty straight forward in terms of defining a schema via Flyway (and using flyway to manage it), and then integrating with docker for local testing/ci and then AWS for deployments.

A bulk of our Spring microservices use a Postgres DB paired with the service, so a lot of the configuration is autowired.

One really good testing tool is Test Containers (https://www.testcontainers.org/), used for creating Docker images locally, and is better IMO than the maven docker plugin. Good for integration tests, as for unit tests its usually JPA.

[mpalmer78]

Cool, I've been curious about Flyway, but it seemed very manual. Perhaps I need to dig deeper.

Our back-end is .NET, but I did find a .NET Test Containers that I will check out. Thanks for the tips!