I don't think Slick supports PostgreSQL NOTIFY , but postgresql-async . It can be used to create Akka Source streams and include it in the playback endpoint, which transmits database notifications to the client:
package controllers import javax.inject.{Inject, Singleton} import akka.actor._ import akka.stream._ import akka.stream.scaladsl._ import com.github.mauricio.async.db.postgresql.PostgreSQLConnection import com.github.mauricio.async.db.postgresql.util.URLParser import com.github.mauricio.async.db.util.ExecutorServiceUtils.CachedExecutionContext import play.api.Logger import play.api.http.ContentTypes import play.api.libs.EventSource import play.api.mvc._ import scala.concurrent.duration._ import scala.concurrent.Await @Singleton class DbNotificationController @Inject()(cc: ControllerComponents, materializer: Materializer) extends AbstractController(cc) { implicit val mat = materializer val configuration = URLParser.parse("jdbc:postgresql://localhost:5233/my_db?user=dbuser&password=pwd") val connection = new PostgreSQLConnection(configuration) Await.result(connection.connect, 5 seconds) val (actor, dbSource) = Source.actorRef[String](Int.MaxValue, OverflowStrategy.dropNew) .toMat(BroadcastHub.sink[String])(Keep.both) .run() connection.sendQuery("LISTEN my_channel") connection.registerNotifyListener { message => val msg = message.payload Logger.debug(s"Sending the payload: $msg") actor ! msg } def index() = Action { Ok(views.html.scaladbnotification()) } def streamDb() = Action { Ok.chunked(dbSource via EventSource.flow).as(ContentTypes.EVENT_STREAM) } }
In the controller described above, when the listener receives a notification from the database, the payload (a String ) in the notification is registered and sent to the actor. Messages that are sent to this actor feed Source , which is used at the endpoint of streamDb . Before messages with a payload are sent to the client, they are converted to the EventSource class.
I adapted the DbNotificationController from a sample streaming application that you can use for experimentation. If you want to do this, obviously, you need to integrate the DbNotificationController into this project:
- Add
"com.github.mauricio" %% "postgresql-async" % "0.2.21" to build.sbt . - Configure PostgreSQL as needed, including
NOTIFY , and configure the database URL in the controller according to your configuration. - Copy and paste the
DbNotificationController into /app/controllers/ . - Copy the following file (name it
scaladbnotification.scala.html ) into app/views/ :
@main { <h1>Server Sent Event from DB</h1> <h1 id="db"></h1> <p> DB events are pushed from the Server using a Server Sent Event connection. </p> <script type="text/javascript" charset="utf-8"> if (!!window.EventSource) { var stringSource = new EventSource("@routes.DbNotificationController.streamDb()"); stringSource.addEventListener('message', function(e) { $('#db').html(e.data.replace(/(\d)/g, '<span>$1</span>')) }); } else { $("#db").html("Sorry. This browser doesn't seem to support Server sent event. Check <a href='http://html5test.com/compare/feature/communication-eventSource.html'>html5test</a> for browser compatibility."); } </script> }
- In the
/conf/routes file, add the following lines:
GET / scala / dbNotification controllers.DbNotificationController.index ()
GET / scala / dbNotification / liveDb controllers.DbNotificationController.streamDb ()
Launch the application using sbt run and navigate to the following URL in your browser:
http: // localhost: 9000 / scala / dbNotification