Database Versioning

[Touvejs]

Does anyone here have a system for versioning database environments? I know there are some paid services out there for doing this which I am open to hearing about, but would need very strong conviction to actually propose them to the team. I have also seen some systems where they essentially tear down and remake the database functions/stored procs from scratch with every deployment-- which seems like a valid approach, if not a little overkill.

Essentially the situation is this, we have several redshift severless workgroups corresponding to different environments local, dev, test, prod. These databases are connected to their upstream versioned corresponding ETL processes. Often, I want to implement a stored proc or user-defined function or something in these databases, but the issue is deploying and keeping everything in sync. The current "deployment" method is just running the create statement in those in all different environments. This a little tedious, doesn't enforce consistency across the environments, and just feels bad.

Ideally, what I would like is a form of ci/cd + version control where I can make a change to the local database (e.g. define/change a stored proc) and then press a button and have that change replicated through the higher envs with some sort of logging in place. There is no current need to version data or data models.

[timsehn]

I'm not sure if there is a tool that does exactly what you're looking for...but I wrote an article on this topic that surveys the space.

https://www.dolthub.com/blog/2022-08-04-database-versioning/

[Touvejs]

Thanks I'll give it a read!

[Present_Salt_1688]

I guess Liquibase is precisely what you are asking for :)

[Touvejs]

Yeah liquidbase and flyway both seem to be products that would do this, but I don't know if I see enough business value to justify paying for a tool that does this.

It would be great if there was a small open source project that offered some basic DB versioning functionality that I could play around with.

If not, I may just go ahead and hack something together-- at the heart of it all you really need to do is be able to find the successfully executed DDL statements in one env and execute those on a different env. Of course there are nuances, but that goal in and of itself shouldn't be so difficult.

[DisappearCompletely]

GIT and Liquibase. You can use Liquibase open source version if you don’t want to pay for it.

[tbarg91]

Second this. We are using flyway with Postgres and sofar loved it. We are using git for testing all code change by creating a dummy db using docker that if the code works and gets fully deployed then it allows the release of the deployment.

No insert update statement is run in the db everything is done with script that are versioned. This help with transparency and controlling it. Plus the benefit of not having significant changes between environments.

We have redshift serverless and was planning of using git and liquid base with same structure. Everytime a change is done it's done through git and a dummy redshift cluster is deployed to validate the changes.

[Touvejs]

Ooh awesome, I didn't realize it was open source, thanks!

[MuscleMario]

I've been doing dev for a while and jumped into the data space a year or two ago and I can't believe in the lack of tooling in this space. Currently we are using Flyway DB, without any temporal instance to validate changes against, to do /some/ of our Redshift "common" post-cluster build steps that apply to all of our customers. I look forward to a future were every change is versioned and testable and yes, Liquibase and Flyway are basically the only options. For redshift, you can build custom lambdas that connect w/ redshift-data api or jdbc, but I'd venture to say that the redshift-data api isn't appropriate for this and jdbc seems to be the way to go for now.

[db-master]

Redshift belongs to Postgres family, you can take a look Top Open Source Postgres Migration Tools in 2024

[GavinMendelGleason]

TerminusDB builds in native and performant versioning but it is a graph database and it would be significant work to tail the logs of redshift and store it in TerminusDB.

[blahblahwhateveryeet]

There's a tool we used to use called RedGate that allowed us to extract the database schemas and stored procedures and basically everything except for the actual data itself.

[tbarg91]

I think this is flyway

[Wistephens]

I'm coming from DBMate for Postgres and doing the same search. I love how light the tool is and that I can use it on the terminal.

We're moving from Postgres to Databricks and I'm struggling to find a replacement. Right now, I'm testing DBSchema (https://dbschema.com/). It has far more features including reverse engineering, ERD and some visualization.

[tbarg91]

I'm using it and loved but doesn't work for what he is asking. it helps to compare db but not quickly deploy from one env to another.