sqlite-simple-interpolate

Write natural SQL statements in Haskell using QuasiQuoters!

The QuasiQuoters support 3 methods of interpolation that can be mixed freely:

• {}: injection-safe field interpolation, e.g. {myName} gets replaced with ? which gets substituted with toField myName.
• @{}: injection-safe row interpolation, e.g. @{myPerson} gets replaced with (?,?) (assuming Person has two fields) which gets substituted with toRow myPerson.
• !{}: injection-vulnerable raw string interpolation. Never use this for user input! Intended for use cases that the anti-injection mechanisms won't allow, e.g. table names: !{myTableName} gets replaced with the value of myTableName :: String.

{-# LANGUAGE QuasiQuotes #-}

module Main where

import Control.Exception (bracket)
import Data.Char (toLower)
import Data.Function ((&))
import qualified Database.SQLite.Simple as SQL
import Database.SQLite.Simple.Interpolate

data Person = Person {name :: String, age :: Integer}

instance SQL.ToRow Person where
  toRow p = SQL.toRow (name p, age p)

table :: String
table = "people"

main :: IO ()
main = bracket (SQL.open ":memory:") SQL.close $ \conn -> do
  -- Create a table, interpolating safe string constants like table names with !{}
  conn & [iexecute|CREATE TABLE !{table} (name TEXT, age INTEGER)|]

  -- Insert a person, safely interpolating a field using {}
  let name = "clive"
  conn & [iexecute|INSERT INTO !{table} VALUES ({name}, 40)|]

  -- Insert a person, safely interpolating an entire row type using @{} (gets replaced with "(?,?)")
  let clara = Person {name = "clara", age = 25}
  conn & [iexecute|INSERT INTO !{table} VALUES @{clara}|]

  -- Use ifold to fold some rows into their sum in haskell
  ageHaskellSum <- conn & [ifold|SELECT age FROM !{table}|] 0 (\acc (SQL.Only x) -> pure (acc + x))
  print (ageHaskellSum :: Int)

  -- Let's calculate the average age of people that are at least 20 years old
  let minAge = 20 :: Int
  [ageAvg] <- conn & [iquery|SELECT AVG(age) FROM !{table} WHERE age >= {minAge}|]
  print (ageAvg :: SQL.Only Double)

  -- You can always use 'isql' directly but you'll have to use uncurry:
  (uncurry $ SQL.execute conn) [isql|INSERT OR REPLACE INTO !{table} VALUES ({name}, 41)|]