Snap with SQLite

Janne Hellsten on December 20, 2012

I recently released snaplet-sqlite-simple 0.3, a compatibility update for the recently released Snap 0.10. This library is glue for hooking up a Snap application to an SQLite database using the sqlite-simple library. Using SQLite with Snap is a handy, light-weight approach for quickly prototyping database-backed web apps – you don’t need to configure a separate SQL server just to run your web app.

While the new snaplet-sqlite-simple 0.3 release doesn’t add any significant new functionality, I thought a few things in its example project would be worth a mention.

This example project implements a simple web app that has a login screen (with new user registration) and a main page where logged in users can drop comments. Users and comments are both persisted into an SQLite database.

Here’s a couple of screenshots to show how it looks like. Let’s start with the login screen:

If you create a new user and login to the app, you’re taken to the main page. Here the logged in user can add comments. The comments get persisted into a database and are associated with the current user (e.g., other users can’t see them.) Here’s how the main page looks like:

The example demonstrates a few basic concepts in a complete example:

  • Connecting to an SQLite database
  • Creating database tables (if not created) on web app’s init
  • Associating your own user data with Snap’s Snap.Snaplet.Auth user objects
  • Making simple database queries and rendering the results on the main screen

Setting up the connection is easy, see the app function in Site.hs.

Creating the database schema on app startup is perhaps less obvious. This also happens on app init:

-- | The application initializer.
app :: SnapletInit App App
-- ...
    -- Grab the DB connection pool from the sqlite snaplet and call
    -- into the Model to create all the DB tables if necessary.
    let connPool = sqlitePool $ d ^# snapletValue
    liftIO $ withResource connPool $ \conn -> Db.createTables conn

The actual table creation is done in Db.hs. This module also contains query functions for saving and listing comments for a given user.

Everything on the main page requires a logged in user. Requiring a logged in user is ensured using withLoggedInUser which either calls a handler with the currently logged in user or redirects to the login screen. It’s defined as:

-- | Run actions with a logged in user or go back to the login screen
withLoggedInUser :: (Db.User -> H ()) -> H ()
withLoggedInUser action =
  currentUser >>= go
  where
    go Nothing  = handleLogin (Just "Must be logged in to view the main page")
    go (Just u) = maybeWhen (userId u) (action . user)
      where
        user uid = Db.User (read . T.unpack $ unUid uid) (userLogin u)

You can use it anywhere you need to access the currently logged in user. For example, here’s how the main page handler deals with the current user:

mainPage :: H ()
mainPage = withLoggedInUser go
  where
    go :: Db.User -> H ()
    go user = do
      comments <- withTop db $ Db.listComments user
      heistLocal (splices comments) $ render "/index"
    splices cs =
      I.bindSplices [("comments", I.mapSplices renderComment cs)]

To learn more about using sqlite-simple with Snap, I recommend you clone the code from Git, build the example, play with the app and read the example source code:

git clone git://github.com/nurpax/snaplet-sqlite-simple.git
cd example
cabal-dev install
./cabal-dev/bin/example
# browse to http://localhost:8000