Working with relational databases in Scala

Good afternoon, today I would like to tell you how our team works with databases. Our company mainly uses Oracle and there are many people in our team who know how to cook it well. We initially wanted to get full access to its capabilities: hierarchical queries, analytic functions, transferring objects and collections as query parameters, and maybe, if there is no other way - hints. Our model is not very complicated, so we consciously abandoned ORM.

Apache DbUtils was taken as a basis and they made a simple Scala wrapper for it. Below, I will tell you how Scala features, especially its latest version 2.10, helped simplify work with the database.

And inquisitive readers who read to the end, waiting for a surprise.

Work with connections

As you know, when working with a database, unless you have a very simple project, you need to reuse connections. Pools are used for this (see DBCP, c3po etc). If you use a pool in the forehead, then sooner or later someone will forget to give the connection pool. Usually, when testing and debugging, such problems are not visible, sometimes they can even go through regression and be transferred to the combat environment, where they will not find themselves immediately.
')
In order to be sure that the connection will always be in the pool, it is necessary to close it automatically. Conveniently, if the connection is issued in a limited scope, and when exiting it closes:

def execute[R](operation: Connection => R): R = { val connection = driver.getConnection() try { operation(connection) } finally { connection.close() } } 

It is clear that this code is too schematic, there is no work with auto commit and rollback of transactions, but this does not affect the general idea. Then you can work with the database as follows:

 database.execute { connection => database.query("select ? from dual", Seq(1))(connection) } 

or simply

 database execute query ("select ? from dual", Seq(1)) 

Here Scala is already great help, because in Java, it is necessary to generate a monstrous anonymous class that kills the whole desire to use a similar approach.

Imports

For such a common component as an abstraction over a database, I don’t want to import a lot of different junk. Unfortunately, now there is no normal solution for grouping imports. The last thing that is googling is the Import Object debate. Until recently, in order to use all the possibilities for working with the database, you had to write three imports.

The number of imports could be optimized by creating a package object, where all public declarations moved: functions, implicit conversions, and constants. Classes remained in the same package. As a result, to import all the declarations necessary for working with the database, you need to write:

  import ru.tcsbank.utils.database._ 

To work with the database simply as `database execute query (" select? From dual ", Seq (1))` our colleague suggested importing the contents of the database object for the current scope:

  import database._ 

Row Interpolation for SQL

A big headache when working with a database is always the transfer of parameters: beginners tend to paste parameters into requests, which create security holes and DBA headaches, ordinary developers forget to transfer the necessary number of parameters or confuse order.

As soon as Scala 2.10 came out, I looked through the feature list. At first glance, I didn’t really like the interpolation of the lines : “I dragged some garbage from PHP, now for the same syntax they will be used, and even before the lines there will be a magical` s`! ”, I thought. After some time, I wrote a request to the database and tried to arrange all the parameters in the correct order. And here I remembered about interpolation! “That's it!”, Flashed through my head, I need to write an interpolator `sql`, which will set the parameters in places, just obvious! After 10 minutes, I already found the desired interpolator on the Internet: http://gist.github.com/mnesarco/4515475 .

He still had to finish to support concatenation, batch requests and out-parameters, but this was a very important step, now our requests have acquired a new look:

 database execute query ( sql"select ${magic} from dual where 1=${one}" ) 

For multiline SQL queries, it is better to use the syntax for multiline strings (multiline strings?: -S) than concatenation, so the code is cleaner and easier to copy to your favorite SQL editor.

 database execute call ( sql"""begin pack.proc(param1 => ${value1}, param2 => ${value2}); end;""" ) 

The biggest surprise for me was that IntelliJ IDEA somehow automatically realized that the text inside my own interpolation was SQLʻem and began to highlight it correctly, which was already beyond the edge of coolness.

Well, Pimp my library now!

It's not a secret for anyone that java.util.ResultSet provides a low-level API. Fundamentally, ResultSet can only be read once, and its rows and columns are pretty unrelated things. Therefore, we have two abstractions for reading from ResultSet - for obtaining a collection of a certain type from all of it, as well as for reading and wrapping the value of its column.

The first abstraction, a function from the ResultSet, is able to create all sorts of multi maps, sets, lists and options, and is responsible for correctly controlling the cursor, grouping data by keys and delegating the creation of specific values ​​of the second abstraction.

The second abstraction is an implicit conversion that expands the capabilities of the `ResultSet`, it works with the current row of the table and allows you to read the basic domain objects for our company: Id, Time, and also knows how to create to receive instances of the Class class, but the most valuable is that can wrap in Option those values ​​where the values ​​themselves may not be.

As a result, we can freely write:

 resultSet.getOptionalString("NICKNAME") resultSet.getId[User]("USER_ID") resultSet.getOptionalId[Account]("ACCOUNT_ID") 

The entire database call may look like

 database execute query ( sql"select id, nickname from user", toSetMultiMap(_.getId[User]("id"), _.getOptionalString("nickname"))) 

Sources

As you understand, we are a commercial company in the field of finance and cannot simply share our code. But today we try ourselves in a new capacity. The source code, examples of which I cited, with pleasant additions, is available on GitHub under the MIT license, that is, you can use it in your projects and adapt it for yourself!

We also have a https://github.com/TinkoffCreditSystems repository, where we will try to continue uploading original solutions. We hope for interesting comments and pull requests.

findings

And the conclusion is approximately as follows: it is not necessary to use ORM, so that work with relational databases is easy and easy. And about 400 lines of code are enough for this.

The only thing that probably is bothering me, work through JDBC still happens synchronously and blocks the threads. It becomes possible to become fully reactive only if you completely switch to a database that has an asynchronous driver. This is a worthy goal for the future.

What do you think could be further improved in working with databases?