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 create
49 a new database connection if a new thread has been spawned. As with
50 "fork"ing, spawning a new thread can break database connections.
51 • Works Anywhere
53 Unlike Apache::DBI, DBIx::Connector runs anywhere -- inside of
55 mod_perl or not. Why limit yourself?
56 • Explicit Interface
58 DBIx::Connector has an explicit interface. There is none of the
60 magical action-at-a-distance crap that Apache::DBI is guilty of,
61 and no global caching. I've personally diagnosed a few issues with
62 Apache::DBI's magic, and killed it off in two different projects in
63 favor of connect_cached(), only to be tripped up by other gotchas.
64 No more.
65 • Optimistic Execution
67 If you use run() and txn(), the database handle will be passed
69 without first pinging the server. For the 99% or more of the time
70 when the database is just there, you'll save a ton of overhead
71 without the ping.
72 DBIx::Connector's other feature is transaction management. Borrowing an
74 interface from DBIx::Class, DBIx::Connector offers an API that
75 efficiently handles the scoping of database transactions so that you
76 needn't worry about managing the transaction yourself. Even better, it
77 offers an API for savepoints if your database supports them. Within a
78 transaction, you can scope savepoints to behave like subtransactions,
79 so that you can save some of your work in a transaction even if part of
80 it fails. See txn() and svp() for the goods.
81
83 Unlike Apache::DBI and connect_cached(), DBIx::Connector doesn't cache
84 database handles. Rather, for a given connection, it makes sure that
85 the connection is just there whenever you want it, to the extent
86 possible. The upshot is that it's safe to create a connection and then
87 keep it around for as long as you need it, like so:
88 my $conn = DBIx::Connector->new(@args);
90 You can store the connection somewhere in your app where you can easily
92 access it, and for as long as it remains in scope, it will try its
93 hardest to maintain a database connection. Even across "fork"s
94 (especially with DBI 1.614 and higher) and new threads, and even calls
95 to "$conn->dbh->disconnect". When you don't need it anymore, let it go
96 out of scope and the database connection will be closed.
97 The upshot is that your code is responsible for hanging onto a
99 connection for as long as it needs it. There is no magical connection
100 caching like in Apache::DBI and connect_cached().
101 Execution Methods
103 The real utility of DBIx::Connector comes from the use of the execution
104 methods, run(), txn(), or svp(). Instead of this:
105 $conn->dbh->do($query);
107 Try this:
109 $conn->run(sub { $_->do($query) }); # returns retval from the sub {...}
111 The difference is that the run() optimistically assumes that an
113 existing database handle is connected and executes the code reference
114 without pinging the database. The vast majority of the time, the
115 connection will of course still be open. You therefore save the
116 overhead of a ping query every time you use run() (or txn()).
117 Of course, if a block passed to run() dies because the DBI isn't
119 actually connected to the database you'd need to catch that failure and
120 try again. DBIx::Connector provides a way to overcome this issue:
121 connection modes.
122 Connection Modes
124 When calling run(), txn(), or svp(), each executes within the context
126 of a "connection mode." The supported modes are:
127 • "ping"
129 • "fixup"
131 • "no_ping"
133 Use them via an optional first argument, like so:
135 $conn->run(ping => sub { $_->do($query) });
137 Or set up a default mode via the mode() accessor:
139 $conn->mode('fixup');
141 $conn->run(sub { $_->do($query) });
142 The return value of the block will be returned from the method call in
144 scalar or array context as appropriate, and the block can use
145 "wantarray" to determine the context. Returning the value makes them
146 handy for things like constructing a statement handle:
147 my $sth = $conn->run(fixup => sub {
149 my $sth = $_->prepare('SELECT isbn, title, rating FROM books');
150 $sth->execute;
151 $sth;
152 });
153 In "ping" mode, run() will ping the database before running the block.
155 This is similar to what Apache::DBI and the DBI's connect_cached()
156 method do to check the database connection, and is the safest way to do
157 so. If the ping fails, DBIx::Connector will attempt to reconnect to the
158 database before executing the block. However, "ping" mode does impose
159 the overhead of the "ping" every time you use it.
160 In "fixup" mode, DBIx::Connector executes the block without pinging the
162 database. But in the event the block throws an exception, if
163 DBIx::Connector finds that the database handle is no longer connected,
164 it will reconnect to the database and re-execute the block. Therefore,
165 the code reference should have no side-effects outside of the database,
166 as double-execution in the event of a stale database connection could
167 break something:
168 my $count;
170 $conn->run(fixup => sub { $count++ });
171 say $count; # may be 1 or 2
172 "fixup" is the most efficient connection mode. If you're confident that
174 the block will have no deleterious side-effects if run twice, this is
175 the best option to choose. If you decide that your block is likely to
176 have too many side-effects to execute more than once, you can simply
177 switch to "ping" mode.
178 The default is "no_ping", but you likely won't ever use it directly,
180 and isn't recommended in any event.
181 Simple, huh? Better still, go for the transaction management in txn()
183 and the savepoint management in svp(). You won't be sorry, I promise.
184 Rollback Exceptions
186 In the event of a rollback in txn() or svp(), if the rollback itself
188 fails, a DBIx::Connector::TxnRollbackError or
189 DBIx::Connector::SvpRollbackError exception will be thrown, as
190 appropriate. These classes, which inherit from
191 DBIx::Connector::RollbackError, stringify to display both the rollback
192 error and the transaction or savepoint error that led to the rollback,
193 something like this:
194 Transaction aborted: No such table "foo" at foo.pl line 206.
196 Transaction rollback failed: Invalid transaction ID at foo.pl line 203.
197 For finer-grained exception handling, you can access the individual
199 errors via accessors:
200 "error"
202 The transaction or savepoint error.
203 "rollback_error"
205 The rollback error.
206 For example:
208 use Try::Tiny;
210 try {
211 $conn->txn(sub {
212 # ...
213 });
214 } catch {
215 if (eval { $_->isa('DBIx::Connector::RollbackError') }) {
216 say STDERR 'Transaction aborted: ', $_->error;
217 say STDERR 'Rollback failed too: ', $_->rollback_error;
218 } else {
219 warn "Caught exception: $_";
220 }
221 };
222 If a svp() rollback fails and its surrounding txn() rollback also
224 fails, the thrown DBIx::Connetor::TxnRollbackError exception object
225 will have the savepoint rollback exception, which will be an
226 DBIx::Connetor::SvpRollbackError exception object in its "error"
227 attribute:
228 use Try::Tiny;
230 $conn->txn(sub {
231 try {
232 $conn->svp(sub { # ... });
233 } catch {
234 if (eval { $_->isa('DBIx::Connector::RollbackError') }) {
235 if (eval { $_->error->isa('DBIx::Connector::SvpRollbackError') }) {
236 say STDERR 'Savepoint aborted: ', $_->error->error;
237 say STDERR 'Its rollback failed too: ', $_->error->rollback_error;
238 } else {
239 say STDERR 'Transaction aborted: ', $_->error;
240 }
241 say STDERR 'Transaction rollback failed too: ', $_->rollback_error;
242 } else {
243 warn "Caught exception: $_";
244 }
245 };
246 });
247 But most of the time, you should be fine with the stringified form of
249 the exception, which will look something like this:
250 Transaction aborted: Savepoint aborted: No such table "bar" at foo.pl line 190.
252 Savepoint rollback failed: Invalid savepoint name at foo.pl line 161.
253 Transaction rollback failed: Invalid transaction identifier at fool.pl line 184.
254 This allows you to see you original SQL error, as well as the errors
256 for the savepoint rollback and transaction rollback failures.
257
259 And now for the nitty-gritty.
260 Constructor
262 "new"
263 my $conn = DBIx::Connector->new($dsn, $username, $password, {
265 RaiseError => 1,
266 AutoCommit => 1,
267 });
268 Constructs and returns a DBIx::Connector object. The supported
270 arguments are exactly the same as those supported by the DBI. Default
271 values for those parameters vary from the DBI as follows:
272 "RaiseError"
274 Defaults to true if unspecified, and if "HandleError" is
275 unspecified. Use of the "RaiseError" attribute, or a "HandleError"
276 attribute that always throws exceptions (such as that provided by
277 Exception::Class::DBI), is required for the exception-handling
278 functionality of run(), txn(), and svp() to work properly. Their
279 explicit use is therefor recommended if for proper error handling
280 with these execution methods.
281 "AutoInactiveDestroy"
283 Added in DBI 1.613. Defaults to true if unspecified. This is
284 important for safe disconnects across forking processes.
285 In addition, explicitly setting "AutoCommit" to true is strongly
287 recommended if you plan to use txn() or svp(), as otherwise you won't
288 get the transactional scoping behavior of those two methods.
289 If you would like to execute custom logic each time a new connection to
291 the database is made you can pass a sub as the "connected" key to the
292 "Callbacks" parameter. See "Callbacks" in DBI for usage and other
293 available callbacks.
294 Other attributes may be modified by individual drivers. See the
296 documentation for the drivers for details:
297 DBIx::Connector::Driver::MSSQL
299 DBIx::Connector::Driver::Oracle
300 DBIx::Connector::Driver::Pg
301 DBIx::Connector::Driver::SQLite
302 DBIx::Connector::Driver::mysql
303 DBIx::Connector::Driver::Firebird
304 Class Method
306 "connect"
307 my $dbh = DBIx::Connector->connect($dsn, $username, $password, \%attr);
309 Syntactic sugar for:
311 my $dbh = DBIx::Connector->new(@args)->dbh;
313 Though there's probably not much point in that, as you'll generally
315 want to hold on to the DBIx::Connector object. Otherwise you'd just use
316 the DBI, no?
317 Instance Methods
319 "dbh"
320 my $dbh = $conn->dbh;
322 Returns the connection's database handle. It will use a an existing
324 handle if there is one, if the process has not been "fork"ed or a new
325 thread spawned, and if the database is pingable. Otherwise, it will
326 instantiate, cache, and return a new handle.
327 When called from blocks passed to run(), txn(), and svp(), dbh()
329 assumes that the pingability of the database is handled by those
330 methods and skips the ping(). Otherwise, it performs all the same
331 validity checks. The upshot is that it's safe to call dbh() inside
332 those blocks without the overhead of multiple "ping"s. Indeed, it's
333 preferable to do so if you're doing lots of non-database processing in
334 those blocks.
335 "run"
337 $conn->run(ping => sub { $_->do($query) });
339 Simply executes the block, locally setting $_ to and passing in the
341 database handle. Returns the value returned by the block in scalar or
342 array context as appropriate (and the block can use "wantarray" to
343 decide what to do).
344 An optional first argument sets the connection mode, overriding that
346 set in the mode() accessor, and may be one of "ping", "fixup", or
347 "no_ping" (the default). See "Connection Modes" for further
348 explication.
349 For convenience, you can nest calls to run() (or txn() or svp()),
351 although the connection mode will be invoked to check the connection
352 (or not) only in the outer-most block method call.
353 $conn->txn(fixup => sub {
355 my $dbh = shift;
356 $dbh->do($_) for @queries;
357 $conn->run(sub {
358 $_->do($expensive_query);
359 $conn->txn(sub {
360 $_->do($another_expensive_query);
361 });
362 });
363 });
364 All code executed inside the top-level call to txn() will be executed
366 in a single transaction. If you'd like subtransactions, nest svp()
367 calls.
368 It's preferable to use dbh() to fetch the database handle from within
370 the block if your code is doing lots of non-database stuff (shame on
371 you!):
372 $conn->run(ping => sub {
374 parse_gigabytes_of_xml(); # Get this out of the transaction!
375 $conn->dbh->do($query);
376 });
377 This is because dbh() will better ensure that the database handle is
379 active and "fork"- and thread-safe, although it will never ping() the
380 database when called from inside a run(), txn() or svp() block.
381 "txn"
383 my $sth = $conn->txn(fixup => sub { $_->do($query) });
385 Starts a transaction, executes the block, locally setting $_ to and
387 passing in the database handle, and commits the transaction. If the
388 block throws an exception, the transaction will be rolled back and the
389 exception re-thrown. Returns the value returned by the block in scalar
390 or array context as appropriate (and the block can use "wantarray" to
391 decide what to do).
392 An optional first argument sets the connection mode, overriding that
394 set in the mode() accessor, and may be one of "ping", "fixup", or
395 "no_ping" (the default). In the case of "fixup" mode, this means that
396 the transaction block will be re-executed for a new connection if the
397 database handle is no longer connected. In such a case, a second
398 exception from the code block will cause the transaction to be rolled
399 back and the exception re-thrown. See "Connection Modes" for further
400 explication.
401 As with run(), calls to txn() can be nested, although the connection
403 mode will be invoked to check the connection (or not) only in the
404 outer-most block method call. It's preferable to use dbh() to fetch the
405 database handle from within the block if your code is doing lots of
406 non-database processing.
407 "svp"
409 Executes a code block within the scope of a database savepoint if your
411 database supports them. Returns the value returned by the block in
412 scalar or array context as appropriate (and the block can use
413 "wantarray" to decide what to do).
414 You can think of savepoints as a kind of subtransaction. What this
416 means is that you can nest your savepoints and recover from failures
417 deeper in the nest without throwing out all changes higher up in the
418 nest. For example:
419 $conn->txn(fixup => sub {
421 my $dbh = shift;
422 $dbh->do('INSERT INTO table1 VALUES (1)');
423 eval {
424 $conn->svp(sub {
425 shift->do('INSERT INTO table1 VALUES (2)');
426 die 'OMGWTF?';
427 });
428 };
429 warn "Savepoint failed\n" if $@;
430 $dbh->do('INSERT INTO table1 VALUES (3)');
431 });
432 This transaction will insert the values 1 and 3, but not 2.
434 $conn->svp(fixup => sub {
436 my $dbh = shift;
437 $dbh->do('INSERT INTO table1 VALUES (4)');
438 $conn->svp(sub {
439 shift->do('INSERT INTO table1 VALUES (5)');
440 });
441 });
442 This transaction will insert both 4 and 5.
444 Superficially, svp() resembles run() and txn(), including its support
446 for the optional connection mode argument, but in fact savepoints can
447 only be used within the scope of a transaction. Thus svp() will start a
448 transaction for you if it's called without a transaction in-progress.
449 It simply redispatches to txn() with the appropriate connection mode.
450 Thus, this call from outside of a transaction:
451 $conn->svp(ping => sub {
453 $conn->svp( sub { ... } );
454 });
455 Is equivalent to:
457 $conn->txn(ping => sub {
459 $conn->svp( sub { ... } );
460 })
461 Savepoints are supported by the following RDBMSs:
463 • PostgreSQL 8.0
465 • SQLite 3.6.8
467 • MySQL 5.0.3 (InnoDB)
469 • Oracle
471 • Microsoft SQL Server
473 • Firebird 1.5
475 For all other RDBMSs, svp() works just like txn(): savepoints will be
477 ignored and the outer-most transaction will be the only transaction.
478 This tends to degrade well for non-savepoint-supporting databases,
479 doing the right thing in most cases.
480 "mode"
482 my $mode = $conn->mode;
484 $conn->mode('fixup');
485 $conn->txn(sub { ... }); # uses fixup mode.
486 $conn->mode($mode);
487 Gets and sets the connection mode attribute, which is used by run(),
489 txn(), and svp() if no mode is passed to them. Defaults to "no_ping".
490 Note that inside a block passed to run(), txn(), or svp(), the mode
491 attribute will be set to the optional first parameter:
492 $conn->mode('ping');
494 $conn->txn(fixup => sub {
495 say $conn->mode; # Outputs "fixup"
496 });
497 say $conn->mode; # Outputs "ping"
498 In this way, you can reliably tell in what mode the code block is
500 executing.
501 "connected"
503 if ( $conn->connected ) {
505 $conn->dbh->do($query);
506 }
507 Returns true if currently connected to the database and false if it's
509 not. You probably won't need to bother with this method;
510 DBIx::Connector uses it internally to determine whether or not to
511 create a new connection to the database before returning a handle from
512 dbh().
513 "in_txn"
515 if ( $conn->in_txn ) {
517 say 'Transacting!';
518 }
519 Returns true if the connection is in a transaction. For example, inside
521 a txn() block it would return true. It will also work if you use the
522 DBI API to manage transactions (i.e., begin_work() or "AutoCommit".
523 Essentially, this is just sugar for:
525 $con->run( no_ping => sub { !$_->{AutoCommit} } );
527 But without the overhead of the code reference or connection checking.
529 "disconnect_on_destroy"
531 $conn->disconnect_on_destroy(0);
533 By default, DBIx::Connector calls "$dbh->disconnect" when it goes out
535 of scope and is garbage-collected by the system (that is, in its
536 DESTROY() method). Usually this is what you want, but in some cases it
537 might not be. For example, you might have a module that uses
538 DBIx::Connector internally, but then makes the database handle
539 available to callers, even after the DBIx::Connector object goes out of
540 scope. In such a case, you don't want the database handle to be
541 disconnected when the DBIx::Connector goes out of scope. So pass a
542 false value to "disconnect_on_destroy" to prevent the disconnect. An
543 example:
544 sub database_handle {
546 my $conn = DBIx::Connector->new(@_);
547 $conn->run(sub {
548 # Do stuff here.
549 });
550 $conn->disconnect_on_destroy(0);
551 return $conn->dbh;
552 }
553 Of course, if you don't need to do any work with the database handle
555 before returning it to your caller, you can just use connect():
556 sub database_handle {
558 DBIx::Connector->connect(@_);
559 }
560 "disconnect"
562 $conn->disconnect;
564 Disconnects from the database. Unless disconnect_on_destroy() has been
566 passed a false value, DBIx::Connector uses this method internally in
567 its "DESTROY" method to make sure that things are kept tidy.
568 "driver"
570 $conn->driver->begin_work( $conn->dbh );
572 In order to support all database features in a database-neutral way,
574 DBIx::Connector provides a number of different database drivers,
575 subclasses of DBIx::Connector::Driver, that offer methods to handle
576 database communications. Although the DBI provides a standard
577 interface, for better or for worse, not all of the drivers implement
578 them, and some have bugs. To avoid those issues, all database
579 communications are handled by these driver objects.
580 This can be useful if you want more fine-grained control of your
582 transactionality. For example, to create your own savepoint within a
583 transaction, you might do something like this:
584 use Try::Tiny;
586 my $driver = $conn->driver;
587 $conn->txn(sub {
588 my $dbh = shift;
589 try {
590 $driver->savepoint($dbh, 'mysavepoint');
591 # do stuff ...
592 $driver->release('mysavepoint');
593 } catch {
594 $driver->rollback_to($dbh, 'mysavepoint');
595 };
596 });
597 Most often you should be able to get what you need out of txn() and
599 svp(), but sometimes you just need the finer control. In those cases,
600 take advantage of the driver object to keep your use of the API
601 universal across database back-ends.
602 "driver_name"
604 my $driver_name = $conn->driver_name;
606 Returns the name of the DBI driver (to be) used to connect to the
608 database.
609 "dsn"
611 my $dsn = $conn->dsn;
613 Returns the DBI Data Source Name originally passed to new() as the
615 first argument.
616
618 • DBIx::Connector::Driver
619 • DBI
621 • DBIx::Class
623 • Catalyst::Model::DBI
625
627 This module was written 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.36.1 2023-08-04 DBIx::Connector(3)