Migrate from PostgreSQL to MySQL using Slick (Scala)

(from wikipedia)

For my rather long engineering career, I did several migrations from one database to another. Usually, I dealt with cases like switching from SQL- to non-SQL or vice versa. This time I had an interesting case of migrating from Postgres to MySQL.

For a rather small database (of 14 GBs of size) where we do not use anything special from what PostgreSQL can provide, I did not expect to face any kind of issues. Sure, some SQL queries in Scala code must be adapted to follow the MySQL syntax, but other than that it was expected to run smoothly.

Even though making the code produce correct data for MySQL was rather easy, I faced some difficulties with adopting it to be compatible with existing data and especially timestamps.

I was surprised to learn that timestamps produced by Slick in MySQL and PostreSQL have a different format. In any case, we use ISO 8601 format to store timestamps for both MySQL and PostgreSQL. So, store such data in the default format like this:

2022–03–11 21:59:00

That means that after the migration, the MySQL compatible code must read and produce the same data, however, that did not happen. Instead, the code produced this:

2022–03–11T21:59:00Z

After checking the official Slick documentation regarding working with ‘Date and Time’ we can see, that Slick saves data as `TIMESTAMP` for PostgreSQL and as `TEXT` for MySQL. In the end that should not be the issue. Custom column mapping should help us convert the data from one type to another. Unfortunately, that did not work out. Basically, it did not make any changes.

The answer and actually, the final solution, I found in the migration guide from 3.2 to 3.3 , that states that custom mapping does not work for dates and suggests to adjust the `Profile` class directly. That was my way to go — create a new `Profile` class based on `slick.jdbc.MySQLProfile` and copy the logic regarding `instantType` from `PostgreSQLProfile`. This way I managed to be absolutely compatible with timestamps produced by MySQL version of Slick. The rest data types worked well out of the box.

Cheers.

--

--

--

Software Engineer. Write about Ruby, Python, Deep Learning and life

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

What am I looking for, in an AI-powered Community Manager.

Stop giving homework to programming job candidates

Assert state of your Kubernetes Cluster in Java using Fabric8 Kubernetes Assertions

Processing Visualisation Experiments

Web-scrapping in Python for job hunting (part 1)

A magnifying glass on top of a book in German

Log Sql Queries using hooks in codeigniter 3

You are a reflection of the 5 people you spend the most time with.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Andrey Eremin

Andrey Eremin

Software Engineer. Write about Ruby, Python, Deep Learning and life

More from Medium

Introduction of MongoDB Scala Driver

MongoDB Database and Collection with Custom Type

REST API with Play Framework

Apache Kafka along with RxJS operators

How to Run a Cassandra Operation in Docker