Getting Liquibase to run an SQL script on start up with SpringBoot Kotlin

Issue

I'm having trouble getting liquibase to execute my sql script in my SpringBoot Kotlin appllication.

Here is my build.gradle.kts

import org.jetbrains.kotlin.gradle.tasks.KotlinCompile

plugins {
    id("org.springframework.boot") version "2.5.4"
    id("io.spring.dependency-management") version "1.0.11.RELEASE"
    kotlin("jvm") version "1.5.21"
    kotlin("plugin.spring") version "1.5.21"
}

group = "com.johncooper"
version = "0.0.1-SNAPSHOT"
java.sourceCompatibility = JavaVersion.VERSION_11

repositories {
    mavenCentral()
}

sourceSets.main {
    java.srcDirs("src/main")
}

sourceSets.test {
    java.srcDirs("src/test", "src/cucumber/kotlin", "src/cucumber")
}

dependencies {
    implementation("org.springframework.boot:spring-boot-starter-data-r2dbc")
    implementation("org.springframework.boot:spring-boot-starter-validation")
    implementation("org.springframework.boot:spring-boot-starter-webflux")
    implementation("com.fasterxml.jackson.module:jackson-module-kotlin")
    implementation("io.projectreactor.kotlin:reactor-kotlin-extensions")
    implementation("org.jetbrains.kotlin:kotlin-reflect")
    implementation("org.jetbrains.kotlin:kotlin-stdlib-jdk8")
    implementation("org.jetbrains.kotlinx:kotlinx-coroutines-reactor")
    implementation("org.json:json:20211205")
    implementation("com.fasterxml.jackson.module:jackson-module-kotlin")
    implementation("org.springdoc:springdoc-openapi-webflux-ui:1.6.4")
    implementation("org.springdoc:springdoc-openapi-kotlin:1.6.3")
    implementation("org.mariadb:r2dbc-mariadb")

    implementation("org.liquibase:liquibase-gradle-plugin:2.0.3")
    implementation("org.liquibase:liquibase-core:2.0.3")

    implementation("org.mariadb.jdbc:mariadb-java-client:3.0.3")
    testImplementation("org.jetbrains.kotlinx:kotlinx-coroutines-test")
    testImplementation("org.junit.jupiter:junit-jupiter-engine")
    testImplementation("org.testcontainers:testcontainers:1.16.0")
    testImplementation("org.testcontainers:junit-jupiter:1.16.0")
    testImplementation("org.testcontainers:mariadb:1.16.3")
    testImplementation("org.testcontainers:r2dbc:1.16.0")
    testImplementation("org.mockito.kotlin:mockito-kotlin:4.0.0")
    testImplementation("org.springframework.boot:spring-boot-starter-test")
    testImplementation("io.projectreactor:reactor-test")
    testImplementation("org.junit.vintage:junit-vintage-engine:5.7.2")
    testImplementation("io.cucumber:cucumber-java:6.11.0")
    testImplementation("io.cucumber:cucumber-spring:6.11.0")
    testImplementation("io.cucumber:cucumber-junit:6.11.0")
}

tasks.withType<KotlinCompile> {
    kotlinOptions {
        freeCompilerArgs = listOf("-Xjsr305=strict")
        jvmTarget = "11"
    }
}

tasks.withType<Test> {
    useJUnitPlatform()
}

tasks {
    test {
        filter {
            excludeTestsMatching("**.RunBDDTests")
        }
    }
}

task<Test>("acceptanceTest") {
    useJUnitPlatform()
    filter {
        includeTestsMatching("**.RunBDDTests")
    }
}

And here is my application.properties -

server.port=9000

spring.r2dbc.url=r2dbc:mariadb://0.0.0.0:3306/mydb
spring.r2dbc.username=testuser
spring.r2dbc.password=mypwd
spring.r2dbc.initialization-mode= always

spring.liquibase.url=jdbc:mariadb://0.0.0.0:3306/mydb
spring.liquibase.user=testuser
spring.liquibase.password=mypwd
spring.liquibase.change-log=classpath:db/changelog/db.changelog-master.xml
spring.liquibase.enabled=true

logging.level.org.springframework.data.r2dbc=INFO
debug=false
logging.level.com.johncooper=INFO

Here is my db.changelog-master.xml located in src/main/resources/db/changelog/ -

<databaseChangeLog
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">
    <include file="data.sql" relativeToChangelogFile="true"/>
</databaseChangeLog>

And here is the script I want execute data.sql -

# liquibase formatted sql
# changeset mydb:data

DROP TABLE IF EXISTS pets;

CREATE TABLE pets (
   id INT AUTO_INCREMENT  PRIMARY KEY,
   name VARCHAR(250) NOT NULL,
   description VARCHAR(250) NOT NULL,
   breed VARCHAR(250) NOT NULL,
   type VARCHAR(250) NOT NULL,
   price VARCHAR(250) NOT NULL
);

INSERT INTO pets (name, description, breed, type, price) VALUES
 ('Nemo', 'fish', 'piranah', 'a fricking fich', '$100')

# rollback DROP TABLE IF EXISTS mydb.pets

There are no errors on running the application ./gradlew bootRun through Intellij. The script simply doesn't get run. Can anybody spot what I am doing wrong?

Update - here is a link to the github repo with my code: https://github.com/johnnyalpha8/Pet-Shop

Solution

While running your setup with debug logs enabled, I've noticed that auto-configuration for Liquibase did not work, as some criteria was not met.

LiquibaseAutoConfiguration.LiquibaseConfiguration:
   Did not match:
      - @ConditionalOnClass did not find required class 'org.springframework.jdbc.core.ConnectionCallback' (OnClassCondition)

The class org.springframework.jdbc.core.ConnectionCallback is provided as part of the spring-boot-starter-data-jdbc. With the following adjustments to your build.gradle.kts, your .sql-file is picked up by Liquibase.

dependencies {
    // - provides the missing class for auto-configuration 
    implementation("org.springframework.boot:spring-boot-starter-data-jdbc")

    ...

    // - this dependency seems to be unnecessary
    // implementation("org.liquibase:liquibase-gradle-plugin:2.0.3")
    // - you provided an outdated version
    // - a version must not be provided as it is managed by Spring Boot 
    implementation("org.liquibase:liquibase-core")

    ...

---

However, your .sql script contains a syntax error. I am not familiar with Liquibase based on .sql-files, but I've got it to run using the .xml-file syntax for Liquibase.

db.changelog-master.xml

<databaseChangeLog
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">
    <include file="db.changelog-0001.xml" relativeToChangelogFile="true"/>
</databaseChangeLog>

db.changelog-0001.xml

<?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"
        xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.3.xsd">
    <changeSet id="1" author="jalpha">
        <createTable tableName="pets">
            <column name="id" type="int" autoIncrement="true">
                <constraints primaryKey="true" nullable="false"/>
            </column>
            <column name="name" type="varchar(250)">
                <constraints nullable="false"/>
            </column>
            <column name="description" type="varchar(250)">
                <constraints nullable="false"/>
            </column>
            <column name="breed" type="varchar(250)">
                <constraints nullable="false"/>
            </column>
            <column name="type" type="varchar(250)">
                <constraints nullable="false"/>
            </column>
            <column name="price" type="varchar(250)">
                <constraints nullable="false"/>
            </column>
        </createTable>

        <insert tableName="pets">
            <column name="name" value="Nemo"/>
            <column name="description" value="fish"/>
            <column name="breed" value="piranah"/>
            <column name="type" value="a fricking fich"/>
            <column name="price" value="$100"/>
        </insert>
    </changeSet>
</databaseChangeLog>

---

Thank you for providing an example repository, this helped reproducing the error immensely.

Answered By - Endzeit