Type-Safe MySQL Queries via Postgres

My team and I are working on NoRedInk's transition from Ruby on Rails and MySQL to Haskell and Postgres. A major benefit of our new stack (Postgres/Haskell/Elm) is type-safety between the boundaries (db to backend, backend to frontend). To ensure our code is type-safe between Haskell and Elm we use servant. Servant allows us to generate types and functions for Elm based on our APIs. To ensure type-safety between Postgres and Haskell we use postgresql-typed. This means that when we change our database schema, our Haskell code won't compile if there is a mismatch, similarly to how our Elm code won't compile if there is a mismatch with our backend endpoints. We're going to focus on type-safety between our databases and Haskell in this post and on how we introduced MySQL into this. But first, let's briefly look at how we can check out Postgres queries at compile-time.

Postgres-Typed

todos :: Int -> PGConnection -> IO [Todo]
todos userId postgresConn = do
  rows <-
    pgQuery
      postgresConn
      [pgSQL|!
        SELECT id, description, completed
        FROM todos
        WHERE user_id = ${userId}
        ORDER BY id ASC
      |]
  pure (fmap todoFromRow rows)

We use Template Haskell to write SQL queries instead of a DSL, allowing our engineers to use their existing knowledge of SQL instead of having to learn some library. pgSQL is a QuasiQuoter it creates a value of type PGQuery which gets executed by pgQuery. The quasi quoted query gets verified against the database schema and executed (ignoring results and without arguments) at compile-time. This is all we need to know from postgresql-typed for this post, but I recommend checking out the docs and looking at their example.

Moving to Haskell and Postgres

Our Rails application uses a MySQL database. Gradually moving functionality/new features to Haskell often means that we need access to data that isn't yet moved. We solved this initially by creating an endpoints on our Rails application and requested the data via http. This proved to be toilsome and complicated development and deploys, and therefore made it harder for teams to commit to building things in Haskell. In order to remove this hurdle, we started to think about reading data directly from our MySQL database. We really liked postgresql-typed, because it gave us the ability to write actual SQL queries and type-safety between the database and Haskell. Unfortunatlly, there isn’t a mysql-typed, but we found an interesting solution provided by Postgres itself; foreign-data-wrappers.

Foreign Data Wrappers

Foreign Data Wrappers (short fdw) offer a way to manage remote data directly within your Postgres database. This allows us to use postgresql-typed to access the MySQL data via Postgres.

CREATE SERVER mysql-schema-name
  FOREIGN DATA WRAPPER mysql_fdw
  OPTIONS (host '${host}', port '${port}');

-- Mapping for the application user, so it can perform queries.
CREATE USER MAPPING FOR ${username}
  SERVER mysql-schema-name
  OPTIONS (username '${username}', password '${passphrase}');

CREATE SCHEMA mysql-schema-name AUTHORIZATION ${username};

IMPORT FOREIGN SCHEMA ${dbname}
  FROM SERVER mysql-schema-name
  INTO mysql-schema-name;

Unfortunately, we quickly ran into problems when converting http requests to the Rails application with queries directly to MySQL. Fdw has some limitations that were blockers for use. The schema imported as a fdw is imported without any constraints other than NOT NULL. An even bigger problem was that some SQL constructs weren’t pushed down to MySQL. As an example the fdw wouldn’t forward LIMIT clauses and therefore grab all rows of a table, send them over the wire, and then apply the LIMIT in Postgres. This obviously has huge performance implications and meant that we needed to find a different solution without suffering type-safety.

Type-Safe MySQL Queries

We had three options; either find a library that would give us the guarantees we were used to from postgresql-typed for MySQL, building our own library or somehow abusing postgresql-typed. Building our own library wasn't an option because that would have exceeded the scope of this project, and we couldn't find a library that met our requirements. Fortunately, postgresql-typed allows us to access getQueryString to get the raw query string from the quasi-quoted query. This we can then execute with mysql-simple instead of postgresql-typed. This allows us to check MySQL and Postgres queries at compile time using a fdw, but connecting directly to Postgres and MySQL at runtime. Finally we can write queries to our MySQL database using the same API as for Postgres and having full type-safety between the database and Haskell.

cats :: Database.MySQL.Simple.Connection -> IO [Cat]
cats mySQLConnection = do
  rows <-
    Database.MySQL.Simple.query_
      mySQLConnection $ -- direct connection to MySQL
      remove "mysql-schema-name." $
      -- ^ The schemaname (fdw) only exists during compile time.
      getQueryString unknownPGTypeEnv
      [pgSql|!
        SELECT id, description, color
        FROM mysql-schema-name.cats
        ORDER BY id ASC
      |]
  pure (fmap catFromRow rows)

I’ve inlined the functions from postgresql-typed and mysql-simple to keep the code examples simple. We actually have two modules MySQL and Postgres that abstract the database specific functions away allowing us to use the exact same API for both databases.

Caveats

We need to prefix tables with the schema name that we used to import the fdw (see mysql-schema-name.cats in the example above). Without the prefix the compilation won't succeed. The problem is that we don't have a schema with this name at runtime. We can simply work around this by running Text.replace "mysql-schema-name." "" sqlString before executing the query. Queries need to use standard SQL features and shouldn't rely on MySQL specific features. This is actually kind of a feature, because it forces us to write queries in a way that simplifies converting them to Postgres later.

Conclusion

While this is a nice solution for us, I don't really recommend you to use MySQL in this way. This solution allows us to simplify the transition between Rails/MySQL and Haskell/Postgres. I would love for library like postgresql-typed to exist for MySQL, but it wasn't feasable to build such a thing. Honestly, I don't even know if it would be possible. I hope this post showed you how to create a nice experience for transitioning to a different technology.
Thanks for reading ❤️

Special thanks to everyone involved in building this:

• Ary
• Gavin
• Jasper
• Michael