NAME¶
Dancer2::Plugin::Database - easy database connections for Dancer2 applications
SYNOPSIS¶
use Dancer2;
use Dancer2::Plugin::Database;
# Calling the database keyword will get you a connected database handle:
get '/widget/view/:id' => sub {
my $sth = database->prepare(
'select * from widgets where id = ?',
);
$sth->execute(params->{id});
template 'display_widget', { widget => $sth->fetchrow_hashref };
};
# The handle is a Dancer::Plugin::Database::Core::Handle object, which subclasses
# DBI's DBI::db handle and adds a few convenience features, for example:
get '/insert/:name' => sub {
database->quick_insert('people', { name => params->{name} });
};
get '/users/:id' => sub {
template 'display_user', {
person => database->quick_select('users', { id => params->{id} }),
};
};
dance;
Database connection details are read from your Dancer2 application config - see
below.
DESCRIPTION¶
Provides an easy way to obtain a connected DBI database handle by simply calling
the database keyword within your Dancer2 application
Returns a Dancer::Plugin::Database::Core::Handle object, which is a subclass of
DBI's "DBI::db" connection handle object, so it does everything
you'd expect to do with DBI, but also adds a few convenience methods. See the
documentation for Dancer::Plugin::Database::Core::Handle for full details of
those.
Takes care of ensuring that the database handle is still connected and valid. If
the handle was last asked for more than "connection_check_threshold"
seconds ago, it will check that the connection is still alive, using either
the "$dbh->ping" method if the DBD driver supports it, or
performing a simple no-op query against the database if not. If the connection
has gone away, a new connection will be obtained and returned. This avoids any
problems for a long-running script where the connection to the database might
go away.
Care is taken that handles are not shared across processes/threads, so this
should be thread-safe with no issues with transactions etc. (Thanks to Matt S
Trout for pointing out the previous lack of thread safety. Inspiration was
drawn from DBIx::Connector.)
CONFIGURATION¶
Connection details will be taken from your Dancer2 application config file, and
should be specified as, for example:
plugins:
Database:
driver: 'mysql'
database: 'test'
host: 'localhost'
port: 3306
username: 'myusername'
password: 'mypassword'
connection_check_threshold: 10
dbi_params:
RaiseError: 1
AutoCommit: 1
on_connect_do: ["SET NAMES 'utf8'", "SET CHARACTER SET 'utf8'" ]
log_queries: 1
handle_class: 'My::Super::Sexy::Database::Handle'
The "connection_check_threshold" setting is optional, if not provided,
it will default to 30 seconds. If the database keyword was last called more
than this number of seconds ago, a quick check will be performed to ensure
that we still have a connection to the database, and will reconnect if not.
This handles cases where the database handle hasn't been used for a while and
the underlying connection has gone away.
The "dbi_params" setting is also optional, and if specified, should be
settings which can be passed to "DBI->connect" as its fourth
argument; see the DBI documentation for these.
The optional "on_connect_do" setting is an array of queries which
should be performed when a connection is established; if given, each query
will be performed using "$dbh->do". (If using MySQL, you might
want to use this to set "SQL_MODE" to a suitable value to disable
MySQL's built-in free data loss 'features', for example:
on_connect_do: "SET SQL_MODE='TRADITIONAL'"
(If you're not familiar with what I mean, I'm talking about the insane default
behaviour of "hmm, this bit of data won't fit the column you're trying to
put it in.. hmm, I know, I'll just munge it to fit, and throw a warning
afterwards - it's not like you're relying on me to, y'know, store what you ask
me to store". See
<
http://effectivemysql.com/presentation/mysql-idiosyncrasies-that-bite/>
for just one illustration. In hindsight, I wish I'd made a sensible
"sql_mode" a default setting, but I don't want to change that now.)
The optional "log_queries" setting enables logging of queries
generated by the helper functions "quick_insert" et al in
Dancer::Plugin::Database::Core::Handle. If you enable it, generated queries
will be logged at 'debug' level. Be aware that they will contain the data
you're passing to/from the database, so be careful not to enable this option
in production, where you could inadvertently log sensitive information.
If you prefer, you can also supply a pre-crafted DSN using the "dsn"
setting; in that case, it will be used as-is, and the driver/database/host
settings will be ignored. This may be useful if you're using some DBI driver
which requires a peculiar DSN.
The optional "handle_class" defines your own class into which database
handles should be blessed. This should be a subclass of
Dancer::Plugin::Database::Core::Handle (or DBI::db directly, if you just want
to skip the extra features).
You will require slightly different options depending on the database engine
you're talking to. For instance, for SQLite, you won't need to supply
"hostname", "port" etc, but will need to supply
"database" as the name of the SQLite database file:
plugins:
Database:
driver: SQLite
database: 'foo.sqlite'
For Oracle, you may want to pass "sid" (system ID) to identify a
particular database, e.g.:
plugins:
Database:
driver: Oracle
host: localhost
sid: ABC12
If you have any further connection parameters that need to be appended to the
dsn, you can put them in as a hash called dsn_extra. For example, if you're
running mysql on a non-standard socket, you could have
plugins:
Database:
driver: mysql
host: localhost
dsn_extra:
mysql_socket: /tmp/mysql_staging.sock
DEFINING MULTIPLE CONNECTIONS¶
If you need to connect to multiple databases, this is easy - just list them in
your config under "connections" as shown below:
plugins:
Database:
connections:
foo:
driver: "SQLite"
database: "foo.sqlite"
bar:
driver: "mysql"
host: "localhost"
....
Then, you can call the "database" keyword with the name of the
database connection you want, for example:
my $foo_dbh = database('foo');
my $bar_dbh = database('bar');
RUNTIME CONFIGURATION¶
You can pass a hashref to the "database()" keyword to provide
configuration details to override any in the config file at runtime if
desired, for instance:
my $dbh = database({ driver => 'SQLite', database => $filename });
(Thanks to Alan Haggai for this feature.)
AUTOMATIC UTF-8 SUPPORT¶
As of version 1.20, if your application is configured to use UTF-8 (you've
defined the "charset" setting in your app config as
"UTF-8") then support for UTF-8 for the database connection will be
enabled, if we know how to do so for the database driver in use.
If you do not want this behaviour, set "auto_utf8" to a false value
when providing the connection details.
GETTING A DATABASE HANDLE¶
Calling "database" will return a connected database handle; the first
time it is called, the plugin will establish a connection to the database, and
return a reference to the DBI object. On subsequent calls, the same DBI
connection object will be returned, unless it has been found to be no longer
usable (the connection has gone away), in which case a fresh connection will
be obtained.
If you have declared named connections as described above in 'DEFINING MULTIPLE
CONNECTIONS', then calling the
database() keyword with the name of the
connection as specified in the config file will get you a database handle
connected with those details.
You can also pass a hashref of settings if you wish to provide settings at
runtime.
CONVENIENCE FEATURES (quick_select, quick_update, quick_insert, quick_delete, quick_count)¶
The handle returned by the "database" keyword is a
Dancer::Plugin::Database::Core::Handle object, which subclasses the
"DBI::db" DBI connection handle. This means you can use it just like
you'd normally use a DBI handle, but extra convenience methods are provided,
as documented in the POD for Dancer::Plugin::Database::Core::Handle.
Examples:
# Quickly fetch the (first) row whose ID is 42 as a hashref:
my $row = database->quick_select($table_name, { id => 42 });
# Fetch all badgers as an array of hashrefs:
my @badgers = database->quick_select('animals', { genus => 'Mellivora' });
# Update the row where the 'id' column is '42', setting the 'foo' column to
# 'Bar':
database->quick_update($table_name, { id => 42 }, { foo => 'Bar' });
# Insert a new row, using a named connection (see above)
database('connectionname')->quick_insert($table_name, { foo => 'Bar' });
# Delete the row with id 42:
database->quick_delete($table_name, { id => 42 });
# Fetch all rows from a table (since version 1.30):
database->quick_select($table_name, {});
# Retrieve a count of rows matching the criteria:
database->quick_count($table_name, {});
There's more extensive documentation on these features in
Dancer::Plugin::Database::Core::Handle, including using the
"order_by", "limit", "columns" options to sort /
limit results and include only specific columns.
HOOKS¶
This plugin uses Dancer2's hooks support to allow you to register code that
should execute at given times - for example:
hook 'database_connected' => sub {
my $dbh = shift;
# do something with the new DB handle here
};
Currrently defined hook positions are:
- "database_connected"
- Called when a new database connection has been established, after
performing any "on_connect_do" statements, but before the handle
is returned. Receives the new database handle as a parameter, so that you
can do what you need with it.
- "database_connection_lost"
- Called when the plugin detects that the database connection has gone away.
Receives the no-longer usable handle as a parameter, in case you need to
extract some information from it (such as which server it was connected
to).
- "database_connection_failed"
- Called when an attempt to connect to the database fails. Receives a
hashref of connection settings as a parameter, containing the settings the
plugin was using to connect (as obtained from the config file).
- "database_error"
- Called when a database error is raised by "DBI". Receives two
parameters: the error message being returned by DBI, and the database
handle in question.
If you need other hook positions which would be useful to you, please feel free
to suggest them!
AUTHOR¶
David Precious, "<davidp@preshweb.co.uk>"
CONTRIBUTING¶
This module is developed on Github at:
<
http://github.com/bigpresh/Dancer-Plugin-Database>
Feel free to fork the repo and submit pull requests! Also, it makes sense to
watch the repo
<
https://github.com/bigpresh/Dancer-Plugin-Database/toggle_watch> on
GitHub for updates.
Feedback and bug reports are always appreciated. Even a quick mail to let me
know the module is useful to you would be very nice - it's nice to know if
code is being actively used.
ACKNOWLEDGEMENTS¶
Igor Bujna
Franck Cuny
Alan Haggai
Christian Sanchez
Michael Stiller
Martin J Evans
Carlos Sosa
Matt S Trout
Matthew Vickers
Christian Walde
Alberto Simo~es
James Aitken (LoonyPandora)
Mark Allen (mrallen1)
Sergiy Borodych (bor)
Mario Domgoergen (mdom)
Andrey Inishev (inish777)
Nick S. Knutov (knutov)
Nicolas Franck (nicolasfranck)
mscolly
BUGS¶
Please report any bugs or feature requests to "bug-dancer-plugin-database
at rt.cpan.org", or through the web interface at
<
http://rt.cpan.org/NoAuth/ReportBug.html?Queue=Dancer2-Plugin-Database>.
I will be notified, and then you'll automatically be notified of progress on
your bug as I make changes.
SUPPORT¶
You can find documentation for this module with the perldoc command.
perldoc Dancer2::Plugin::Database
You can also look for information at:
- •
- RT: CPAN's request tracker
<http://rt.cpan.org/NoAuth/Bugs.html?Dist=Dancer2-Plugin-Database>
- •
- AnnoCPAN: Annotated CPAN documentation
<http://annocpan.org/dist/Dancer2-Plugin-Database>
- •
- CPAN Ratings
<http://cpanratings.perl.org/d/Dancer2-Plugin-Database>
- •
- Search CPAN
<http://search.cpan.org/dist/Dancer2-Plugin-Database/>
You can find the author on IRC in the channel "#dancer" on
<irc.perl.org>.
LICENSE AND COPYRIGHT¶
Copyright 2010-13 David Precious.
This program is free software; you can redistribute it and/or modify it under
the terms of either: the GNU General Public License as published by the Free
Software Foundation; or the Artistic License.
See
http://dev.perl.org/licenses/ for more information.
SEE ALSO¶
Dancer::Plugin::Database::Core and Dancer::Plugin::Database::Core::Handle
Dancer, Dancer2
DBI
Dancer::Plugin::SimpleCRUD