Multiple YesodPersist instance

[Anton Cheshkov]

Hello All, 

I have several databases. First is main DB of my Yesod Application. The second is database of different application. How to make yesod application to work with multiple database with YesodPersist. 

Also i would like to prevent modification of external database with yesod migration mechanism and others unknown for me. I just want to validate scheme of database i'm going to work.

My hypothesis is i have to use subsite mechanism. But i do not imagine right now how to get access to persist layer of subsite.

Thanks.

P.S.: i feel i seen a topic about my question, i can not find the answer in history.

--
Best regards,
Cheshkov Anton

[Hugo Gomes]

Hi Anton,

here is how i would do it:

1. Create a new config file for your other db, lets say config/otherDB.yml
2. Parse that config file in makeFoundation of Application.hs
3. Initialize your new db
    * Either keep a separate pool of connections in your foundation datatype (App)
    * Open/Close connection whenever you need an access to the database (and perhaps manager the pool with an application in between Yesod and the DB)
4. In Foundation.hs create a new runDB function (perhaps name it runDB2) to run your other db actions.
5. ???
6. profit

so taking those steps in consideration, here is my proposal for the code using the approach of opening and closing the connection (if you choose to keep another separate pool of connections then it should be easier since it really ammounts to copy paste what currently exists).

(Assuming postgresql with postgresql-simple):
import Database.PostgreSQL.Simple
1. copy/paste your config/postgresql.yml to config/postgresql2.yml and change the options to match your 2nd DB

2. in makeFoundation, parse the config/postgresql2.yml right bellow dbconf is setup:

    pgconf <- withYamlEnvironment "config/postgresql2.yml" (appEnv conf)
                (\(Object o) ->do
                    database <- o .: "database"
                    host     <- o .: "host"
                    port     <- o .: "port"
                    user     <- o .: "user"
                    password <- o .: "password"
                    -- no need for pool size
                    -- the pool can be defined in pgbouncer or other fancy tool
                    return $ ConnectInfo { connectHost = host
                                         , connectPort = port
                                         , connectUser = user
                                         , connectPassword = password
                                         , connectDatabase = database
                                         })

3. Add pgconf to the App datatype:
  
    a) in Foundation.hs change your foundation datatype to something like:
data App = App                                                                 
    { settings :: AppConfig DefaultEnv Extra
    , getLogger :: Logger
    , getStatic :: Static -- ^ Settings for static file serving.
    , connPool :: Database.Persist.Store.PersistConfigPool Settings.PersistConfig -- ^ Database connection pool.
    , httpManager :: Manager
    , persistConfig :: Settings.PersistConfig

    -- New stuff here:
    , pgConn :: ConnectInfo -- ^ 2nd db connection
    }
   
   b) Initialize the DB and add the connectionInfo to the foundation datatype instance in makeFoundation of Application.hs:
 
    c <- connect pgconf                                                        
    -- Initializae your sql tables here
    close c
    return $ App conf setLogger s p manager dbconf pgconf  -- add the pgconf created in 2. to the App datatype

4. Create a new runDB2 to ease the usage of this new DB:
    Dump it anywhere in your Foundation.hs:

runDB2 f = do
    y <- getYesod
    liftIO $ do
        conn <- connect $ pgConn y
        res <- f conn
        close conn
        return res


Again, all of this should be easier if you want yesod to manage your connection pools, since it would only be necessary to replicate (by copy pasting) the existing code. As for the migration just don't call runMigration migrateAll in your makeFoundation if you don't want it.

I hope this is useful somehow to you,

good luck :)

2012/6/23 Anton Cheshkov <ache...@gmail.com>

[Anton Cheshkov]

Thank you Hugo! 

From this examle i found that Yesod does not support natively multiple backend interaction in one application. 

2012/6/25 Hugo Gomes <mr.hug...@gmail.com>

[Hugo Gomes]

Anton,

that was just my approach to your question, i'm sure that there are better solutions.

With Yesod you have Haskell and all its related libraries and tools, the scaffolded website is just some code generated around Yesod based on what is considered the most "standard" practices for most people without sacrificing minimality.

Yesod is more than the scaffolded website, it is about how you work with your foundation datatype when you get http requests. Yesod tries not force you into a particular implementation or code practice. You can use persistent or any other DB backend that you wish in Yesod, the scaffolded website uses persistent, but like everything in the scaffolded website, it is just a default choice.

2012/6/25 Anton Cheshkov <ache...@gmail.com>

[Alexandr Kurilin]

Hey folks, is this still the best solution out there for supporting multiple databases or have there been changes in that area since 2012?