We already have a database set up, some example data inserted and we’re using it in part of our application. Cool. Now it is time to discuss a little more about configuration of the database.
You might have spotted that we’re using runSqlPool instead of runSqlConn or even runSqlite – that is because – obviously – Yesod uses a pool of database connections. While it might not seem critical, it’s quite important for the configuration. First of all, pool size has to be defined. By default the scaffolding takes all startup parameters from YAML file – config/settings.yml (config/test-settings.yml for test configuration overrides). It’s loaded in Settings.hs, so if you ever need to add configuration options (and I guarantee that you will need to), do it there.
Database configuration is near the end of the file:
database: # See config/test-settings.yml for an override during tests database: "_env:SQLITE_DATABASE:civil-project.sqlite3" poolsize: "_env:SQLITE_POOLSIZE:10"
it has a quite convenient syntax, that allows deployment without modification of configuration files – if an environment variable with a given name (here: SQLITE_DATABASE and SQLITE_POOLSIZE) exists, it is used as the value, otherwise the default (civil-project.sqlite3 and 10) is taken. Now, while analysis of pool size have no sense here, since that’s a parameter used mostly for performance reasons, the default location of the database is quite unfortunate. It leaves us with stuff between consecutive application runs, require to manage the schema during development etc. Or manually delete database file each time, which is not that bad, but not perfect either, especially that it lies in the middle of source.
A perfect solution would be to run database in memory. This is possible, and not very hard – theoretically it should be sufficient to change configuration option to database: "_env:SQLITE_DATABASE:':memory'". Not obvious change, as it requires additional apostrophes, but not hard as well. And that’s enough to run SQLite database in memory. That would solve a lot of problems with the schema – database would be created from scratch each time. We’d have to keep example data somewhere in source, but we’ll need that for test reasons anyway so that’s no big deal. So, perfect, right?
No.
Unfortunately, there is one issue that causes this great plan to fail – in memory database does not play well with connection pools. Connections from pool are broken when there is no activity, and this causes in memory database to be deleted. At least that’s the scenario described on Yesod’s Google Groups.
So, there are two choices – we can either use a pool and a disk database or a single connection and in memory database. Since we’re now dealing with development issues, not testing, we can live with disk storage (we’ll rethink it when designing our tests). However, to provide some minimal encapsulation, we’re going to create a directory, workspace, and put the database there. Just create the directory and change database location (database: "_env:SQLITE_DATABASE:workspace/civil-project.sqlite3" is the line you might be looking for), remove the old database (or move it to workspace) and that’s it for the database location.
Now let’s do some cleanup in the code. First, routes. In our case, /projects redirects to ProjectsR handler, and /project/[x] to ProjectR handler. That’s not exactly how most web applications behave – in typical case it would be /project and /project/[x] (note lack of plural form in the first one). It’s not a big change, so let’s do it. Now our routes contain:
/static StaticR Static appStatic /auth AuthR Auth getAuth /favicon.ico FaviconR GET /robots.txt RobotsR GET / HomeR GET POST /comments CommentR POST /project ProjectsR GET /project/#Int ProjectR GET
I’m not going to get rid of the scaffolding routes yet (/comments, /), but in the future we’re gonna remove them, no worries.
Second thing is a little more complicated – we’re going to show all projects from our database in the main list, not just the ones we have hardcoded (that’s no difference for now, but in the next post we’re gonna explore forms and add some new projects – it’ll make a difference then).
You can start with removing import Database.Project(projects) from Handler/Projects.hs (that’s the easiest part), and then do the magic.
Database actions run in their own monad, which cannot be easily mixed with the one from layout. Therefore the code will complicate quite a bit:
getProjectsR = do
projects <- runDB $ do
dbProjects <- selectList [] []
return $ map entityVal dbProjects
defaultLayout $ do
setTitle "Projects"
$(widgetFile "projects")
As you can see, there is one “master monad” wrapping two smaller ones – one for fetching existing projects from database, the other for generating HTML response. Check it out, it really works!
There are a few things worth noting here – first of all, this will work only for small lists of projects – they are loaded into memory all at once, thus big lists would cause out of memory error. Second is the entityVal function, which extracts value from a database record. You might remember that in Handler/Project.hs we achieved similar functionality by pattern matching to Entity projectId project (exact code was Entity _ project). Both are fine, feel free to use whichever one suits you best.
And that’s it – we’ve managed to use DB in project list as well! Now, since we are already able to read from database, it would be nice to be able to write something. And that is what we’ll be dealing with in the next post – forms and simple data upload.
Stay tuned!
codemore code
~~~~