slick-pg: PostgreSQL types and operators for the Slick query builder

What the extension actually adds

slick-pg provides Slick extensions for PostgreSQL to support a series of pg data types and related operators and functions. Slick itself is a query builder for Scala, and its standard PostgreSQL profile covers the common column types. What this library adds is everything else.

The supported types list covers ARRAY, Date and Time, Enum, Range, Hstore, LTree, JSON, Inet and MacAddr, text search, and PostGIS Geometry.

The supported features list is separate and matters just as much. It covers inherits, which is PostgreSQL's table inheritance, composite types at a basic level, aggregate functions, window functions, Large Object, and Pg_trgm for trigram-based similarity matching.

That feature list is the more interesting half, because those are the things Slick has no concept of. Slick's query model is a typed expression language that compiles to SQL, and it has no way to express an aggregate, a window function, a large object or an inherited table without the library providing the vocabulary.

The scope is stated in the testing note. It is tested on PostgreSQL 11, 12, 13 and 14 with Slick 3.6.1, and Java 8 or newer is required, except for the play-json addon which needs Java 11 or newer.

The repository is a Scala sbt project, with build.sbt at the root, src/, core/ and addons/ subprojects and an examples/ directory. The core/ and addons/ split is the packaging: the addons are optional pieces you depend on only if you use the corresponding feature, such as the play-json addon and the PostGIS support.

The profile pattern, and how the support traits compose

The integration model is a profile, and the README's example shows it in full.

import com.github.tminglei.slickpg._


trait MyPostgresProfile extends ExPostgresProfile
                          with PgArraySupport
                          with PgDate2Support
                          with PgRangeSupport
                          with PgHStoreSupport
                          with PgPlayJsonSupport
                          with PgSearchSupport
                          with PgPostGISSupport
                          with PgNetSupport
                          with PgLTreeSupport {
  def pgjson = "jsonb" // jsonb support is in postgres 9.4.0 onward; for 9.3.x use "json"

Each support trait contributes one domain of the database: arrays, the java.time and slickpg date types, ranges, hstore, Play JSON, text search, PostGIS, network addresses and ltree. You mix in the ones you need and leave the rest out.

The type system is then configured separately, in the API object, with a parallel set of implicits. The example defines a string list type mapper for a text array, and a Play JSON array type mapper built on the pgjson value:

    implicit val strListTypeMapper = new SimpleArrayJdbcType[String]("text").to(_.toList)
    implicit val playJsonArrayTypeMapper =
      new AdvancedArrayJdbcType[JsValue](pgjson,
        (s) => utils.SimpleArrayUtils.fromString[JsValue](Json.parse(_))(s).orNull,
        (v) => utils.SimpleArrayUtils.mkString[JsValue](_.toString())(v)
      ).to(_.toList)

So the type mapping is where the actual work is. A column of type text[] becomes a List[String] in Scala, and a column of jsonb[] becomes a list of parsed JSON values, with the conversion in both directions written explicitly.

Two details in the example are worth noticing. The pgjson value is a method returning the column type name, and the comment explains why it is a method rather than a constant: it is set to jsonb for PostgreSQL 9.4 onward and json for 9.3, so it is a version-dependent choice. And the capabilities override adds insertOrUpdate to the profile's computed capabilities, with a comment explaining it enables native upsert support on PostgreSQL 9.5 and newer.

Using the extensions in queries

The query-level example is where the extensions pay off, because each operator has a Scala form that compiles to a specific PostgreSQL operator.

class TestTable(tag: Tag) extends Table[Test](tag, Some("xxx"), "Test") {
  def id = column[Long]("id", O.AutoInc, O.PrimaryKey)
  def during = column[Range[Timestamp]]("during")
  def location = column[Point]("location")
  def text = column[String]("text", O.DBType("varchar(4000)"))
  def props = column[Map[String,String]]("props_hstore")
  def tags = column[List[String]]("tags_arr")

The column definitions show the mapping in one place. A Range of Timestamp, a Point from PostGIS, a varchar(4000) for text, a Map of String to String for hstore, and a List of String for an array.

The query methods then use the operators. A range column is tested with the @& operator for overlap, and the comment gives the generated SQL: select * from test where during && ?. An array column uses the same operator for intersection, where tags && ?. An hstore column is read with the >> operator for a key lookup, where case(props -> ? as [T]) == ?. A PostGIS column uses dWithin for a distance filter, generating ST_DWithin(location, ?, ?).

The search method is the most interesting, because it composes three extension points into one query:

  def search(queryStr: String) = tests
    .filter( t => {tsVector(t.text) @@ tsQuery(queryStr.bind)})
    .map(r => (r.id, r.text, tsRank(tsVector(r.text), tsQuery(queryStr.bind))))
    .sortBy(_._3)

The comment above it gives the generated SQL: select id, text, ts_rank(to_tsvector(text), to_tsquery(?)) from test where to_tsvector(text) @@ to_tsquery(?) order by ts_rank(to_tsvector(text), to_tsquery(?)). So the tsVector and tsQuery helpers produce the to_tsvector and to_tsquery calls, and tsRank produces the ranking function, all while staying inside Slick's expression language rather than dropping to raw SQL.

The README notes the samples are for Slick Lifted Embedding SQL, and that slick-pg also supports Slick Plain SQL, with details in the source and tests.

Configurable type mappers and the built-in table

Since version 0.2.0, slick-pg has supported configurable type mappers, and the mechanism is described in the README with a reference to the extracted core project.

All the pg type operator and function related code, and some core type mapper logic, were extracted into a new sub project called slick-pg_core, with the operator, function and type mapper binding code retained in the main project. The consequence is stated plainly: if you need to bind a different Scala type or mapper to a pg type operator or function, you can do it as slick-pg currently does.

So the extraction was not cosmetic. It created a seam where the mapping logic can be reconfigured, which is the difference between a library that hard-codes one Scala type per PostgreSQL type and one where the mapping is a value you can construct differently.

The built-in table is the reference for what you get without configuring anything. A List of T maps to ARRAY with no third-party dependency. The java.sql date types, including Date, Time, Timestamp, the slickpg Interval and Calendar, map to date, time, timestamp, interval and timestamptz with no third-party dependency. The java.time types, LocalDate, LocalTime, LocalDateTime, Duration, ZonedDateTime and OffsetDateTime, map to the same set of pg types and are marked as built-in with no third-party dependency. A scala Enumeration maps to enum, again with no third-party dependency.

The joda types are the exception, mapping LocalDate, LocalTime, LocalDateTime, Period and DateTime to the same pg type set, and requiring joda-time v2.12.7. The slickpg Range maps to range.

The practical point for a user is that the java.time family is covered out of the box, so a modern Scala codebase does not need joda, and the absence of a third-party dependency for the common cases means the extension does not force a date library choice on you.

Limits: version range, Slick coupling and the size of the API

Several limits follow from the design and from the age of the project.

The first is the tested version range. The README says the library is tested on PostgreSQL 11, 12, 13 and 14 with Slick 3.6.1. PostgreSQL 14 is several major versions behind, and Slick 3.6.1 is a specific point release of the query builder. The library has been pushed to recently, but the stated test matrix has not obviously widened, and a type or operator that changed in a later PostgreSQL release is not covered by that claim.

The second is the Slick coupling. Every operator here is a Scala extension to Slick's expression language, which means the extension is tied to how Slick models columns, how it compiles to SQL and how its plain SQL mode differs from lifted embedding. The README explicitly separates the two modes, with the samples given for Lifted Embedding SQL and the plain SQL usage left to the source and tests. That is a fair arrangement, and it is also two things to learn rather than one.

The third is the size of the API you inherit. Each support trait adds operators and type mappers, and mixing in ten of them means ten sets of implicits in scope, with the attendant risk of ambiguous implicits when two of them offer a similar shape. The example avoids the problem by defining only what it needs, but the README does not discuss how to resolve ambiguity between traits, and with enough traits mixed in that is a realistic problem.

The fourth is that this is a bridge to PostgreSQL, not a database abstraction. Every operator here is a PostgreSQL operator, so the code is not portable to another database, and the library does nothing to hide that. The upsert capability override is a good example: it adds a PostgreSQL capability, and if you switch to another database the override itself becomes the thing that will not compile.

The fifth is the project scale. It is one maintainer's extension library, with a Gitter chat badge and no GitHub releases beyond the tags, and the most recent releases are v0.23.1 from June 2025 and v0.23.0 from March 2025.

This extension against plain Slick and against writing the SQL by hand

Two comparisons make the choice clearer.

The first is against plain Slick with the standard PostgreSQL profile. The difference in approach is that the standard profile covers the column types Slick's author considered portable, and everything PostgreSQL-specific is absent. If your schema uses only integers, strings, dates and the odd array-free column, plain Slick is simpler and the extension adds nothing. The moment you add a range column, an hstore column, a JSONB array or a PostGIS point, you are writing raw SQL fragments inside Slick queries, which is exactly what the extension removes.

The second comparison is against writing those SQL fragments yourself. The value of the extension is that the operator, the column type and the type mapper are consistent with each other. A hand-written fragment binds a parameter correctly but returns whatever the driver hands back, and the mapping from that to a Scala value is your code to write and to keep consistent across every query that touches the column. The extension gives you a List for text[], a Range for a range column and a Point for a geometry column, once.

The third comparison is worth mentioning against a different kind of library, a general-purpose SQL library for Scala with first-class support for many databases. Those give you one query language that works everywhere, at the price of not exposing the PostgreSQL features that have no equivalent elsewhere. The extension takes the opposite position, being entirely PostgreSQL-specific and entirely typed.

The choice, then, is between portability and depth. This library is for code that is committed to PostgreSQL and wants the database's full type system available in a typed query builder.

Editorial conclusion

Adopt slick-pg when you use Slick against PostgreSQL and have outgrown the standard profile, since the alternative is hand-written SQL for arrays, ranges, hstore and spatial queries. Do not adopt it if your queries fit inside plain Slick, because every support trait pulls in more of the API than you need. Verify first that the PostgreSQL version you run is one the library was tested against, that the Slick version you use matches the one the extension was validated with, and that the type mapping you need is in the built-in table rather than something you have to configure yourself.

Frequently asked questions

Official sources

1. Issues
2. License: BSD-2-Clause
3. README
4. Releases
5. tminglei/slick-pg on GitHub