external db changes and maven usecase - newbie getting started

General Discussion
1

Hello all,
I’ve gone through a number of the documentation (including the maven liquibase plugin), and I’m kinda running into a block regarding my use-case of how I would like to use liquibase (which, hear me out, may be inappropriate or appropriate for this scenario).

The point-of-view is from an application developer trying to manage change.

Environment:
DEV database (mysql, ms sql, etc)
STAGING database(mysql, ms sql, etc)
LIVE database(mysql, ms sql, etc)

Process/flow:
This is where I’m a little unsure – we have (well, trying) to have all database changes done only in the DEV environment.  However, here is the catch – those changes could be done by external tools such as Modelling tools, DB-specific administration tools, or scripts.  All those changes are done, basically, outside of our control for this conversation (I’m sure a lot of app developers have similar environments).  

We would like to manage/control the end-result of all the aggregated changes and create the delta scripts that will actually be applied to STAGING, and future LIVE.  Also, I very much like the SQLDOC change document and although it will not get the user/comments based on the external changes, at least keeping track of those changes in that format is very useful.

Also, as an application developer, we use Maven and would like to leverage if possible, but if this scenario is best handled outside of Maven that would be fine.

How can we use Liquibase for this scenario?
*Establish a baseline (for each major version/release).
*Throughout the iterations keep track of changes (even though they were done externally to liquibase). Generate sqldoc like change documentation if possible/reasonable.
*Throughout the iterations in preparation for release (which may still be several iterations), create the SQL output of changes from the baseline to current desired state.  Possibly SQL output in between releases, but do not want to confuse things and just pick which approach is best managed/best practice.  Liquibase would not make changes to STAGING, instead create the SQL scripts that would be applied to STAGING.
*Once released re-establish baseline and repeat from top.

–hopefully not have to manually create the dataset/changesets and instead reverse engineer from existing database implementation.

I definitely hope to grow to actually leverage the full functionality of liquibase, but need to get it started and used regularly first to recognize the usefulness/value (i.e. “Manager, if you want to see the author/comments in the sqldoc change logs, dont use the external toosl and instead to do it this way…”).

Thank you for any feedback,
-D

2

Sorry for the slow response. 

Making changes in the dev environment is what liquibase is designed for, but there is no good way to integrate liquibase seamlessly with external tools making database changes.  What needs to be stored in the databasechangelog files are all the changes made to the database, and the best way to manage those files is to build them up one step at a time.

There is the diff tool (including outputing the diff as a databasechangelog XML) which you could use to compare your dev databases with the last release, but the liqubase diff tool doesn’t check all possible differences, and even if it did there are inherent problems with relying on database diffs. 

Depending on your 3rd party tools, you may be able to integrate liquibase into them so changes they make are either auto-added to the changelog or you have a process where developers capture the SQL of the changes and add them to the changelog files.  At points during development, you can use the diff tool as a sanity check that nothing had gotten forgotten between dev machine(s) and a purely-liquibase-updated script.  It’s not ideal but it would work. 

Not knowing exactly what you are trying to use, the number of changes you make, etc. makes it difficult to make recommendations.

You can follow the scenario you outlined using the diff as changelog option, but like I said above you will find limitations.

You should be able to use maven or the command line tool for any of the options above.

Does that help you?

Nathan

3

Thanks for your reply Nathan.  I’m aware of some of the challenges with a diff-centric approach, and your article really helped to reinforce that.

I started a proof-of-concept using the ant tasks, and one place I got stuck was diff between a baseline changelog and the current state of the database.  I see how to do it with two databases, but not with one being the baseline/changelog.

Originally posted by: Nathan
have a process where developers capture the SQL of the changes and add them to the changelog files.

Could you provide more detail on this potential (be gentle, I’m still a newbie!) – my gut is to commit any SQL changes I get from anyone into source control.  I would like to maintain that trace/tracking (potentially in a changelog file, thats cool), but now I’m getting worried about three different states - baseline, changes-to-date, current state.  At least with a purely diff approach, it’s only baseline and current state (unless I reset my baseline…but then I want to do a future dbdoc on the original baseline and the ‘current’ baseline).

Basically, an app developer working with db developer that doesn’t use the same/similar tools, and trying to solve these kinds of deliverables:

*What changed/changes need to be made to the database when we promote (actual SQL code).
*Documentation of the changes (dbdoc).
*Be able to keep this information up-to-date throughout an active development lifecycle, instead of trying to ‘last-minute’ resolve it all.

4

The basic workflow liquibase was designed around is this:

  1. Developer needs to add new feature to app
  2. Developer sees that a database change will be needed to support the new code
  3. Developer adds node(s) to the changelog file(s) which are committed to source control along with the new code changes
  4. Other developers bring in Developer’s code and database changes.  They run “liquibase update” which applies all unrun changeSets to their database so the new code works correctly
  5. Repeat 1-5 until ready for release
  6. Release new code and run “liquibase update” on the production database to apply all new changeSets

It can be a bit of a mental leap, because there isn’t necessarily a baseline database, or even a “version” to the database.  Each changeSet is independent and can be reordered and conditionally applied as needed. There is also no need to worry about doing diffs in the process because you just run “liquibase update” and your database (any database) will be up to date.  It is a lot like source control.  Subversion (as an example) is just storing a series of changes that need to be applied to a file.  When you do a SVN update, you are simply applying those changes to whatever changes you already have locally.  The biggest difference with liqubase is that those changes are not automatically generated by a tool, but manually created.

Steps 1-6 above can vary significantly depending on your workflow.  If you have db devs separate from app devs, for example, you would need to coordinate having the db devs get their changeSets into the database changelog file before work can begin on the feature.  There is also flexibility on applying the changes to production.  You can, for example, run the updateSql command against production to generate a script of changes that a DBA can do a final check on before applying them.  You can also run the futureRollbackSql command to get a script of what would need to be ran to roll back a release that the DBA can inspect before release. 

The important part, however, is getting the changes into the database changelog.  I’ve generally found 3rd party tools to not work well because you end up applying the change in two different (often importantly different) ways and you cannot be certain that your tool-upgraded database is really exactly the same as your liquibase-updated database.  I’m not sure what your db devs do or the tools you use, but you may be able to push for having your app devs make the database changes directly in liquibase and move the db devs to more of a change inspection/global change view/focus on database tuning role.

Nathan