Scala Tutorial: Create CRUD with Slick and MySQL

Scala Tutorial: Create CRUD with Slick and MySQL

We are certainly leaving at the epoch of flourishing data stores. Myriads of NoSQL solutions have emerged over the last couple of years

We are certainly leaving at the epoch of flourishing data stores. Myriads of NoSQL and NewSQL solutions have emerged over the last couple of years, and even these days the new ones are popping up here and there from time to time.

Nonetheless, long-time players in the form of relational database are still being used in the vast majority of software systems. Bullet-proof and battle-tested, they are number one choice for storing critical to business data.

1. Introduction

When we talk about Java and JVM platform in general, JDBC is a standard way of interfacing with relational databases. As such, mostly every single relational database vendor provides a JDBC driver implementation so it becomes possible to connect to the database engine from the application code.

In many respects, JDBC is old-fashioned specification which hardly fits into modern reactive programming paradigms. Although there are some discussions to revamp the specification, in reality no active work is happening in this direction, at least publicly.

2. Database Access, the Functional Way

Inability to speed up the changes in specification does not mean nothing could be done. There many, many different frameworks and libraries available on JVM to access relational databases. Some of them aim to be as close to SQL as possible, others going further than that offering sort of “seamless” mapping of the relational model to programming language constructs (so called class of ORM or object-relational mapping solutions). Although to be fair, most of them are built on top of JDBC abstractions nonetheless.

In this regards, Slick (or in full, Scala Language-Integrated Connection Kit) is a library to provide access to relational databases from Scala application. It is heavily based on functional programming paradigm and as such is often being referred as functional relational mapping (or FRM) library. Slick’s ultimate promise is to reinvent the way to access relational databases by means of regular operations over collections, so familiar to every Scala developer, with strong emphasize on type safety.

Despite having 3.1.1 release out not so long ago, it is relatively very young library, still on its way to reach a certain level of maturity. Many early adopters do remember how significant the difference between versions 2.x and 3.x was. Luckily, things are getting more stable and smoother, with the first milestone of upcoming 3.2 release being just a few weeks old.

Slick goes with the times and is fully asynchronous library. And, as we are going to see very soon, implements reactive streams specification as well.

Configuration

Slick fully supports quite a number of popular open-source and commercial relational database engines. To demonstrate that we are going to use at least two of them: MySQL for production deployment and H2 for integration testing.

To give Slick the credits, it is very easy to get started with when the application is developed against single relational database engine. But it is a little bit tricky to configure and use Slick in JDBC-driver independent fashion, due to differences in the database’s capabilities. As we are targeting at least two different engines, MySQL and H2, we are certainly going to take this route.

Typical way of configuring database connections in Slick is to have a dedicated named section in the application.conf file, for example:

db {
  driver = "slick.driver.MySQLDriver$"

  db {
  	url = "jdbc:mysql://localhost:3306/test?user=root&password=password"
  	driver = com.mysql.jdbc.Driver
  	maxThreads = 5
  }
}

The configuration should look familiar to JVM developers working with relational database over JDBC. It is worth to mention that Slick supports database connection pooling out of the box using brilliant HikariCP library. The maxThreads setting hints Slick to configure connection pool of the maximum size of 5.

If you are curious why there are two driver settings in the configuration, here is the reason. The first driver setting identifies the Slick-specific JDBC profile (Slick driver), while the second one points out to JDBC driver implementation to use.

To take care of this configuration we are going to define a dedicated DbConfiguration trait, although the purpose of introducing this trait may not be so obvious for now:

trait DbConfiguration {
  lazy val config = DatabaseConfig.forConfig[JdbcProfile]("db")
}

4. Table Mappings

Arguably the first thing to start with in relational databases universe is data modeling. Essentially, it translates to creation of the database schema, tables, their relations and constraints. Luckily, Slick makes it extremely easy to do.

As an exercise, let us build a sample application to manage users and their addresses, represented by those two classes.

case class User(id: Option[Int], email: String, 
  firstName: Option[String], lastName: Option[String])

case class Address(id: Option[Int], userId: Int, 
  addressLine: String, city: String, postalCode: String)

In turn, our relation data model is going to be constituted from just two tables, USERS and ADDRESSES. Let us use Slick capabilities to shape that out in Scala.

trait UsersTable { this: Db =>
  import config.driver.api._

  private class Users(tag: Tag) extends Table[User](tag, "USERS") {
    // Columns
    def id = column[Int]("USER_ID", O.PrimaryKey, O.AutoInc)
    def email = column[String]("USER_EMAIL", O.Length(512))
    def firstName = column[Option[String]]("USER_FIRST_NAME", O.Length(64)) 
    def lastName = column[Option[String]]("USER_LAST_NAME", O.Length(64))

    // Indexes
    def emailIndex = index("USER_EMAIL_IDX", email, true)

    // Select
    def * = (id.?, email, firstName, lastName) <> (User.tupled, User.unapply)
  }

  val users = TableQuery[Users]
}


For the people familiar with SQL language, there is definitely a very close resemblance with CREATE TABLE statement. However, Slick also has a way to define seamless conversion between domain entity represented by Scala class (User) to table row (Users) and vice versa, using * projection (literally translates to SELECT * FROM USERS).

The one subtle detail we haven’t touched upon yet is Db trait (referenced by this: Db => construct). Let us take a look on how it is defined:

trait Db {
  val config: DatabaseConfig[JdbcProfile]
  val db: JdbcProfile#Backend#Database = config.db
}


The config is the one from DbConfiguration while db is a new database instance. Later on in the UsersTable trait the respective types for the relevant JDBC profile are introduced into the scope using import config.driver.api._ declaration.

The mapping for the ADDRESSES table looks very much the same, except the fact we need a foreign key reference to the USERS table.

trait AddressesTable extends UsersTable { this: Db =>
  import config.driver.api._

  private class Addresses(tag: Tag) extends Table[Address](tag, "ADDRESSES") {
    // Columns
    def id = column[Int]("ADDRESS_ID", O.PrimaryKey, O.AutoInc)
    def addressLine = column[String]("ADDRESS_LINE")
    def city = column[String]("CITY") 
    def postalCode = column[String]("POSTAL_CODE")

    // ForeignKey
    def userId = column[Int]("USER_ID")
    def userFk = foreignKey("USER_FK", userId, users)
      (_.id, ForeignKeyAction.Restrict, ForeignKeyAction.Cascade)

    // Select
    def * = (id.?, userId, addressLine, city, postalCode) <> 
     (Address.tupled, Address.unapply)
  }

  val addresses = TableQuery[Addresses]
}

The users and addresses members serve as a façade to perform any database access operations against respective tables.

5. Repositories

Although repositories are not specific to Slick per se, defining a dedicated layer to communicate with database engine is always a good design principle. There would be only two repositories in our application, UsersRepository and AddressesRepository.

class UsersRepository(val config: DatabaseConfig[JdbcProfile])
    extends Db with UsersTable {

  import config.driver.api._
  import scala.concurrent.ExecutionContext.Implicits.global

  ...  
}

class AddressesRepository(val config: DatabaseConfig[JdbcProfile]) 
    extends Db with AddressesTable {

  import config.driver.api._
  import scala.concurrent.ExecutionContext.Implicits.global

  ...
}

All data manipulations we are going to show case later on are going to be part of one of those classes. Also, please notice the presence of Db trait in the inheritance chain.

6. Manipulating Schemas

Once the table mappings (or simplify database schema) are defined, Slick has a capability to project it to a sequence of DDL statements, for example:

def init() = db.run(DBIOAction.seq(users.schema.create))
def drop() = db.run(DBIOAction.seq(users.schema.drop))

def init() = db.run(DBIOAction.seq(addresses.schema.create))
def drop() = db.run(DBIOAction.seq(addresses.schema.drop))

7. Inserting

In the simplest scenarios adding a new row to the table is as easy as adding an element to users or addresses (instances of TableQuery), for example:

def insert(user: User) = db.run(users += user)

That works fine when primary keys are assigned from the application code. However, in case when primary keys are generated on database side (for example using auto-increments), like for Users and Addresses tables, we have to ask for these primary identifiers to be returned to us:

def insert(user: User) = db
  .run(users returning users.map(_.id) += user)
  .map(id => user.copy(id = Some(id)))

8. Querying

Querying is one of the Slick distinguishing features which really shines. As we already mentioned, Slick tries hard to allow using Scala collection semantics over database operations. However it works surprisingly well please note that you are not working with the standard Scala types but the lifted ones: the technique known as lifted embedding.

Let us take a look on this quick example on the one of the possible ways to retrieve user from the table by its primary key:

def find(id: Int) = 
   db.run((for (user <- users if user.id === id) yield user).result.headOption)

Alternatively to for comprehension we could just use filtering operation, for example:

def find(id: Int) = db.run(users.filter(_.id === id).result.headOption)

The results (and generated SQL query by the way) are exactly the same. In case we need to fetch user and its address, we could use a couple of query options here as well, starting with a typical join:

def find(id: Int) = db.run(
  (for ((user, address) <- users join addresses if user.id === id) 
    yield (user, address)).result.headOption)

Or, alternatively:

def find(id: Int) = db.run(
  (for {
     user <- users if user.id === id
     address <- addresses if address.userId === id 
  } yield (user, address)).result.headOption)


Slick querying capabilities are really very powerful, expressive and readable. We have just looked at a couple of typical examples but please glance through official documentation to find much more.

9. Updating

Updates in Slick are represented as a combination of a query (which basically outlines what should be updated) and essentially the update itself. For example, let us introduce a method to update user’s first and last names:

def update(id: Int, firstName: Option[String], lastName: Option[String]) = { 
def update(id: Int, firstName: Option[String], lastName: Option[String]) = { 
  val query = for (user <- users if user.id === id) 
    yield (user.firstName, user.lastName) 
  db.run(query.update(firstName, lastName)) map { _ > 0 }
}

10. Deleting

Similarly to updates, the delete operation is basically just a query to filter out the rows to be removed, for example:

def delete(id: Int) = 
  db.run(users.filter(_.id === id).delete) map { _ > 0 }

11. Streaming

Slick offers the capability to stream results of the database query. Not only that, its streaming implementation fully supports reactive streams specification and could be used right away in conjunction with Akka Streams.

For example, let us stream the results from users table and collect them as a sequence using Sink.fold processing stage.

def stream(implicit materializer: Materializer) = Source
  .fromPublisher(db.stream(users.result.withStatementParameters(fetchSize=10)))
  .to(Sink.fold[Seq[User], User](Seq())(_ :+ _))
  .run()

Please be advised that Slick’s streaming feature is really very sensitive to relational database and JDBC driver you are using and may require more exploration and tuning. Definitely do some extensive testing to make sure the data is streamed properly.

12. SQL

In case there is a need to run a custom SQL queries, Slick has nothing against that and as always tries to make it as painless as possible, providing useful macros. Let say we would like to read user’s first and last names directly using plain old SELECT statement.

def getNames(id: Int) = db.run(
  sql"select user_first_name, user_last_name from users where user_id = #$id"
    .as[(String, String)].headOption)


It is as easy as that. In case the shape of the SQL query is not known ahead of time, Slick provides the mechanisms to customize the result set extraction. In case you are interested, official documentation has very good section dedicated to plain old SQL queries.

13. Testing

There are multiple ways you can approach testing of the database access layer when using Slick library. The traditional one is by using in-memory database (like H2 for example), which in our case translates into minor configuration change inside application.conf:

db {
  driver = "slick.driver.H2Driver$"

  db {
  	url = "jdbc:h2:mem:test1;DB_CLOSE_DELAY=-1"
  	driver=org.h2.Driver
  	connectionPool = disabled
  	keepAliveConnection = true
  }
}

Please notice that if in production configuration we turned database connection pooling on, the test one uses just single connection and pool is explicitly disabled. Everything else essentially stays the same. The only thing we have to take care of is creating and dropping the schema between test runs. Luckily, as we saw in section Manipulating Schemas, it is very easy to do with Slick.

class UsersRepositoryTest extends Specification with DbConfiguration 
    with FutureMatchers with OptionMatchers with BeforeAfterEach {

  sequential

  val timeout = 500 milliseconds
  val users = new UsersRepository(config)

  def before = {
    Await.result(users.init(), timeout)
  }

  def after = {
    Await.result(users.drop(), timeout)
  }

  "User should be inserted successfully" >> { implicit ee: ExecutionEnv =>
    val user = User(None, "[[email protected]](/cdn-cgi/l/email-protection)<script data-cfhash="f9e31" type="text/javascript">/* <![CDATA[ */!function(t,e,r,n,c,a,p){try{t=document.currentScript||function(){for(t=document.getElementsByTagName('script'),e=t.length;e--;)if(t[e].getAttribute('data-cfhash'))return t[e]}();if(t&&(c=t.previousSibling)){p=t.parentNode;if(a=c.getAttribute('data-cfemail')){for(e='',r='0x'+a.substr(0,2)|0,n=2;a.length-n;n+=2)e+='%'+('0'+('0x'+a.substr(n,2)^r).toString(16)).slice(-2);p.replaceChild(document.createTextNode(decodeURIComponent(e)),c)}p.removeChild(t)}}catch(u){}}()/* ]]> */</script>", Some("Tom"), Some("Tommyknocker"))
    users.insert(user) must be_== (user.copy(id = Some(1))).awaitFor(timeout)
  }
}

Very basic Specs2 test specification with single test step to verify that new user is properly inserted into database table.

In case for any reasons you are developing your own database driver for Slick, there is a helpful Slick TestKit module available along with example driver implementation.

14. Conclusions

Slick is extremely expressive and powerful library to access relational databases from Scala applications. It is very flexible and in most cases offers multiple alternative ways of accomplishing things at the same time trying hard to maintain the balance between making developers highly productive and not hiding the fact that they dial with relational model and SQL under the hood.

Hopefully, we all are Slick-infected right now and are eager to try it out. Official documentation is a very good place to begin learning Slick and get started.

MySQL Database Tutorial for Beginners to Advanced Part 1

MySQL Database Tutorial for Beginners to Advanced Part 1

Learn about MySQL Database from scratch. Go from zero to hero in MySQL Database with this complete course.

Learn about MySQL Database from scratch. Go from zero to hero in MySQL Database with this complete course.

In this course you will learn about MySQL Database from scratch. We will assume that you are a complete beginner and by the end of the course you will be at advanced level. In this complete course you shall get demonstration of every single topic that we have explained. This course is fully practical without neglecting the theories and basics.



How to Create Docker Image with MySQL Database

How to Create Docker Image with MySQL Database

In this tutorial, you will learn how to create Docker images and using bind mounts to customize MySQL database

In this tutorial, you will learn how to create Docker images and using bind mounts to customize MySQL database

Many **developers **use **Docker **to spin up a local database. This makes it easy to test their code and write data without installing and configuring a lot of tools. In this piece, I’ll explain how you can **customize **your Docker database. While I’ll be explaining the steps for MySQL, it’s the same for PostgreSQL!

Follow along and let’s create a custom MySQL which contains your desired tables and data.

Creating SQL scripts

We will create **SQL **scripts which contain the **SQL **statements we want to execute on our database. Create a directory in which we will work. Also, create a subdirectory where we will store our .sql scripts.

$ mkdir -p ~/my-mysql/sql-scripts
$ cd ~/my-mysql/sql-scripts

I want to customize my **database **with a table called employees. The table needs to contain one row with an employee (first name, last name, department, and email).

Write a CreateTable.sql. This file contains the SQL statement to create a table called employees. We will add four columns to our table

CREATE TABLE employees (
first_name varchar(25),
last_name  varchar(25),
department varchar(15),
email  varchar(50)
);

Write a InsertData.sql. This file contains our statement to insert data in the table ‘employees’.

INSERT INTO employees (first_name, last_name, department, email) 
VALUES ('Lorenz', 'Vanthillo', 'IT', '[email protected]');

Execute the tree command to verify that your two scripts exist and are saved in the right directory.

$ cd ~/my-mysql
$ tree
└── sql-scripts
    ├── CreateTable.sql
    └── InsertData.sql

Creating a Docker Image for Your Customized MySQL Database

Now that the scripts are ready, we can write a Dockerfile to create our own Docker image (based on the official image of MySQL).

$ cd ~/my-mysql/
$ vi Dockerfile

Content of Dockerfile:

# Derived from official mysql image (our base image)
FROM mysql
# Add a database
ENV MYSQL_DATABASE company
# Add the content of the sql-scripts/ directory to your image
# All scripts in docker-entrypoint-initdb.d/ are automatically
# executed during container startup
COPY ./sql-scripts/ /docker-entrypoint-initdb.d/

Create your Docker image:

$ cd ~/my-mysql/
$ docker build -t my-mysql .
Sending build context to Docker daemon  4.608kB
Step 1/2 : FROM mysql
latest: Pulling from library/mysql
Digest: sha256:691c55aabb3c4e3b89b953dd2f022f7ea845e5443954767d321d5f5fa394e28c
Status: Downloaded newer image for mysql:latest
 ---> 5195076672a7
Step 2/2 : ADD sql-scripts/ /docker-entrypoint-initdb.d/
 ---> 25065c3d93c0
Successfully built 25065c3d93c0
Successfully tagged my-mysql:latest

And start your **MySQL **container from the image:

$ docker run -d -p 3306:3306 --name my-mysql \
-e MYSQL_ROOT_PASSWORD=supersecret my-mysql

Now we can verify. We will exec inside the container:

$ docker exec -it my-mysql bash
[email protected]:/# mysql -uroot -p
Enter password: (supersecret)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| company            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
mysql> use company;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| employees         |
+-------------------+
1 row in set (0.00 sec)
mysql> show columns from employees;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| first_name | varchar(25) | YES  |     | NULL    |       |
| last_name  | varchar(25) | YES  |     | NULL    |       |
| department | varchar(15) | YES  |     | NULL    |       |
| email      | varchar(50) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql> select * from employees;
+------------+-----------+------------+-------------------+
| first_name | last_name | department | email             |
+------------+-----------+------------+-------------------+
| Lorenz     | Vanthillo | IT         | [email protected] |
+------------+-----------+------------+-------------------+
1 row in set (0.01 sec)

It works! We have our customized MySQL database Docker image! This is a great solution for local development between multiple developers. By sharing the Docker image, every developer can use the **database **by just starting a container from the image.

It’s important to note, however, that this is not always the best solution:

  • If you insert a lot of data your image size will grow significantly
  • Need to build a new image when you want to update the data

That’s why there is another way to customize your Docker MySQL.

Use Bind Mounts to Customize Your MySQL Database in Docker

In this last section, we will simply mount the scripts inside the official MySQL Docker container.

$ docker run -d -p 3306:3306 --name my-mysql \
-v ~/my-mysql/sql-scripts:/docker-entrypoint-initdb.d/ \
-e MYSQL_ROOT_PASSWORD=supersecret \
-e MYSQL_DATABASE=company \
mysql

And we can verify again! Use the same steps as we did before: exec inside the container and check if the table and data exist!

This method is more flexible but it will be a little bit harder to distribute among the developers. They all need to store the scripts in a certain directory on their local machine and they need to point to that directory when they execute the docker run command.

Conclusion

In this piece, I’ve described how you can create your custom MySQL database in Docker. We’ve explored the two methods:

  • Custom MySQL Docker image
  • Bind mounts to mount scripts inside a container

We also discussed some of the advantages and disadvantages of each method.

We hope you enjoyed it!

Insert File Into MySQL Database In Java

Sometimes we have a requirement to insert file into database rather then storing it in the file system. For a developer, it is always hard to decide this. You can find good discussion in different forums on web over storing the file either in database or file system. I believe choosing the same depends on the project requirement rather than preferences. You can choose database over file system when file size is small and user’s file needs to be more tightly coupled, secured and confidential. Since this discussion is out of scope of this article, here is a good resource,&nbsp;<a href="https://habiletechnologies.com/blog/better-saving-files-database-file-system/" target="_blank">Which is Better ? Saving Files in Database or File System</a>&nbsp;for your reference.

Introduction

Sometimes we have a requirement to insert file into database rather then storing it in the file system. For a developer, it is always hard to decide this. You can find good discussion in different forums on web over storing the file either in database or file system. I believe choosing the same depends on the project requirement rather than preferences. You can choose database over file system when file size is small and user’s file needs to be more tightly coupled, secured and confidential. Since this discussion is out of scope of this article, here is a good resource, Which is Better ? Saving Files in Database or File System for your reference.

Well, today we will see how to insert image file into MySQL database using JDBC. We will use BLOB datatype of MySQL for storing files....

( Read More )