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
  3. Create a text file called changelog (.xml, .sql, .json, or .yaml) in your project directory and add a changeset.
<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
	xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
	xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
	xmlns:pro="http://www.liquibase.org/xml/ns/pro"
	xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
		http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd
		http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd
		http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd">
    <changeSet id="1" author="Liquibase">
    <createTable tableName="test_table">
           <column name="test_id" type="int">
                 <constraints primaryKey="true"/>
           </column>
           <column name="test_column" type="varchar"/>
    </createTable>
    </changeSet>
</databaseChangeLog>
-- liquibase formatted sql

-- changeset liquibase:1
CREATE TABLE test_table (test_id INT, test_column VARCHAR, PRIMARY KEY (test_id))

Tip: Formatted SQL changelogs generated from Liquibase versions before 4.2 might cause issues because of the lack of space after a double dash ( -- ). To fix this, add a space after the double dash. For example: -- liquibase formatted sql instead of --liquibase formatted sql and -- changeset myname:create-table instead of --changeset myname:create-table

databaseChangeLog:
   - changeSet:
       id: 1
       author: Liquibase
       changes:
       - createTable:
           columns:
           - column:
               name: test_column
               type: INT
               constraints:  
                   primaryKey:  true  
                   nullable:  false  
                   tableName: test_table
{ 
  "databaseChangeLog": [
  {
	"changeSet": {
	  "id": "1",
      "author": "Liquibase",
	  "changes": [
	    {
		  "createTable": {
		    "columns": [
			{
			  "column": 
		      {
				"name": "test_column",
				"type": "INT",
				"constraints": 
			  {
				"primaryKey": true,
				"nullable": false
				}
				}
			  }]
			,
			"tableName": "test_table"
		  }
		}]
	  }
	}]
  }
  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.

  2. 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.
    3. 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.

  3. 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.
    4. 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