[Dancer-users] D::P::Database: Changing Databases dynamically between routes

[David Precious]

On Fri, 27 Jan 2012 14:40:46 +0530
Gurunandan Bhat <gbhat at pobox.com> wrote:

> Hi,
> 
> I am trying to use Dance::Plugin::Database with SQLite in a situation
> where different databases must be connected to in different routes.
> For example:
> 
> get route_1 => sub {
> 
>     my $dbfile_1 = "...";
>     my $dbh = database({
>         driver: 'SQLite',
>         database => $dbfile_1,
>         .... Other Options
>     });
> 
>     ... Work with $dbh
> 
> }
[...] 
> My Config file contains:
> 
> plugins:
>   Database:
>     driver: 'SQLite'
> 
> Since I dont know what the database is going to be I dont have the
> database setting here (probably incorrect, but I dont know any better)

Well, by providing a hashref of settings to the database() keyword,
you're overriding anything in the config file anyway.

> 
> Can't connect to data source 'dbi:SQLite' because I can't work out
> what driver to use (it doesn't seem to contain a 'dbi:driver:' prefix
> and the DBI_DRIVER env var is not set) at
> /home/nandan/perl5/perlbrew/perls/perl-5.14.2/lib/site_perl/5.14.2/Dancer/Plugin/Database.pm
> line 163

Have you accidentally called database() with no params at some point?
That would cause that error, as it would then use the details in the
config file, which don't contain the database name (which is of course
required for the SQLite driver, so it knows what file to look at...)


> Could you suggest the correct way to set the database dynamically in
> each route?

You've seen Dancer::Plugin::Database's support for multiple named
connection settings, yes/

https://metacpan.org/module/Dancer::Plugin::Database#DEFINING-MULTIPLE-CONNECTIONS

I would configure each database in the config file, as e.g.:

     plugins:
          Database:
              connections:
                  foo:
                      driver: 'SQLite'
                      database: 'foo.sqlite'
                  bar:
                      driver: 'SQLite'
                      database: 'bar.sqlite'

... etc.

Then, in your code, you can just say database('foo') to get a DB handle
configured to talk to the 'foo' database, and database('bar') to talk
to the 'bar' database.

I think that would be far cleaner, and keeps the configuration details
of which database to talk to out of your code, which is definitely a
bonus - config belongs in the config file, not in the code :)

Cheers

Dave P


-- 
David Precious ("bigpresh") <davidp at preshweb.co.uk>
http://www.preshweb.co.uk/     www.preshweb.co.uk/twitter
www.preshweb.co.uk/linkedin    www.preshweb.co.uk/facebook
www.preshweb.co.uk/cpan        www.preshweb.co.uk/github