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

Prerequisites

Before using Liquibase and SQLite, ensure you have completed the following:

Driver Information

To use Liquibase and SQLite, you must install the JDBC driver:

  1. Download the sqlite-jdbc-<version>.jar file for a version of SQLite that is supported by Liquibase. You can also download this jar from Maven’s central repository.
  2. Place the sqlite-jdbc-<version>.jar file in the liquibase/lib directory.
  3. Note: If you put the sqlite-jdbc-<version>.jar file in any other directory, specify the path to it in the liquibase.properties file: classpath: ../path_to_drivers/sqlite-jdbc-<version>.jar.

  4. Open your liquibase.properties file and specify the driver value in it:
  5. driver: org.sqlite.JDBC

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

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

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

Testing 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 your liquibase.properties file:
  3. url: jdbc:sqlite:example.db
  4. Create a changelog file called dbchangelog (.xml, .sql, .json, or .yaml) in your project directory and add a changeset to it.
  1. Specify your changelog in the liquibase.properties file. It should look like this:
  2. changeLogFile: 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 see whether the connection is successful. This will create a database called example.db in your project folder if it does not already exist.
  4. liquibase --changeLogFile=dbchangelog.xml status

    Note: If you did not specify any properties in your 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 updateSQL 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 --changeLogFile=dbchangelog.xml updateSQL
    liquibase --changeLogFile=dbchangelog.xml update

    After your first update, you will see a new table along with the DATABASECHANGELOG and DATABASECHANGELOGLOCK tables added to the database:

    • DATABASECHANGELOG table. This table keeps a record of all the changesets that were deployed. When you deploy, the changesets in the changelog are compared with the DATABASECHANGELOG tracking table, and only the new changesets that were not found in the DATABASECHANGELOG will be deployed.
    • DATABASECHANGELOGLOCK table. This table is used internally by Liquibase to manage access to the DATABASECHANGELOG table during deployment and ensure only one instance of Liquibase is updating the database at a time, whether that is creating, updating, or deleting changes.

Related Links