Using Liquibase with SQLite

SQlite is a relational database management system that implements a small, fast, and self-contained embedded SQL database engine.

Supported Versions

  • 3.X
  • 3.34.0 – officially certified and tested with Test Harness

Prerequisites

Install Drivers

To use Liquibase and SQLite, install the JDBC driver. Liquibase includes a pre-installed driver for SQLite in the liquibase/lib directory. However, you can also install the driver from GitHub or Maven’s central repository. For more information, see Adding and Updating Liquibase Drivers.

Note: If you place the sqlite-jdbc-<version>.jar file in a different directory, specify the path in the Liquibase properties file, as follows: classpath: ../path_to_drivers/sqlite-jdbc-<version>.jar.

If you use Maven, add the following XML fragments to your Maven pom.xml file:

<dependency>
    <groupId>org.xerial</groupId>
    <artifactId>sqlite-jdbc</artifactId>
    <version>(version)</version>
 </dependency>

Maven will use this information to download the SQLite JDBC library from Maven’s central repository into your local repository.

Test Your Connection

For Liquibase and SQLite to work, you need to:

  1. Ensure your SQLite database is configured. You can use sqlite3 commands like .databases and .show to check the status of the database.
  2. Specify the database URL in the Liquibase properties file, as follows:
  3. url: jdbc:sqlite:example.db
  4. Create a text file called changelog (.xml, .sql, .json, or .yaml) in your project directory and add a changeset.
  1. Specify your changelog as a connection profile in the Liquibase properties file, as the following example shows:
  2. changelog-file: dbchangelog.xml
    url: jdbc:sqlite:example.db
    driver: org.sqlite.JDBC

    Note: By default, SQLite databases do not require authentication, so the username and password attributes do not have to be specified. For more information, see SQLite User Authentication.

  3. Run the status command to determine whether the connection is successful. This action creates a database called example.db in your project folder, if it does not already exist.
  4. liquibase --changelog-file=dbchangelog.xml status

    Note: If you did not specify any properties in the Liquibase properties file, they must be passed into the command line as attributes.

  5. You can preview any database changes you want to make using the update-sql command, which will compare your changelog to your database and display the SQL needed to add your undeployed changesets. Then run your first database update with the update command.
  6. liquibase --changelog-file=dbchangelog.xml update-sql
    liquibase --changelog-file=dbchangelog.xml update

From a database UI tool, ensure that your database contains the table you added along with the DATABASECHANGELOG table and DATABASECHANGELOGLOCK table.

Related Links