Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages


home | help
Dancer2::Plugin::DatabUser3Contributed Perl DocumeDancer2::Plugin::Database(3)

       Dancer2::Plugin::Database - easy	database connections for Dancer2

	   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 = ?',
	       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} }),


       Database	connection details are read from your Dancer2 application
       config -	see below.

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

       Connection details will be taken	from your Dancer2 application config
       file, and should	be specified as, for example:

		   driver: 'mysql'
		   database: 'test'
		   host: 'localhost'
		   port: 3306
		   username: 'myusername'
		   password: 'mypassword'
		   connection_check_threshold: 10
		       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
       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:

		   driver: SQLite
		   database: 'foo.sqlite'

       For Oracle, you may want	to pass	"sid" (system ID) to identify a
       particular database, e.g.:

		   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

		  driver: mysql
		  host:	localhost
		      mysql_socket: /tmp/mysql_staging.sock

       If you need to connect to multiple databases, this is easy - just list
       them in your config under "connections" as shown	below:

			   driver: "SQLite"
			   database: "foo.sqlite"
			   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');

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

       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.

       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.

       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

       There's 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.

       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:

	   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.

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

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

	   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!

       David Precious, "<>"

       This module is developed	on Github at:


       Feel free to fork the repo and submit pull requests!  Also, it makes
       sense to	watch the repo <
       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.

       Igor Bujna

       Franck Cuny

       Alan Haggai

       Christian SA!nchez

       Michael Stiller

       Martin J	Evans

       Carlos Sosa

       Matt S Trout

       Matthew Vickers

       Christian Walde

       Alberto SimA<micro>es

       James Aitken (LoonyPandora)

       Mark Allen (mrallen1)

       Sergiy Borodych (bor)

       Mario Domgoergen	(mdom)

       Andrey Inishev (inish777)

       Nick S. Knutov (knutov)

       Nicolas Franck (nicolasfranck)


       Please report any bugs or feature requests to
       "bug-dancer-plugin-database at", or through the web
       interface at
       I will be notified, and then you'll automatically be notified of
       progress	on your	bug as I make changes.

       You can find documentation for this module with the perldoc command.

	   perldoc Dancer2::Plugin::Database

       You can also look for information at:

       o   RT: CPAN's request tracker


       o   AnnoCPAN: Annotated CPAN documentation


       o   CPAN	Ratings


       o   Search CPAN


       You can find the	author on IRC in the channel "#dancer" on

       Copyright 2010-2016 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 for more information.

       Dancer::Plugin::Database::Core and

       Dancer, Dancer2



perl v5.32.0			  2016-08-13	  Dancer2::Plugin::Database(3)


Want to link to this manual page? Use this URL:

home | help