sqlite-simple-effectful

Adaptation of the sqlite-simple library for the effectful ecosystem.

Getting started

This package provides a dynamic SQLite effect that can be used to run SQLite queries in an effectful context.

Use useConnection to obtain a connection to the database and run queries.

import Effectful.SQLite.Simple (SQLite)
import Effectful.SQLite.Simple qualified as SQL

import Effectful
import Effectful.Concurrent (runConcurrent)
import Data.Function ((&))
import Control.Concurrent.MVar qualified as MVar

app :: (SQLite :> es) => Eff es [User]
app = do
  SQL.useConnection \conn -> do
    SQL.query_ conn "SELECT * FROM users"

The effect can be interpreted with runSQLiteUnsync, suitable for single-threaded applications, or runSQLiteSync, which is safe to use in multi-threaded applications.

main :: IO [User]
main =
  SQL.withConnection "users.db" \conn -> do
    connVar <- MVar.newMVar conn
    app
      & SQL.runSQLiteSync connVar
      & runConcurrent
      & runEff

Pooled connections

The module Effectful.SQLite.Simple.RW provides an interpreter backed by connection pools.

SQLite allows multiple connections to read/write concurrently, but concurrent writes will lead to contention, performance degradation, and SQLITE_BUSY errors. We avoid this by:

• Having separate pools for reading and writing.
• Configuring the write pool to have a maximum of 1 connection, thus serializing all writes.

We additionally set the database’s journal mode to WAL, so that readers will not block the writer and the writer will not block readers.

Note that even in WAL mode, SQLITE_BUSY errors can still occur.

The useReadConnection and useWriteConnection operations retrieve a pooled connection from the context that can be used to run “read” or “write” operations.

import Effectful.SQLite.Simple.RW (SQLite)
import Effectful.SQLite.Simple.RW qualified as SQL

import Effectful
import Effectful.Concurrent.Async (runConcurrent, concurrently)
import Effectful.Fail (Fail, runFail)
import Control.Monad (void)
import Data.Function ((&))

reader :: (SQLite :> es) => Eff es [User]
reader = do
  SQL.useReadConnection \conn -> do
    SQL.query_ conn "SELECT * FROM users"

writer :: (SQLite :> es, Fail :> es) => Eff es ()
writer = do
  SQL.useWriteConnection \conn -> do
    SQL.withImmediateTransaction conn do
      [userId :: SQL.Only UserId] <- SQL.query conn "SELECT id FROM users WHERE username = ?" (SQL.Only @Text "dcastro")
      SQL.execute conn "DELETE FROM articles WHERE author_id = ?" userId

main :: IO (Either String ())
main = do
  let dbPath = "users.db"
  pools <-
    SQL.newPools
      =<< SQL.newPoolsConfig
        (SQL.open dbPath) -- Action to create a new read connection.
        60                -- Read connections' idle timeout in seconds.
        32                -- Max number of read connections.
        (SQL.open dbPath) -- Action to create a new write connection.
        60                -- Write connections' idle timeout in seconds.

  let app = void $ concurrently (void reader) writer

  app
    & SQL.runSQLiteWithPools pools
    & runConcurrent
    & runFail
    & runEff

Using multiple connections

The package also provides “labeled effects” for handling connections to multiple databases in the same application.

See:

• Effectful.SQLite.Simple.Labeled
• Effectful.SQLite.Simple.RW.Labeled

Credits

The API was inspired by effectful-postgresql.