• What is fa-db
• Usage
• Concepts
• Slick module
• Testing
• How to Release

This library is a less traditional way to facilitate data between an application and an SQL Database.

Traditionally application directly applies SQL queries or use some ORM framework. While the first approach mixes two rather different domain languages within one source, the second too often fails in case of more complicated queries and table relations.

This library offers a different approach.

The idea is that the application transfers data to and from using database stored procedures/functions (from here on referenced in this library as DB functions). This establishes a stable contract between the DB and the application. To emphasize – the data are both read and written to/from DB using DB functions.

The purpose of the library then is to facilitate an easy and natural – meaning Scala style – call of the DB functions within the application.

Benefits:

• Stable contract between the application and the DB
• Early locking of the data model
• Separation of the domains while keeping each part doing what they are good for:
  • the application parsing and utilizing the data
  • the DB storing and retrieving the data effectively
• Better data security and consistency protection

Currently, the library is developed with Postgres as the target DB. But the approach is applicable to any DB supporting stored procedure/functions – Oracle, MS-SQL, ...

Import one of the two available module at the moment. Slick module works with Scala Futures. Doobie module works with any effect type (typically IO or ZIO) provided cats effect's Async instance is available.

libraryDependencies *= "za.co.absa.db.fa-db" %% "slick"  % "X.Y.Z"
libraryDependencies *= "za.co.absa.db.fa-db" %% "doobie"  % "X.Y.Z"

Modules:

• Core
• Slick
• Doobie

<dependencies>
    <dependency>
        <groupId>za.co.absa.db.fa-db</groupId>
        <artifactId>slick_2.12</artifactId>
        <version>${latest_version}</version>
    </dependency>
    <dependency>
        <groupId>za.co.absa.db.fa-db</groupId>
        <artifactId>doobie_2.12</artifactId>
        <version>${latest_version}</version>
    </dependency>
</dependencies>

Modules:

• Core
• Slick
• Doobie

<dependencies>
    <dependency>
        <groupId>za.co.absa.db.fa-db</groupId>
        <artifactId>slick_2.13</artifactId>
        <version>${latest_version}</version>
    </dependency>
    <dependency>
        <groupId>za.co.absa.db.fa-db</groupId>
        <artifactId>doobie_2.13</artifactId>
        <version>${latest_version}</version>
    </dependency>
</dependencies>

Text about status codes returned from the database function can be found here.

As the name suggests it runs on Slick library and also brings in the Slickpg library for extended Postgres type support.

It brings:

• class SlickPgEngine - implementation of Core's DBEngine executing the queries via Slick
• class SlickSingleResultFunction - abstract class for DB functions returning single result
• class SlickMultipleResultFunction - abstract class for DB functions returning sequence of results
• class SlickOptionalResultFunction - abstract class for DB functions returning optional result
• class SlickSingleResultFunctionWithStatus - abstract class for DB functions with status handling; it requires an implementation of StatusHandling to be mixed-in (StandardStatusHandling available out-of-the-box)
• class SlickMultipleResultFunctionWithStatus - as SlickSingleResultFunctionWithStatus but for multiple record retrieval
• class SlickMultipleResultFunctionWithAggStatus - as SlickMultipleResultFunctionWithStatus but it aggregates the statuses into a single record. It requires an implementation of StatusAggregator to be mixed-in (ByFirstErrorStatusAggregator, ByFirstRowStatusAggregator, and ByMajorityErrorsStatusAggregator available out of the box)
• class SlickOptionalResultFunctionWithStatus - as SlickSingleResultFunctionWithStatus but the returning record is optional
• trait FaDbPostgresProfile - to bring support for Postgres and its extended data types in one class (except JSON, as there are multiple implementations for this data type in Slick-Pg)
• object FaDbPostgresProfile - instance of the above trait for direct use

When getting result from PositionedResult for these types HStore -> Option[Map[String, String]] and macaddr -> MacAddrString type inference doesn't work well. So instead of:

val pr: PositionedResult = ???
val hStore: Option[Map[String, String]] = pr.<<
val macAddr: Option[MacAddrString] = pr.<<

explicit extraction needs to be used:

val pr: PositionedResult = ???
val hStore: Option[Map[String, String]] = pr.nextHStoreOption
val macAddr: Option[MacAddrString] = pr.nextMacAddrOption

As the name suggests it runs on Doobie library. The main benefit of the module is that it allows to use any effect type (typically IO or ZIO) therefore is more suitable for functional programming. It also brings in the Doobie-Postgres library for extended Postgres type support.

It brings:

• class DoobieEngine - implementation of Core's DBEngine executing the queries via Doobie. The class is type parameterized with the effect type.
• class DoobieSingleResultFunction - abstract class for DB functions returning single result
• class DoobieMultipleResultFunction - abstract class for DB functions returning sequence of results
• class DoobieOptionalResultFunction - abstract class for DB functions returning optional result
• class DoobieSingleResultFunctionWithStatus - abstract class for DB functions with status handling; it requires an implementation of StatusHandling to be mixed-in (StandardStatusHandling available out-of-the-box)
• class DoobieMultipleResultFunctionWithStatus - as DoobieSingleResultFunctionWithStatus but for multiple record retrieval
• class DoobieMultipleResultFunctionWithAggStatus - as DoobieMultipleResultFunctionWithStatus but it aggregates the statuses into a single record. It requires an implementation of StatusAggregator to be mixed-in (ByFirstErrorStatusAggregator, ByFirstRowStatusAggregator, and ByMajorityErrorsStatusAggregator available out of the box)
• class DoobieOptionalResultFunctionWithStatus - as DoobieSingleResultFunctionWithStatus but the returning record is optional

Since Doobie also interoperates with ZIO, there is an example of how a database connection can be properly established within a ZIO application. Please see this file for more details.

sbt jacoco

Note: this command will start all tests in the project.

Code coverage will be generated on path:

{project-root}/fa-db/{module}/target/scala-{scala_version}/jacoco/report/html

There are now integration tests as part of the project (at the time of writing they are in the Slick and Doobie modules).

For the tests to work properly a running Postgres instance is needed as well as all DB objects must be placed on the DB. We automated this process, see demo_database/README.md for more details.

How to execute the unit tests only:

sbt test

How to execute the integration tests only:

sbt testIT

Please see this file for more details.