Exploring data from a SQLite database

SQLite is a relational database that enforces a strict schema. It is simply a file on a machine that we can interact with through Structured Query Language (SQL). There is an easy-to-use Haskell library to send these SQL commands to our database.

In this recipe, we will use such a library to extract all data from a SQLite database.

Getting ready

We need to install the SQLite database if it isn't already set up. It can be obtained from http://www.sqlite.org. On Debian systems, we can get it from apt-get using the following command:

$ sudo apt-get install sqlite3

Now create a simple database to test our code, using the following commands:

$ sqlite3 test.db "CREATE TABLE test \
(id INTEGER PRIMARY KEY, str text); \
INSERT INTO test (str) VALUES ('test string');"

We must also install the SQLite Haskell package from Cabal as follows:

$ cabal install sqlite-simple

This recipe will dissect the example code presented on the library's documentation page available at http://hackage.haskell.org/package/sqlite-simple/docs/Database-SQLite-Simple.html.

How to do it…

1. Use the OverloadedStrings language extension and import the relevant libraries, as shown in the following code:

   {-# LANGUAGE OverloadedStrings #-}
   
   import Control.Applicative
   import Database.SQLite.Simple
   import Database.SQLite.Simple.FromRow

2. Define a data type for each SQLite table field. Provide it with an instance of the FromRow typeclass so that we may easily parse it from the table, as shown in the following code snippet:

   data TestField = TestField Int String deriving (Show)
   
   instance FromRow TestField where
     fromRow = TestField <$> field <*> field

3. And lastly, open the database to import everything as follows:

   main :: IO ()
   main = do
     conn <- open "test.db"
     r <- query_ conn "SELECT * from test" :: IO [TestField]
     mapM_ print r
     close conn