> ## Documentation Index
> Fetch the complete documentation index at: https://docs.activeviam.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Schema migrations

> How to manage database schema migrations for Atoti Limits using Liquibase, covering four use cases based on default or custom schemas and whether Liquibase is used, with Maven plugin commands for each case

We have added [Liquibase](https://www.liquibase.com/) changesets to facilitate database migrations. This section
describes how to migrate your database schema by [configuring Liquibase](https://docs.liquibase.com/home.) and using
the [Liquibase Maven plugin](https://docs.liquibase.com/tools-integrations/maven/home.).

We use the Maven plugin for ease of maintenance, but there are other [Liquibase alternatives](https://www.liquibase.com/download) - such as the CLI, Spring or Docker - which you can also utilize.

<Note>
  We recommend following best practices when it comes to updating your database. For example, backing up the database before a migration and
  testing the scripts against a test instance of your database first. If in doubt, please contact your database administrator.
</Note>

# Overview

There are four main use cases for managing database schema migrations:

<table><thead><tr><th /><th>Default Schema</th><th>Custom Schema</th></tr></thead><tbody><tr><td>With Liquibase</td><td><a href="./limits-database-migration">Case 1</a></td><td><a href="./limits-database-migration">Case 2</a></td></tr><tr><td>Without Liquibase</td><td><a href="./limits-database-migration">Case 3</a></td><td><a href="./limits-database-migration">Case 4</a></td></tr></tbody></table>

i.e.:

* **Case 1:** You use the default Atoti Limits schema and want to manage database schema migrations using Liquibase.
* **Case 2:** You have customized the Atoti Limits schema and want to manage database schema migrations using Liquibase.
* **Case 3:** You use the default Atoti Limits schema and want to manage database schema migrations using some other mechanism.
* **Case 4:** You have customized the Atoti Limits schema and want to manage database schema migrations using some other mechanism.

<Warning>
  Please carefully select the appropriate case for your business needs. If you plan to use Liquibase, **you must first initialize your database schema with Liquibase.**
</Warning>

## Prerequisites

If you want to use Liquibase to manage your database migrations, you first need to generate your initial database schema with Liquibase.
This is because Liquibase stores a checksum in your database to determine where you are in the migration process.

#### Initialize your Liquibase schema

##### 1. Default Atoti Limits Schema

If you are using the default Atoti Limits schema, you can generate the initial database schema
using the provided `master-changelog.yaml` in the `src/main/resources/liquibase` folder of the `limits-starter` module.
The `master-changelog.yaml` file is used to apply all the changesets listed in  `limits-starter/src/main/resources/liquibase/changelogs` in the correct order.

<Note>
  The `limits-3-3-0-changelog-0.yaml` file is used to initialize the schemas for tables internal to Atoti Limits.
  The `limits-3-3-0-changelog-1.yaml` file is used to initialize the schemas for tables external to Atoti Limits (eg. Activiti).
  If you don’t want Liquibase to manage migrations for these external tables you can remove this file from `master-changelog.yaml`.
  If you want Liquibase to manage migrations for these external tables, you must ensure that Activiti
  does not try to migrate the schemas by setting the property `spring.database-schema-update = drop-create`.
  See [the Activiti documentation](https://www.activiti.org/userguide/#creatingDatabaseTable) for more information.
</Note>

Here’s how to initialize the database schema:

1. Configure the `liquibase.properties` file with the connections to your database. We have provided a sample in `limits-starter/src/main/resources/liquibase`.

2. Add the `liquibase-core` dependency to your `pom.xml` file. This dependency is required to run the Liquibase Maven plugin and is not provided by `limits-starter`.

   ```xml theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
   <dependency>
       <groupId>org.liquibase</groupId>
       <artifactId>liquibase-core</artifactId>
       <version>4.28.0</version>
   </dependency>
   ```

3. If not on your classpath, add the required database driver to your `pom.xml` file. See the [liquibase example pom.xml](https://docs.liquibase.com/tools-integrations/maven/maven-properties.) for more info.

4. Ensure your database server is running.

5. Run `mvn liquibase:update` to apply the changes to your database.

<Note>
  You may also run `mvn liquibase:updateSQL` before the update command to inspect the raw SQL for potential issues.
</Note>

6. You can now use Liquibase to manage your database schema migrations.

##### 2. Custom Atoti Limits Schema

If you have customized the Atoti Limits schema, you will need to create your own `master-changelog.yaml` and include your custom changesets.
Please see the Liquibase documentation, specifically [Maven’s `generateChangeLog` goal](https://docs.liquibase.com/tools-integrations/maven/commands/maven-generatechangelog.) for how this can be done. You must then apply the same steps as in the previous section.

## Migrate your schema

### Case 1: Default schema with Liquibase

1. Ensure `liquibase.properties` is configured to point to your database.
2. Ensure your database is running.
3. Run `mvn liquibase:update` from `limits-starter`.

### Case 2: Custom schema with Liquibase

If you have a custom schema and are using Liquibase, you will need to create a `master-changelog.yaml` file that includes your custom changesets.
Once created, please follow the same steps as in [Case 1](./limits-database-migration#case-1-default-schema-with-liquibase).

### Case 3: Default schema without Liquibase

If you are not using Liquibase, you can still use the changesets to generate the SQL required for migrations and apply them accordingly.
To do so, you can run `mvn liquibase:updateSQL` to generate the SQL required for the migration.
This will create a file by default in `limits-starter/src/main/resources/liquibase/migrate.sql` which you can then investigate and apply to your database.

### Case 4: Custom schema without Liquibase

If you have a custom schema and are not using Liquibase then you will need to implement your own migration strategy.
Note that the step to generate the SQL outlined in [Case 3](./limits-database-migration#case-3-default-schema-without-liquibase)
with `mvn liquibase:updateSQL` will be useful to get the changes that have been applied to our default schema.
You may ignore any references to the `DATABASECHANGELOG`, `DATABASECHANGELOGLOCK` and `DATABASECHANGELOGHISTORY` tables
which are specific to [Liquibase](https://docs.liquibase.com/concepts/tracking-tables/tracking-tables.).

## FAQ

<AccordionGroup>
  <Accordion title="Why not use the SQL changelog format?">
    Liquibase offers [two formats for changelogs](https://docs.liquibase.com/start/home.): the SQL model and the platform-agnostic model.
    We chose the platform-agnostic model because it is more flexible and allows us to support multiple databases, which is how persistence in Atoti Limits is natively designed.
  </Accordion>

  <Accordion title="Why are the changesets in YAML format?">
    We felt YAML was the most human-readable format available. The other options were XML and JSON.
  </Accordion>

  <Accordion title="Can I print the difference between my existing database and the new database when I run the migration?">
    Yes, you may use the `mvn liquibase:diff` command to view any schema differences. This will also generate a changelog.
  </Accordion>

  <Accordion title="Can I print the SQL that will be executed before running a migration?">
    Yes, you may run `mvn liquibase:updateSQL` to view the raw SQL that will be executed. This will not alter your database.
  </Accordion>

  <Accordion title="Can I rollback a migration?">
    Yes, please see the [Maven rollback command](https://docs.liquibase.com/tools-integrations/maven/commands/maven-rollback.) for options on how to rollback migrations.
  </Accordion>
</AccordionGroup>