Extending Esqueleto for Type-Safe Postgresql Queries

It's no great secret that I'm not a huge fan of SQL DSLs. They tend to be great for the simple stuff, but they break down very quickly as soon as you try to do anything non-trivial. So it's a bit odd that I find myself picking up a Haskell one - Esqueleto.

Why am I risking heartbreak? Well, because as long as I can stay on the Persistent/Esqueleto path, I'll get DB migrations for free, and compile-time integrity checks that can cover my database, webserver and frontend in one pass.

That kind of compile-time checking - for almost-free - is not to be sniffed at. If I change a datastructure, I know that the all the server code, all the UI code and the complete database schema are in sync. I don't think I've ever worked on a project that's achieved that dream, so it's worth a little perseverance.

As predicted, Esqueleto broke down for me pretty quickly. But it got back up off the floor quickly too, and that's the point of this post: Let's see how we can get a typesafe database query that uses SQL features the library authors didn't consider...

My use-case, I confess, is weird. But that's sort of the point - this is stuff the authors wouldn't have foreseen. I am importing some data from a shoddy source, and then cleaning it up later. So I already had this SQL statement:

  UPDATE product
  SET description = trim(chr(160) from description)
  WHERE description != trim(chr(160) from description);

For some reason $DATA_SUPPLIER is sticking weird characters around the description, and we didn't catch it on import. We want to update any products that have the magic character chr(160) stuck on them.

Let's see that update statement naively ported to Esqueleto:

  tidyDescriptions :: SqlPersistM ()
  tidyDescriptions =
    update $
    \product -> do set product [ProductDescription =. trimDescription product]
                   where_ ((product ^. ProductDescription) !=. (trimDescription product))
    where trimDescription product =
            (trim (chr (val 160))
                  (product ^. ProductDescription))

The syntax is a little funky, but even if you don't really read Haskell you can probably see how it's trying to say the same thing as the SQL above.

Unfortunately, Esqueleto has already broken down on us: trim and chr are apparently too Postgres-specific, and aren't supported.

So what shall we do? Break out a horrible, string-guzzling rawSql function, losing type safety and opening up SQL Injection vectors? No! We just teach Esqueleto about these functions. First chr:

chr :: (IsString s)
    => SqlExpr (Value Int) -> SqlExpr (Value s)
chr = unsafeSqlFunction "chr"

This code basically reads as, "There's a function called =chr= that takes an integer and returns a string, both in a SQL-ish context. Trust me. There really is."

And then:

trim :: (IsString s)
     => SqlExpr (Value s) -> SqlExpr (Value s) -> SqlExpr (Value s)
trim pattern target =
  unsafeSqlFunction "trim"
                    (unsafeSqlBinOp "FROM" pattern target)

Which reads as, "There's a function called trim that takes two strings and has weird syntax, but if you call it this way you'll get a string back. Honest."

With that, our update statement works. We don't get type-safety within these functions - Haskell is just blindly taking our promise that these types are correct - but I can live with that. This is code we'll write once and never change. The important thing is that every use of these new functions get the guarantees we're looking for, and they were pretty trivial to add.

So an SQL DSL that doesn't abandon you to DSHell. Cautiously, I think I could make friends here...

Tweets by krisajenkins