Best practice to update the Database using the SQL scripts

Camunda 7 Topics Discussion & Questions
1

Hi,

i am complete new and i want to know, if the naming of the SQL scripts (e.g. located in https://app.camunda.com/nexus/service/rest/repository/browse/public/org/camunda/bpm/distro/camunda-sql-scripts/7.12.0/) is fix.

The background is, that we have to update the camunda DB (MSSQL) automatically and therefore we have to implement an algorithm to identify the correct SQL script(s) by comparing the current camunda version and the latest released camunda version.

Kind Regards,
Andy

2

Hi Andy,

we used Flyway (https://flywaydb.org/) for this. There, you can configure path to your scripts… E.g. in SpringBoot we do it with:

spring:
  flyway:
    enabled: true
    locations: "classpath:db/migration"
    ignore-missing-migrations: true
    out-of-order: true
  jpa:
    generate-ddl: false
    hibernate.ddl-auto: validate
    show-sql: false
    open-in-view: true

and put all the scripts into this folder, renaming them according to Flyway schema (V_1_0_0__identity.sql). After Flyway runs, it creates a DB table and stores the version (and checksum) of the script it already applied.

An alternative for Flyway (is more advanced) is Liquibase.

Works like a charm.

3