Setup Liquibase with an existing project - open source version - create-changelog, diff-changelog

Liquibase Development
1

Hi all,

I have just realized that handling stored procedures, functions, triggers, views etc. with create-changelog and diff-changelog commands is not possible in the free version, and it is a big letdown as we use these db objects quite a bit. The plan we had in our company was to setup Liquibase with our existing projects as outlined here with create-changelog command to start with. Then we would use diff-changelog as a part of our regular CI/CD cycle, having the changelogs included in the codebase, properly versioned and all.

Are there any suggested work-arounds, ways to use Liquibase as intended, but having the ability to also include stored procedures, functions, triggers, views etc ? I guess using the sqlFile change type? Any other tips?

Thank you!

2

You can definitely handle stored procedures, functions, triggers, and view with Liquibase free version. You will need to use custom SQL, which mean you code the CREATE statements. You can put the SQL into sql raw (sqlFile), sql formatted, xml, yaml, or json format files.

Here are a couple of examples.

Formatted SQL:

--liquibase formatted sql

--changeset BOB:create_functionv1 endDelimiter:/ runOnChange:true stripComments:false
--comment: Create test_func function
    create or replace function test_func return date is
      v_dummy1 date;
    begin
      select sysdate into v_dummy1 from dual;
      return v_dummy1;
    end test_func;
    /  
--rollback drop function test_func;

XML:

<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">

<changeSet id="create_procedurev1" author="BOB" runOnChange="true">
  <comment>Create the test_proc stored procedure</comment> 
  <sql endDelimiter="/">
    create or replace procedure test_proc is
      v_dummy1 date;
    begin
      select sysdate into v_dummy1 from "DUAL";
    end test_proc;
    /  
  </sql>
  <rollback>
    DROP PROCEDURE test_proc;
  </rollback>
</changeSet>

</databaseChangeLog>
1 Like
3

Yep thanks, figured that much. I was referring to the create-changelog, diff-changelog commands which do not take these db objects into account. We’ll have to manage with these items done manually for the time being.

1 Like
4

Correct. Liquibase has reserved that functionality for the licensed version.

1 Like
5

Daryl, thanks for your help. If I may, how would you suggest I go about the setup of Liquibase with my project in place already? Since generate-changelog won’t cover for all db objects, the DATABASECHANGELOG won’t have entries for all these missing objects. Then, as you already suggested, I write the SQL CREATE statements for all these missing objects, correct?

But how do I get these changes into the DATABASECHANGELOG table, so that Liquibase has the right starting point going forward? I was thinking of creating a root xml changelog which would includeAll these sqls, and then run changelog-sync-sql and changelog-sync command. Is that a solid approach?

Thanks again!

6

You have 2 options:

  1. Just start using Liquibase going forward, without regard to existing objects in your database.
    This is the option that most application use at my work.

  2. Create changelogs/changesets to represent all of the existing objects, and then run “changelog-sync” to populate the databasechangelog table and make it look like Liquibase deployed the objects, then start using Liquibase for new objects going forward. Here is some info on this option:
    How to set up Liquibase with an Existing Project and Multiple Environments
    This options takes much more work to get going at the beginning.

1 Like
7

yep, thought so. thank you!