What is Reddit's opinion of Liquibase?

From 3.5 billion Reddit comments

I couldn't really see the point of features like undo being a paid thing when the most obvious open-source competitor to it, Liquibase, has had it for ages. I guess it makes sense if all your existing migration files are Flybase???

Also, Liquibase migrations are basically SQL statements too: http://www.liquibase.org/documentation/sql_format.html

--liquibase formatted sql --changeset nvoxland:1 create table test1 ( id int primary key, name varchar(255) ); --rollback drop table test1; --changeset nvoxland:2 insert into test1 (id, name) values (1, ‘name 1′); insert into test1 (id, name) values (2, ‘name 2′);

In 2004 I rolled my own. it worked mostly. Far better than nothing.

Then I used something out of carbon5 that had a few more features and a bit more road time.

My current favorite solution liquibase:

http://www.liquibase.org

awesome tool.

> out of curiousity, why do you like the database agnostic abstraction? I've always preferred flyway's method of versioning and have never felt like this was a shortcoming.

Sorry for the broad statement without the justification. There are a couple of reasons:

• In reality Liquibase also supports SQL change set definitions, but I lean away from them because the tendency to use Server specific extensions or non-standard SQL dialog is too high, so if you switch or want to prototype locally on some other database, then it's particularly challenging. For example ALTER TABLE vs. RENAME COLUMN

• a clear refactoring like renameColumn could translate to saving several steps such as copy table, alter table, update, drop table, rename table as an optimization if it makes sense for that particular database

• It's just clearer to see what's going on when you see a renameColumn

That being said, Liquibase can certainly use a lot of improvements too, so by no means am I saying it's perfect. It just is the closest to meeting my needs / philosophy :)

Check out Flyway and Liquibase. Both take the idea of the entire schema being a version. Updates to the schema require a change script which is tracked and checked against the current schema. The scripts themselves can be checked into source control.

My shop uses liquibase to keep the database under change control. It's not perfect (some schema changes need to be done with an online schema change tool instead of LB so that we don't lock up tables for hours and bring the site down) but it's way better than cutting and pasting.

I really love this library. Combining Jooq with its gradle plugin and Liquibase could make any Java <-> RDBMS project better!

You should really be using some kind of migrations system. Even as I solo dev who 100% controls all my own servers, I'd never do what you're doing (big single SQL script) any time within the last 10 years or so. That's not too far off not using version control at all.

Sounds like you have a team of people, has nobody brought this up?

If you're using a framework or ORM, it might be built in. Otherwise most languages have a multiple you can pick from. There's also a number that aren't tied to your application language at all. Just command line tools:

• http://www.liquibase.org/
• https://flywaydb.org/

I'm mostly programming in Node now, so I use TypeORM. It does both syncing and generating migration files. Some of them require you to write the migration files yourself, and some will auto-generate based on your models or local dev database.

Some ORMs also support just syncing the destination database schema with the ORM model code without the use of migration files at all. But you might be a bit big for that if you've got multiple devs. That's more for smaller projects, and not really ideal in production.

What language and framework are you using?

I've never used it in anger, but Liquibase can diff DBs: http://www.liquibase.org/documentation/diff.html

Looks like there's a few things it can't cope with according to the docs, but maybe worth evaluating.

Liquibase has worked really well for me in the past.

A little bit of database-independent XML (it can do anything you can do in SQL, but it has shortcuts to make common database schema changes database-independent. Very useful if you ever want in-memory integration tests from H2/HSQLDB), it can read in existing database structures and generate a changelog (so you don't have to start from scratch), user-readable table of what version your database is on, and it's easy to generate SQL files just in case you run into the hard-headed DevOps folk who doesn't want to let you touch his database with anything but SQL.

Stuff the xml file in your git repo and you're good to go

For keeping versioning and doing create/update like you want take a look at liquibase, it can do what you want and you can keep the scripts in GIT, also if the application use maven/ant it can be integrated into it giving you the option to deploy application and DB changes at the same time. Actually liquibase does what you do, it keeps a table with the changes applied that's how it knows what change to apply or rollback and in what sequence.

But since you want to use ONE script to "rule them all" then you will have to stick with the XML format and liquibase "datatypes" so they can be translated "correctly" to Oracle or SQL Server. You will have to minimize the use of DB specific datatypes/features to avoid problems or maybe use the <precondictions> tag to execute specific sql scripts to a specific database, I've never use this feature so I don't know if it works.

Another option you want to look at it is Oracle SQL Developer Datamodeler. I've used it for Oracle only and it works well with it. The only issue is it uses subversion, I don't know if there's a plugin for git. The claim the DDL can be exported to SQL Server but I've never tried.

http://www.oracle.com/technetwork/developer-tools/datamodeler/datamodelerfaq-167683.html#exportimport >Export a DDL script file for Oracle, DB2, UDB and SQL Server. There is a DDL file editor wizard to help you in defining the set of objects and different options in generation. A compare/merge functionality allows two models to be compared to create the update Alter statements

> Super useful.

Use something like

Git + http://www.liquibase.org/

I'm not affiliated with the project but I want more people using it.

Otherwise you're just locked into SQL Server. Especially if they have ever increasing prices and shitty operating system from a management/automation perspective (I admit they have been making improvements but why hitch up to the person that's always the late comer)

Hey, as the others have suggested, look into DevOps roles as well. Tools/Frameworks that enable automation like Chef, Puppet are useful. Even with DBA roles, knowing DB fundamentals is good, but learning DB optimizations and tools like Liquibase http://www.liquibase.org/ provide you with an added edge.

Also, check this set of free services/tools offered by GithHub for students. https://education.github.com/pack

We are using Liquibase for all our projects (Postgres, Oracle, SQL Server). Not sure if the fact that it needs a Java runtime is problem for a .Net shop.

The Postgres Wiki contains a list of Postgres specific migration tools:

https://wiki.postgresql.org/wiki/Change_management_tools_and_techniques

I think you are mixing different aspects/responsibilities in your question.

As far as the "create a database in one long script" is concerned, you are right - that is a bad idea. The process of maintaining the database schema and its changes over time (adding columns, adding tables, removing columns, renaming things, creating indexes and so on) is referred to as "schema migration".

For security reasons, the user that creates (or changes) the tables is very often not the same (database) user as the one that the application uses to read and write the data.

So the schema migration is typically done outside of the application (or at least it should be).

The usual setup is to have one initial creation script (that might be split up in e.g. one script per table or any other structure) and then delta scripts that migrate the database schema from version x to version x + 1. All those scripts should be stored in your source control system (e.g. git).

Typically people use tools to manage those scripts and run them in a controlled manner. The tools will e.g. know which changes have already been applied to a database and which not. This is very important if you maintain different environments, e.g. development, test, production.

We are using Liquibase for this purpose, but there are many other similar tools, e.g. Flyway or Sqitch are other very popular tools.

You could also keep the database up-to-date with the CLI of Liquibase. Then you don't need to manually use the sql (Link to get the sql) But if you want database versioning in python maybe you could look for a wrapper (just searched and found some). Then you could use liquibase directly in your code and you are not required to manually write wrapper code.

Ah, right. This sort of thing unquestionably works better if you use it right from the start, certainly. We solved that problem by baking an initial snapshot into our database docker image, and then applying liquibase migrations from that point on.

Can you export your entire database to a SQL file, and then have your very first liquibase script use something like http://www.liquibase.org/documentation/changes/sql_file.html to run the whole thing against a new from-scratch instance? That would get you up to your snapshot in one go, then build from there.

I have a Docker container with Liquibase installed on it. I use that to perform any initialization, updates, deletes, etc to my postgres database as part of a bootstrapping process.

You can use https://hub.docker.com/r/sequenceiq/liquibase/

I prefer http://www.liquibase.org/ over Flyway, as it provides an abstraction over SQL with Database Refactorings. Besides the obvious of being database agnostic (mysql, PostgreSQL, MS SQL, etc...), this means it could use the optimal solution based on the underlying database.

Thanks that makes sense. It's much better to use http://www.liquibase.org/ for that since

• it's language agnostic

• it's database agnostic (e.g. it has database refactorings that don't use SQL at all)

Related to this, I've been meaning to ask you guys about management of schema changes on the mailing list. Are there plans in this area yet? Will you support one of the existing tools like Squitch or Liquibase?

> Of course versioning is a problem with SQL, but the idea is that the rules should change very rarely.

Git + http://www.liquibase.org/

I'm not affiliated with the project but I want more people using it.

> You can use other versioning solutions with sql files that are then used to pushed changes to production.

Git + http://www.liquibase.org/

I'm not affiliated with the project but I want more people using it.

Should be using a master changelog that references discrete changelogs. See best practices.

Should be documenting the changeset, the comments get pushed into the DATABASECHANGELOG and the first person that has to look at that will thank you.

The example changelog is a very bad one, you shouldn't be using <sql> unless you have to. Liquibase is also about portability between implementations. It should have been:

<createTable tableName="user"> <createColumn ...> </createTable>

And so on.

If you ARE going to use <sql> then you need to specify your own rollback. Liquibase doesn't do this for you unless you're using the aforementioned (and you should be using them) createTable, createIndex, createColumn, etc.

Just a note for anyone who hasn't used Liquibase: you can also do this programmatically, or through Spring, or with a Maven plugin, or on the command line using a JAR, etc. So another benefit (not listed) is that you have a lot of options in when and how changes are deployed.

P.S. This is directed at the article, not you specifically, unless you're the author too...

/u/remy_porter already touched on the importance of putting migration scripts in source control. Once you do, you need some way of automatically running those scripts. There are a few tools that can do that:

dbsc (disclaimer: I am the author)

sqitch

Liquibase