8 ms·
Database Version Control with Liquibase
- thesimon 6y agoI've been using it in production and it's quite good, just two things I've learned: * XML is a lot better for configuration if you don't have a very good YAML verification. Wrong indention and stuff can be easy to miss * Make sure your k8s probes work with long-running SQL operations.
- capitol_ 6y ago+1 on avoiding yaml, it becomes horrible to maintain when the files gets big
- wtetzner 6y agoIf you're parsing it dynamically, I agree. If you're validating it against a static data structure after parsing it, it's not bad.
- radiowave 6y agoYes. If you're using XML with Liquibase, it's definitely worth using an editor that supports XML schemas for autocomplete and validation as you type.
- freebasenic 6y agoI 100% agree. I started using liquibase a couple of weeks ago, and the autocomplete has saved my ass plenty of times already.
- siscia 6y agoThere is a typo in the subtitle :)
- kioleanu 6y agoI’v been using it for quite some time now and I love it. Very versatile command set and I love that I can have the exact same database structure automatically with H2 for tests and postgres for production. Relatively flat learning curve also
- jareds 6y agoWhat's the benefit of Liquibase over Flyway? I've been using Flyway and find it pretty nice. I enjoy being able to write migration as properly named sql scripts and not dealing with XML.
- TheGuyWhoCodes 6y agoI haven't used Flyway but the XML format has some benefits like being DB agnostic and defining context (eg. production, testing etc.) You can also include multiple change sets in one xml file, this helps with keeping the number of files down. if you want you can just write pure SQL inside the xml with the <sql> tag.
- TeeWEE 6y agoFlyway works fine for us too and is super simple to use, simple sql files... Nothing wrong with flyway. Note: We ony implemented rolling forward. Rolling back is hard. Esepcially for DROPing collumns. Btw https://github.com/amacneil/dbmate https://github.com/amacneil/dbmate also seems cool.
- gdsdfe 6y agoHow does liquibase compare to flyway?
- rickette 6y agoThe main difference: in Liquibase you write migrations using XML and in Flyway you write migrations in SQL. At least that was the original differentiator, things might have changed in the last couple of years. I've always preferred Flyway since SQL is the natural language to interact with your RDBMS. I would only consider writing migrations in XML if I have to support multiple database implementations e.g. when you built a product like Jira or SonarQube that support different databases (but think twice wether you want to go down that rabbit hole).
- devone2 6y agoYou can write migration in sql using liquibase as well. Just use tag <sql> and you can write your favorite dialect. Also you can set sql dialect on migration so you can support multiple db engines. https://docs.liquibase.com/change-types/community/sql.html https://docs.liquibase.com/change-types/community/sql.html
- cmiles74 6y agoI think the issue here is that the <sql> tag is embedded in a larger XML document. IMHO, XML is no fun to work with by hand.
- danpalmer 6y agoLooks like a solid basic implementation, along the same lines as Django migrations or Alembic in the Python world. I always find these tools a bit lacking though. Over the long term, things that I find mattering are: - Correctness of changes being made - Support in getting to Zero-downtime migrations - Support in CI around testing against the right schemas - Squashing changes/garbage collecting after a given point - Support for applying the same change to multiple production environments (where you can't rollback one because another failed). These are all hard problems! Some are quite specific to certain kinds of application too. But companies always seem to end up building custom tooling for this sort of stuff. I'm sure there's a place for a tool that gets the right balance between support for these complex use-cases and defining a way of working.
- djrobstep 6y agoUsing a schema diff tool can help with a number of the problems you mention - you can test correctness, autogenerate changes, and because you can diff from current -> target without version numbers, no need to keep a chain of migration files hanging around.
- danpalmer 6y agoThat's true, it would solve some of these problems. I do think the ultimate solution needs an awareness of both the diff and the migration plan though, to be able to test for things like whether changes can be made without downtime in a particular order. Unfortunately I suspect that the tool _also_ needs runtime instrumentation of the database in some cases to understand behaviour enough to address all of these concerns.
- samcolvin 6y agoIs it compatible with clickhouse?
- TheGuyWhoCodes 6y agoLiquibase is great, been using it for about 6 years in production. Liquibase also allow you to run java based change sets not only XML or YAML which is very good for more complex migration logic but you have to be careful not to use your DAOs or some service that might mutate in the future to support a schema change but rather use pure sql hardcode to that change set, as change sets run in order you can be sure of the current state of your schema.
- sunaurus 6y agoI recommend checking out Dbmate as an alternative to Liquibase. I've previously used Liquibase, Flyway, Django's built in migration tools, and others, but Dbmate is my favorite tool for migrations by far due to its simplicity. It's completely language agnostic, super easy to embed into any deployment pipelines, and works using regular SQL (so you don't need to learn a new syntax). https://github.com/amacneil/dbmate https://github.com/amacneil/dbmate
- cies 6y agoThanks for this... It ticks all my boxes (timestamps, schema.sql file, plain SQL migrations, etc.), it is ActiveRecord inspired (and that was the migration tool I liked most), it seems to integrate really well will CI/CD, and I have never heard of it!
- thornygreb 6y agonice, similar to tern which I've been using lately after using flyway for years. https://github.com/jackc/tern https://github.com/jackc/tern
- yborg 6y ago>doesn't support Oracle or MS-SQL Not exactly a Liquibase alternative for anybody working in the enterprise space.
- javajosh 6y agoFYI Liquibase supports SQL too. Not sure what you mean about "language agnostic" - Liquibase does have a lot of plugins and stuff in the Java world, and its written in Java, but it doesn't require Java. The biggest Java-specific thing that I like is that you can install it as a project-specific dependency rather than a global CLI, which keeps it clean.
- deleted 6y ago[deleted]
- chsanch 6y agoI've been using Sqitch https://sqitch.org/ https://sqitch.org/ lately with really good results. It's also a good alternative to consider.
- shekharshan 6y agoMy organization is a big fan of Liquibase. We execute Liquibase using Jenkins. It has a lot of awesome features such as ability to re-execute some changesets if file has SHA signature has changed. You can also decide to re-run some SQL files on every deployment. For example we run ANALYZE on our Postgres RDBMS at each deployment, just to ensure that as part of deployment the stats is updated for the query planner.
- move-on-by 6y agoI too am a fan. The `updateTestingRollback` command is great for testing to ensure the complete schema is defined and no manual changes are needed. I run it before running the unit and integration tests. The `validate` command is also good, but doing the full update, rollback, and update again is much better at finding issues in a controlled environment. (ie. don't run it in prod). > updateTestingRollback tests rollback support by deploying all pending changesets to the database, executes a rollback sequentially for the equal number of changesets that were deployed, and then runs the update again deploying all changesets to the database. [0] 0: https://docs.liquibase.com/commands/community/updatetestingrollback.html https://docs.liquibase.com/commands/community/updatetestingr...
- shekharshan 6y agoThanks, that is really great. I didn't know about that one.
- jasonkester 6y agoIt's frustrating watching this space. The problem is so trivially solved that there's almost no product to build. But people want to build something to solve it, so they build a much more complicated solution that can be configured to solve any potential database migration scenario, regardless of whether anybody in history has ever needed to handle that scenario. Here's how you actually version databases: change_0030-0031.sql --- alter table buns add column seed_count int update version_number set version = 31 Check that in to source control and you're 90% done. Extra points for building the tool that spins through all the files in source control and executes the ones that come before the current version_number on the server in question. And for automatically creating a change_0031-0032.sql file for the next guy to use (with that update line pre-filled.) You'll be hard pressed to spend an entire day building this, but sadly nobody does. Everybody downloads some overcomplicated monstrosity like the one in the article that describes changes in XML and whatever else. Then spends two days fiddling with config files to get it working. I'd give up complaining about it if I didn't keep ending up working with shops that use stuff like this.
- 101008 6y agoI even think that this is what Django does automatically with its migrations, isn't?
- cpdean 6y agoI like alembic because you label your migrations as nodes in a graph so that I can do work in my branch, but properly navigate backwards in the migration history to a common ancestor with my coworker so I can migrate forwards to their branch and pair with them on something. I'm frustrated to see people make a bunch of new tools that ignore the lessons from old tools, but for me this has been solved for a while.
- sz4kerto 6y agoThis is what Liquibase does. It does some other stuff for you as well, quite simple ones, but time has proven (to me/us) that they're all useful in real-life scenarios: - exclusive lock to prevent multiple instances running at the same time - rollbacks - forcing immutability > Then spends two days fiddling with config files to get it working. Look, dozens and dozens of developers have been using this for many years at us without problems. If it takes two days to set it up: let it be. And we're writing our migration scripts in SQL, not XML.
- krab 6y agoI realized only a while ago how very powerful Django is - when I was choosing technology for a new client's project. Just for context - over the last 13 years, I developed professionally in PHP, Java, Scala and Python, together with some code in Go and Rust. I can patch C and C++ code. By saying professionally, I mean being paid to do it as a main job. The integrated nature of Django (ORM, admin, migrations) is a killer, but very rare to see. Spring + Hibernate doesn't come even close. Play framework seems to go in this direction but misses a lot of stuff. Maybe it's matched in modern PHP or in Microsoft land but I don't have much experience there. You can collect all the components and tie them together, but you're focusing on assembling the tooling when you could be focusing on the real problem. Being able to develop alone or in a very small team means real agility and low overhead. The time to start matters very much. I know Django has its own pain points (for me, it's mostly performance). Each part of the stack has a better alternative as a standalone library. But I'm willing to sacrifice using the top tools in favor of using the top stack. Most of the web apps have a core product that's unique, but otherwise they all need to deal with database, authentication, authorization, CRUD admin interface etc.
- o1lab 6y agoIt's been a while I tried Django. Does Django admin provide a way to model schema in GUI and create migrations automatically ?
- jbman223 6y agoNope, still done though code, at least in the base framework.
- krab 6y agoNo GUI but the migrations are created automatically once you define the models. Have a look at http://forestadmin.com http://forestadmin.com. It's a fully GUI admin component. I found it when looking for Django alternatives when developing in Java.
- kioleanu 6y ago>Maybe it's matched in modern PHP or in Microsoft land but I don't have much experience there Yes, Laravel has something like you're describing: https://laravel.com/docs/5.3/migrations https://laravel.com/docs/5.3/migrations
- o1lab 6y agoIf you need a GUI tool that creates schema migrations automatically as you change schema - try XgeneCloud [1]. The GUI database client generates up and down SQL files automatically when you click and change schema. Currently, it supports MySQL, MSSQL, PostgresSQL, SQLite. Here is a simple demo how this GUI works : https://youtu.be/IKvraKy0S90 https://youtu.be/IKvraKy0S90 [1] : https://github.com/xgenecloud/xgenecloud https://github.com/xgenecloud/xgenecloud (Full disclosure : I'm the creator)
- janvdberg 6y agoSeems there is a spelling error within the first line? "Introduction to managing DB shcema changes with Liquibase"
- caymanjim 6y agoThis page doesn't really demonstrate the product well. There are links to some other pages that show somewhat more complicated changes, but they still don't demonstrate any complex transformations or migrations. Maybe it can do these things; if so, and the author wants to sell people on its capabilities, they should be front and center. XML is a non-starter for most people. I know this is a Java product, and Java developers have a perverse love affair with XML, but literally no one else uses it. The page mentions that changesets can be written in JSON and YAML, but there are no examples. XML is a horrid abomination and I have a viscerally negative reaction to seeing it. At least show something that doesn't cause PTSD to the viewer. I've used Rails DB migrations (written in Ruby with ActiveRecord's DSL), Alembic (written in Python with SQLAlchemy's DSL), and roll-my-own pure SQL, and nothing beats SQL. You're working with a database, why not work with its language? Any migration of significant complexity requires the use of actual SQL, and trying to abstract that away just results in mixing languages and obscuring the change being made. It only takes a few lines of code to manage the versioning.
- agentultra 6y agohttps://sqitch.org/ https://sqitch.org/ is a good tool in this space as well -- it allows you to write your schemas in plain SQL files, manages verification, and ensures that you can commit your schema changes in any order and it will resolve dependencies for you.
- brylie 6y agoAre there any similar, open-source libraries for database schema introspection and documentation? For example, I would like to provide a UI for users to document database tables and columns. At a higher level, it would also be useful to audit views to get an idea of which tables and columns are in high demand and might need indexes.
- thunderbong 6y agoInitially, I thought it was about data versions within a database. However, this seems like the traditional database migrations which has been used since forever, isn't it? For our Ruby shop, we use Sequel Migrations[0]. For generic migrations, using pure SQL, MyBatis[1] migrations is fantastic. [0]: https://github.com/jeremyevans/sequel/blob/master/doc/migration.rdoc https://github.com/jeremyevans/sequel/blob/master/doc/migrat... [1]: https://mybatis.org/migrations/ https://mybatis.org/migrations/
- domano 6y agoI am convinced that https://github.com/golang-migrate/migrate https://github.com/golang-migrate/migrate is enough. We've used it for ~3 years now and if it is enough for a platform with hundreds of scaled containers, including migration from docker swarm to gcp kubernetes and managed sql then it should be enough for all kinds of cases.
- drwiggly 6y agoMost databases have introspection capability, via sql. These tools should be using that. We shouldn't be making delta files, just check in schema defs along with normal code. The "migration" will consume the schema defs and generate a script that checks it against the db, and only execute schema commands that are needed. Most dbs can support drop/create of procs/triggers/other things quickly, some have alter support to just re-set them. Just never drop columns or tables, those require a human anyway, unless they're tiny. I wrote a tool that did this for mysql, and can be adapted for others as needed. Every branch could generate a database boot script easily, no crazy up down things all over the place and guessing on order.
- mrits 6y ago"Just never drop columns or tables, those require a human anyway, unless they're tiny." In a lot of common multi-tenant designs you could have thousands of schemas. You can't have a human doing this. Additionally, the hard part of schema versioning is really the data migration and rollback steps which you failed to mention.
- miked85 6y agoI have yet to work at a company where DBAs weren't vehemently opposed to using tooling such as Liquibase. Old habits die hard.