Best practices for seeding a production database

General Discussion
1

We often have developers that want to seed data when they create a new table. What are the best practices and how does rollback work? I’ve read about loadData but I’m still curious as to how a rollback works when you use loadData. Is there a better way to load seed data? This is for Microsoft SQL Server.

2

Hi @GeoffCraig,

LoadData is the right thing to do what you need, and for rollback you can add that as part of the changeset configuration, it would be something like:

<changeSet id="1" author="liquibaseuser">
    <loadData file="names.csv" tableName="testTable"/>
    <rollback>
        <dropTable tableName="testTable"/> // or do what you need here
    </rollback>
</changeSet>

Hope this helps, if you have any other question just let us know.

Thanks,
Daniel.

3

Thank you for your reply @mallod. We did a loadData changelog with no rollback and now rollback is completely broken because liquibase says it doesn’t know how to rollback a loadData transaction. Is the only way to use loadData without breaking rollback is to use a rollback like you have in your example? Could we do an empty rollback so that we can rollback further in the changelog if needed?

4

Hey @GeoffCraig,

Would you mind sharing what are you trying with an example (generic if there is sensitive data you cannot share) and the error you are getting. Then I can try to reproduce it here, and if I don’t know what’s happening I’ll check it with someone from the team.

Thanks,
Daniel.

5

Hello @mallod,

We posted to stackoverflow and it has the change log. sql - Liquibase Rollback functionality stopped working after applying seed data - Stack Overflow

This is the error we are receiving so we can’t rollback past this.

Rollback-Error
Rollback-Error926×236 86.1 KB

Thank you for the assistance!

6

You need to add a custom rollback section to the “loadData” change, since “auto-rollback” is not supported for that change-type, which is what the error message “No inverse to liquibase.change.core.LoadDataChange” is trying to tell you (not a great message).

Since you are using yaml, add this below your “changes” section in the “loadData” changeset:

  rollback:
    - sql: delete from AppetiteCategoryWeight;