# Lenses Metadata Database

Lenses state can be stored:

* on the local filesystem - (quick start and default option; deprecated, it will be removed in the next major version)
* in a PostgreSQL database - (**recommended**) and takes preference when configured
* in a Microsoft SQL Server database

{% hint style="success" %}
Start with Postgres if possible to avoid migrations from H2 when moving to production. H2 is not recommended in production environments.

If any Postgres configuration is defined either in lenses.conf or security.conf, the storage mode will switch to Postgres.

There is no migration support from H2-to-MSSQL or PostgreSQL-to-MSSQL or MSSQL-to-PostgreSQL

Databases settings go in **security.conf.**
{% endhint %}

## Local storage <a href="#local-storage" id="local-storage"></a>

By default, Lenses will store its internal state in the `storage` folder. We advise explicitly setting this location, ensuring the Lenses process has permission to read and write files in this directory and have an upgrade and backup policy.

```bash
lenses.storage.directory = "/path/to/persistent/data/directory"
```

## PostgreSQL <a href="#postgresql" id="postgresql"></a>

Lenses can persist their internal state to a remote PostgreSQL database server.

Current minimum requirements:

* Postgres server running version 9.6 or higher

The recommended configuration is to create a dedicated login role and database for the agent, setting the agent role as the database owner. This will mean the agent will only be able to manage that database and require no superuser privileges.

Example psql command for initial setup:

```bash

# login as superuser and add Lenses role and database
psql -U postgres -d postgres <<EOF
CREATE ROLE lenses WITH LOGIN PASSWORD 'changeme';
CREATE DATABASE lenses OWNER lenses;
EOF
```

You can then configure Lenses as so:

```bash
lenses.storage.postgres.host="my-postgres-server"
lenses.storage.postgres.port=5431 # optional, defaults to 5432
lenses.storage.postgres.username="lenses"
lenses.storage.postgres.database="lenses"
lenses.storage.postgres.password="changeme"
```

Additional configuration for the PostgreSQL database connection can be passed under the `lenses.storage.postgres.properties` configuration prefix. The supported parameters can be found in the [PostgreSQL documentation](https://jdbc.postgresql.org/documentation/head/connect.html). For example:

```bash
# require SSL encryption with full host verification
lenses.storage.postgres.properties.ssl=true
lenses.storage.postgres.properties.sslmode="verify-full"
lenses.storage.postgres.properties.sslcert="/path/to/certs/lenses.crt.pem"
lenses.storage.postgres.properties.sslkey="/path/to/certs/lenses.key.pk8"
lenses.storage.postgres.properties.sslpassword="mypassword"
lenses.storage.postgres.properties.sslrootcert="/path/to/certs/CA.crt.pem"
```

## Migration of local storage to PostgreSQL <a href="#migration-of-local-storage-to-postgresql" id="migration-of-local-storage-to-postgresql"></a>

Enabling PostgreSQL storage for an existing Lenses installation means the data will be automatically migrated to the PostgreSQL schema on the first run.

After this process has succeeded, a `lensesdb.postgresql.migration` file will be created in the local storage directory to flag that the migration has already been run. You can then delete the local storage directory and remove the `lenses.storage.directory` configuration.

If, for whatever reason, you want to re-run the migration to PostgreSQL, deleting the `lensesdb.postgresql.migration` file will cause Lenses to re-attempt migration on the next restart. The migration process will fail if it encounters any data that can’t be migrated into PostgreSQL, so re-running the migration should only be done on an empty PostgreSQL schema to avoid duplicate record failures.

## Microsoft SQL Server <a href="#postgresql" id="postgresql"></a>

Lenses can persist their internal state to a remote Microsoft SQL Server database server.

Current minimum requirements: MSSQL 2019.

The recommended configuration is to create a dedicated login role and database for the agent, setting the agent role as the database owner. This will mean the agent will only be able to manage that database and require no superuser privileges.

You can then configure Lenses as so:<br>

```bash
lenses.storage.mssql.database=lenses
lenses.storage.mssql.host=my-mssql-server
lenses.storage.mssql.port=1433
lenses.storage.mssql.password=changeme
lenses.storage.mssql.username=lenses

```

Additional configuration for the MSSQL database connection can be passed under the `lenses.storage.mssql.properties` configuration prefix. The full list and information can be found [here](https://learn.microsoft.com/en-us/sql/connect/jdbc/setting-the-connection-properties?view=sql-server-ver16).

## Connection pooling <a href="#connection-pooling" id="connection-pooling"></a>

Lenses use the HikariCP library for high-performance database connection pooling.

The default settings should perform well but can be overridden via the `lenses.storage.hikaricp` configuration prefix. The supported parameters can be found in the HikariCP documentation.

{% hint style="info" %}
Camelcase configuration keys are not supported in agent configuration and should be translated to "dot notation"
{% endhint %}

For example:

```bash
# set maximumPoolSize to 25
lenses.storage.hikaricp.maximum.pool.size=25
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.lenses.io/latest/devx/5.5/deployment/configuration/lenses-metadata-database.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.