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…

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

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

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

Tech Concepts

Programming languages

Tech Tools

Unlimited access to the largest independent learning library in tech of over 8,000 expert-authored tech books and videos.

Innovative learning tools, including AI book assistants, code context explainers, and text-to-speech.

50+ new titles added per month and exclusive early access to books as they are being written.

Haskell Data Analysis cookbook

By : Nishant Shukla

Haskell Data Analysis cookbook

By: Nishant Shukla

Overview of this book

Exploring data from a SQLite database

Getting ready

How to do it…

Haskell Data Analysis cookbook

By : Nishant Shukla

Haskell Data Analysis cookbook

By: Nishant Shukla

Overview of this book

Exploring data from a SQLite database

Getting ready

How to do it…

Confirmation

Buy this book with your credits?

Submit Your Feedback

Create a Free Account To Continue Reading

Sign in to activate your 7-day free access