In order for the database to be used in the application, it needs to contain all required tables, indexes, and other definitions.

We'll represent our store as a simple table with the name of the item serving as a primary key and a non-negative count of each item:

CREATE TABLE article (
  name  VARCHAR PRIMARY KEY,
  count INTEGER NOT NULL CHECK (count >= 0)
);

We'll place this definition into db_migrations/V1__inventory_table.sql and use Flyway to check that our database is in the correct state during startup time.

The Flyway code for migrations is straightforward:

def initialize(transactor: HikariTransactor[IO]): IO[Unit] = {
  transactor.configure { dataSource =>
    IO {
      val flyWay = new Flyway()
      flyWay.setLocations("classpath:db_migrations")
      flyWay.setDataSource(dataSource)
      flyWay.migrate()
    }
  }
}

Given a transactor (which we'll describe a bit later, at the moment we'll talk about doobie), we use the datasource it provides to create an instance of Flyway, configure it to use proper migrations location, and perform the migration. Please note that the initialization logic is wrapped into the IO effect and thus delayed until the effect is evaluated.

The transactor is created using the utility provided by doobie from the configuration:

def transactor(c: DBConfig): IO[HikariTransactor[IO]] = {
  HikariTransactor
   .newHikariTransactor[IO](c.driver, c.url, c.user, c.password)
}

Again it is wrapped in IO so no effects will be evaluated until we run the result of this function.

Before going over to the definition of the database repository, let's have a quick look at the configuration abstraction we've used in the transactor method.