Dancer2::Plugin::Database(3pm)

1Dancer2::Plugin::DatabaUssee(r3)Contributed Perl DocumenDtaantcieorn2::Plugin::Database(3)
2
3
4

NAME

6       Dancer2::Plugin::Database - easy database connections for Dancer2
7       applications
8

SYNOPSIS

10           use Dancer2;
11           use Dancer2::Plugin::Database;
12
13           # Calling the database keyword will get you a connected database handle:
14           get '/widget/view/:id' => sub {
15               my $sth = database->prepare(
16                   'select * from widgets where id = ?',
17               );
18               $sth->execute(params->{id});
19               template 'display_widget', { widget => $sth->fetchrow_hashref };
20           };
21
22           # The handle is a Dancer::Plugin::Database::Core::Handle object, which subclasses
23           # DBI's DBI::db handle and adds a few convenience features, for example:
24           get '/insert/:name' => sub {
25               database->quick_insert('people', { name => params->{name} });
26           };
27
28           get '/users/:id' => sub {
29               template 'display_user', {
30                   person => database->quick_select('users', { id => params->{id} }),
31               };
32           };
33
34           dance;
35
36       Database connection details are read from your Dancer2 application
37       config - see below.
38

DESCRIPTION

40       Provides an easy way to obtain a connected DBI database handle by
41       simply calling the database keyword within your Dancer2 application
42
43       Returns a Dancer::Plugin::Database::Core::Handle object, which is a
44       subclass of DBI's "DBI::db" connection handle object, so it does
45       everything you'd expect to do with DBI, but also adds a few convenience
46       methods.  See the documentation for
47       Dancer::Plugin::Database::Core::Handle for full details of those.
48
49       Takes care of ensuring that the database handle is still connected and
50       valid.  If the handle was last asked for more than
51       "connection_check_threshold" seconds ago, it will check that the
52       connection is still alive, using either the "$dbh->ping" method if the
53       DBD driver supports it, or performing a simple no-op query against the
54       database if not.  If the connection has gone away, a new connection
55       will be obtained and returned.  This avoids any problems for a long-
56       running script where the connection to the database might go away.
57
58       Care is taken that handles are not shared across processes/threads, so
59       this should be thread-safe with no issues with transactions etc.
60       (Thanks to Matt S Trout for pointing out the previous lack of thread
61       safety.  Inspiration was drawn from DBIx::Connector.)
62

CONFIGURATION

64       Connection details will be taken from your Dancer2 application config
65       file, and should be specified as, for example:
66
67           plugins:
68               Database:
69                   driver: 'mysql'
70                   database: 'test'
71                   host: 'localhost'
72                   port: 3306
73                   username: 'myusername'
74                   password: 'mypassword'
75                   connection_check_threshold: 10
76                   dbi_params:
77                       RaiseError: 1
78                       AutoCommit: 1
79                   on_connect_do: ["SET NAMES 'utf8'", "SET CHARACTER SET 'utf8'" ]
80                   log_queries: 1
81                   handle_class: 'My::Super::Sexy::Database::Handle'
82
83       The "connection_check_threshold" setting is optional, if not provided,
84       it will default to 30 seconds.  If the database keyword was last called
85       more than this number of seconds ago, a quick check will be performed
86       to ensure that we still have a connection to the database, and will
87       reconnect if not.  This handles cases where the database handle hasn't
88       been used for a while and the underlying connection has gone away.
89
90       The "dbi_params" setting is also optional, and if specified, should be
91       settings which can be passed to "DBI->connect" as its fourth argument;
92       see the DBI documentation for these.
93
94       The optional "on_connect_do" setting is an array of queries which
95       should be performed when a connection is established; if given, each
96       query will be performed using "$dbh->do".  (If using MySQL, you might
97       want to use this to set "SQL_MODE" to a suitable value to disable
98       MySQL's built-in free data loss 'features', for example:
99
100         on_connect_do: "SET SQL_MODE='TRADITIONAL'"
101
102       (If you're not familiar with what I mean, I'm talking about the insane
103       default behaviour of "hmm, this bit of data won't fit the column you're
104       trying to put it in.. hmm, I know, I'll just munge it to fit, and throw
105       a warning afterwards - it's not like you're relying on me to, y'know,
106       store what you ask me to store".  See
107       <http://effectivemysql.com/presentation/mysql-idiosyncrasies-that-bite/>
108       for just one illustration.  In hindsight, I wish I'd made a sensible
109       "sql_mode" a default setting, but I don't want to change that now.)
110
111       The optional "log_queries" setting enables logging of queries generated
112       by the helper functions "quick_insert" et al in
113       Dancer::Plugin::Database::Core::Handle.  If you enable it, generated
114       queries will be logged at 'debug' level.  Be aware that they will
115       contain the data you're passing to/from the database, so be careful not
116       to enable this option in production, where you could inadvertently log
117       sensitive information.
118
119       If you prefer, you can also supply a pre-crafted DSN using the "dsn"
120       setting; in that case, it will be used as-is, and the
121       driver/database/host settings will be ignored.  This may be useful if
122       you're using some DBI driver which requires a peculiar DSN.
123
124       The optional "handle_class" defines your own class into which database
125       handles should be blessed.  This should be a subclass of
126       Dancer::Plugin::Database::Core::Handle (or DBI::db directly, if you
127       just want to skip the extra features).
128
129       You will require slightly different options depending on the database
130       engine you're talking to.  For instance, for SQLite, you won't need to
131       supply "hostname", "port" etc, but will need to supply "database" as
132       the name of the SQLite database file:
133
134           plugins:
135               Database:
136                   driver: SQLite
137                   database: 'foo.sqlite'
138
139       For Oracle, you may want to pass "sid" (system ID) to identify a
140       particular database, e.g.:
141
142           plugins:
143               Database:
144                   driver: Oracle
145                   host: localhost
146                   sid: ABC12
147
148       If you have any further connection parameters that need to be appended
149       to the dsn, you can put them in as a hash called dsn_extra. For
150       example, if you're running mysql on a non-standard socket, you could
151       have
152
153          plugins:
154              Database:
155                  driver: mysql
156                  host: localhost
157                  dsn_extra:
158                      mysql_socket: /tmp/mysql_staging.sock
159
160   DEFINING MULTIPLE CONNECTIONS
161       If you need to connect to multiple databases, this is easy - just list
162       them in your config under "connections" as shown below:
163
164           plugins:
165               Database:
166                   connections:
167                       foo:
168                           driver: "SQLite"
169                           database: "foo.sqlite"
170                       bar:
171                           driver: "mysql"
172                           host: "localhost"
173                           ....
174
175       Then, you can call the "database" keyword with the name of the database
176       connection you want, for example:
177
178           my $foo_dbh = database('foo');
179           my $bar_dbh = database('bar');
180

RUNTIME CONFIGURATION

182       You can pass a hashref to the "database()" keyword to provide
183       configuration details to override any in the config file at runtime if
184       desired, for instance:
185
186           my $dbh = database({ driver => 'SQLite', database => $filename });
187
188       (Thanks to Alan Haggai for this feature.)
189

AUTOMATIC UTF-8 SUPPORT

191       As of version 1.20, if your application is configured to use UTF-8
192       (you've defined the "charset" setting in your app config as "UTF-8")
193       then support for UTF-8 for the database connection will be enabled, if
194       we know how to do so for the database driver in use.
195
196       If you do not want this behaviour, set "auto_utf8" to a false value
197       when providing the connection details.
198

GETTING A DATABASE HANDLE

200       Calling "database" will return a connected database handle; the first
201       time it is called, the plugin will establish a connection to the
202       database, and return a reference to the DBI object.  On subsequent
203       calls, the same DBI connection object will be returned, unless it has
204       been found to be no longer usable (the connection has gone away), in
205       which case a fresh connection will be obtained.
206
207       If you have declared named connections as described above in 'DEFINING
208       MULTIPLE CONNECTIONS', then calling the database() keyword with the
209       name of the connection as specified in the config file will get you a
210       database handle connected with those details.
211
212       You can also pass a hashref of settings if you wish to provide settings
213       at runtime.
214

CONVENIENCE FEATURES

216       The handle returned by the "database" keyword is a
217       Dancer::Plugin::Database::Core::Handle object, which subclasses the
218       "DBI::db" DBI connection handle.  This means you can use it just like
219       you'd normally use a DBI handle, but extra convenience methods are
220       provided.
221
222       There's extensive documentation on these features in
223       Dancer::Plugin::Database::Core::Handle, including using the "order_by",
224       "limit", "columns" options to sort / limit results and include only
225       specific columns.
226

HOOKS

228       This plugin uses Dancer2's hooks support to allow you to register code
229       that should execute at given times - for example:
230
231           hook 'database_connected' => sub {
232               my $dbh = shift;
233               # do something with the new DB handle here
234           };
235
236       Currrently defined hook positions are:
237
238       "database_connected"
239           Called when a new database connection has been established, after
240           performing any "on_connect_do" statements, but before the handle is
241           returned.  Receives the new database handle as a parameter, so that
242           you can do what you need with it.
243
244       "database_connection_lost"
245           Called when the plugin detects that the database connection has
246           gone away.  Receives the no-longer usable handle as a parameter, in
247           case you need to extract some information from it (such as which
248           server it was connected to).
249
250       "database_connection_failed"
251           Called when an attempt to connect to the database fails.  Receives
252           a hashref of connection settings as a parameter, containing the
253           settings the plugin was using to connect (as obtained from the
254           config file).
255
256       "database_error"
257           Called when a database error is raised by "DBI".  Receives two
258           parameters: the error message being returned by DBI, and the
259           database handle in question.
260
261       If you need other hook positions which would be useful to you, please
262       feel free to suggest them!
263

AUTHOR

265       David Precious, "<davidp@preshweb.co.uk>"
266

CONTRIBUTING

268       This module is developed on Github at:
269
270       <http://github.com/bigpresh/Dancer-Plugin-Database>
271
272       Feel free to fork the repo and submit pull requests!  Also, it makes
273       sense to watch the repo <https://github.com/bigpresh/Dancer-Plugin-
274       Database/toggle_watch> on GitHub for updates.
275
276       Feedback and bug reports are always appreciated.  Even a quick mail to
277       let me know the module is useful to you would be very nice - it's nice
278       to know if code is being actively used.
279

ACKNOWLEDGEMENTS

281       Igor Bujna
282
283       Franck Cuny
284
285       Alan Haggai
286
287       Christian Sánchez
288
289       Michael Stiller
290
291       Martin J Evans
292
293       Carlos Sosa
294
295       Matt S Trout
296
297       Matthew Vickers
298
299       Christian Walde
300
301       Alberto Simões
302
303       James Aitken (LoonyPandora)
304
305       Mark Allen (mrallen1)
306
307       Sergiy Borodych (bor)
308
309       Mario Domgoergen (mdom)
310
311       Andrey Inishev (inish777)
312
313       Nick S. Knutov (knutov)
314
315       Nicolas Franck (nicolasfranck)
316
317       mscolly
318

BUGS

320       Please report any bugs or feature requests to
321       "bug-dancer-plugin-database at rt.cpan.org", or through the web
322       interface at
323       <http://rt.cpan.org/NoAuth/ReportBug.html?Queue=Dancer2-Plugin-Database>.
324       I will be notified, and then you'll automatically be notified of
325       progress on your bug as I make changes.
326

SUPPORT

328       You can find documentation for this module with the perldoc command.
329
330           perldoc Dancer2::Plugin::Database
331
332       You can also look for information at:
333
334       •   RT: CPAN's request tracker
335
336           <http://rt.cpan.org/NoAuth/Bugs.html?Dist=Dancer2-Plugin-Database>
337
338       •   AnnoCPAN: Annotated CPAN documentation
339
340           <http://annocpan.org/dist/Dancer2-Plugin-Database>
341
342       •   CPAN Ratings
343
344           <http://cpanratings.perl.org/d/Dancer2-Plugin-Database>
345
346       •   Search CPAN
347
348           <http://search.cpan.org/dist/Dancer2-Plugin-Database/>
349
350       You can find the author on IRC in the channel "#dancer" on
351       <irc.perl.org>.
352

LICENSE AND COPYRIGHT

354       Copyright 2010-2016 David Precious.
355
356       This program is free software; you can redistribute it and/or modify it
357       under the terms of either: the GNU General Public License as published
358       by the Free Software Foundation; or the Artistic License.
359
360       See http://dev.perl.org/licenses/ for more information.
361