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

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

<p>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.</p>

<p>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.</p>

<p>The biggest challenge, in my experience is how to handle <em>changing</em> 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 <code>DROP TABLE</code> or <code>ALTER TABLE DROP COLUMN</code> 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, <em>then</em> 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).</p>

<p>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.</p>

CC BY-SA 4.0
2018-08-23T13:24:40.867Z
Thanks for your thoughts. I'll be digging into liquibase a bit deeper in this sprint then. Spent all the time I can on research, have to pick a database management system to try stuff out on next. Only have 2 weeks left in the sprint and lots more stories to answer.
J Anderson
CC BY-SA 4.0
2018-08-22T21:22:25.403Z
2018-08-22T21:22:25.403Z
AnoE
<p>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.<br>
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.<br>
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.<br>
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.<br>
Thanks in advance for any assistance.</p>

<p>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.</p>

CC BY-SA 4.0
2018-08-22T19:55:59.857Z
Howdy J, welcome to DevOps SE.  I think this has been covered in https://devops.stackexchange.com/questions/742/what-practices-or-tools-enable-continuous-deployment-of-databases already.  Is that what you had in mind?
chicks
CC BY-SA 4.0
2018-08-22T19:56:18.287Z
Possible duplicate of [What practices or tools enable Continuous Deployment of Databases?](https://devops.stackexchange.com/questions/742/what-practices-or-tools-enable-continuous-deployment-of-databases)
chicks
CC BY-SA 4.0
2018-08-22T20:43:44.957Z
That has some direction if I were to "roll my own" but I don't have a year to build a solution. Thinking that a year later there may be solutions out there already.
J Anderson
<jenkins><deployment><databases><subversion>
Does anyone have resources, tools, ideas, for automating database create, update, modify, delete
J Anderson
# Does anyone have resources, tools, ideas, for automating database create, update, modify, delete

(Asked by: username_0 on 2018-08-22)

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.

**Tags:** jenkins, deployment, databases, subversion

> **Comments:**
> **username_1**: Howdy J, welcome to DevOps SE.  I think this has been covered in https://devops.stackexchange.com/questions/742/what-practices-or-tools-enable-continuous-deployment-of-databases already.  Is that what you had in mind?  
> **username_1**: Possible duplicate of [What practices or tools enable Continuous Deployment of Databases?](https://devops.stackexchange.com/questions/742/what-practices-or-tools-enable-continuous-deployment-of-databases)  
> **username_0**: That has some direction if I were to "roll my own" but I don't have a year to build a solution. Thinking that a year later there may be solutions out there already.  

---

## 1 answers

### Answer by username_2 on 2018-08-22. Score: 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.

> **Comments:**
> **username_0**: Thanks for your thoughts. I'll be digging into liquibase a bit deeper in this sprint then. Spent all the time I can on research, have to pick a database management system to try stuff out on next. Only have 2 weeks left in the sprint and lots more stories to answer.  

---

