Slick extensions for PostgreSQL, to support a series of pg data types and related operators/functions.
text
Searchpostgis
Geometrybasic
) 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
+).
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.
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.
scala Type | pg Type | dev 3rd-party library dependency |
---|---|---|
List[T] | ARRAY | no 3rd party dependencies |
java.sql Date Time Timestamp slickpg Interval Calendar |
date time timestamp interval timestamptz |
no 3rd party dependencies |
java.time LocalDateLocalTime LocalDateTime Duration ZonedDateTime OffsetDateTime |
date time timestamp interval timestamptz timestamptz |
(built-in) no 3rd party dependencies |
joda LocalDateLocalTime LocalDateTime Period DateTime |
date time timestamp interval timestamptz |
joda-time v2.12.7 |
scala Enumeration |
enum | no 3rd party dependencies |
slickpg Range[T] |
range | no 3rd party dependencies |
slickpg LTree |
ltree | no 3rd party dependencies |
Map[String,String] | hstore | no 3rd party dependencies |
slickpg InetString |
inet | no 3rd party dependencies |
slickpg MacAddrString |
macaddr | no 3rd party dependencies |
slickpg JsonString |
json | no 3rd party dependencies |
json4s JValue |
json | json4s v4.0.7 |
play-json JsValue |
json | play-json v2.10 or v3.0 |
spray-json JsValue |
json | spray-json v1.3.5 |
argonaut json Json |
json | argonaut v6.3.9 |
circe json Json |
json | circe v0.14.6 |
uPickle json Json |
json | uPickle v3.1.4 |
jawn json Json |
json | jawn v1.5.1 |
(TsQuery+TsVector) | text search |
no 3rd party dependencies |
jts Geometry |
postgis geometry |
jts v1.14.0 |
locationtech's jts Geometry |
postgis geometry |
locationtech'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
basic
Composite type support, usage casesTo 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:
slick-pg
v0.7.0, to easy usage, but I restored to publish them as independent jars from slick-pg
v0.10.0, because of the issue pointed out by @timcharper in #183.slick-pg_date2
was merged into major jar from slick-pg
v0.15.0, so you needn't add it as a depencensy any more.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")
Licensing conditions (BSD-style) can be found in LICENSE.txt.