Database module

The DB modules provide database connectivity and operations for the Pillars application. There are two database modules:

  • db: the main database module using Skunk for interacting with PostgreSQL databases

  • db-doobie: a Doobie-based database module for interacting with databases using JDBC drivers

Use only one of these two modules in your application. Using both modules in the same application will result in a conflict.

Database Configuration

The configuration is read from the application’s configuration file under the db section.

Skunk

The database configuration is defined in the pillars.db.DatabaseConfig case class. It includes the following fields:

  • host: The host of the database.

  • port: The port of the database.

  • database: The name of the database.

  • username: The username for the database.

  • password: The password for the database.

  • system-schema: The schema for pillars configuration in the database.

  • app-schema: The schema for the application in the database.

  • ssl: The SSL mode for the database. Accepted values are none, trusted and system. See Skunk documentation for more information.

  • pool-size: The size of the connection pool.

  • debug: A flag indicating whether to enable debug mode.

  • probe: The configuration for the database probe.

Doobie

The database configuration is defined in the pillars.db_doobie.DatabaseConfig case class. It includes the following fields:

  • driver-class-name: The JDBC driver class name.

  • url: The JDBC URL of the database.

  • username: The username for the database.

  • password: The password for the database.

  • system-schema: The schema for pillars configuration in the database.

  • app-schema: The schema for the application in the database.

  • pool-size: The size of the connection pool.

  • debug: A flag indicating whether to enable debug mode.

  • probe: The configuration for the database probe.

  • statement-cache: Configuration of the statement cache. It contains the following fields:

    • enabled: A flag indicating whether to enable the statement cache.

    • size: The size of the statement cache.

    • sql-limit: The maximum length of the SQL string to be cached.

Using the DB Module

To use the DB module, you need to import it and then access it.

import pillars.db.* // using skunk
import pillars.db // using doobie

For Skunk:

import pillars.db.*
import skunk.*

def foo[F[_]](using Pillars[F]) =
    sessions.use: session =>
        session.unique(sql"SELECT 1".query[Int])

For Doobie:

import pillars.db_doobie.*
import doobie.*

def foo[F[_]](using Pillars[F]) =
    sql"SELECT 1".query[Int].unique.transact(db.transactor)

Probe

The DB module provides a probe for health checks.

val isHealthy: F[Boolean] = dbModule.probes.head.check

This will return a boolean indicating whether the database is healthy or not.