Convert DB to Liquibase xml

General Discussion
1

Hi,

I’m testing out liquibase for our dbs. we already have an existing dbs. I want to add our entire db into liquibase xml change log. I understand Liquibase would just work in existing db but I will only have change sets from that point. if I want to create a new db using changelog, I wont be able to without entire db schema and table create change sets in xml.
is there any way to do it ? like converting sql script to xml ?

Thanks.

2

You can use the generateChangeLog command to capture the current state of the database into changelog format. Be aware that not all objects types are extracted.

https://docs.liquibase.com/commands/community/generatechangelog.html

1 Like
3

Thanks Dary. I’m testing liquibase with Postgresql database. May i know what are the objects that might not get extracted in postgresql.

4

Hi @Ashad

I’m not 100% sure but what I know is if you are using Liquibase Pro, most of the objects (almost all) would get covered with generateChangelog. However, if you using Liquibase Open Source then generateChangelog will not extract :

  • function
  • stored procedure
  • trigger

for your PostgreSQL database. There could be more addition of objects to this list.

Hope this helps!

Cheers!

Rakhi Agrawal

1 Like
5

Thanks, Rakhi,
I’m currently using Liquibase Open Source for testing purposes.

1 Like
6

Hi @rakhi,

Thanks for your input. Could you please give me some advice what is the right way to use liquibase in my environment. I have a db in postgres and ms sql and there are couple of schemas in db. now when there is a change db, i write sql statement for both db and run them manually in each db. since i believe liquibase can update any db with xml changeset. I’m planning on using xml. Is this a good idea ?
Is there a way to keep changelog for each sql statement types of create, insert, alter, functions or changelogs for each schemas. what is the right way to do ?

7

Hi @Ashad

Happy to know you are planning to use Liquibase in your project.

Using XML changelogs and changesets is a good idea. It has a lot of features which allows us to use different functions for DBs. Please follow this link for Liquibase best practices.

By “a way to keep changelog” if you mean to store them or keep them segregated, then you can have it in different folders.
For eg: You can create directory structure as follows -

- Project
    - masterChangelog.xml
    - changesets
        - create
            - all your create changesets goes here
        - insert
            - all your insert changesets goes here
        - update
            - all your update changesets goes here
        - delete 
            - all your delete changesets goes here

To segregate your changesets for each schema you can similarly keep a directory structure of your own type. The way I have used in one of my projects is, I follow a format for naming a changeset file :
<operation>_<schema>_<table_name>.xml
eg: insert_mySchema_myTable.xml

To execute it for different schemas please refer to my answer on this post. Link here : StackOverFlow Post

I hope I got your question right and could answer it.

Please let us know if you have any other queries or if you face any issues at the time of implementation.

Cheers!

Rakhi Agrawal

1 Like
8

Thanks again @rakhi.
Your answer is really useful.

I have another issue in using liquibase xml format. There is no proper documentation on how Liquibase handle datatypes for different database types. I use mssql and postgres. I looked up to a table in a third party site on liquibase equivalent datatypes in other database. There are certain datatypes do not go accordingly to our need. Is there any way to manipulate this ? for eg:- like clob in Liquibase means varcahr in postgres and nvarchar in mssql.

Thank You.

9

You can use changelog parameters to control the datatypes used by a dbms. Here is a snippet from changelog parameters.

<property  name="clob.type"  value="clob"  dbms="oracle,postgresql"/>  
<property  name="clob.type"  value="longtext"  dbms="mysql"/>  
<property  name="table.name"  value="tableA"/>  

<changeSet  id="1"  author="joe">  
     <createTable  tableName="${table.name}">  
         <column  name="id"  type="int"/>  
         <column  name="${column1.name}"  type="${clob.type}"/>  
         <column  name="${column2.name}"  type="int"/>  
     </createTable>  
</changeSet>
1 Like