Home

Awesome

Slick-pg

Join the chat at https://gitter.im/tminglei/slick-pg

Slick extensions for PostgreSQL, to support a series of pg data types and related operators/functions.

Currently supported pg types:

Currently supported pg features:

** Tested on PostgreSQL 11/12/13/14 with Slick 3.5.1.
** Java 8+ is required (except for play-json addon, which requires java 11+).

Usage

Before using it, you need integrate it with PostgresDriver maybe like this:

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"

  // Add back `capabilities.insertOrUpdate` to enable native `upsert` support; for postgres 9.5+
  override protected def computeCapabilities: Set[slick.basic.Capability] =
    super.computeCapabilities + slick.jdbc.JdbcCapabilities.insertOrUpdate

  override val api = MyAPI

  object MyAPI extends ExtPostgresAPI with ArrayImplicits
                                      with Date2DateTimeImplicitsDuration
                                      with JsonImplicits
                                      with NetImplicits
                                      with LTreeImplicits
                                      with RangeImplicits
                                      with HStoreImplicits
                                      with SearchImplicits
                                      with SearchAssistants {
    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)
  }
}

object MyPostgresProfile extends MyPostgresProfile

then in your codes you can use it like this:

import MyPostgresProfile.api._

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")

  def * = (id, during, location, text, props, tags) <> ((Test.apply _).tupled, Test.unapply)
}

object tests extends TableQuery(new TestTable(_)) {
  // will generate sql like:
  //   select * from test where id = ?
  def byId(ids: Long*) = tests
        .filter(_.id inSetBind ids)
        .map(t => t)
  // will generate sql like:
  //   select * from test where tags && ?
  def byTag(tags: String*) = tests
        .filter(_.tags @& tags.toList.bind)
        .map(t => t)
  // will generate sql like:
  //   select * from test where during && ?
  def byTsRange(tsRange: Range[Timestamp]) = tests
        .filter(_.during @& tsRange.bind)
        .map(t => t)
  // will generate sql like:
  //   select * from test where case(props -> ? as [T]) == ?
  def byProperty[T](key: String, value: T) = tests
        .filter(_.props.>>[T](key.bind) === value.bind)
        .map(t => t)
  // will generate sql like:
  //   select * from test where ST_DWithin(location, ?, ?)
  def byDistance(point: Point, distance: Int) = tests
        .filter(r => r.location.dWithin(point.bind, distance.bind))
        .map(t => t)
  // will generate sql like:
  //   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(?))
  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)
}

...

p.s. The code samples above are for Slick Lifted Embedding SQL. Aside from that, slick-pg also supports Slick Plain SQL--for details and usages please refer to source code and tests.

Configurable type/mappers

Since v0.2.0, slick-pg started to support configurable type/mappers.

Here's the related technical details:

All pg type oper/functions related codes and some core type mapper logics were extracted to a new sub project "slick-pg_core", and the oper/functions and type/mappers binding related codes were retained in the main project "slick-pg".

So, if you need bind different scala type/mappers to a pg type oper/functions, you can do it as "slick-pg" currently did.

Built in supported type/mappers

scala Typepg Typedev 3rd-party library dependency
List[T]ARRAYno 3rd party dependencies
java.sql Date <br> Time<br> Timestamp<br> slickpg Interval<br> Calendardate<br> time<br> timestamp<br> interval<br> timestamptzno 3rd party dependencies
java.time LocalDate<br> LocalTime<br> LocalDateTime<br> Duration<br> ZonedDateTime <br> OffsetDateTimedate<br> time<br> timestamp<br> interval<br> timestamptz <br> timestamptz(built-in) no 3rd party dependencies
joda LocalDate<br> LocalTime<br> LocalDateTime<br> Period<br> DateTimedate<br> time<br> timestamp<br> interval<br> timestamptzjoda-time v2.12.7
scala Enumerationenumno 3rd party dependencies
slickpg Range[T]rangeno 3rd party dependencies
slickpg LTreeltreeno 3rd party dependencies
Map[String,String]hstoreno 3rd party dependencies
slickpg InetStringinetno 3rd party dependencies
slickpg MacAddrStringmacaddrno 3rd party dependencies
slickpg JsonStringjsonno 3rd party dependencies
json4s JValuejsonjson4s v4.0.7
play-json JsValuejsonplay-json v2.10 or v3.0
spray-json JsValuejsonspray-json v1.3.5
argonaut json Jsonjsonargonaut v6.3.9
circe json Jsonjsoncirce v0.14.6
uPickle json JsonjsonuPickle v3.1.4
jawn json Jsonjsonjawn v1.5.1
(TsQuery+TsVector)text searchno 3rd party dependencies
jts Geometrypostgis geometryjts v1.14.0
locationtech's jts Geometrypostgis geometrylocationtech's jts v1.19.0

Warning: When your work with time data that contain Timezone, be wary of your postgres configuration. By default ZonedDateTime in Java 8 contains more information than timestamptz in Postgres. As a consequence, when you store a ZonedDateTime you are not guaranteed to get the same timezone as the original class instance. Prefer OffsetDateTime instead. cf Issue #248

Details

Install

To use slick-pg in sbt project, add the following to your project file:

libraryDependencies += "com.github.tminglei" %% "slick-pg" % "0.22.2"

If you need joda-time support, pls append dependency:

libraryDependencies += "com.github.tminglei" %% "slick-pg_joda-time" % "0.22.2"

If you need jts geom support, pls append dependency:

libraryDependencies += "com.github.tminglei" %% "slick-pg_jts" % "0.22.2"
or
libraryDependencies += "com.github.tminglei" %% "slick-pg_jts_lt" % "0.22.2"

If you need json4s support, pls append dependency:

libraryDependencies += "com.github.tminglei" %% "slick-pg_json4s" % "0.22.2"

If you need play-json support, pls append dependency:

libraryDependencies += "com.github.tminglei" %% "slick-pg_play-json" % "0.22.2"
or
libraryDependencies += "com.github.tminglei" %% "slick-pg_play-json3" % "0.22.2"

If you need spray-json support, pls append dependency:

libraryDependencies += "com.github.tminglei" %% "slick-pg_spray-json" % "0.22.2"

If you need argonaut json support, pls append dependency:

libraryDependencies += "com.github.tminglei" %% "slick-pg_argonaut" % "0.22.2"

If you need circe json support, pls append dependency:

libraryDependencies += "com.github.tminglei" %% "slick-pg_circe-json" % "0.22.2"

If you need uPickle json support, pls append dependency:

libraryDependencies += "com.github.tminglei" %% "slick-pg_upickle-json" % "0.22.2"

Or, in maven project, you can add slick-pg to your pom.xml like this:

<dependency>
    <groupId>com.github.tminglei</groupId>
    <artifactId>slick-pg_2.13</artifactId>
    <version>0.22.2</version>
</dependency>
<!-- other addons if necessary -->
...

Notes:

Build instructions

slick-pg uses SBT for building and requires Java 8, since it provides support for java.date in addon date2. Assume you have already installed SBT, then you can simply clone the git repository and build slick-pg in the following way:

./sbt update
./sbt compile

To run the test suite, you need:

Then you can run the tests like this:

./sbt test

ps: in the code of unit tests, the slick database is setup like this:

val db = Database.forURL(url = "jdbc:postgresql://localhost/test?user=postgres", driver = "org.postgresql.Driver")

License

Licensing conditions (BSD-style) can be found in LICENSE.txt.