What tool(s) do you use to code review and deploy SQL scripts?

We have been building https://github.com/bytebase/bytebase for 3+ years. You can think it of as GitHub/GitLab for SQL changes, with integrated GitOps, code review and deployment.

You can further check out this tutorial to get a feel of our GitOps solution

https://www.bytebase.com/docs/tutorials/database-change-mana...

We use Liquibase with git. DB changes required for the corresponding code changes are required to be committed as a Liquibase changelog with the same branch name, and the PRs both need approval.

What we are not good about is making sure the changelogs have revert capability... some devs do it right and others not so much

We have a repo with migrations written in SQL, and we use tern[1] to apply them.

I’m not sure anything more complex is needed. I tried using Liquibase at some point and it was a nightmare compared to just using SQL.

[1]: https://github.com/jackc/tern

We are a python shop (end to end machine learning, so we do all of our data pipelines ourselves).

So ours is pytest that builds up a fake database + tables to unit test the scripts. And otherwise it is the normal review process for pull requests.

So ours is daisy chained as well, but I am quite happy with it.

Also RedGate, but Flyway has some reasons to recommend it over RedGate Deploy depending on your DBAs/workflows: https://flywaydb.org/

(Though I don't think it is "complete" or "perfect", either.)

EF Migrations are in a really good place now if you like/don't mind C# as a language (and you can easily embed SQL inside the C#, too, but there are benefits to being able to also run high level C# code). With today's tooling you can package your migration "runner application" as a single deployable executable for most platforms. You can build the executable once and run it in all your environments. (The same tool that updates your QA and Staging updates your Prod, testably running the same migrations.) Given the single executable deployable I might even consider using it for projects not themselves written in C#.

We use https://sqitch.org/ and we’re fairly happy with it. Sqitch manages the files to deploy which are applied fits to a local database.

We use GitHub actions for deployment and database migrations are just one step of the pipeline. The step invokes sqitch deploy which runs all the pending migration files.

Then, all the approval process is standard for the environment. We require approvals in pull requests before merging to the main branch.

We have the schema encoded in our own XML format, and our tool for applying such an XML doesn't do destructive operations. We also have no logic in stored procs. So schema changes are very safe and doesn't need much additional review.

We commit schema file alongside the rest of the code, and it gets applied as part of the CI run to our dev and QA/test DBs.

So not a direct answer I suppose, but it really cuts down on what you need to do manually against the DB.

Alembic or Django native migrations for typical Postgres migrations, I prefer the first to second, because I can configure so much more. As for Clickhouse, it's been a wild manual migration ride, we are questioning ourselves whether to use Flyway, because being a tiny startup we don't have enough time to port the entire thing.

A regular code repo with the scripts (with pull/merge requests for review) and then a CI job that builds containers with something like dbmate https://github.com/amacneil/dbmate that can then be run against any staging/prod environment.

My favorite method: Active Record + Capistrano.

Active Record is a Ruby library that provides ORM support in Ruby on Rails, and includes an elegant way for creating reversible migrations.

Capistrano allows you to configure and run tasks on a remote server, such as pulling the latest version of code from the git repo and running the newest migrations.

Is liquibase still the industry standard?

Another is migrations as code using an ORM library.

Last gig was doing that and diff’ing sql schema and bootstrap files, depending on the db. We eventually turned them into one time docker containers which actually made life easy

For what use case?

If it is for a CMS / Web based thing that modifies the structure of the database or inserts some seed data - Use Migrations

If it is for some ETL type job where you are using SQL then migrating to a tool like DBT is probably wise.

.NET entity framework migrations, so we have small come line app that uses migrations and well scripts are scripts. Deployment as all other apps we have.

Alembic migrations or Django ORM migrations and Ansible

Another +1 for Liquibase + Maven.

The key here is to put _everything_ into a 100% replayable transaction log, which is what said combination does.

A few years ago, I was using Flyway, which well met our scenarios. There might be better products now.

does drizzle orm help here? i heard it helps in migrations but never really tried.

Liquibase and Jenkins.