Easily Import Data into your Database

May 7, 2021
Easily Import Data into your Database

We recently saw a tweet looking for an easy way to import data into a database. Many Liquibase users may not know that Liquibase can easily help with this!

Liquibase does far more than CSV data loading. You can manage any database schema change just like app code change in source control. But databases don’t just have code! They have data, too. In some instances, you may want to load data into your database. For example, your application may hold insurance policies that are described in the database. Using the Liquibase loadData functionality can make sure those changes are applied correctly.

You can easily do this with the following XML. (JSON and YAML, are supported, too.)

<changeSet author="liquibase-docs" id="loadData-example">
    <loadData catalogName="cat" 
            commentLineStartsWith="//"
            encoding="UTF-8"  
            file="example/users.csv"  
            quotchar="'" 
            relativeToChangelogFile="true" 
            schemaName="public" 
            separator=";" 
            tableName="person" 
            usePreparedStatements="true"> 
      <column header="header1"  
                 name="id"
                 type="NUMERIC"/> 
      <column index="3"  
                 name="name" 
                 type="BOOLEAN"/>
    </loadData>
</changeSet>

Maybe you have data in the table that simply needs to be replaced with the CSV file data? To do that, use the delete functionality.

Remember to add runAlways=”true” to both of these changesets. That way, Liquibase will make sure the data is always up to date. Here’s a full example:

<changeSet  author="liquibase-docs"  id="delete-example" runAlways=”true”>  
    <delete  catalogName="cat"  
           schemaName="public"  
            tableName="person"/>  
</changeSet>

<changeSet author="liquibase-docs" id="loadData-example" runAlways=”true”>
    <loadData catalogName="cat" 
            commentLineStartsWith="//"
            encoding="UTF-8"  
            file="example/users.csv"  
            quotchar="'" 
            relativeToChangelogFile="true" 
            schemaName="public" 
            separator=";" 
            tableName="person" 
            usePreparedStatements="true"> 
      <column header="header1"  
                 name="id"
                 type="NUMERIC"/> 
      <column index="3"  
                 name="name" 
                 type="BOOLEAN"/>
    </loadData>
</changeSet>

Load your data with Liquibase

Need help or have questions about loading data into your database with Liquibase? We’re happy to help. You can always try out our expert support through a Liquibase Pro trial or head over to get help on our community forum.

ARTICLE AUTHOR

Robert Reeves
Robert Reeves
CTO & Co-Founder at Liquibase

Robert advocates for Liquibase customers and provides technical architecture leadership. Prior to co-founding Liquibase, Robert was a Director at the Austin Technology Incubator. Robert co-founded Phurnace Software in 2005. He invented and created the flagship product, Phurnace Deliver, which provides middleware infrastructure management to multiple Fortune 500 companies.