Postgres Specific Features in Slick Using Slick-PG

1. Introduction

Relational databases are the most used type of database in modern applications. They provide a structured way to store and retrieve data and ensure that we’ll retrieve the correct data. In the Scala ecosystem, Slick is a popular Object Relational Mapper, or ORM, for interaction with relational databases. It blends functional programming principles with robust SQL capabilities.

PostgreSQL, or Postgres as it is most commonly referred to, is a powerful and robust, open-source choice for relational databases and it is the most commonly used one. In this article, we’ll explore some PostgreSQL-specific features of Slick, including support for JSON, arrays, and advanced date/time data types, which integrate seamlessly into our applications using the slick-pg extension library.

2. The slick-pg Library

The library provides support for various Postgres-specific data types and features, such as array types, date/time, JSON, LTree, Hstore, and PostGIS. It also supports table inheritance and window functions. The library’s documentation provides a list of all the features.

We need to add the necessary dependencies in our build.sbt file to use the library:

libraryDependencies ++ = Seq(
  "com.typesafe.slick" %% "slick" % "3.5.1",
  "com.typesafe.slick" %% "slick-hikaricp" % "3.5.1",
  "org.postgresql" % "postgresql" % "42.7.4",
  "com.github.tminglei" %% "slick-pg" % "0.22.2"
)

The first dependency is the slick library itself, as the slick-pg library is an extension on top of that. Then, we need a connection pool that the slick-hikaricp library provides. As we want to connect to a Postgres instance, we also need to provide the necessary drivers and finally, we can add the slick-pg library.

2.1. Connecting to the Database

Now, we are ready to build a simple application to connect to our database. First, let’s create our connection profile:

trait BaeldungPostgresProfile extends ExPostgresProfile {

  override protected def computeCapabilities: Set[slick.basic.Capability] =
    super.computeCapabilities + slick.jdbc.JdbcCapabilities.insertOrUpdate

  override val api = BaeldungApi

  object BaeldungApi extends ExtPostgresAPI {
  }
}

object BaeldungPostgresProfile extends BaeldungPostgresProfile

The profile defines the capabilities that we want our ORM to use. At this point, we haven’t defined anything specific, although ExPostgresProfile contains some extra capabilities, like window function support. Next, we need to define our connection properties. We can do this in many ways. In this example, we’ll load the properties from our application.conf file:

postgres {
  connectionPool = "HikariCP"
  dataSourceClass = "org.postgresql.ds.PGSimpleDataSource"
  properties = {
    serverName = "localhost"
    portNumber = "5432"
    databaseName = "baeldung"
    user = "baeldung"
    password = "password"
  }
}

We have now defined that we want a Hikari connection pool that’ll connect to a Postgres database with the specified credentials. So now, we’ll define in our app that we want to load this specific profile:

object PostgresConnection {
  val db: slick_pg.BaeldungPostgresProfile.backend.Database = Database.forConfig("postgres")
}

2.2. Defining the Entities

The only thing left to have a complete application is to define our entities. For this example, we’ll use a single table and thus, a single entity:

case class BaeldungEntity(id: Long)

Our entity has only an id field for now. Next, we’ll define the correlation with the database table:

class BaeldungEntityTable(tag: Tag) extends Table[BaeldungEntity](tag, None, "baeldung_entity") {
  val id = column[Long]("id", O.PrimaryKey, O.AutoInc, O.SqlType("BIGSERIAL"))
  override def * = (id).mapTo[BaeldungEntity]
}

val baeldungEntityTable = TableQuery[BaeldungEntityTable]

With the baeldungEntityTable variable, we can perform queries in our table.

3. Date-Time Types

Since we have now defined our basic backbone application, we’re ready to add some Postgres-specific features. Let’s start by adding a timestamptz, or timestamp with timezone column in our table. According to the documentation of the library, when we use date/time data that also contain timezone, we can use both ZonedDateTime and OffsetDateTime variables, although the latter is preferred, as the first one cannot guarantee that we’ll retrieve the same timezone as the one we stored.

3.1. Updating the Entities

Our entity definition changes as follows:

case class BaeldungEntity(id: Long, createdAt: OffsetDateTime)

And our table definition has the following changes:

val createdAt = column[OffsetDateTime]("created_at", O.SqlType("TIMESTAMPTZ"))
override def * = (id, createdAt).mapTo[BaeldungEntity]

To let our ORM know how to handle this new field, we also need to modify our profile, extending the slick-pg date/time specific traits:

trait BaeldungPostgresProfile extends ExPostgresProfile with PgDate2Support

object BaeldungApi extends ExtPostgresAPI with Date2DateTimeImplicitsDuration

We’ve now defined that the profile will also extend the PgDate2Support trait and our object also uses the Date2DateTimeImplicitsDuration trait that both contain all the necessary transformations to support the new field. Although this should be enough for our app, in Scala 3 version the date/time support doesn’t work as expected and we manually need to define the transformations. We can read more about this in an open issue of the library.

3.2. Adding a Custom Formatter

Slick-pg library manipulates the date/time data types as strings when it reads or writes from and to the database. We’ll extend this behavior by adding our own formatter that can handle timezones:

trait TimestamptzPostgresProfile extends PostgresProfile {

  override val columnTypes: PostgresJdbcTypes = new PostgresJdbcTypes {

    override val offsetDateTimeType: OffsetDateTimeJdbcType = new OffsetDateTimeJdbcType {

      override def sqlType: Int = {
        java.sql.Types.TIMESTAMP_WITH_TIMEZONE
      }
    }
  }
}

By extending the PostgresProfile trait, we define a new mapping. Here, we’ve defined that we want to map the OffsetDateTimeJdbcType to timestamptz type.

Next, we want to define how we’ll handle the database value to cast it to OffsetDateTime and vice versa. Slick handles date/time values as strings. So we need to define a formatter:

private val formatter =
  new DateTimeFormatterBuilder()
    .append(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"))
    .optionalStart()
    .appendFraction(ChronoField.NANO_OF_SECOND, 0, 6, true)
    .optionalEnd()
    .optionalStart()
    .appendOffset("+HH:mm:ss", "+00")
    .optionalEnd()
    .toFormatter()

Our formatter can handle different strings of dates, with or without nanoseconds, and different types of timezone formats.

Finally, we need to define how to set, get, and update values:

override def setValue(
  v: OffsetDateTime,
  p: PreparedStatement,
  idx: Int
): Unit = {
  p.setObject(idx, v)
}

override def getValue(r: ResultSet, idx: Int): OffsetDateTime = {
  r.getString(idx) match {
    case null         => null
    case date: String => OffsetDateTime.from(formatter.parse(date))
    }
}

override def updateValue(
  v: OffsetDateTime,
  r: ResultSet,
  idx: Int
): Unit = {
  r.updateObject(idx, v)
}

We’ll now extend our profile with our profile trait, so we can use timestamptz types:

trait BaeldungPostgresProfile extends ExPostgresProfile with PgDate2Support with TimestamptzPostgresProfile

4. Array Types

One of Postgres’s features that makes it so popular is that it can also be used for unstructured data storage. It supports lists, maps, and various other data types. Let’s assume that our entity represents a shopping cart and we want to store the prices of the products we have inside it in a list. First thing we need to do, is alter our entity definition:

case class BaeldungEntity(id: Long, createdAt: OffsetDateTime, prices: List[Double])

And we add the following changes to our table definition:

val prices = column[List[Double]]("prices", O.SqlType("DOUBLE PRECISION[]"))
override def * = (id, createdAt, prices).mapTo[BaeldungEntity]

We’ve now defined that we expect an array of double numbers and it’ll be mapped to a list of double numbers. The only thing left is to also add the support in our profile:

trait BaeldungPostgresProfile extends ExPostgresProfile with PgDate2Support with TimestamptzPostgresProfile with PgArraySupport

object BaeldungApi extends ExtPostgresAPI
  with Date2DateTimeImplicitsDuration with ArrayImplicits {
  implicit val doubleListTypeMapper: JdbcType[List[Double]] = new SimpleArrayJdbcType[Double]("DOUBLE PRECISION").to(_.toList)
}

We’ve added the PgArraySupport and we’ve defined our implicit mapper that’ll map the array data type to a list.

5. JSON Types

As we’ve mentioned above, Postgres supports a lot of unstructured data types. JSON is one of them and it’s widely used for these cases. In order to be able to use them, we need to choose a library that can manipulate JSON values. We can choose from many libraries, like play-json, circe and argonaut. In this example, we’re going to use the json4s library. First, we need to add all the necessary dependencies in our build.sbt file:

libraryDependencies += "org.json4s" %% "json4s-native" % "4.0.7",
libraryDependencies += "com.github.tminglei" %% "slick-pg_json4s" % "0.22.2"

Each JSON library has its own supporting module in slick-pg library that we also need to include. Now, we’re ready to define our column in our entity:

case class BaeldungEntity(
  id: Long,
  createdAt: OffsetDateTime,
  prices: List[Double],
  metadata: JValue
)

And our changes in the table:

val metadata = column[JValue]("metadata", O.SqlType("JSONB"))

override def * = (id, createdAt, prices, metadata).mapTo[BaeldungEntity]

We also need to define the support in our profile:

trait BaeldungPostgresProfile
  extends ExPostgresProfile
  with PgDate2Support
  with TimestamptzPostgresProfile
  with PgArraySupport
  with PgJson4sSupport {

  override val pgjson = "jsonb"
  type DOCType = org.json4s.native.Document
  override val jsonMethods =
    org.json4s.native.JsonMethods.asInstanceOf[JsonMethods[DOCType]]

  object BaeldungApi
    extends ExtPostgresAPI
    with Date2DateTimeImplicitsDuration
    with ArrayImplicits
    with JsonImplicits

We’ve added the PgJson4sSupport and JsonImplicits traits that’ll take care of all the mappings for us. We also have to define which Postgres data type is defined as JSON, which in our case is jsonb. Next, we define in what type the mapper should handle the database JSON documents, which is defined inside the json4s library as Document. The jsonMethods is a trait of json4s library that contains the methods to parse and render the actual document value. In our case, we’ve explicitly defined that we want these methods to be applied to a Document type variable.

6. Conclusion

In this article, we’ve explored different Postgres-specific data types, like arrays, timestamp with timezone and JSON, and how to use them in an application using the slick-pg extension for the slick library.