A CRUD application: Lagom ES & CQRS with MySQL

Reading Time: 2 minutes

In this blog, we will look at how we have implemented a CRUD application using Relational Database rather than using a NoSQL database with Lagom.

Lagom is a framework built on top of Akka and Play, with the intent of allowing users to easily and reliably build microservice-based applications. The framework and the concepts behind it are heavily based on CQRS (Command Query Responsibility Segregation) and ES (Event Sourcing). This dictates how the state is handled and persisted internally. By default, Lagom uses the Cassandra key-value store for persistence. But in my scenario NoSQL database is too big to handle as my records are very less so using Cassandra would be an overhead for me as proper data modeling and updating a field is cumbersome. So I took MySQL as my database instead of Cassandra. Now let’s get started:


libraryDependencies += "mysql" % "mysql-connector-java" % "8.0.15"

view raw
hosted with ❤ by GitHub


Firstly I provided MySQL configurations in the application.conf file for my database:

play.application.loader = com.knoldus.user.impl.UserLoader
jdbc-defaults.slick {
profile = "slick.jdbc.MySQLProfile$"
jndiName = DefaultDS
db.default {
driver = "com.mysql.cj.jdbc.Driver"
url = "jdbc:mysql://localhost:3306/db?autoReconnect=true"
username = ${?USERNAME}
password = ${?PASSWORD}

view raw
hosted with ❤ by GitHub

Then, after providing configurations I created a database itself as in MySQL we have to create a database (a keyspace in Cassandra which got generated itself) db as shown in the configuration above for:

db.default.url =”jdbc:mysql://localhost:3306/db?autoReconnect=true”

Event processor

An event processor we usually create for Cassandra didn’t work for me. So I changed processor a bit for MySQL to have it workable by overriding methods explicitly:

override def buildHandler(): ReadSideProcessor.ReadSideHandler[Event] = {
new ReadSideHandler[Event] {
override def globalPrepare(): Future[Done] = createTable
override def handle(): Flow[EventStreamElement[Event], Done, NotUsed] = {
Flow[EventStreamElement[Event]].mapAsync(4) { eventElement =>
eventElement.event match {
case addEvent: AddEvent => processAdded(addEvent)
case updateEvent: UpdateEvent => processUpdated(updateEvent)
case deleteEvent: DeleteEvent => processDeleted(deleteEvent)

createTable denotes the method which creates table let’s say, User table.
processAdded denotes the method which executes insert query.
processUpdated denotes the method which updates the user table.
processDeleted denotes the method which deletes a record from the user table.


Remember to inject (session: JdbcSession)  into your class to get the connection to execute the queries.

def createTable: Future[Done] = { val query =
|email VARCHAR(64) NOT NULL,
session.withConnection(_.prepareStatement(query).execute()).map(_ => Done)

 In the same way, we can insert into MySQL using the statement below:

def processUserAdded(addEvent: AddEvent): Future[Done] = {
session.withConnection { connection =>
val statement = connection.prepareStatement("INSERT INTO user (orgId, email, name) VALUES (?, ?, ?)")
statement.setInt(1, addEvent.user.orgId)
statement.setString(2, addEvent.user.email)
statement.setString(3, addEvent.user.name)
}.map(_ => Done)

processUpdated(updateEvent) and processDeleted(deleteEvent) can be created in the same way.


Rest State, Command, Event, Entity would be the same as we do with Cassandra. I had to deal with fewer data so, I took MySQL. We can use Postgres as well, it depends on the use case if you have large data and fewer updates go with Cassandra with proper data modeling. You can refer lagom-scala-mysql-es repository for the code here on Github.

That’s it, pretty easy. We have implemented Lagom Event Sourcing with MySQL successfully. I hope it helped. For any queries or doubts please comment below, I would like to answer.

Till then Stay tuned!! 🙂


Written by 

Charmy is a Software Consultant having experience of more than 1.5 years. She is familiar with Object Oriented Programming Paradigms and has familiarity with Technical languages such as Scala, Lagom, Java, Apache Solr, Apache Spark, Apache Kafka, Apigee. She is always eager to learn new concepts in order to expand her horizon. Her hobbies include playing guitar and Sketching.