Migrate from PostgreSQL to MySQL using Slick (Scala)

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 producing correct data for MySQL was rather easy, I faced some difficulties with adopting it for be compatible with existing data and especially timestamps.

I was surprised to learn that timestamps produced by Slick in MySQL and PostreSQL have different format. In any case, we use ISO 8601 format to store timestamps for both MySQL and PostreSQL. 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 mappind 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.

Comments

[Ritik Raj]

Migrating from PostgreSQL to MySQL using Slick is indeed a challenging task, especially when working with date and time formats. Your experience resonates with common issues developers face during migrations, particularly with how timestamps are handled differently between the two databases. It's great that you found a workaround by customizing the Profile class to align MySQL’s timestamp handling with PostgreSQL’s.

For others navigating similar migrations, here are a few considerations and tips:
Key Points:

Timestamp Differences:
    PostgreSQL stores timestamps in TIMESTAMP format by default, whereas MySQL often uses TEXT when interfacing via Slick.
    The ISO 8601 format (YYYY-MM-DDTHH:mm:ssZ) is an excellent choice for interoperability, but it requires consistency across systems.

Custom Mappings in Slick:
    As you noted, custom column mappings may not work directly for timestamps.
    Extending the Profile class, as detailed in the Slick Migration Guide, is the way to go. Borrowing logic from PostgreSQLProfile ensures compatibility.

Database-Specific Features:
    Both PostgreSQL and MySQL have unique capabilities that may not translate directly. For instance, PostgreSQL supports richer data types like JSONB and custom range types.

Testing the Migration:
    Before running migrations in production, test the data consistency using tools like pgloader or custom scripts to validate row counts, formats, and queries.

Additional Resources:

For a deeper dive into the nuances of PostgreSQL and MySQL, the Vultr documentation provides insights into these databases:

Learn about PostgreSQL Date and Time Handling, which can help refine your understanding of timestamp management.

Check out MySQL Best Practices for optimizing queries and understanding MySQL-specific data handling.

Final Thoughts:
Database migrations require meticulous attention to details like data types, SQL syntax, and performance considerations. Combining tools like Slick with custom adaptations, as you’ve done, is an excellent approach. For anyone starting similar migrations, be sure to consult reliable resources like Vultr’s guides for PostgreSQL and MySQL to navigate these challenges effectively.