The Project | About Us | Contribute | Donations | License

HOME

Course Content

In this section, we will explore how to interact with databases using Haskell. We will cover the following topics:

Introduction to Database Libraries in Haskell
Setting Up a Database Connection
Performing Basic CRUD Operations
Using Persistent Library
Practical Exercises

Introduction to Database Libraries in Haskell

Haskell offers several libraries for database access, including:

HDBC: A low-level database interface.
Persistent: A high-level ORM (Object-Relational Mapping) library.
Esqueleto: A SQL DSL (Domain Specific Language) for type-safe SQL queries.

For this tutorial, we will focus on the Persistent library due to its ease of use and powerful features.

Setting Up a Database Connection

Installing Dependencies

First, ensure you have the necessary dependencies installed. Add the following to your stack.yaml or cabal file:

dependencies:
- persistent
- persistent-sqlite
- persistent-template
- persistent-postgresql
- persistent-mysql

Configuring the Database

Create a file named database.hs and set up the database connection:

{-# LANGUAGE GADTs #-}
{-# LANGUAGE OverloadedStrings #-}
{-# LANGUAGE QuasiQuotes #-}
{-# LANGUAGE TemplateHaskell #-}
{-# LANGUAGE TypeFamilies #-}

import Database.Persist
import Database.Persist.Sqlite
import Database.Persist.TH
import Control.Monad.IO.Class (liftIO)
import Control.Monad.Logger (runStdoutLoggingT)
import Data.Text (Text)

share [mkPersist sqlSettings, mkMigrate "migrateAll"] [persistLowerCase|
User
    name Text
    age Int
    deriving Show
|]

main :: IO ()
main = runStdoutLoggingT $ withSqliteConn "test.db" $ \backend -> liftIO $ do
    runSqlConn (runMigration migrateAll) backend
    putStrLn "Database setup complete."

Explanation

Language Extensions: Enable necessary language extensions for Persistent.
Imports: Import required modules.
Entity Definition: Define a User entity with name and age fields.
Database Connection: Set up a SQLite connection and run migrations.

Performing Basic CRUD Operations

Creating a Record

Add the following function to database.hs to create a new user:

createUser :: Text -> Int -> IO ()
createUser name age = runStdoutLoggingT $ withSqliteConn "test.db" $ \backend -> liftIO $ do
    runSqlConn (insert_ (User name age)) backend
    putStrLn "User created."

Reading Records

Add a function to read users from the database:

getUsers :: IO ()
getUsers = runStdoutLoggingT $ withSqliteConn "test.db" $ \backend -> liftIO $ do
    users <- runSqlConn (selectList [] []) backend
    mapM_ (print . entityVal) users

Updating a Record

Add a function to update a user's age:

updateUserAge :: Text -> Int -> IO ()
updateUserAge name newAge = runStdoutLoggingT $ withSqliteConn "test.db" $ \backend -> liftIO $ do
    runSqlConn (updateWhere [UserName ==. name] [UserAge =. newAge]) backend
    putStrLn "User age updated."

Deleting a Record

Add a function to delete a user:

deleteUser :: Text -> IO ()
deleteUser name = runStdoutLoggingT $ withSqliteConn "test.db" $ \backend -> liftIO $ do
    runSqlConn (deleteWhere [UserName ==. name]) backend
    putStrLn "User deleted."

Using Persistent Library

Persistent Library Overview

The Persistent library provides a high-level interface for database operations. It abstracts away the SQL queries and allows you to work with Haskell data types directly.

Example: Full CRUD Operations

Here is a complete example that demonstrates all CRUD operations:

main :: IO ()
main = do
    -- Setup database
    runStdoutLoggingT $ withSqliteConn "test.db" $ \backend -> liftIO $ runSqlConn (runMigration migrateAll) backend

    -- Create a user
    createUser "Alice" 30

    -- Read users
    putStrLn "Users in database:"
    getUsers

    -- Update user age
    updateUserAge "Alice" 31

    -- Read users again
    putStrLn "Users after update:"
    getUsers

    -- Delete user
    deleteUser "Alice"

    -- Read users again
    putStrLn "Users after deletion:"
    getUsers

Practical Exercises

Exercise 1: Add a New Entity

Add a new entity Post with fields title (Text) and content (Text). Write functions to perform CRUD operations on Post.

Exercise 2: Query with Conditions

Write a function to retrieve all users older than a given age.

Exercise 3: Join Queries

Write a function to retrieve all posts by a specific user. (Hint: You may need to add a foreign key relationship between User and Post.)

Solutions

Solution 1: Add a New Entity

share [mkPersist sqlSettings, mkMigrate "migrateAll"] [persistLowerCase|
User
    name Text
    age Int
    deriving Show
Post
    title Text
    content Text
    userId UserId
    deriving Show
|]

createPost :: Text -> Text -> UserId -> IO ()
createPost title content userId = runStdoutLoggingT $ withSqliteConn "test.db" $ \backend -> liftIO $ do
    runSqlConn (insert_ (Post title content userId)) backend
    putStrLn "Post created."

Solution 2: Query with Conditions

getUsersOlderThan :: Int -> IO ()
getUsersOlderThan age = runStdoutLoggingT $ withSqliteConn "test.db" $ \backend -> liftIO $ do
    users <- runSqlConn (selectList [UserAge >. age] []) backend
    mapM_ (print . entityVal) users

Solution 3: Join Queries

getPostsByUser :: Text -> IO ()
getPostsByUser userName = runStdoutLoggingT $ withSqliteConn "test.db" $ \backend -> liftIO $ do
    users <- runSqlConn (selectList [UserName ==. userName] []) backend
    case users of
        [Entity userId _] -> do
            posts <- runSqlConn (selectList [PostUserId ==. userId] []) backend
            mapM_ (print . entityVal) posts
        _ -> putStrLn "User not found."

Conclusion

In this section, we covered how to set up a database connection, perform basic CRUD operations, and use the Persistent library in Haskell. We also provided practical exercises to reinforce the learned concepts. In the next section, we will explore testing and debugging in Haskell.

Database Access