# Database

## Postgres

Once you have created a role for the agent to use you can then configure the Agent in the **`lenses.conf`** file:

{% code title="lenses.conf" %}

```properties
lenses.storage.postgres.host="my-postgres-server"
lenses.storage.postgres.port=5432
lenses.storage.postgres.username="lenses_agent"
lenses.storage.postgres.database="lenses_agent"
lenses.storage.postgres.password="changeme"
```

{% endcode %}

Additional configurations for the PostgreSQL database connection can be passed under the **lenses.storage.postgres.properties** configuration prefix. 

{% hint style="success" %}
One Postgres server can be used for all agents by using a separate database or schema each. 

For the **Agent** see [lenses.storage.postgres.schema](https://docs.lenses.io/latest/devx/6.0/deployment/configuration/configuration-reference#postgressql) or [lenses.storage.postgres.database](https://docs.lenses.io/latest/devx/6.0/deployment/configuration/configuration-reference#postgressql)
{% endhint %}

The supported parameters can be found in the [PostgreSQL documentation](https://jdbc.postgresql.org/documentation/head/connect.html). For example:

{% code title="lenses.conf" %}

```properties
# 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"
```

{% endcode %}

### Database Role

{% code title="terminal" %}

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

{% endcode %}

## Microsoft SQL Server

To configure Lenses to use a Microsoft SQL Server database, you need to add the following settings to your `lenses.conf` file. This example mirrors the structure of the PostgreSQL configuration you provided.

{% code title="lenses.conf" %}

```properties
lenses.storage.mssql.host="my-mssql-server"
lenses.storage.mssql.port=1433
lenses.storage.mssql.database="lenses_db"
lenses.storage.mssql.schema="lenses_schema"
lenses.storage.mssql.username="lenses_user"
lenses.storage.mssql.password="changeme"
```

{% endcode %}

***

#### Database and Login Creation

Before starting Lenses, you must create the database, schema, and login credentials on your Microsoft SQL Server instance. You can use a tool like SQL Server Management Studio (SSMS) or the `sqlcmd` command-line utility to execute these commands.

{% code title="SQL Server Commands" overflow="wrap" %}

```sql
-- Create the database for Lenses
CREATE DATABASE lenses_db;
GO

-- Switch to the newly created database
USE lenses_db;
GO

-- Create a login (user) for Lenses to use
CREATE LOGIN lenses_user WITH PASSWORD = 'changeme';
GO

-- Create a database user linked to the login
CREATE USER lenses_user FOR LOGIN lenses_user;
GO

-- Create a schema for Lenses
CREATE SCHEMA lenses_schema;
GO

-- Grant the necessary permissions to the user on the schema
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, ALTER ON SCHEMA::lenses_schema TO lenses_user;
GO
```

{% endcode %}

***

#### Advanced Configuration with Properties

You can pass additional JDBC driver properties using the `lenses.storage.mssql.properties` prefix. This is useful for enabling features like connection encryption. The supported parameters can be found in the [Microsoft JDBC Driver documentation](https://learn.microsoft.com/en-us/sql/connect/jdbc/setting-the-connection-properties).

For example, to enforce SSL encryption and validate the server certificate:

{% code title="lenses.conf" %}

```properties
# Require SSL encryption
lenses.storage.mssql.properties.encrypt="true"
lenses.storage.mssql.properties.trustServerCertificate="false"
lenses.storage.mssql.properties.hostNameInCertificate="my-mssql-server.example.com"
```

{% endcode %}

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

The Agent uses 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
```