Haskell persistent w/ esqueleto: read entire table, and count records

Question

I have the following schema:

share [ mkPersist sqlSettings, mkMigrate "migrateAll" ] [persistLowerCase|
AdminChan
  timestamp T.Text
  name      T.Text
  msg       T.Text
BanHost
  timestamp T.Text
  host      T.Text
  isBanned  Bool
  reason    T.Text
|]

Now say I want to get all the records in the admin_chan table in the form of [AdminChan]. How can I do that? I have this function:

dumpDbTbl :: SqlPersistT IO [AdminChan]
dumpDbTbl = map entityVal <$> (select . from $ return)

1) But how do I get the [AdminChan] out of the SqlPersistT IO stack? (Note that I'm not using yesod.)

2) Also, is it possible to rewrite the above function such that it becomes polymorphic and can also work with the BanHost table?

3) How can I write a function, using esqueleto, that will return the number of records in a table?

Thanks!

score 1 · Accepted Answer · answered Aug 28 '15 at 22:48

You use a function like runSqlPersistM or runSqlPersistMPool to evaluate your query in the IO monad. For example:

dumpDbTbl :: (MonadIO m) => SqlPersistT m [AdminChan]
dumpDbTbl = map entityVal <$> (select . from $ return)

main :: IO ()
main = runStderrLoggingT $ withSqlitePool ":memory:" 4 $ \pool -> liftIO $ do
    flip runSqlPersistMPool pool $ do
        runMigration migrateAll

        admins <- dumpDbTbl

        liftIO $ print (admins :: [AdminChan])

Yes, you can, but you have to include some extra constraints:

dumpDbTbl :: (PersistEntity b, MonadIO m,
              PersistEntityBackend b ~ SqlBackend) => SqlPersistT m [b]
dumpDbTbl = map entityVal <$> (select . from $ return)

You use the esqueleto function countRows:

countHostBans :: (MonadIO m) => SqlPersistT m [(T.Text, Int)]
countHostBans =
    map (\(Value v, Value c) -> (v, c)) <$> (select $ from $ \banHost -> do
        let c = countRows
        groupBy (banHost ^. BanHostHost)
        return (banHost ^. BanHostHost, c))

Haskell persistent w/ esqueleto: read entire table, and count records

1 Answers1