sqlite-simple-interpolate: Interpolated SQLite queries via quasiquotation

[ bsd3, database, library ] [ Propose Tags ]

Modules

[Index] [Quick Jump]

Flags

Automatic Flags
NameDescriptionDefault
testsEnabled

Use -f <flag> to enable a flag, or -f -<flag> to disable that flag. More info

Downloads

Maintainer's Corner

Package maintainers

For package maintainers and hackage trustees

Candidates

  • No Candidates
Versions [RSS] 0.1, 0.1.1, 0.2.0.0
Change log CHANGELOG.md
Dependencies base (>=4.14 && <5), custom-interpolation (>=0.1 && <0.2), sqlite-simple (>=0.1), template-haskell (>=2.16 && <2.20) [details]
License BSD-3-Clause
Author ruby0b
Maintainer ruby0b
Category Database
Home page https://github.com/ruby0b/sqlite-simple-interpolate
Source repo head: git clone git://github.com/ruby0b/sqlite-simple-interpolate.git
Uploaded by ruby0b at 2023-01-13T02:40:56Z
Distributions NixOS:0.1.1
Downloads 73 total (26 in the last 30 days)
Rating (no votes yet) [estimated by Bayesian average]
Your Rating
  • λ
  • λ
  • λ
Status Docs uploaded by user
Build status unknown [no reports yet]

Readme for sqlite-simple-interpolate-0.2.0.0

[back to package description]

sqlite-simple-interpolate

Hackage Build Status License Hackage-Deps

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)|]