Database

This page describes configuring the database connection for the Lenses Agent. There are two options for the backing storage: Postgres or Microsoft SQL Server.

Postgres

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

lenses.conf

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"

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

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 or lenses.storage.postgres.database

The supported parameters can be found in the PostgreSQL documentation. For example:

lenses.conf

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

Database Role

terminal

# 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

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.

lenses.conf

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"

---

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.

SQL Server Commands

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

---

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.

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

lenses.conf

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

Connection pooling

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.

Camelcase configuration keys are not supported in agent configuration and should be translated to dot notation.

For example:

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