Does anyone have resources, tools, ideas, for automating database create, update, modify, delete

Question

I've been tasked to find a way to automate promotion of database creation, changes and other modification related to application changes in a devops world.
My databases include MS SQL, Oracle, MySQL and could include NoSQL databases. I've been doing what I can with web searches and all I seem to be able to find are powershell maintenance scripts (backups, rebuild indexes, etc). Nothing really about taking DDL out of a VCS like subversion, using jenkins or other tool to then apply those changes to either an existing database or create a new database. Our databases are a mixture of on-prem and AWS, with future Azure possible.
I did find a TOAD tool that will be available later this year that seems to do this, however I'm trying to see if there is a less expensive way.
I've been developing applications, databases and supporting the same for 40 years. I can figure it out - however I'd like to not have to recreate the wheel if one exists. Or if there is a best practices, or if there is someone else that has tools they use.
Thanks in advance for any assistance.

The suggested question from over a year ago has some ideas, however it is not close to what I'm looking for. All the answers are over a year old, and as we know software changes at almost a weekly rate. The answers in that question help if I have to "roll my own" solution.

Answer 1

Database migrations are a pretty standard thing for a long time, I believe - not related that much to DevOps, though obviously needed.

There are ready-made tools which are agnostic of your programming environment, i.e., not directly tied to whatever ORM the devs are using.

Strategies of how to actually decide which DDLs/DMLs to feed to the DB might vary; i.e., in the simplest case you just keep track of one global "DB version"; or you have a table which lists individual migrations that have been applied already; or you define a "wanted" state of the DB, and the tool then creates the need DDLs itself to change whatever state the DB is in right now to the new one.

One example of such a standalone tool would be Liquibase (open source), which has a plethora of ways to be configured. I mention that because you say you don't have much time, and it is rather quick to get going - it has one mode where the "configuration" is just a simple file of SQL intermixed with flat (and small) meta information as SQL comments.

The biggest challenge, in my experience is how to handle changing of data structures; one way would be to make all migrations such that they never lose any data, and never break existing code (or data). I.e., you would almost never include a DROP TABLE or ALTER TABLE DROP COLUMN in just any migration, but would let stuff drop out of use, and when a table, or column (or whatever other DB object) finally is not in use anymore, and is inconceivable to be used again, then you drop it. To change, for example, the type of a column, you would create a new one with the correct type, and phase the data over; and so on. This places the work straight on the shoulders of the devs, and not on the migration tool. Which is fine, as this stuff must be in tight correspondence with the DB-centric code of your application, anyways (even if hidden behind an ORM layer).

This is pretty much the same approach as for the software development itself; i.e., creating new features but with a switch (deploying the software with the switch "off", and then being able to toggle quickly), and rolling out parallel implementations of the same feature; only removing the old code when the new one is absolutely proven.