Sirocco, scirocco, /sɪˈrɒkoʊ/, jugo or, rarely, siroc (Catalan: Xaloc, Greek: Σορόκος, Spanish: Siroco, Occitan: Siròc, Eisseròc) is a Mediterranean wind that comes from the Sahara and can reach hurricane speeds in North Africa and Southern Europe.
— Wikipedia

Sirocco is a small database abstraction on top of jdbc.

1. Connection

To create a connection:

{--
    This module configures the database connection config
-}
module songs.db.Db where

import sirocco.JDBC (Connection)
import sirocco.Sirocco (createConnection)

databaseURI = "jdbc:h2:~/test" (1)

--- Creates a new connection
connection :: IO Connection
connection = createConnection databaseURI "sa" "" (2)
1 String representing the jdbc uri
2 sirocco.Sirocco.createConnection function to connect to the database

2. Query

To perform a prepared statement query you should be using the function Sirocco.query:

query :: IO Connection -> Maybe String -> [Maybe SqlParam] -> IO [CatchAll [SqlValue]]

The function receives:

  • Connection

  • Query

  • Parameters

And returns a list of records of type CatchAll [SqlValue]. A CatchAll type is an alias for Either Throwable and [SqlValue] represents every column of each row. So basically we will be expecting a list of rows containing list of column values.

Lets see a SELECT example:

findAll :: IO  [Model.Band]
findAll = mapToBands select
  where sql    = Just "SELECT * FROM bands"  (1)
        params = [] :: [Maybe SqlParam]      (2)
        select = query connection sql params (3)
1 Defining the SQL query
2 Defining the list of params (empty this time)
3 Executing the query

After executing the query we will be mapping the result to another datatatype.

2.1. Mapping results

In the previous example we were mapping our result to a list of music bands. Sirocco has a helper method to map a given database row to a defined user data type.

mapRowsTo :: ([SqlValue] -> a) -> IO [CatchAll [SqlValue]] ->  IO [a]

Basically you need to define a function converting from a given row (strictly speaking a list of columns of a given row) to another type.

In our band example this is how it was implemented:

--- Maps a list of rows to Model.Band instances
mapToBands :: IO [CatchAll [SqlValue]] -> IO [Model.Band]
mapToBands = mapRowsTo toBand

--- Maps a single row to a Model.Band instances
toBand :: [SqlValue] -> Model.Band
toBand ((SqlLong id):(SqlString name):(SqlLong year):[]) = Model.Band { id = id, name = name, year = year }
toBand x = error $ "Error while parsing Band" ++ show x

2.2. Parameters

A param is of type Sirocco.SqlParam, that’s why we can pass a list of parameters to our query, if we were using simple types, Frege wouldn’t allow us to create a list of different types.

class ToParam a where
  toParam :: Maybe a -> Maybe SqlParam

In order to convert a simple type to a SqlParam we can use the different implemented instances of class type Sirocco.ToParam. In the following example we would like to convert a string to a valid param:

--- Finds a specific band by id
findById :: Maybe String -> IO (Maybe Model.Band)
findById id = getFirst bands
  where
    sql    = Just "SELECT * FROM bands WHERE id = ?"
    params = toParam <$> [id]
    bands  = mapToBands (query connection sql params)
In the example you can also say [toParam id]. You will be using this, specially when trying to create params combining different types, e.g: `[(toParam . Just) 1, (toParam . Just) "Metallica"]

Here’s another example:

findAllByYears :: Maybe Int -> Maybe Int -> IO [Model.Band]
findAllByYears from to = bands
  where
    sql    = Just "SELECT * FROM bands WHERE year >= ? and year <= ?" (1)
    params = toParam <$> [from, to] (2)
    result = query connection sql params (3)
    bands  = mapToBands result (4)
1 Defining a query with two parameters
2 Creating required parameters
3 Executing query
4 Mapping result

3. Execute

Sirocco.execute can be used when defining database structures (create, drop, alter…​):

execute :: IO Connection -> Maybe String -> IO Bool

It only returns a Bool value indicating whether the action was a success or a failure.

--- Drops the Bands table
dropBandTable :: IO ()
dropBandTable = do
  records <- execute connection $ Just "DROP TABLE bands"
  println $ "Table bands deleted"

--- Creates the Bands table
createBandTable :: IO ()
createBandTable = do
  stmt   <- execute connection $ Just "CREATE TABLE bands (id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, name TEXT NOT NULL, year INTEGER NOT NULL)"
  println $ "Table bands created"

4. Update

Sirocco.update can be used when manipulating the database (delete, insert, update):

update :: IO Connection -> Maybe String -> [Maybe SqlParam] -> IO Int

It will return the number of rows affected for the SQL statement.

deleteBandById :: Maybe Int -> IO Int
deleteBandById id = result
  where
    sql    = Just "DELETE FROM bands WHERE id = ?" (1)
    params = toParam <$> [id] (2)
    result = update connection sql params (3)
1 Query
2 Params
3 Query execution

Many times if your table has an auto-generated primary key, it comes handy to know the primary key of the record you have just inserted. For this you can use the following function:

insertAndReturnGeneratedKey :: IO Connection -> Maybe String -> [Maybe SqlParam] -> IO Long