> ## 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.

# Configure persistence

> How to configure data persistence in Atoti Limits, covering the CSV, Persistent, and CSV-to-Persistent load modes, how to configure the external datasource connection, and how to generate database DDLs

# Configuration

Atoti Limits provides a set of services that allow you to manage objects that exist within
the application at runtime.

## Managed objects

Atoti Limits manages three types of objects:

1. [Limit Structures](../../user-ref/input-files/limit_structures)
2. [Limits](../../user-ref/input-files/limits_approve)
3. [Incidents](../evaluation-tasks/incidents)

These objects may be loaded from an external database, where they can be managed from within Atoti Limits and any changes will be propagated to the external database of choice.

To work with persistent data in Atoti Limits, you need to [specify the load mode](#1-specifying-the-load-mode)
and [configure the connection to the external datasource](#2-configuring-the-connection-to-the-external-datasource).

### Prerequisite: Install the database driver

To use your database, add any relevant drivers to your `pom.xml` and configure connection properties in your
`application.yml`. For example, for PostgreSQL this might look as follows:

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

Configure JPA properties:

```properties theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
spring.datasource.driver-class-name=org.postgresql.Driver
```

### 1. Specify the load mode

You can specify one of three modes when loading data into Atoti Limits:

1. `CSV`
2. `PERSISTENT`
3. `CSV_TO_PERSISTENT`

#### CSV load mode

This is the default load mode for Atoti Limits. In this mode, data is loaded from CSV files,
similar to previous versions of Atoti Limits. You don’t need to configure the external datasource
in this mode.
You don’t need to explicitly enable this mode, however it can be done so by setting the property
`limits.data.mode=csv`.

#### Persistent load mode

This is the load mode required to work with persistent data in Atoti Limits. In this mode,
data is loaded from an external database using JPA (Java Persistence API),
specifically [Spring’s Data JPA](https://spring.io/projects/spring-data-jpa).
To use this load mode:

1. Enable it by setting the property `limits.data.mode=persistent`
2. [Configure the external datasource](#2-configuring-the-external-datasource)

### 2. Configure the external datasource

<Warning>
  This section assumes that the database has been initialized. Please note that we use Liquibase for
  database migrations. If you want to use our migration scripts, initialize your database accordingly.
  For details, see [Schema Migrations](./limits-database-migration).
</Warning>

#### Generate the DDLs

To generate the DDL ([Data Definition Language](https://en.wikipedia.org/wiki/Data_definition_language))
for any of the datasources, use Spring properties. For example, to generate the
DDLs for the `limits-application` datasource, you can set the following properties:

```yaml theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
limits:
    application:
        datasource:
          url: "your-url"
          username: your-username
          password: your-password
          properties:
            jakarta:
              persistence:
                schema-generation:
                  create-source: metadata
                  drop-source: metadata
                  scripts:
                    action: drop-and-create
                    create-target: create-limits.sql
                    drop-target: drop-limits.sql
```

Once you start Atoti Limits this will generate two files, `create-limits.sql` and `drop-limits.sql`,
in the root directory of the server project. If you are not using Liquibase, the DDL can then be used
to create your database.

#### Configure the connection to the external datasource

The connection to an external datasource is configured using properties. The default properties
are as follows:

```yaml theme={"languages":{"custom":["/engine/python-sdk/0.9/languages/pycon.tmLanguage.json"]}}
limits:
  application:
    datasource:
      url: "jdbc:h2:mem:limits-application;DB_CLOSE_DELAY=-1;"
      username: app
      password:
      properties:
        hibernate:
          dialect: org.hibernate.dialect.H2Dialect
          format_sql: true
          hbm2ddl:
            auto: update
          globally_quoted_identifiers: true
      hikari:
        connectionTimeout: 30000
        idleTimeout: 60000
        minimumIdle: 1
        maximumPoolSize: 10
        poolName: "limits-application"
```

Let’s discuss some of these in more detail:

* The properties are prefixed by `limits.application.datasource`. This follows the Spring Boot
  convention for configuring datasources and is what we expect when the datasources are configured
  in `LimitsJpaConfig.java`.
* The `url` property specifies the JDBC URL for the database. In this case, we are using an
  in-memory H2 database named `limits-application`.

<Warning>
  This is not recommended in production. [H2](https://www.h2database.com/html/main.) is only intended for development purposes and is not a production grade database. We expect users to use another database. See the [MSSQL example](#mssql-example) for an example of how to configure a connection to a Microsoft SQL Server database.
</Warning>

* The `hibernate.hbm2ddl.auto` property specifies the behavior of the database schema. In this
  case, we are using `update`, which means that the schema will be updated based on the entities in
  the application.

<Warning>
  This is not recommended in production. We expect users to manage their own database schema.
</Warning>

* The `globally_quoted_identifiers` property is set to `true`. This is to escape reserved keywords
  in the database when defining columns. For example, the LimitStructure field `group` will be
  mapped to the column name `group`, which is
  a [reserved keyword in H2](https://www.h2database.com/html/advanced.html#keywords), so we
  delegate to Hibernate to escape.

For more information on the available properties, please see
the [Spring documentation](https://docs.spring.io/spring-boot/docs/current/reference/html/application-properties.html#appendix.application-properties.data).

#### CSV to Persistent load mode

This mode is used to migrate limits from the CSV sources to the external database. For more information, see [Limits Data Migration](./limits-data-migration).

For information on extending persistence in Atoti Limits, see [Adding Custom Persistence](../dev-extensions/custom-persistence).