Slick extensions for PostgreSQL, to support a series of pg data types and related operators/functions.
- ARRAY
- Date/Time
- Enum
- Range
- Hstore
- LTree
- JSON
- Inet/MacAddr
text
Searchpostgis
Geometry
- inherits
- composite type (
basic
) - aggregate functions
- window functions
- Large Object
** Tested on PostgreSQL
v9.6
with Slick
v3.2.0
.
** Java 8
is required.
Before using it, you need integrate it with PostgresDriver maybe like this:
import com.github.tminglei.slickpg._
trait MyPostgresDriver extends ExPostgresDriver
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[Capability] =
super.computeCapabilities + JdbcProfile.capabilities.insertOrUpdate
override val api = MyAPI
object MyAPI extends API with ArrayImplicits
with DateTimeImplicits
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 MyPostgresDriver extends MyPostgresDriver
then in your codes you can use it like this:
import MyPostgresDriver.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.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.9.7 / joda-convert v1.8.1 |
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 v3.5.0 |
play-json JsValue |
json | play-json v2.6.0 |
spray-json JsValue |
json | spray-json v1.3.3 |
argonaut json Json |
json | argonaut v6.2 |
circe json Json |
json | circe v0.7.0 |
jawn json Json |
json | jawn v0.10.4 |
(TsQuery+TsVector) | text search |
no 3rd party dependencies |
jts Geometry |
postgis geometry |
jts v1.13 |
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
- Array's oper/functions, usage cases
- JSON's oper/functions, usage cases for json4s, play-json, spray-json and argonaut json
- Date/Time's oper/functions, usage cases for java date, java 8 date and joda time
- Enum's oper/functions, usage cases
- Range's oper/functions, usage cases
- HStore's oper/functions, usage cases
- LTree's oper/functions, usage cases
- Inet/MacAddr's oper/functions, usage cases
- Search's oper/functions, usage cases
- Geometry's oper/functions, usage cases for postgis
basic
Composite type support, usage cases- Aggregate functions, usage cases
- Window functions, usage cases
To use slick-pg
in sbt project, add the following to your project file:
libraryDependencies += "com.github.tminglei" %% "slick-pg" % "0.15.0-RC"
If you need
joda-time
support, pls append dependency:
libraryDependencies += "com.github.tminglei" %% "slick-pg_joda-time" % "0.15.0-RC"
If you need
jts
geom support, pls append dependency:
libraryDependencies += "com.github.tminglei" %% "slick-pg_jts" % "0.15.0-RC"
If you need
json4s
support, pls append dependency:
libraryDependencies += "com.github.tminglei" %% "slick-pg_json4s" % "0.15.0-RC"
If you need
play-json
support, pls append dependency:
libraryDependencies += "com.github.tminglei" %% "slick-pg_play-json" % "0.15.0-RC"
If you need
spray-json
support, pls append dependency:
libraryDependencies += "com.github.tminglei" %% "slick-pg_spray-json" % "0.15.0-RC"
If you need
argonaut json
support, pls append dependency:
libraryDependencies += "com.github.tminglei" %% "slick-pg_argonaut" % "0.15.0-RC"
If you need
circe json
support, pls append dependency:
libraryDependencies += "com.github.tminglei" %% "slick-pg_circe-json" % "0.15.0-RC"
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.12</artifactId>
<version>0.15.0-RC</version>
</dependency>
<!-- other addons if necessary -->
...
Note: the plugins' code were ever merged to the main project and published in an all-in-one jar from 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
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:
- create a user 'test' and db 'test' on your local postgres server, and
- the user 'test' should be an super user and be the owner of db 'test'
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.