Lesson 41. Using databases in Haskell
After reading lesson 41, you’ll be able to
- Connect to a SQLite database from Haskell
- Translate SQL rows into Haskell data types
- Create, read, update, and delete database data with Haskell
In this lesson, you’ll learn how to work with databases when using Haskell. In particular, you’ll use the SQLite3 relational database management system (RDBMS) and the sqlite-simple Haskell library. You’ll explore this topic by building a command-line interface for a tool-lending library. This will require you to perform all of the essential CRUD tasks commonly associated with RDBMS use. The CRUD tasks are as follows:
- Create—Adding new data to the database
- Read—Querying the database for information
- Update—Modifying existing data in the database
- Delete—Removing data from the database
You’ll use the sqlite-simple library to interact with your database. sqlite-simple is a mid-level abstraction over the database, meaning that while many of the low-level connection details are abstracted away, you’ll still be writing a large amount of raw SQL queries. The most important abstraction that sqlite-simple helps with is transforming SQL queries into lists of Haskell data types.
Here’s the rundown of the project you’ll be building. Suppose you have a friend who’s setting up a basic community tool-lending library. Your friend needs a system to help manage the basics of keeping track of the inventory and checking out tools from the library. From the RDBMS side of things, this project will involve three tables: tools, users, and checkedout. From the Haskell standpoint, you’ll worry about modeling only users and tools. By the end of this lesson, you’ll have a command-line application that supports the following operations:
- Listing all of the users and tools
- Listing all the checked-out and available tools
- Adding new users to the database
- Checking items back in
- Recording the number of times a tool has been checked out, and how recently
By the end of this lesson, we’ll have covered performing the majority of database operations through Haskell.
41.1. Setting up your project
In this lesson, you’ll keep all of your code in a stack project named db-lesson. As in all the lessons in this unit, you’ll keep your code in the Main module for simplicity (though this project could easily be refactored into multiple files). You’ll start with your app/Main.hs file. Here’s the starter code, including the imports you’ll use for this lesson.
Listing 41.1. The app/Main.hs file starter code
module Main where import Control.Applicative import Database.SQLite.Simple 1 import Database.SQLite.Simple.FromRow 2 import Data.Time 3 main :: IO () main = print "db-lesson"
- 1 This is the library you’ll use to work with SQLite.
- 2 FromRow is an important type class you’ll need.
- 3 You’ll also explore using types of dates in this lesson.
The build-depends you need to add to the project’s .cabal file is sqlite-simple, the module you’ll be using to interact with SQLite, and time, which will help you manage dates.
Listing 41.2. The changes to build-depends in db-lesson.cabal
build-depends: base
, db-notes
, time
, sqlite-simple
You’ll also use the OverloadedStrings extension, because many of your strings will be interpreted as SQL queries by the SQLite Simple library.
Listing 41.3. Use the OverloadedStrings extension in db-lesson.cabal
extensions: OverloadedStrings
After all of this code is in place, you can run the setup and build commands in stack to ensure that your project is set up correctly.
41.2. Using SQLite and setting up your database
For this lesson, you’ll use the SQLite3 database management system, because it’s easy to install and get started with. SQLite can be downloaded from www.sqlite.org. Because SQLite3 is designed to be easy to deploy, setup should be straightforward on your system (if you don’t already have it installed).
Your tool library will use a database that consists of a users table, a tools table, and a checkedout table that represents which tools are checked out to which person. Figure 41.1 provides a basic entity-relationship diagram to show how your tables are set up.
Figure 41.1. The setup of your database
You’ll start your project off with sample data as well. Here’s the code to build your database; this code will go in a file named build_db.sql, which should be kept in the root directory of your db-lesson project.
Listing 41.4. The code for building your database
DROP TABLE IF EXISTS checkedout;
DROP TABLE IF EXISTS tools;
DROP TABLE IF EXISTS users;
CREATE TABLE users (
id INTEGER PRIMARY KEY,
username TEXT
);
CREATE TABLE tools (
id INTEGER PRIMARY KEY,
name TEXT,
description TEXT,
lastReturned TEXT, 1
timesBorrowed INTEGER
);
CREATE TABLE checkedout (
user_id INTEGER,
tool_id INTEGER
);
INSERT INTO users (username) VALUES ('willkurt');
INSERT INTO tools (name,description,lastReturned,timesBorrowed)
VALUES ('hammer','hits stuff','2017-01-01',0);
INSERT INTO tools (name,description,lastReturned,timesBorrowed)
VALUES ('saw','cuts stuff','2017-01-01',0);
- 1 SQLite doesn’t support date types.
To run SQLite, you have to call sqlite3 from the command line. You also need to pass in the database name, which is tools.db. Finally, you’ll pipe in the build.sql file you just saved. Here’s the command you need:
$ sqlite3 tools.db < build_db.sql
You can check out your database by using the sqlite3 command along with the path to your database file. Once there, you can run SQL queries to ensure that everything has been installed correctly. The sqlite> prompt indicates that you’re using your RDBMS interactively:
$ sqlite3 tools.db sqlite> select * from tools; 1|hammer|hits stuff|2017-01-01|0 2|saw|cuts stuff|2017-01-01|0
Because your goal is to work with SQLite through Haskell, you won’t be directly using the sqlite command again. If you like, you can always use sqlite directly to double-check the results of the code you’ve run.
41.2.1. Your Haskell data
One of the challenges when working with Haskell and an RBDMS such as SQLite is that your types in Haskell are typically much richer and more expressive than those in the RBDMS. SQLite, for example, doesn’t support any types representing dates. This is a similar problem to what you have working with JSON data. Before diving into creating data in your database, let’s look at your data from the Haskell perspective. Here’s the definition of your Tool data type.
Listing 41.5. The definition of Tool, which should live in app/Main.hs
data Tool = Tool
{ toolId :: Int
, name :: String
, description :: String
, lastReturned :: Day
, timesBorrowed :: Int
}
There’s one type you haven’t seen before in this definition of Tool, which is the Day type. The Day type is part of the Data.Time module. Data.Time contains a variety of useful time-related functions. Here’s an example of getting the current time with getCurrentTime and transforming it into a Day type by using utctDay:
GHCi> getCurrentTime 2017-02-26 07:05:12.218684 UTC GHCi> utctDay <$> getCurrentTime 2017-02-26
The other type you need to model is your User. The User type is much simpler than your Tool type, having values only for its id and userName.
Listing 41.6. The User data type
data User = User
{ userId :: Int
, userName :: String
}
The User and Tool types enable you to perform computation on any data that comes from your database. The most common computation will be displaying data about the users and tools you query. You can make sure the results look as you prefer by making these types instances of Show.
Listing 41.7. Making User and Tool instances of Show
instance Show User where
show user = mconcat [ show $ userId user
, ".) "
, userName user]
instance Show Tool where
show tool = mconcat [ show $ toolId tool
, ".) "
, name tool
, "\n description: "
, description tool
, "\n last returned: "
, show $ lastReturned tool
, "\n times borrowed: "
, show $ timesBorrowed tool
, "\n"]
When you print your results, they should look like this:
1.) willkurt 1.) hammer description: hits stuff last returned: 2017-01-01 times borrowed: 0
Now you’re ready to start interacting with your database!
Why is mconcat preferred for combining your strings over ++?
- Fewer characters to type
- ++ works only on lists, not text
- mconcat makes your code easier to refactor with text types
The answer is 3. Although you used String in this lesson, you often would prefer to use the Text type. mconcat works on all major string types: String, Text, and ByteString. This makes refactoring code to change types as easy as changing type signatures.
41.3. Creating data—inserting users and checking out tools
Of the four operations covered under CRUD, the first is Create. You’ve just used raw SQL to create your tables and data; now you’re going to do this in Haskell. To insert data into your database, you need to connect to the database, create a SQL string, and then execute the SQL.
41.3.1. Adding new users to your database
At this point, you have only one user in your database. You need a command to add a user. You’ll start with a function that takes a userName and inserts it into your database. To do this, you’ll use the execute command, which allows you to insert users into your database. Your query string will contain a (?), which allows you to safely pass values into your string. Before you can insert the user, you also need to establish a connection to your database. This connection is then passed along with your query and query parameters to the execute action. Here’s your addUser action.
Listing 41.8. addUser action connects to database and inserts a user
addUser :: String -> IO ()
addUser userName = do
conn <- open "tools.db" 1
execute conn "INSERT INTO users (username) VALUES (?)" 2
(Only userName) 3
print "user added"
close conn 4
- 1 First you have to open a connection to your database.
- 2 You execute a command by using the connection, a query, and query parameters.
- 3 The Only constructor is used for your single-element tuple of parameters.
- 4 It’s important to close the connection when you’re finished.
The Only constructor is used to create single-element tuples. This is needed because execute expects you to pass in a tuple of a particular size for your values.
This is a good start, but most of your code will have to access the database, so the bulk of this IO action will need to be repeated verbatim. You can abstract this out by creating a withConn action to automatically handle opening and closing your database.
Listing 41.9. withConn lets you abstract out connecting to the database
withConn :: String -> (Connection -> IO ()) -> IO () withConn dbName action = do conn <- open dbName action conn close conn
This function takes a String, which is the name of the database, and an action that takes a connection as an argument. The end result is an action of type IO (). You’ve successfully made it possible to add users to the database from Haskell.
addUser :: String -> IO ()
addUser userName = withConn "tools.db" $
\conn -> do
execute conn "INSERT INTO users (username) VALUES (?)"
(Only userName)
print "user added"
41.3.2. Creating checkouts
Another useful insert to perform is to create a checkout. A checkout requires both a userId and a toolId. The code for your checkout is similar to that of adding a user, but you need to pass in two values.
Listing 41.10. Checking out by adding the toolId and userId to checkedout
checkout :: Int -> Int -> IO ()
checkout userId toolId = withConn "tools.db" $
\conn -> do
execute conn
"INSERT INTO checkedout
(user_id,tool_id) VALUES (?,?)"
(userId,toolId)
Notice that (userId,toolId) is a plain tuple with no Only data constructor needed.
With checkout and addUser, you have the basis for many of the key operations you want your application to perform. You can test these actions, but you have no way of seeing whether your results worked without opening SQLite to see if the database has changed. In the next section, you’ll look at reading data from your database and transforming it into your Haskell data types.
41.4. Reading data from the database and FromRow
The challenge when working with SQL data in Haskell is that you need an easy way to make instances of Haskell data types from raw data. To achieve this, the sqlite-simple library includes a type class called FromRow. Here’s the definition of FromRow, which contains only one method you need to implement, fromRow.
Listing 41.11. The definition of the FromRow type class
class FromRow a where fromRow :: RowParser a
The fromRow method returns a RowParser of type a, where a is the same type as whatever type you’re making an instance of FromRow. You won’t directly use fromRow, but it will be used by functions to query your data. The result is that if you implement FromRow, you can easily transform queries into lists of your data types.
41.4.1. Making your data an instance of FromRow
Creating an instance of FromRow is similar to creating an instance of FromJSON in the previous lesson. You have to tell the RowParser how to construct your data types. The key part is a function from SQLite.Simple called field. The field function is used internally by SQLite.Simple to consume the data from a row and transform it into the values used by your type constructors. Here are the instances of FromRow for both User and Tool.
Listing 41.12. Making User and Tool instances of FromRow
instance FromRow User where
fromRow = User <$> field
<*> field
instance FromRow Tool where
fromRow = Tool <$> field
<*> field
<*> field
<*> field
<*> field
Now that User and Tool are both instances of FromRow, you can make queries against your database and translate them directly into lists of users and tools.
41.4.2. Listing users and tools
To query your data, you use two related functions: query and query_ (notice the underscore). Looking at their type signatures, you can see the difference:
query :: (FromRow r, ToRow q) => Connection -> Query -> q -> IO [r] query_ :: FromRow r => Connection -> Query -> IO [r]
The type signatures of these two functions are the same, except the underscore version takes one less argument. The query function assumes that you’re passing in a query string and parameter for that query. The query_ function with an underscore is for queries that take queries with no parameters as arguments. Also notice that Query is its own type. You’ve been treating your queries as strings, but this is all thanks to the OverloadedStrings extension, which is automatically translating for you.
Why do you need two functions, query and query_?
Primarily because of the way Haskell handles types, Haskell doesn’t support variable arguments. An alternative to making two functions is to use a Sum type that represents both sets of arguments and use pattern matching.
You’ll use these queries to print your users and tools. Here’s a printUsers function; notice that you must specify the type you’re querying.
Listing 41.13. Printing users from your database
printUsers :: IO ()
printUsers = withConn "tools.db" $
\conn -> do
resp <- query_ conn "SELECT * FROM users;" :: IO [User]
mapM_ print resp
The printUsers action also takes advantage of the fact that User is an instance of Show, displaying the user data as you expect. Now that you can print users, you can also test adding users:
GHCi> printUsers 1.) willkurt GHCi> addUser "test user" "user added" GHCi> printUsers 1.) willkurt 2.) test user
The next thing you want to do is print tools. The only added complication you have with tools is that you’d like to run several queries:
- List all of your tools
- List the checked-out tools
- List the available tools
To help you out, you’ll write a printToolQuery function that takes a query and prints out the tools returned by that query. Here’s the printTool query along with the code for the other queries’ lists.
Listing 41.14. A generic way to run any queries of tools from your database
printToolQuery :: Query -> IO ()
printToolQuery q = withConn "tools.db" $
\conn -> do
resp <- query_ conn q :: IO [Tool]
mapM_ print resp
printTools :: IO ()
printTools = printToolQuery "SELECT * FROM tools;"
printAvailable :: IO ()
printAvailable = printToolQuery $ mconcat [ "select * from tools "
, "where id not in "
, "(select tool_id from
checkedout);"]
printCheckedout :: IO ()
printCheckedout = printToolQuery $
mconcat [ "select * from tools "
, "where id in "
, "(select tool_id from checkedout);"]
In GHCi, you can test these actions as well as make sure that your checkout action from earlier works as expected:
GHCi> printTools 1.) hammer description: hits stuff last returned: 2017-01-01 times borrowed: 0 2.) saw description: cuts stuff last returned: 2017-01-01 times borrowed: 0 GHCi> checkout 1 2 GHCi> printCheckedOut 2.) saw description: cuts stuff last returned: 2017-01-01 times borrowed: 0
Two major steps are left until you can put together your project. You need to be able to check tools back in, and after those tools are back in, you need to update them. Because updating is next in your CRUD process, you’ll look at updating your data.
41.5. Updating existing data
When a tool is checked back in, you want to do two updates. First, you want to increment its existing timesBorrowed by 1; second, you want to update the lastReturned date to the current date. This requires you to update an existing row in your database, which is the most complex step in your process if you want to ensure that you avoid errors.
The first thing you want to do is select a tool from your database by ID. Your selectTool function takes a connect and a toolId to look up the tool. It returns a rather complicated type: IO (Maybe Tool). The IO indicates that your database operations always occur in an IO context. The inner Maybe type is used because it’s possible to pass in an incorrect ID, resulting in an empty query. Here’s the code for selectTool, along with a helper function firstOrNothing.
Listing 41.15. Safely selecting a Tool by ID
selectTool :: Connection -> Int -> IO (Maybe Tool)
selectTool conn toolId = do
resp <- query conn
"SELECT * FROM tools WHERE id = (?)"
(Only toolId) :: IO [Tool]
return $ firstOrNothing resp
firstOrNothing :: [a] -> Maybe a
firstOrNothing [] = Nothing
firstOrNothing (x:_) = Just x
The firstOrNothing function looks at the list of results returned by your query. If the list is empty, it returns Nothing; if you have results (presumably just one, because the ID is unique), it returns the first one.
After you have your tool, you need to update it. Getting the current day requires an IO action, so you’ll assume that value is passed in to your function so you can keep your updateTool function pure. The updateTool function takes an existing tool and returns a new tool with an updated lastReturned date and timesBorrowed count, using record syntax (lesson 11).
Listing 41.16. updateTool updates your tool
updateTool :: Tool -> Day -> Tool
updateTool tool date = tool
{ lastReturned = date
, timesBorrowed = 1 + timesBorrowed tool
}
Next you need a way to insert your update of a Maybe Tool. Because the tool is a Maybe Tool, you want your code to update your table only if the Maybe value isn’t Nothing. Your updateOrWarn action will tell you that the item isn’t found if the value is Nothing; otherwise, it’ll update the necessary fields in your database.
Listing 41.17. Safely updating your database
updateOrWarn :: Maybe Tool -> IO ()
updateOrWarn Nothing = print "id not found"
updateOrWarn (Just tool) = withConn "tools.db" $
\conn -> do
let q = mconcat ["UPDATE TOOLS SET "
,"lastReturned = ?,"
," timesBorrowed = ? "
,"WHERE ID = ?;"]
execute conn q (lastReturned tool
, timesBorrowed tool
, toolId tool)
print "tool updated"
Finally, you need to tie all of these steps together. Your final action, updateToolTable, takes a toolId, fetches the current date, and then performs the necessary steps to update the tool in the table.
Listing 41.18. updateToolTable combines all the steps for updating the tool table
updateToolTable :: Int -> IO ()
updateToolTable toolId = withConn "tools.db" $
\conn -> do
tool <- selectTool conn toolId
currentDay <- utctDay <$> getCurrentTime
let updatedTool = updateTool <$> tool
<*> pure currentDay
updateOrWarn updatedTool
The updateToolTable action allows you to safely update the tools table, and will inform you if an error occurs while updating the data. The final step you have to look at is checking an ite//m back in, which is the case of deleting a row from checkedout.
You can also use the ToRow type class. But ToRow is much less useful, because it transforms your data types into a tuple of values. As you can see from our examples of creating and updating values, you either don’t have all the information you need (in the case of creating) or need only a specific subset. For reference, here’s how to make Tool an instance of ToRow (note that you need to import Data.Text as T):
instance ToRow Tool where
toRow tool = [ SQLInteger $ fromIntegral $ toolId tool
, SQLText $ T.pack $ name tool
, SQLText $ T.pack $ description tool
, SQLText $ T.pack $ show $ lastReturned tool
, SQLInteger $ fromIntegral $ timesBorrowed tool ]
The SQLText and SQLInteger constructors transform Haskell Text and Integer types to SQL data. In practice, you’ll likely use ToRow much less often than FromRow. Still, it’s good to know it exists.
41.6. Deleting data from your database
The final step of the CRUD process is deletion. Deleting your data is simple: you use the execute action just as you did when creating data. Your checkin action takes a toolID and deletes the row from the checkedout table. Because each tool can be checked out to only one person at time, the tool ID is all the information you need.
Listing 41.19. Checking in a tool with checkin
checkin :: Int -> IO ()
checkin toolId = withConn "tools.db" $
\conn -> do
execute conn
"DELETE FROM checkedout WHERE tool_id = (?);"
(Only toolId)
As mentioned in the preceding section, you never want to check in a tool in isolation, but want to ensure that the tool’s information is updated. Your final database action will be checkinAndUpdate, which calls checkin and then updateToolTable.
Listing 41.20. Making sure your tool is updated when it’s checked in
checkinAndUpdate :: Int -> IO () checkinAndUpdate toolId = do checkin toolId updateToolTable toolId
At this point, you’ve seen every part of the CRUD process for working with a database when using Haskell. With all these basic tools in place, you can put together the rest of your command-line interface and check it out!
41.7. Putting it all together
You’ve written all the code you need for your database interaction. All you need now is to wrap these actions into a usable interface. Most of your database updates require a prompt from the user requesting either a username or an ID. Here are some IO actions that capture this behavior.
Listing 41.21. Organizing your database actions
promptAndAddUser :: IO () promptAndAddUser = do print "Enter new user name" userName <- getLine addUser userName promptAndCheckout :: IO () promptAndCheckout = do print "Enter the id of the user" userId <- pure read <*> getLine print "Enter the id of the tool" toolId <- pure read <*> getLine checkout userId toolId promptAndCheckin :: IO () promptAndCheckin = do print "enter the id of tool" toolId <- pure read <*> getLine checkinAndUpdate toolId
You can then bundle all of your actions, prompting the user into a single action that takes a command from the user and then performs that command. Notice that each of these commands, except quit, uses the >> operator (which performs an action, throws away the result, and performs the next) to call main. This allows your command-line interface to repeatedly prompt the user for more input until the user quits your program.
Listing 41.22. performCommand organizes all the commands the user can enter
performCommand :: String -> IO () performCommand command | command == "users" = printUsers >> main | command == "tools" = printTools >> main | command == "adduser" = promptAndAddUser >> main | command == "checkout" = promptAndCheckout >> main | command == "checkin" = promptAndCheckin >> main | command == "in" = printAvailable >> main | command == "out" = printCheckedout >> main | command == "quit" = print "bye!" | otherwise = print "Sorry command not found" >> main
Why can’t you use >>= instead of >>?
- You can; it works fine.
- >>= implies that main accepts an argument, which it doesn’t.
- >>= isn’t a valid Haskell operator.
The answer is 2. When you use >>=, you’re passing an argument in a context; the >> operator is used when you want to chain together actions and disregard their output.
Finally, here’s your revised main. You’ve been able to factor out most of the code you need into separate parts so your main IO action is minimal.
Listing 41.23. Your final main IO action
main :: IO () main = do print "Enter a command" command <- getLine performCommand command
The careful reader may notice that performCommand calls main, and main executes the performCommand action, leading to code that’s recursive. In most languages, this would be a receipt for a stack overflow, but Haskell is smart about handling this. Haskell will notice that each function calls the other last, and is able to optimize this safely.
Now you can build your program and run it to test it out:
$ stack exec db-lesson-exe "Enter a command" users 1.) willkurt "Enter a command" adduser "Enter new user name" test user "user added" "Enter a command" tools 1.) hammer description: hits stuff last returned: 2017-01-01 times borrowed: 0 2.) saw description: cuts stuff last returned: 2017-01-01 times borrowed: 0 "Enter a command" checkout "Enter the id of the user" 1 "Enter the id of the tool" 2 "Enter a command" out 2.) saw description: cuts stuff last returned: 2017-01-01 times borrowed: 0 "Enter a command" checkin "enter the id of tool" 2 "tool updated" "Enter a command" in 1.) hammer description: hits stuff last returned: 2017-01-01 times borrowed: 0 2.) saw description: cuts stuff last returned: 2017-02-26 times borrowed: 1 "Enter a command" quit "bye!"
You’ve successfully implemented all the CRUD operations in Haskell, and created a useful tool that your friend can use to manage the tool-lending library.
Summary
In this lesson, our objective was to teach you how to make a simple database-driven application using SQLite.Simple. You were able to use the FromRow instance to make it easy for users to transform data from your SQLite3 database into Haskell types. You learned how to create, read, update, and delete data from a database through Haskell. In the end, you developed a simple application that allows you to perform a range of tasks related to managing a tool library. Let’s see if you got this.
Create an IO action named addTool, like addUser, to add a tool to the database.
Add an addtool command that prompts the user for information and then adds the tool by using the addTool action from the preceding question.