I'm trying to write a simple Active Record style DAO in Slick and I'm finding it very hard going. I'd like to insert a row using the case class representation, set its "updated" column using database time and return the new row values as a case class.
i.e. the DAO should look like this:
class FooDao {
def db: JdbcBackend.Database
def insert(foo: FooRow): Future[FooRow] = {
???
}
}
// This class is auto-generated by slick codegen
case class FooRow(
id: Int,
created: Option[java.sql.Timestamp]
updated: Option[java.sql.Timestamp],
x1: String,
x2: String,
x3: String)
I want the returned FooRow object to have the correct ID and created timestamp that was assigned by the database.
I'll also want an update method that updates a FooRow, using the database clock for the updated column and returning a new FooRow
Things I have tried:
Using "returning"
I can insert the case class and get the generated id using the returning helper as described at Slick 3.0 Insert and then get Auto Increment Value, i.e.
class FooDao {
def db: JdbcBackend.Database
def insert(foo: FooRow): Future[FooRow] = {
db.run((Foos returning Foos.map(_.id)) += fooRow)
.map(id => fooRow.copy(id = id))
}
}
This works but I can't see any way to use CURRENT_TIMESTAMP() in the insert statement to get the updated column filled in. The types just don't allow it.
Using sqlu
I can write the SQL that I'm aiming for using sqlu, but then I can't see any way to get the generated key out of Slick. The JDBC API allows it via getGeneratedKeys() but I can't see how to access it here:
class FooDao {
def db: JdbcBackend.Database
def insert(foo: FooRow): Future[FooRow] = {
val sql: DBIO[Int] = sqlu"""
INSERT INTO foos
(created,
updated,
x1,
x2,
x3)
VALUES (current_timestamp(),
current_timestamp(),
$foo.x1,
$foo.x2,
$foo.x3)
"""
db.run(sql)
.map(rowCount => ???)
}
}
Using active-slick
The https://github.com/strongtyped/active-slick project looks like nearly what I need, but it doesn't appear to support updated/created columns.
Using database default values / triggers
I suppose I could implement the created column using a database default value, but how would I prevent Slick from passing in an explicit value in its insert statement and overruling that?
I suppose I could implement the updated column using a database trigger, but I'd prefer to keep my logic in the Scala layer and keep the database simple.
Using the web-server clock instead of the DB clock
The closest I can get to this at the moment is to use the web-server clock instead of the DB clock:
class FooDao {
def db: JdbcBackend.Database
def insert(foo: FooRow): Future[FooRow] = {
val now = Some(Timestamp.from(Instant.now()))
val toInsert = fooRow.copy(
created = now,
updated = now)
db.run((Foos returning Foos.map(_.id)) += toInsert)
.map(id => toInsert.copy(id = id))
}
}
However, I'd much prefer to use the database clock for these columns, in case different web servers have different times.
Any ideas would be gratefully received. I'm strongly considering abandoning Slick and using JOOQ, where this would be easy.
