Simple Haskell: using a sqlite3 database

I have been using Lisp languages professionally since the early 1980s. While I now use Java, Ruby, and Clojure for much of my work, I have been slowly been getting up to speed using Haskell over the last 5 years. My difficulties using Haskell are caused almost 100% when I need write impure Haskell code. This occasional discomfort is made up for by the fun and productivity of writing pure Haskell code. Using haskell-mode in Emacs I get the same happy feeling writing pure Haskell code that I used to get using Common Lisp, Scheme, and Clojure - and with the advantages of a strongly typed language!

I like to mock up test data and write the pure code first and then write impure code that needs to access the web, RDF data stores, relational databases, file IO, etc. For me, as a student of Haskell, this is the easiest way to write Haskell programs.

About 15 years ago, in one of my Java artificial intelligence books I wrote an example program that provides a natural language processing (NLP) interface to relational databases. I have decided that I would like to do the same, but in Haskell, and take advantage of what I have learned in the last 15 years. Writing the code to convert natural language queries into SQL queries is pure Haskell code (given mockup data for database metadata and sample table data, and test NLP queries) and I am enjoying working on that. Eventually I will need to write some impure code that accesses the popular databases. To make the initial development as easy as possible (a good idea since I may never totally finish this side project) I have decided that I will use sqlite and the sqlite-simple library. For the first proof of concept/prototype, I don't expect to need much impure code. A good thing!

This reminds me of a comment Erik Meijer made when he was teaching the edX functional programming class. He said that as developers we can think of pure Haskell code a being islands and impure code that has to maintain state and interact with the world as the ocean containing the islands. I like this metaphor!

I write little code snippets (or sometimes mini-projects) to experiment with nonpure Haskell code and the following listing, derived from the sqlite-simple library, contains the small experiments with the functionality that I need for now. I thought it was worth sharing in case this saves anyone else some time:

{-# LANGUAGE OverloadedStrings #-}
import Database.SQLite.Simple

{-
   Create sqlite database:
     sqlite3 test.db "create table test (id integer primary key, str text);"

   This is derived from the example at github.com/nurpax/sqlite-simple
-}

main :: IO ()
main = do
  conn <- open "test.db"
  -- start by getting table names in database:
  do
    r <- query_ conn "SELECT name FROM sqlite_master WHERE type='table'" :: IO [(Only String)]
    print "Table names in database test.db:"
    mapM_ (print . fromOnly) r
  
  -- get the metadata for table test in test.db:
  do
    r <- query_ conn "SELECT sql FROM sqlite_master WHERE type='table' and name='test'" :: IO [(Only String)]
    print "SQL to create table 'test' in database test.db:"
    mapM_ (print . fromOnly) r
  
  -- add a row to table 'test' and print out the rows in table 'test':
  do
    execute conn "INSERT INTO test (str) VALUES (?)"
      (Only ("test string 2" :: String))
    r2 <- query_ conn "SELECT * from test" :: IO [(Int, String)]
    print "number of rows in table 'test':"
    print (length r2)
    print "rows in table 'test':"
    mapM_ print  r2
    
  close conn

Just to make this example complete, here is my stack.yaml file:

resolver: lts-4.0
packages: - '.'
extra-deps: []
flags: {}

And here is my sqlite.cabal file:

name:                sqlite
version:             0.1.0.0
synopsis:            Experiment with sqlite-simple
description:         Derived from example in github.com/nurpax/sqlite-simple
homepage:            https://github.com/mark-watson?tab=repositories
license:             Apache2
license-file:        LICENSE
author:              Mark Watson
maintainer:          markw@markwatson.com
copyright:           2016 Mark Watson
category:            Web
build-type:          Simple
-- extra-source-files:
cabal-version:       >=1.10

executable test1
  hs-source-dirs:      .
  main-is:             test1.hs
  ghc-options:         -threaded -rtsopts -with-rtsopts=-N
  build-depends:       base
                     , sqlite-simple
  default-language:    Haskell2010

Here is a build and sample run (assuming that the sqlite database test.db has been created as per the comments in the first source listing):

✗ stack build
✗ stack exec test1
"Table names in database test.db:"
"test"
"SQL to create table 'test' in database test.db:"
"CREATE TABLE test (id INTEGER PRIMARY KEY, str text)"
"number of rows in table 'test':"
3
"rows in table 'test':"
(1,"test string 2")
(2,"test string 2")
(3,"test string 2")

I would like to thank Janne Hellsten for maintaining the sqlite-simple library and I would also like to thank the developers of stack. Using stack has solved most of my build issues with Haskell. Thanks!