How to use PostgreSQL with Haskell: selda

Okay, what if we did something quite similar but quite different?

Selda “is a Haskell library for interacting with SQL-based relational databases” (PostgreSQL or SQLite). “The library was inspired by LINQ and Opaleye.”

Install selda (0.5.2.0 released in 2022) and selda-postgresql.

Enable OverloadedLabels.

How to connect to a database

Create connection info:

connectionInfo :: PGConnectInfo
connectionInfo =
  PGConnectInfo
    { pgHost = Hardcoded.host
    , pgPort = Hardcoded.portNumber
    , pgDatabase = Hardcoded.database
    , pgUsername = Just Hardcoded.user
    , pgPassword = Just Hardcoded.password
    , pgSchema = Nothing
    }

And use it with withPostgreSQL:

withPostgreSQL connectionInfo $ do
  doFoo
  doBar

How to define tables

First, we declare normal types and derive SqlRow, for example, for product:

data Product = Product
  { id :: ID Product
  , label :: Text
  , description :: Maybe Text
  }
  deriving (Generic, Show)
  deriving anyclass (SqlRow)

Then, we use table to declare a table:

productTable :: Table Product
productTable = table "product" [#id :- autoPrimary]

We specify constraints by linking selectors of the table to the definitions. We use autoPrimary for auto-incrementing primary keys, primary for regular primary keys, and foreignKey for foreign keys:

mappingTable :: Table ProductCategory
mappingTable =
  table
    "product_category"
    [ #product_id :- foreignKey productTable #id
    , #category_id :- foreignKey categoryTable #id
    ]

See the repo for the rest of the boilerplate.

How to modify data

We can use rawStm from Database.Selda.Unsafe to execute raw queries:

cleanUp :: SeldaM PG ()
cleanUp =
  rawStm "truncate warehouse, product_category, product, category"

SeldaM is an alias for SeldaT IO, SeldaT is a Selda computation — a concrete implementation (of MonadSelda) with Selda SQL capabilities.

At the end we’ll turn it into IO:

withPostgreSQL connectionInfo $ do
  cleanUp

To insert data, we can use insert_ that doesn’t return anything, insert that returns the number of inserted rows, and insertWithPK that returns the primary key of the last inserted row.

insertStuff :: SeldaM PG ()
insertStuff = do
  productId <-
    insertWithPK
      productTable
      [ Product def "Wood Screw Kit 1" (Just "245-pieces")
      , Product def "Wood Screw Kit 2" Nothing
      ]
  liftIO $ putStrLn $ "Inserted product with id: " <> show productId

  rows <-
    insert
      categoryTable
      [Category def "Screws", Category def "Wood Screws", Category def "Concrete Screws"]
  liftIO $ putStrLn $ "Inserted categories: " <> show rows

We use def when we want to use the default value, which is the case with ids.

How to query data

We can get all the rows from the given table using select:

  selectProduct :: Query t (Row t Product)
  selectProduct = select productTable

💡 Note that we can use compile from Database.Selda.Debug to print sql queries that will be executed.

And execute the query with query:

queryData :: SeldaT PG IO ()
queryData = do
  result1 <- query selectProduct
  liftIO $ putStrLn $ "Query 1: " <> show result1

🤷 Somehow, here, Selda didn’t want to read/parse back the ids it just inserted:

elephants-exe: [SELDA BUG] fromSql: RowID column with non-int value: SqlInt32...

If we change the type from ID Foo to Int32, the select works, but then insert with auto-incremental primary keys and other functionality doesn’t 🤷

So let’s ignore this for now because other queries work fine.

We can select specific fields and narrow down the results:

select2 :: Query t (Col t Text :*: Col t (Maybe Text))
select2 = do
  p <- selectProduct
  restrict (p ! #label .== "Wood Screw Kit 2")
  pure (p ! #label :*: p ! #description)

Query is parameterized over a scope parameter t, ensuring that queries are always well-scoped, but we don’t have to worry about it now.

We use ! with selectors to extract a column, restrict to filter the rows, and .== to compare for equality. A result is an inductive tuple — one or more values separated by the :*: data constructor.

We can also use isIn:

select3 = do
  p <- selectProduct
  restrict (p ! #label `isIn` ["Wood Screw Kit 2", "Wood Screw Kit 3"])
  pure (p ! #label)

How to use transactions

We use transaction:

insertWithTransaction :: SeldaT PG IO ()
insertWithTransaction = transaction $ do
  productId <- insertWithPK productTable [Product def "Drywall Screws Set" (Just "8000pcs")]
  categoryId <- insertWithPK categoryTable [Category def "Drywall Screws"]
  insert_ mappingTable [ProductCategory productId categoryId]
  insert_ warehouseTable [Warehouse def productId 10 def def]
  liftIO $ putStrLn $ "Insert with transaction"

How to query using joins

We use restrict and leftJoin to query with joins:

join :: Query s (Col s Int32 :*: (Col s Text :*: (Col s (Maybe Text) :*: Col s (Coalesce (Maybe Text)))))
join = do
  w <- select warehouseTable
  p <- select productTable
  restrict (w ! #product_id .== p ! #id)

  pc <- leftJoin (\pc -> pc ! #product_id .== p ! #id) (select mappingTable)
  c <- leftJoin (\c -> just (c ! #id) .== pc ? #category_id) (select categoryTable)

  pure (w ! #quantity :*: p ! #label :*: p ! #description :*: c ? #label)

We use ? to extract a column from the nullable row.

The generated query:

SELECT 
  "quantity_2", 
  "label_6", 
  "description_7", 
  "label_13_15" 
FROM 
  (
    SELECT 
      "id_12_14", 
      "label_13_15", 
      "category_id_9_11", 
      "label_6", 
      "description_7", 
      "quantity_2" 
    FROM 
      (
        SELECT 
          "product_id_8_10", 
          "category_id_9_11", 
          "id_5", 
          "label_6", 
          "description_7", 
          "quantity_2" 
        FROM 
          (
            SELECT 
              "id_5", 
              "label_6", 
              "description_7", 
              "product_id_1", 
              "quantity_2" 
            FROM 
              (
                SELECT 
                  "product_id" AS "product_id_1", 
                  "quantity" AS "quantity_2" 
                FROM 
                  "warehouse"
              ) AS q0, 
              (
                SELECT 
                  "id" AS "id_5", 
                  "label" AS "label_6", 
                  "description" AS "description_7" 
                FROM 
                  "product"
              ) AS q1 
            WHERE 
              ("product_id_1" = "id_5")
          ) AS q3 
          LEFT JOIN (
            SELECT 
              "product_id_8" AS "product_id_8_10", 
              "category_id_9" AS "category_id_9_11" 
            FROM 
              (
                SELECT 
                  "product_id" AS "product_id_8", 
                  "category_id" AS "category_id_9" 
                FROM 
                  "product_category"
              ) AS q2
          ) AS q4 ON "product_id_8_10" = "id_5"
      ) AS q6 
      LEFT JOIN (
        SELECT 
          "id_12" AS "id_12_14", 
          "label_13" AS "label_13_15" 
        FROM 
          (
            SELECT 
              "id" AS "id_12", 
              "label" AS "label_13" 
            FROM 
              "product"
          ) AS q5
      ) AS q7 ON (
        Cast("id_12_14" AS INT)
      ) = "category_id_9_11"
  ) AS q8

Errors

From Selda’s tutorial: “While the types keep queries nice and safe, Haskell's type errors can be a bit daunting even under the best circumstances.” In practice, type inference rarely needed guidance, and the compilation errors were relatively clear.

The only problem we’ve encountered was the mismatch of ID and SqlInt32.

All Selda functions may throw SeldaError:

errors :: SeldaM PG ()
errors = do
  insertDuplicateScrew
  insertDuplicateScrew
    `catch` (\(err :: SeldaError) -> liftIO $ putStrLn $ "Caught Selda Error: " <> displayException err)
 where
  insertDuplicateScrew = insert_ productTable [Product def "Duplicate screw" Nothing]

elephants-exe: SqlError "error executing query INSERT INTO \"product_category\" (\"product_id\", \"category_id\") VALUES ($1, $2)': ERROR: insert or update on table \"product_category\" violates foreign key constraint \"product_category_category_id_fkey\"\nDETAIL: Key (category_id)=(748) is not present in table \"category\".\n"

Resources

Selda comes with a simple overview and example. There is also a tutorial.

Migrations

The library has a Migrations module for upgrading a table from one schema to another.

In summary

Selda allows us to write type-safe queries in a linear, natural style.

Depending on your experience and situation, you can use SeldaM straight, or you may need to get familiar with mtl, exceptions, lifting/unlifting IO, etc.

Blog