1DBIx::Connector(3) User Contributed Perl Documentation DBIx::Connector(3)
2
6 DBIx::Connector - Fast, safe DBI connection and transaction management
7
9 use DBIx::Connector;
10 # Create a connection.
12 my $conn = DBIx::Connector->new($dsn, $username, $password, {
13 RaiseError => 1,
14 AutoCommit => 1,
15 });
16 # Get the database handle and do something with it.
18 my $dbh = $conn->dbh;
19 $dbh->do('INSERT INTO foo (name) VALUES (?)', undef, 'Fred' );
20 # Do something with the handle more efficiently.
22 $conn->run(fixup => sub {
23 $_->do('INSERT INTO foo (name) VALUES (?)', undef, 'Fred' );
24 });
25
27 DBIx::Connector provides a simple interface for fast and safe DBI
28 connection and transaction management. Connecting to a database can be
29 expensive; you don't want your application to re-connect every time you
30 need to run a query. The efficient thing to do is to hang on to a
31 database handle to maintain a connection to the database in order to
32 minimize that overhead. DBIx::Connector lets you do that without having
33 to worry about dropped or corrupted connections.
34 You might be familiar with Apache::DBI and with the DBI's
36 "connect_cached()" constructor. DBIx::Connector serves a similar need,
37 but does a much better job. How is it different? I'm glad you asked!
38 • Fork Safety
40 Like Apache::DBI, but unlike "connect_cached()", DBIx::Connector
42 create a new database connection if a new process has been
43 "fork"ed. This happens all the time under mod_perl, in POE
44 applications, and elsewhere. Works best with DBI 1.614 and higher.
45 • Thread Safety
47 Unlike Apache::DBI or "connect_cached()", DBIx::Connector will
49 create a new database connection if a new thread has been spawned.
50 As with "fork"ing, spawning a new thread can break database
51 connections.
52 • Works Anywhere
54 Unlike Apache::DBI, DBIx::Connector runs anywhere -- inside of
56 mod_perl or not. Why limit yourself?
57 • Explicit Interface
59 DBIx::Connector has an explicit interface. There is none of the
61 magical action-at-a-distance crap that Apache::DBI is guilty of,
62 and no global caching. I've personally diagnosed a few issues with
63 Apache::DBI's magic, and killed it off in two different projects in
64 favor of "connect_cached()", only to be tripped up by other
65 gotchas. No more.
66 • Optimistic Execution
68 If you use "run()" and "txn()", the database handle will be passed
70 without first pinging the server. For the 99% or more of the time
71 when the database is just there, you'll save a ton of overhead
72 without the ping.
73 DBIx::Connector's other feature is transaction management. Borrowing an
75 interface from DBIx::Class, DBIx::Connector offers an API that
76 efficiently handles the scoping of database transactions so that you
77 needn't worry about managing the transaction yourself. Even better, it
78 offers an API for savepoints if your database supports them. Within a
79 transaction, you can scope savepoints to behave like subtransactions,
80 so that you can save some of your work in a transaction even if part of
81 it fails. See "txn()" and "svp()" for the goods.
82
84 Unlike Apache::DBI and "connect_cached()", DBIx::Connector doesn't
85 cache database handles. Rather, for a given connection, it makes sure
86 that the connection is just there whenever you want it, to the extent
87 possible. The upshot is that it's safe to create a connection and then
88 keep it around for as long as you need it, like so:
89 my $conn = DBIx::Connector->new(@args);
91 You can store the connection somewhere in your app where you can easily
93 access it, and for as long as it remains in scope, it will try its
94 hardest to maintain a database connection. Even across "fork"s
95 (especially with DBI 1.614 and higher) and new threads, and even calls
96 to "$conn->dbh->disconnect". When you don't need it anymore, let it go
97 out of scope and the database connection will be closed.
98 The upshot is that your code is responsible for hanging onto a
100 connection for as long as it needs it. There is no magical connection
101 caching like in Apache::DBI and "connect_cached()".
102 Execution Methods
104 The real utility of DBIx::Connector comes from the use of the execution
105 methods, "run()", "txn()", or "svp()". Instead of this:
106 $conn->dbh->do($query);
108 Try this:
110 $conn->run(sub { $_->do($query) }); # returns retval from the sub {...}
112 The difference is that the "run()" optimistically assumes that an
114 existing database handle is connected and executes the code reference
115 without pinging the database. The vast majority of the time, the
116 connection will of course still be open. You therefore save the
117 overhead of a ping query every time you use "run()" (or "txn()").
118 Of course, if a block passed to "run()" dies because the DBI isn't
120 actually connected to the database you'd need to catch that failure and
121 try again. DBIx::Connector provides a way to overcome this issue:
122 connection modes.
123 Connection Modes
125 When calling "run()", "txn()", or "svp()", each executes within the
127 context of a "connection mode." The supported modes are:
128 • "ping"
130 • "fixup"
132 • "no_ping"
134 Use them via an optional first argument, like so:
136 $conn->run(ping => sub { $_->do($query) });
138 Or set up a default mode via the "mode()" accessor:
140 $conn->mode('fixup');
142 $conn->run(sub { $_->do($query) });
143 The return value of the block will be returned from the method call in
145 scalar or array context as appropriate, and the block can use
146 "wantarray" to determine the context. Returning the value makes them
147 handy for things like constructing a statement handle:
148 my $sth = $conn->run(fixup => sub {
150 my $sth = $_->prepare('SELECT isbn, title, rating FROM books');
151 $sth->execute;
152 $sth;
153 });
154 In "ping" mode, "run()" will ping the database before running the
156 block. This is similar to what Apache::DBI and the DBI's
157 "connect_cached()" method do to check the database connection, and is
158 the safest way to do so. If the ping fails, DBIx::Connector will
159 attempt to reconnect to the database before executing the block.
160 However, "ping" mode does impose the overhead of the "ping" ever time
161 you use it.
162 In "fixup" mode, DBIx::Connector executes the block without pinging the
164 database. But in the event the block throws an exception, if
165 DBIx::Connector finds that the database handle is no longer connected,
166 it will reconnect to the database and re-execute the block. Therefore,
167 the code reference should have no side-effects outside of the database,
168 as double-execution in the event of a stale database connection could
169 break something:
170 my $count;
172 $conn->run(fixup => sub { $count++ });
173 say $count; # may be 1 or 2
174 "fixup" is the most efficient connection mode. If you're confident that
176 the block will have no deleterious side-effects if run twice, this is
177 the best option to choose. If you decide that your block is likely to
178 have too many side-effects to execute more than once, you can simply
179 switch to "ping" mode.
180 The default is "no_ping", but you likely won't ever use it directly,
182 and isn't recommended in any event.
183 Simple, huh? Better still, go for the transaction management in "txn()"
185 and the savepoint management in "svp()". You won't be sorry, I promise.
186 Rollback Exceptions
188 In the event of a rollback in "txn()" or "svp()", if the rollback
190 itself fails, a DBIx::Connector::TxnRollbackError or
191 DBIx::Connector::SvpRollbackError exception will be thrown, as
192 appropriate. These classes, which inherit from
193 DBIx::Connector::RollbackError, stringify to display both the rollback
194 error and the transaction or savepoint error that led to the rollback,
195 something like this:
196 Transaction aborted: No such table "foo" at foo.pl line 206.
198 Transaction rollback failed: Invalid transaction ID at foo.pl line 203.
199 For finer-grained exception handling, you can access the individual
201 errors via accessors:
202 "error"
204 The transaction or savepoint error.
205 "rollback_error"
207 The rollback error.
208 For example:
210 use Try::Tiny;
212 try {
213 $conn->txn(sub {
214 # ...
215 });
216 } catch {
217 if (eval { $_->isa('DBIx::Connector::RollbackError') }) {
218 say STDERR 'Transaction aborted: ', $_->error;
219 say STDERR 'Rollback failed too: ', $_->rollback_error;
220 } else {
221 warn "Caught exception: $_";
222 }
223 };
224 If a "svp()" rollback fails and its surrounding "txn()" rollback also
226 fails, the thrown DBIx::Connetor::TxnRollbackError exception object
227 will have the savepoint rollback exception, which will be an
228 DBIx::Connetor::SvpRollbackError exception object in its "error"
229 attribute:
230 use Try::Tiny;
232 $conn->txn(sub {
233 try {
234 $conn->svp(sub { # ... });
235 } catch {
236 if (eval { $_->isa('DBIx::Connector::RollbackError') }) {
237 if (eval { $_->error->isa('DBIx::Connector::SvpRollbackError') }) {
238 say STDERR 'Savepoint aborted: ', $_->error->error;
239 say STDERR 'Its rollback failed too: ', $_->error->rollback_error;
240 } else {
241 say STDERR 'Transaction aborted: ', $_->error;
242 }
243 say STDERR 'Transaction rollback failed too: ', $_->rollback_error;
244 } else {
245 warn "Caught exception: $_";
246 }
247 };
248 });
249 But most of the time, you should be fine with the stringified form of
251 the exception, which will look something like this:
252 Transaction aborted: Savepoint aborted: No such table "bar" at foo.pl line 190.
254 Savepoint rollback failed: Invalid savepoint name at foo.pl line 161.
255 Transaction rollback failed: Invalid transaction identifier at fool.pl line 184.
256 This allows you to see you original SQL error, as well as the errors
258 for the savepoint rollback and transaction rollback failures.
259
261 And now for the nitty-gritty.
262 Constructor
264 "new"
265 my $conn = DBIx::Connector->new($dsn, $username, $password, {
267 RaiseError => 1,
268 AutoCommit => 1,
269 });
270 Constructs and returns a DBIx::Connector object. The supported
272 arguments are exactly the same as those supported by the DBI. Default
273 values for those parameters vary from the DBI as follows:
274 "RaiseError"
276 Defaults to true if unspecified, and if "HandleError" is
277 unspecified. Use of the "RaiseError" attribute, or a "HandleError"
278 attribute that always throws exceptions (such as that provided by
279 Exception::Class::DBI), is required for the exception-handling
280 functionality of "run()", "txn()", and "svp()" to work properly.
281 Their explicit use is therefor recommended if for proper error
282 handling with these execution methods.
283 "AutoInactiveDestroy"
285 Added in DBI 1.613. Defaults to true if unspecified. This is
286 important for safe disconnects across forking processes.
287 In addition, explicitly setting "AutoCommit" to true is strongly
289 recommended if you plan to use "txn()" or "svp()", as otherwise you
290 won't get the transactional scoping behavior of those two methods.
291 If you would like to execute custom logic each time a new connection to
293 the database is made you can pass a sub as the "connected" key to the
294 "Callbacks" parameter. See "Callbacks" in DBI for usage and other
295 available callbacks.
296 Other attributes may be modified by individual drivers. See the
298 documentation for the drivers for details:
299 DBIx::Connector::Driver::MSSQL
301 DBIx::Connector::Driver::Oracle
302 DBIx::Connector::Driver::Pg
303 DBIx::Connector::Driver::SQLite
304 DBIx::Connector::Driver::mysql
305 DBIx::Connector::Driver::Firebird
306 Class Method
308 "connect"
309 my $dbh = DBIx::Connector->connect($dsn, $username, $password, \%attr);
311 Syntactic sugar for:
313 my $dbh = DBIx::Connector->new(@args)->dbh;
315 Though there's probably not much point in that, as you'll generally
317 want to hold on to the DBIx::Connector object. Otherwise you'd just use
318 the DBI, no?
319 Instance Methods
321 "dbh"
322 my $dbh = $conn->dbh;
324 Returns the connection's database handle. It will use a an existing
326 handle if there is one, if the process has not been "fork"ed or a new
327 thread spawned, and if the database is pingable. Otherwise, it will
328 instantiate, cache, and return a new handle.
329 When called from blocks passed to "run()", "txn()", and "svp()",
331 "dbh()" assumes that the pingability of the database is handled by
332 those methods and skips the "ping()". Otherwise, it performs all the
333 same validity checks. The upshot is that it's safe to call "dbh()"
334 inside those blocks without the overhead of multiple "ping"s. Indeed,
335 it's preferable to do so if you're doing lots of non-database
336 processing in those blocks.
337 "run"
339 $conn->run(ping => sub { $_->do($query) });
341 Simply executes the block, locally setting $_ to and passing in the
343 database handle. Returns the value returned by the block in scalar or
344 array context as appropriate (and the block can use "wantarray" to
345 decide what to do).
346 An optional first argument sets the connection mode, overriding that
348 set in the "mode()" accessor, and may be one of "ping", "fixup", or
349 "no_ping" (the default). See "Connection Modes" for further
350 explication.
351 For convenience, you can nest calls to "run()" (or "txn()" or "svp()"),
353 although the connection mode will be invoked to check the connection
354 (or not) only in the outer-most block method call.
355 $conn->txn(fixup => sub {
357 my $dbh = shift;
358 $dbh->do($_) for @queries;
359 $conn->run(sub {
360 $_->do($expensive_query);
361 $conn->txn(sub {
362 $_->do($another_expensive_query);
363 });
364 });
365 });
366 All code executed inside the top-level call to "txn()" will be executed
368 in a single transaction. If you'd like subtransactions, nest "svp()"
369 calls.
370 It's preferable to use "dbh()" to fetch the database handle from within
372 the block if your code is doing lots of non-database stuff (shame on
373 you!):
374 $conn->run(ping => sub {
376 parse_gigabytes_of_xml(); # Get this out of the transaction!
377 $conn->dbh->do($query);
378 });
379 This is because "dbh()" will better ensure that the database handle is
381 active and "fork"- and thread-safe, although it will never "ping()" the
382 database when called from inside a "run()", "txn()" or "svp()" block.
383 "txn"
385 my $sth = $conn->txn(fixup => sub { $_->do($query) });
387 Starts a transaction, executes the block, locally setting $_ to and
389 passing in the database handle, and commits the transaction. If the
390 block throws an exception, the transaction will be rolled back and the
391 exception re-thrown. Returns the value returned by the block in scalar
392 or array context as appropriate (and the block can use "wantarray" to
393 decide what to do).
394 An optional first argument sets the connection mode, overriding that
396 set in the "mode()" accessor, and may be one of "ping", "fixup", or
397 "no_ping" (the default). In the case of "fixup" mode, this means that
398 the transaction block will be re-executed for a new connection if the
399 database handle is no longer connected. In such a case, a second
400 exception from the code block will cause the transaction to be rolled
401 back and the exception re-thrown. See "Connection Modes" for further
402 explication.
403 As with "run()", calls to "txn()" can be nested, although the
405 connection mode will be invoked to check the connection (or not) only
406 in the outer-most block method call. It's preferable to use "dbh()" to
407 fetch the database handle from within the block if your code is doing
408 lots of non-database processing.
409 "svp"
411 Executes a code block within the scope of a database savepoint if your
413 database supports them. Returns the value returned by the block in
414 scalar or array context as appropriate (and the block can use
415 "wantarray" to decide what to do).
416 You can think of savepoints as a kind of subtransaction. What this
418 means is that you can nest your savepoints and recover from failures
419 deeper in the nest without throwing out all changes higher up in the
420 nest. For example:
421 $conn->txn(fixup => sub {
423 my $dbh = shift;
424 $dbh->do('INSERT INTO table1 VALUES (1)');
425 eval {
426 $conn->svp(sub {
427 shift->do('INSERT INTO table1 VALUES (2)');
428 die 'OMGWTF?';
429 });
430 };
431 warn "Savepoint failed\n" if $@;
432 $dbh->do('INSERT INTO table1 VALUES (3)');
433 });
434 This transaction will insert the values 1 and 3, but not 2.
436 $conn->svp(fixup => sub {
438 my $dbh = shift;
439 $dbh->do('INSERT INTO table1 VALUES (4)');
440 $conn->svp(sub {
441 shift->do('INSERT INTO table1 VALUES (5)');
442 });
443 });
444 This transaction will insert both 4 and 5.
446 Superficially, "svp()" resembles "run()" and "txn()", including its
448 support for the optional connection mode argument, but in fact
449 savepoints can only be used within the scope of a transaction. Thus
450 "svp()" will start a transaction for you if it's called without a
451 transaction in-progress. It simply redispatches to "txn()" with the
452 appropriate connection mode. Thus, this call from outside of a
453 transaction:
454 $conn->svp(ping => sub {
456 $conn->svp( sub { ... } );
457 });
458 Is equivalent to:
460 $conn->txn(ping => sub {
462 $conn->svp( sub { ... } );
463 })
464 Savepoints are supported by the following RDBMSs:
466 • PostgreSQL 8.0
468 • SQLite 3.6.8
470 • MySQL 5.0.3 (InnoDB)
472 • Oracle
474 • Microsoft SQL Server
476 • Firebird 1.5
478 For all other RDBMSs, "svp()" works just like "txn()": savepoints will
480 be ignored and the outer-most transaction will be the only transaction.
481 This tends to degrade well for non-savepoint-supporting databases,
482 doing the right thing in most cases.
483 "mode"
485 my $mode = $conn->mode;
487 $conn->mode('fixup');
488 $conn->txn(sub { ... }); # uses fixup mode.
489 $conn->mode($mode);
490 Gets and sets the connection mode attribute, which is used by "run()",
492 "txn()", and "svp()" if no mode is passed to them. Defaults to
493 "no_ping". Note that inside a block passed to "run()", "txn()", or
494 "svp()", the mode attribute will be set to the optional first
495 parameter:
496 $conn->mode('ping');
498 $conn->txn(fixup => sub {
499 say $conn->mode; # Outputs "fixup"
500 });
501 say $conn->mode; # Outputs "ping"
502 In this way, you can reliably tell in what mode the code block is
504 executing.
505 "connected"
507 if ( $conn->connected ) {
509 $conn->dbh->do($query);
510 }
511 Returns true if currently connected to the database and false if it's
513 not. You probably won't need to bother with this method;
514 DBIx::Connector uses it internally to determine whether or not to
515 create a new connection to the database before returning a handle from
516 "dbh()".
517 "in_txn"
519 if ( $conn->in_txn ) {
521 say 'Transacting!';
522 }
523 Returns true if the connection is in a transaction. For example, inside
525 a "txn()" block it would return true. It will also work if you use the
526 DBI API to manage transactions (i.e., "begin_work()" or "AutoCommit".
527 Essentially, this is just sugar for:
529 $con->run( no_ping => sub { !$_->{AutoCommit} } );
531 But without the overhead of the code reference or connection checking.
533 "disconnect_on_destroy"
535 $conn->disconnect_on_destroy(0);
537 By default, DBIx::Connector calls "$dbh->disconnect" when it goes out
539 of scope and is garbage-collected by the system (that is, in its
540 "DESTROY()" method). Usually this is what you want, but in some cases
541 it might not be. For example, you might have a module that uses
542 DBIx::Connector internally, but then makes the database handle
543 available to callers, even after the DBIx::Connector object goes out of
544 scope. In such a case, you don't want the database handle to be
545 disconnected when the DBIx::Connector goes out of scope. So pass a
546 false value to "disconnect_on_destroy" to prevent the disconnect. An
547 example:
548 sub database_handle {
550 my $conn = DBIx::Connector->new(@_);
551 $conn->run(sub {
552 # Do stuff here.
553 });
554 $conn->disconnect_on_destroy(0);
555 return $conn->dbh;
556 }
557 Of course, if you don't need to do any work with the database handle
559 before returning it to your caller, you can just use "connect()":
560 sub database_handle {
562 DBIx::Connector->connect(@_);
563 }
564 "disconnect"
566 $conn->disconnect;
568 Disconnects from the database. Unless "disconnect_on_destroy()" has
570 been passed a false value, DBIx::Connector uses this method internally
571 in its "DESTROY" method to make sure that things are kept tidy.
572 "driver"
574 $conn->driver->begin_work( $conn->dbh );
576 In order to support all database features in a database-neutral way,
578 DBIx::Connector provides a number of different database drivers,
579 subclasses of DBIx::Connector::Driver, that offer methods to handle
580 database communications. Although the DBI provides a standard
581 interface, for better or for worse, not all of the drivers implement
582 them, and some have bugs. To avoid those issues, all database
583 communications are handled by these driver objects.
584 This can be useful if you want more fine-grained control of your
586 transactionality. For example, to create your own savepoint within a
587 transaction, you might do something like this:
588 use Try::Tiny;
590 my $driver = $conn->driver;
591 $conn->txn(sub {
592 my $dbh = shift;
593 try {
594 $driver->savepoint($dbh, 'mysavepoint');
595 # do stuff ...
596 $driver->release('mysavepoint');
597 } catch {
598 $driver->rollback_to($dbh, 'mysavepoint');
599 };
600 });
601 Most often you should be able to get what you need out of "txn()" and
603 "svp()", but sometimes you just need the finer control. In those cases,
604 take advantage of the driver object to keep your use of the API
605 universal across database back-ends.
606
608 • DBIx::Connector::Driver
609 • DBI
611 • DBIx::Class
613 • Catalyst::Model::DBI
615
617 This module is managed in an open GitHub repository
618 <http://github.com/theory/dbix-connector/>. Feel free to fork and
619 contribute, or to clone <git://github.com/theory/dbix-connector.git>
620 and send patches!
621 Found a bug? Please post <http://github.com/theory/dbix-
623 connector/issues> or email <mailto:bug-dbix-connector@rt.cpan.org> a
624 report!
625
627 This module was written and is maintained by:
628 • David E. Wheeler <david@kineticode.com>
630 It is based on documentation, ideas, kibbitzing, and code from:
632 • Tim Bunce <http://tim.bunce.name>
634 • Brandon L. Black <blblack@gmail.com>
636 • Matt S. Trout <mst@shadowcat.co.uk>
638 • Peter Rabbitson <ribasushi@cpan.org>
640 • Ash Berlin <ash@cpan.org>
642 • Rob Kinyon <rkinyon@cpan.org>
644 • Cory G Watson <gphat@cpan.org>
646 • Anders Nor Berle <berle@cpan.org>
648 • John Siracusa <siracusa@gmail.com>
650 • Alex Pavlovic <alex.pavlovic@taskforce-1.com>
652 • Many other DBIx::Class contributors
654
656 Copyright (c) 2009-2013 David E. Wheeler. Some Rights Reserved.
657 This module is free software; you can redistribute it and/or modify it
659 under the same terms as Perl itself.
660perl v5.32.1 2021-01-27 DBIx::Connector(3)