Some fun with Kotlin, Exposed and MySQL
Tags: kotlin, exposed, sql, development
Published: 2020-04-09 15:40:30

---
title: Some fun with Kotlin, Exposed and MySQL
published: true
date: 2020-03-19 09:16:14 UTC
tags: kotlin,exposed,sql,development
canonical_url: https://lengrand.fr/kotlin-exposed-imdb/
---

 ![Some fun with Kotlin, Exposed and MySQL](https://images.unsplash.com/photo-1489875347897-49f64b51c1f8?ixlib=rb-1.2.1&q=80&fm=jpg&crop=entropy&cs=tinysrgb&w=1080&fit=max&ixid=eyJhcHBfaWQiOjExNzczfQ)

_We are living strange times right now. Playing around with new technology as half the world is on lockdown feels vain, and I struggle to put my mind to the task._

_But I also can't let myself down, so here we are!_

A couple months back, I built [Star Recognizer](https://starrecognizer.com/), a simple [Elm](https://elm-lang.org/) application that allows you to take a picture of a movie and know who are the actors playing in it. It is very crude still, but built on a real use case :). I have a good memory for faces, and always argue with my partner about such and such actor. Not any more!

One of the things that bugged me is that [iMDB](https://www.imdb.com/) offers no open API, so it is not easy to provide much information in the app itself. Ideally, I would like something like : This is Heath Ledger, and he is most liked for _[The Dark Knight](https://en.wikipedia.org/wiki/The_Dark_Knight_(film))_ and _[The Imaginarium of Doctor Parnassus](https://en.wikipedia.org/wiki/The_Imaginarium_of_Doctor_Parnassus)._

<!--kg-card-begin: image-->

![Some fun with Kotlin, Exposed and MySQL](https://lengrand.fr/content/images/2020/03/image-1.png)<figcaption>an example of result from Star Recognizer</figcaption>

<!--kg-card-end: image-->

Then I saw that iMDB offered a daily snapshot version of [their datasets](https://www.imdb.com/interfaces/) and I felt like refreshing my SQL skills while doing some [Kotlin](https://kotlinlang.org/).

The goal is to get a fully automated ingestion mechanism running on Docker that anyone can spin quickly, featured with a simple API that can be used. There are many options available, but for this project I decided to give [**Exposed**](https://github.com/JetBrains/Exposed) a spin, which is a Kotlin SQL Framework from the folks at **Jetbrains**.

This article is a collection of tips and tricks I've learnt when playing around with Exposed. A proper post about the project will come later once I'm done :).

## Importing large amounts of data in the database

Most of the examples indicated in the exposed documentation show the insertion of limited amounts of data in the database.

In my case, The total size of the dataset to be ingested is almost 4Gbs (1Gbs if taking only actors and movies in), **for a total of over 87 Million lines (!!).**

<!--kg-card-begin: image-->

![Some fun with Kotlin, Exposed and MySQL](https://lengrand.fr/content/images/2020/03/Carbonize-2020-03-19-at-9.32.32-AM.png)<figcaption>Size of the iMDB datasets</figcaption>

<!--kg-card-end: image-->

I was obviously not going to fit everything in a single transaction.

So I created a function that allows me to split a given dataset into multiple transactions, depending on its size :

<!--kg-card-begin: image-->

![Some fun with Kotlin, Exposed and MySQL](https://lengrand.fr/content/images/2020/03/Carbonize-2020-03-19-at-9.41.05-AM.png)<figcaption>Splitting a dataset ingestion into multiple transactions</figcaption>

<!--kg-card-end: image-->

_Disclaimer : I am totally not happy about that current function. It's definitely not idiomatic IMHO, and I'll come back to it later. But it does the job for now, and I want to focus on getting the API running in the short run._

I learnt two things while doing this :

- I had to revert to a `for` loop to run the transaction. I originally had a `.map()` but this would eagerly keep all the file in memory, which would generate `Out of Memory` exceptions_._
- Even though we are using the `batchInsert` method from Exposed, what actually happens in the background is that we generate a single INSERT SQL statement per line in mySQL. An obviously not desired solution. The solution is to use the `rewriteBatchedStatements=true` and leverage MySQL to rewrite those statements. The improvement in my case was well over a 2000% speedup! Enough that [I asked that note to be part of the official documentation](https://github.com/JetBrains/Exposed/wiki/DSL#batch-insert).

## Using references between tables

I am fortunate enough that iMDB uses `nconst` and `tconst` as unique identifiers of respectively actors and titles. Those unique ids can be found across the tables and we can make good use of it.

Here are definitions of my movie titles, and their ratings :

<!--kg-card-begin: image-->

![Some fun with Kotlin, Exposed and MySQL](https://lengrand.fr/content/images/2020/03/Carbonize-2020-03-19-at-10.00.31-AM.png)<figcaption>Simple definition of titles and ratings</figcaption>

<!--kg-card-end: image-->

Now, let's try to find the first Batman movie by rating:

<!--kg-card-begin: image-->

![Some fun with Kotlin, Exposed and MySQL](https://lengrand.fr/content/images/2020/03/Carbonize-2020-03-19-at-10.01.52-AM.png)

<!--kg-card-end: image-->

This query, on a fully loaded database, takes 3.1 seconds on average on my MBP.

Now, let us leverage references from the framework, and define a relation between the two tables. The only difference is that we define `tconst` as a reference to Ratings in the Titles table. So we will change our Titles object's first line to be _:_

<!--kg-card-begin: image-->

![Some fun with Kotlin, Exposed and MySQL](https://lengrand.fr/content/images/2020/03/image-2.png)<figcaption>Adding references to the Ratings in Titles.</figcaption>

<!--kg-card-end: image-->

The very same request takes now 2.1 seconds on average on my laptop. You really shouldn't do without :).

## Using database options

The main Exposed README example gives an overview of operations that are being done as part of a database transaction.

- In my case, I do things always in at least two steps : I populate the database, and then I run the query, which means I was having two transactions. The **H2 in-memory database** was always failing at the second transaction, telling me that my table didn't exist. Well, turns out [it is a well know thing](https://stackoverflow.com/questions/5763747/h2-in-memory-database-table-not-found). H2 will close the connection by default after the first transaction. The fix is actually quite simple : add the following option to the database to keep the connection open :`;DB_CLOSE_DELAY=-1`.
- Another simple but useful tip : On my local setup, the database connection is not setup to use HTTPS. Starting the MySQL connection would generate errors : 

> _Establishing SSL connection without server's identity verification is not recommended. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification._

This is fixed as indicated simply by adding the following option to your connection string `?useSSL=false`.

## Data repetition makes schemes error prone

One of the things that kept biting me with Exposed so far is the fact that you feel like the same information is repeated several times across your application, making contract errors very easy if you copy/paste or are not focused enough.

This is especially true when marshalling / demarshalling my schemas .

Let's look at my Ratings table for example, and how to populate it :

<!--kg-card-begin: image-->

![Some fun with Kotlin, Exposed and MySQL](https://lengrand.fr/content/images/2020/03/Carbonize-2020-03-19-at-10.08.49-AM.png)<figcaption>Interacting with the Ratings table in the database</figcaption>

<!--kg-card-end: image-->

Just for clarity, because I am processing [a TSV file](https://en.wikipedia.org/wiki/Tab-separated_values), the insert function takes a line of the file and create an `InsertStatement` out of it.

If we look at `averageRating` for example, this specific name is used three different times in different places. It happened a few times that I mix a letter, or place the name in the wrong place, which can create bugs that are annoying to find. Especially since those two functions are typically placed in different files. This is an issue you are less likely to find with an annotation based system for example.

But again, this is a minor annoyance and the strong typing helps tremendously already anyways.

I am far from done with this project, as you can see, the snippets are still rough and there will be some refactoring coming. So far I have used the opportunity to investigate a new framework which I had not done in a while, and refresh my SQL knowledge in the process. Plus it can be fun and useful for some once it's done!

It is bar far not a complete project, but you can check [the current status](https://github.com/jlengrand/exposed-imdb) here :).

Till next time!