How to use PostgreSQL with Haskell: selda
Tags: haskell, functional, postgres, database
Published: 2023-10-03 03:32:13

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

[Selda](https://selda.link/) *“is a Haskell library for interacting with SQL-based relational databases”* (PostgreSQL or SQLite). *“The library was inspired by [LINQ](https://en.wikipedia.org/wiki/Language_Integrated_Query) and [Opaleye](http://hackage.haskell.org/package/opaleye).”*

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

Enable `OverloadedLabels`.

### How to connect to a database

Create connection info:

```haskell
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`:

```haskell
withPostgreSQL connectionInfo $ do
  doFoo
  doBar
```

### How to define tables

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

```haskell
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:

```haskell
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:

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

*See the [repo](https://github.com/Zelenya/elephants) 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`](https://hackage.haskell.org/package/selda-0.5.2.0/docs/Database-Selda.html#t: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`:

```haskell
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.

```haskell
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
```

```haskell
  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`:

```haskell
  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`:

```haskell
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:

```haskell
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`:

```haskell
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`:

```haskell
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:

```haskell
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:

```haskell
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`](https://hackage.haskell.org/package/selda-0.5.2.0/docs/Database-Selda.html#t:SeldaError):

```haskell
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](https://selda.link/tutorial/).

### Migrations

The library has a [`Migrations`](https://hackage.haskell.org/package/selda-0.5.2.0/docs/Database-Selda-Migrations.html) 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.