Composable SQL snippet with parameters injected. Abstracts over placeholders and matching of encoders.

It has an instance of IsString, so having the OverloadedStrings extension enabled you can construct it directly from string literals.

Here's an example:

selectSubstring :: Text -> Maybe Int32 -> Maybe Int32 -> Snippet
selectSubstring string from to =
  "select substring(" <> param string <>
  foldMap (\ x -> " from " <> param x) from <>
  foldMap (\ x -> " for " <> param x) to <>
  ")"

Having a decoder you can lift it into Statement using toStatement or directly execute it in Session using toSession.

Compile a snippet into SQL text with placeholders.

Construct a statement dynamically, specifying the parameters in-place in the declaration of snippet and providing a result decoder and specifying whether the statement should be prepared.

The injection of the parameters is handled automatically, generating parametric statements with binary encoders under the hood.

This is useful when the SQL of your statement depends on the parameters. Here's an example:

selectSubstring :: Text -> Maybe Int32 -> Maybe Int32 -> Statement () Text
selectSubstring string from to = let
  snippet =
    "select substring(" <> Snippet.param string <>
    foldMap (mappend " from " . Snippet.param) from <>
    foldMap (mappend " for " . Snippet.param) to <>
    ")"
  decoder = Decoders.singleRow (Decoders.column (Decoders.nonNullable Decoders.text))
  in toStatement snippet decoder

Without the Snippet API you would have had to implement the same functionality thus:

selectSubstring' :: Text -> Maybe Int32 -> Maybe Int32 -> Statement () Text
selectSubstring' string from to = let
  sql = case (from, to) of
    (Just _, Just _) -> "select substring($1 from $2 to $3)"
    (Just _, Nothing) -> "select substring($1 from $2)"
    (Nothing, Just _) -> "select substring($1 to $2)"
    (Nothing, Nothing) -> "select substring($1)"
  encoder =
    Encoders.param (string >$ Encoders.text) <>
    foldMap (\ x -> Encoders.param (x >$ Encoders.int8)) from <>
    foldMap (\ x -> Encoders.param (x >$ Encoders.int8)) to
  decoder = Decoders.singleRow (Decoders.column (Decoders.nonNullable Decoders.text))
  in Statement.unpreparable sql encoder decoder

As you can see, the Snippet API abstracts over placeholders and matching encoder generation, thus also protecting you from all sorts of related bugs.

Execute in Session.Session, providing a result decoder.

This is merely a shortcut defined thus:

toSession snippet decoder =
  Session.statement () (Snippet.toStatement snippet decoder)

Execute in Pipeline.Pipeline, providing a result decoder.

This is merely a shortcut defined thus:

toPipeline snippet decoder =
  Pipeline.statement () (Snippet.toStatement snippet decoder)

Parameter encoded using an implicitly derived encoder from the type.

Parameter with an explicitly defined encoder.

SQL chunk.