Integration with spring boot

[dzasti]

Hello, is there a possibility to run liquibase cassandra extension via spring boot? Can’t find any example online. Also I’ve done some research: spring-projects/spring-boot#29991 (comment) , #4 (comment). Seems confusing, could someone show me an example, if its possible ?

thanks

[maximevw]

Hello @dzasti,

It should be possible to use Spring Liquibase with Cassandra as I successfully ran it with the example below.

First of all, I used a slightly modified version of liquibase-cassandra using cassandra-jdbc-wrapper (4.9.1 or above). For further details, see here: #148 (comment)
But, I guess it should also work with Simba JDBC driver (if you have an appropriate licence).

The dependencies to include in your POM file are:

<!-- Using Spring Boot 3.1.4 and Java 17 -->
<dependency>
	<groupId>org.springframework.boot</groupId>
	<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
	<groupId>org.liquibase</groupId>
	<artifactId>liquibase-core</artifactId>
</dependency>
<!-- Include cassandra-jdbc-wrapper if you don't use Simba JDBC, otherwise include a dependency to Simba driver -->
<dependency>
	<groupId>com.ing.data</groupId>
	<artifactId>cassandra-jdbc-wrapper</artifactId>
	<version>4.10.0</version>
</dependency>
<dependency>
	<groupId>org.liquibase.ext</groupId>
	<artifactId>liquibase-cassandra</artifactId>
	<!-- Use here a version modified to use cassandra-jdbc-wrapper instead of Simba if necessary-->
	<version>4.23.2</version>
</dependency>

<!-- Required to avoid runtime exceptions due to a conflict of version between Spring Boot dependencies and cassandra-jdbc-wrapper -->
<dependencyManagement>
	<dependencies>
		<dependency>
			<groupId>com.datastax.oss</groupId>
			<artifactId>java-driver-core</artifactId>
			<version>4.17.0</version>
		</dependency>
	</dependencies>
</dependencyManagement>

Assuming you have a Cassandra instance running on localhost, port 9042, with a keyspace named test_keyspace.

In your application.properties file, you should have this:

# Liquibase configuration
spring.liquibase.url=jdbc:cassandra://localhost:9042/test_keyspace?localdatacenter=datacenter1&user=cassandra&password=cassandra&compliancemode=Liquibase
spring.liquibase.change-log=classpath:/db/changelog/db.changelog-master.yaml
spring.liquibase.driver-class-name=com.ing.data.cassandra.jdbc.CassandraDriver

# Spring datasource configuration
spring.datasource.name=cassandra_datasource
spring.datasource.url=jdbc:cassandra://localhost:9042/test_keyspace?localdatacenter=datacenter1&user=cassandra&password=cassandra
spring.datasource.driver-class-name=com.ing.data.cassandra.jdbc.CassandraDriver
spring.datasource.hikari.minimum-idle=1

In your project resources, you should have the following files:

• db/changelog/db.changelog-master.yaml

databaseChangeLog:
  - include:
      file: classpath:/db/changelog/init.sql

• db/changelog/init.sql. Note the extension MUST be "sql" otherwise Liquibase will fail to parse the script.

USE test_keyspace;
CREATE TABLE IF NOT EXISTS tbl_test (
keyname text PRIMARY KEY,
bool_col boolean,
int_col int);

INSERT INTO tbl_test(keyname, bool_col, int_col) VALUES ('key1', true, 1) IF NOT EXISTS;
INSERT INTO tbl_test(keyname, bool_col, int_col) VALUES ('key2', false, 2) IF NOT EXISTS;

Finally, your Spring Boot application:

@SpringBootApplication
public class SpringLiquibaseCassandraApplication {

	@Autowired
	JdbcTemplate jdbcTemplate;

	@EventListener(ApplicationReadyEvent.class)
	public void checkData() throws SQLException {
		jdbcTemplate.query("SELECT * FROM tbl_test", rs -> {
            System.out.println("------------------------------------");
            System.out.println("keyname:  " + rs.getString(1));
            System.out.println("bool_col: " + rs.getBoolean(2));
            System.out.println("int_col:  " + rs.getInt(3));
        });
    }

	public static void main(String[] args) {
		SpringApplication.run(SpringLiquibaseCassandraApplication.class, args);
	}

}

Running your application, the output should be the following, proving the Liquibase changes have been executed successfully:

------------------------------------
keyname:  key1
bool_col: true
int_col:  1
------------------------------------
keyname:  key2
bool_col: false
int_col:  2

[shubha11m]

as like we are connecting sql db from spring boot using liquibase it is possible to connect aws keyspace using liquibase without jdbc
in this example you guys put local cassendra can we use amazone keypace to run our jar at ec2 and connect with keyspace and create tables there

[maximevw]

@shubha11m Liquibase is based on JDBC, so it's not possible to use it without JDBC. But it should not really be a problem.
Theoretically, it could be possible to connect to AWS Keyspaces, but as I said in previous discussions, some operations performed by Liquibase are currently not supported by AWS Keyspaces (see #297 for further details).
However, to connect to AWS Keyspaces using JDBC, you could use this minimal connection string:
jdbc:cassandra://cassandra.{ec2-region}.amazonaws.com:9142/{keyspace}?compliancemode=Liquibase (the parameter compliancemode=Liquibase is only required when used with Liquibase, {keyspace} is the target keyspace you want to use and {ec2-region} has to be replaced by the appropriate EC2 region of your server). The documentation of the JDBC connection string for the driver used by liquibase-cassandra is available here.

[shubha11m]

HI @maximevw thankyou for you response so as previously the snip of code is there to connect keyspace with spring boot is it whole code or do we need to add cql config file and other than that and are we not able to put our changes into xml file rather then .sql

i had applied all changes #212 (comment)
but when i run the appliction its just run as plain spring boot application and not executing the
the liquibase chnagelog file

[maximevw]

Hi @shubha11m

The example given in this issue is just an example among a lot of possible configurations, so, obviously you can adapt it according to your needs. If you prefer use xml changelog, you can.

A lot of documentation about running Liquibase with SpringBoot is available here and in the linked pages.

Maybe try to specify the changelog location in the application.properties file:

spring.liquibase.change-log=classpath:db/changelog/db.changelog-master.xml # Adapt the path to match your changelog file.

Also, ensure the dependencies are up-to-date (those mentioned in this example are old and may contain some bugs):

<dependency>
	<groupId>com.ing.data</groupId>
	<artifactId>cassandra-jdbc-wrapper</artifactId>
	<version>4.12.0</version>
</dependency>
<dependency>
	<groupId>org.liquibase.ext</groupId>
	<artifactId>liquibase-cassandra</artifactId>
	<version>4.28.0</version>
</dependency>

[nicobte]

Hi @maximevw, I have tried with the versions you suggested, scripts appears as executed in databasechangelog table but the changes are not reflected, do you have any idea of the problem?

[maximevw]

Hello @nicobte
Hard to say... did you check the logs of your Spring Boot application? By default, they should indicate each item in the changelog and if it ran. Did you not see any errors? You can also set a lower level of log if necessary.
It also could depend on your changelog, some changes could be filtered for some reason.

[Danil9966]

@maximevw i have a question about usage of cassandra-jdbc-wrapper:4.12.0 in last release of liquibase-cassandra (4.28). Seems, like in LockServiceCassandra there are multiple checks for updatedRows count. but with debugging i've realized, that in cassandra-jdbc-wrapper DefaultOptionSet always returns 0 in getSQLUpdateResponse() method. so it's impossible to get correct updated rows count for LockServiceCassandra

[maximevw]

Hello @Danil9966

Cassandra database is not able to return the number of updated rows, that's why you meet this behaviour, but when running with Liquibase, your JDBC URL should always have the query parameter compliancemode=Liquibase as explained here. In this case, the default option set is not used and replaced by a specific behaviour for Liquibase.

[Danil9966]

@maximevw thank you! Is there a proper way to set this query parameter from configs or java code? CassandraDataSource (4.12.0 version) doesn't have complienceMode parameter

[maximevw]

@Danil9966 Indeed, CassandraDataSource currently doesn't support compliance mode parameter. For this, it's better to open a dedicated issue here in the JDBC driver project.

Alternatively, using Java code, you have to use the DriverManager for now, for example:

final String url = "jdbc:cassandra://localhost:9042/keyspace?localdatacenter=DC1&compliancemode=Liquibase";
final Connection connection = DriverManager.getConnection(url);

[nicobte]

Thanks @maximevw. I managed to solve it. I was wrong using the changelogSync function.

I made a custom Liquibase implementation, so I excluded auto configuration by @SpringBootApplication(exclude = {LiquibaseAutoConfiguration.class}) .

Here is what I made, hopefully it can help someone else:

Dependencies:

'org.liquibase:liquibase-core:4.28.0'
 'org.liquibase.ext:liquibase-cassandra:4.28.0'
'com.ing.data:cassandra-jdbc-wrapper:4.12.0'

String jdbcUrl = String.format(
                "jdbc:cassandra://%s:%s/%s"
                        + "?compliancemode=Liquibase"
                        + "&consistency=%s"
                        + "&localdatacenter=%s",
                host,
                port,
                keyspace,
                consistency,
                datacenter
        );
        
String changelogFile = "db/changelog/db.changelog-master.yaml";        
        
void migrate() {
        try (DatabaseConnection jdbcConnection = new JdbcConnection(DriverManager.getConnection(jdbcUrl, username, password));
        Database database = DatabaseFactory.getInstance().findCorrectDatabaseImplementation(jdbcConnection)
            ) {
                ChangeLogParameters parameters = createChangeLogParameters(database);
                executeChangelog(database, parameters, changelogFile);
            } catch (SQLException | DatabaseException | CommandExecutionException e) {
                throw new RuntimeException("Error running migrations", e);
            }
}


ChangeLogParameters createChangeLogParameters(Database database) {
        ChangeLogParameters parameters = new ChangeLogParameters(database);
        parameters.set("myKeyspace", keyspace); // to be able to use keyspace in changelogs files, e.g. CREATE TABLE ${myKeyspace}.my_table(..);
        return parameters;
    }
    
    
  void executeChangelog(
            Database database, ChangeLogParameters parameters, String changelogFile
    ) throws CommandExecutionException {
        CommandScope updateCommand = new CommandScope(UpdateCommandStep.COMMAND_NAME);
        updateCommand.addArgumentValue(UpdateCommandStep.CHANGELOG_FILE_ARG, changelogFile);
        updateCommand.addArgumentValue(DbUrlConnectionArgumentsCommandStep.DATABASE_ARG, database);
        updateCommand.addArgumentValue(DatabaseChangelogCommandStep.CHANGELOG_PARAMETERS, parameters);
        updateCommand.execute();
    }
    
    

db.changelog-master.yaml

databaseChangeLog:
  - include:
      file: "db/changelog/create_table.yaml"
  - include:
      file: "db/changelog/insert_values.yaml"

create_table.yaml

databaseChangeLog:
  - changeSet:
      id: 1
      author: foobar
      changes:
        - sql:
            dbms: "cassandra"
            splitStatements: true
            sql: |
              CREATE TABLE IF NOT EXISTS ${myKeyspace}.my_table(
              id text PRIMARY KEY,
              name text
              );

db/changelog/insert_values.yaml"

databaseChangeLog:
  - changeSet:
      id: 2
      author: foobar
      changes:
        - sql:
            dbms: "cassandra"
            splitStatements: true
            sql: |
              INSERT INTO ${myKeyspace}.my_table(id, name) VALUES ('1', 'foo') IF NOT EXISTS;
              INSERT INTO ${myKeyspace}.my_table(id, name) VALUES ('2', 'bar') IF NOT EXISTS;