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!

Answer 1

1. 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])
   

2. 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)
   

3. 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))