# 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/deployment/configuration/configuration-reference#postgressql) or [lenses.storage.postgres.database](https://docs.lenses.io/latest/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
```


---

# 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/deployment/configuration/agent/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.