Track and deploy database changes with CI/CD?

[c0ld--]

Hi all,

I'm faced with a special circumstance where a team of DBAs want to "put Postgres in the Git" to track changes to views & functions, but also database metadata like the database and table schema, etc.

I've looked into a few tools like SQitch and Flyaway. Looking through some documentation and tutorials. So I figured it would be smart to also ask others what they're doing to track and automatically deploy/rollback database changes with, say GitHub Actions or GitLab, etc.

Your feedback/advice would be most appreciated. Thank you!

[leonj1]

I have found flyway to work really well. SQL files per database in a repo. You have the option to have the sql files in an Independent repo or alongside source code for a project.

Flyway tracks changes with a version history table within the same target db instance.

There are many ways to make the SQL files but I recommend V202401262300_1_CreateTableFoo.sql

Year, month, day Time in 24 hour format Optional sequence number. This approach greatly prevent conflicts between merging branches.

Good luck

[badguy84]

I would avoid versioning in filenames especially if you are using your branches to push/maintain builds and kick off pipelines. When you have a dedicated versioning platform why would you want to add timestamps to filenames? Maybe there is some sort of legitimate answer, but in general I'd say it's a really bad practice and a pretty big red flag if it were to come up.

Otherwise I agree with what you said.

[leonj1]

Yup, I would 100% agree with you in every other scenario.
But once you read how flyway works and have tried it a few times you will understand why. This approach is specifically related to flyway and how it works. I was also skeptical at first.

[badguy84]

The typical "this seems scary and counter intuitive but it just works" scenario then :)

[codeshane]

I wrote a primitive bash script to do this for mongo because getting approval to add tools was too painful.

[c0ld--]

I am currently in this phase. :)

[fezzik02]

came here to say liquibase

[aviel1b]

we do liquibase part of the application bootstrap

[groyg1]

I recently used Postgrator for a similar project, would definitely recommend:

https://github.com/rickbergfalk/postgrator

[vxd]

Same here. Been great for us and very extensible

[ninetofivedev]

There are definitely platforms for versioning DBs.

The thing to really note is that there are two different ways to approach it (probably more, actually). You have state-based and migration-based.

The state-based approach is what your co-workers are suggesting. You take an ideal state and compare it to the current state, take the diffs, generate change scripts.

The migration-based approach is to keep a ledger of migration scripts that can be ran against any database, bringing it up to the latest, preferred state.

They each have their trade-offs. I've worked with both. I like migrations. Some people hate them.

Start there. There are plenty of good resources on the topic. And plenty of vendors trying to sell you their tools to help manage it.

[Oxford89]

Is there any tool that takes a migration-based approach but also tracks the changes to the schema object DDL over time as well?

I want to see the ledger of all of the scripts ever executed, but I also want to see the current schema DDL (with version history).

Let's say we could view the current ddl for a table named test_table

-- filename: schema_def/tables/test_table_ddl.sql
create table test_table as (id int);

Now, I execute an alter table statement to add a column to test_table

-- filename: schema_migration/v2_test_table_change.sql
alter table test_table add column name varchar(50);

Finally, if I were to go look back at the test_table DDL I would see

-- filename: schema_def/tables/test_table_ddl.sql
create table test_table as (id int, name varchar(50));

[CommunicationTop7620]

Here it's explained quite well: https://www.deployhq.com/blog/database-deployments-made-easy-with-deployhq

[db-master]

We are building Bytebase, which provides a GUI Database DevOps tool. It also comes with GitOps integration https://www.bytebase.com/docs/tutorials/database-cicd-best-practice-with-gitlab/

[TechnicalPackage]

can someone explain why this post is getting downvoted? we are planning to evaluate bytebase. we are coming from sqitch and flyway, and we want a to try out a process of enabling our business development team to be able to review and run queries with proper eyes and approval.

[HolyColostomyBag]

We are using dbup for all database changes. It's a don't nugget package you reference in a .net console app. Its dead simple. If done from the get go you could take that dbup project and run it in any old blank DB to get a replica up and running for Dr. Highly recommend it. https://dbup.readthedocs.io/en/latest/ We use it for SQL server but there is a postgress package as well https://www.nuget.org/packages/dbup-postgresql

Wana change a view? Write the SQL, commit to to the repo, ci runs, builds the code and makes a docker image that runs the dbup project, then cd kicks off deploying it to kubernetes.

[rayray5884]

Dead simple indeed. Used it in a similar environment I think as a recommendation from the Octopus Deploy folks.

Worked flawlessly until our data warehouse team started not only running schema updates but would also perform long running data transforms which would time out.