NAME

Database::Async - provides a database abstraction layer for IO::Async

SYNOPSIS

# Just looking up one thing?
my ($id) = $db->query(
 q{select id from some_table where name = ?},
 bind => ['some name']
)->single
 # This is an example, so we want the result immediately - in
 # real async code, you'd rarely call Future->get, but would
 # typically use `->then` or `->on_done` instead
 ->get;
# or, with Future::AsyncAwait, try:
my ($id) = await $db->query(
 q{select id from some_table where name = ?},
 bind => ['some name']
)->single;

# Simple query
$db->query(q{select id, some_data from some_table})
   ->row_hashrefs
   ->each(sub {
       printf "ID %d, data %s\n", $_->{id}, $_->{some_data};
   })
   # If you want to complete the full query, don't forget to call
   # ->get or ->retain here!
   ->retain;

# Transactions
$db->transaction(sub {
 my ($tx) = @_;
})->commit
 # This returns a Future, so if you want to wait for it to complete,
 # call `->get` (throws an exception if something goes wrong)
 # or `->await` (just waits for it to succeed or fail, but ignores
 # the result).
->get;

DESCRIPTION

Database support for IO::Async. This is the base API, see Database::Async::Engine and subclasses for specific database functionality.

This is an early preview release.

DBI provides a basic API for interacting with a database, but this is very low level and uses a synchronous design. See DBIx::Async if you're familiar with DBI and want an interface that follows it more closely.

Typically a database only allows a single query to run at a time. Other queries will be queued.

Set up a pool of connections to provide better parallelism:

my $dbh = Database::Async->new(
    uri  => 'postgresql://write@maindb/dbname?sslmode=require',
    pool => {
        max => 4,
    },
);

Queries and transactions will then automatically be distributed among these connections. However, note that:

• all queries within a transaction will be made on the same connection

• ordering guarantees are weaker: queries will be started in order on the next available connection

With a single connection, you could expect:

Future->needs_all(
 $dbh->do(q{insert into x ...}),
 $dbh->do(q{select from x ...})
);

to insert the rows first, then return them in the select call. With a pool of connections, that's not guaranteed.

Pool configuration

The following parameters are currently accepted for defining the pool:

• min - minimum number of total connections to maintain, defaults to 0

• max - maximum permitted active connections, default is 1

• ordering - how to iterate through the available URIs, options include random and serial (default, round-robin behaviour).

• backoff - algorithm for managing connection timeouts or failures. The default is an exponential backoff with 10ms initial delay, 30s maximum, resetting on successful connection.

See Database::Async::Pool for more details.

DBI

The interface is not the same as DBI, but here are some approximate equivalents for common patterns:

selectall_hashref

In DBI:

print $_->{id} . "\n" for
 $dbh->selectall_hashref(
  q{select * from something where id = ?},
  undef,
  $id
 )->@*;

In Database::Async:

print $_->{id} . "\n" for
 $db->query(
  q{select * from something where id = ?},
  bind => [
   $id
  ])->row_hashrefs
    ->as_arrayref
    ->@*

In DBI:

my $sth = $dbh->prepare(q{select * from something where id = ?});
for my $id (1, 2, 3) {
 $sth->bind(0, $id, 'bigint');
 $sth->execute;
 while(my $row = $sth->fetchrow_hashref) {
  print $row->{name} . "\n";
 }
}

In Database::Async:

my $sth = $db->prepare(q{select * from something where id = ?});
(Future::Utils::fmap_void  {
 my ($id) = @_;
 $sth->bind(0, $id, 'bigint')
  ->then(sub { $sth->execute })
  ->then(sub {
   $sth->row_hashrefs
    ->each(sub {
     print $_->{name} . "\n";
    })->completed
  })
} foreach => [1, 2, 3 ])->get;

METHODS

transaction

Resolves to a Future which will yield a Database::Async::Transaction instance once ready.

txn

Executes code within a transaction. This is meant as a shorter form of the common idiom

$db->transaction
   ->then(sub {
    my ($txn) = @_;
    Future->call($code)
     ->then(sub {
      $txn->commit
     })->on_fail(sub {
      $txn->rollback
     });
   })

The code must return a Future, and the transaction will only be committed if that Future resolves cleanly.

Returns a Future which resolves once the transaction is committed.

METHODS - Internal

You're welcome to call these, but they're mostly intended for internal usage, and the API may change in future versions.

uri

Returns the configured URI for populating database instances.

pool

Returns the Database::Async::Pool instance.

pool_args

Returns a list of standard pool constructor arguments.

configure

Applies configuration, see IO::Async::Notifier for details.

Supports the following named parameters:

• uri - the endpoint to use when connecting a new engine instance

• engine - the parameters to pass when instantiating a new Database::Async::Engine

• pool - parameters for setting up the pool, or a Database::Async::Pool instance

• encoding - default encoding to apply to parameters, queries and results, defaults to binary

ryu

A Ryu::Async instance, used for requesting sources, sinks and timers.

new_source

Instantiates a new Ryu::Source.

new_sink

Instantiates a new Ryu::Sink.

new_future

Instantiates a new Future.

METHODS - Internal, engine-related

request_engine

Attempts to instantiate and connect to a new Database::Async::Engine subclass. Returns a Future which should resolve to a new Database::Async::Engine instance when ready to use.

engine_instance

Loads the appropriate engine class and attaches to the loop.

engine_ready

Called by Database::Async::Engine instances when the engine is ready for queries.

queue_query

Assign the given query to the next available engine instance.

SEE ALSO

There's a range of options for interacting with databases - at a low level:

• DBIx::Async - runs DBI in subprocesses, very inefficient but tries to make all the methods behave a bit like DBI but deferring results via Futures.

• DBI - synchronous database access

• Mojo::Pg - attaches a DBD::Pg handle to an event loop

• Mojo::mysql - apparently has the ability to make MySQL "fun", an intriguing prospect indeed

and at higher levels, DBIx::Class or one of the many other ORMs might be worth a look. Nearly all of those will use DBI in some form or other. Several years ago I put together a list, the options have doubtless multiplied since then:

Asynchronous ORMs

The list here is sadly lacking:

• Async::ORM - asynchronous ORM, see also article in http://showmetheco.de/articles/2010/1/mojolicious-async-orm-and-dbslayer.html

Synchronous ORMs

If you're happy for the database to tie up your process for an indefinite amount of time, you're in luck - there's a nice long list of modules to choose from here:

• DBIx::Class - one of the more popular choices

• Rose::DB::Object - written for speed, appears to cover most of the usual requirements, personally I found the API less intuitive than other options but it appears to be widely deployed

• Fey::ORM - "newer" than the other options, also appears to be reasonably flexible

• DBIx::DataModel - UML-based Object-Relational Mapping (ORM) framework

• Alzabo - another ORM which includes features such as GUI schema editing and SQL diff

• Class::DBI - generally considered to be superceded by DBIx::Class, which provides a compatibility layer for existing applications

• Class::DBI::Lite - like Class::DBI but lighter, presumably

• ORMesque - lightweight class-based ORM using SQL::Abstract

• Oryx - Object persistence framework, meta-model based with support for both DBM and regular RDBMS backends, uses tied hashes and arrays

• Tangram - An object persistence layer

• KiokuDB - described as an "Object Graph storage engine" rather than an ORM

• DBIx::DataModel - ORM using UML definitions

• Jifty::DBI - another ORM

• ORLite - minimal SQLite-based ORM

• Ormlette - object persistence, "heavily influenced by Adam Kennedy's ORLite". "light and fluffy", apparently!

• ObjectDB - another lightweight ORM, currently has only DBI as a dependency

• ORM - looks like it has support for MySQL, PostgreSQL and SQLite

• fytwORM - described as a "bare minimum ORM used for prototyping / proof of concepts"

• DBR - Database Repository ORM

• SweetPea::Application::Orm - specific to the SweetPea web framework

• Jorge - ORM Made simple

• Persistence::ORM - looks like a combination between persistent Perl objects and standard ORM

• Teng - lightweight minimal ORM

• Class::orMapper - DBI-based "easy O/R Mapper"

• UR - class framework and object/relational mapper (ORM) for Perl

• DBIx::NinjaORM - "Flexible Perl ORM for easy transitions from inline SQL to objects"

• DBIx::Oro - Simple Relational Database Accessor

• LittleORM - Moose-based ORM

• Storm - another Moose-based ORM

• DBIx::Mint - "A mostly class-based ORM for Perl"

Database interaction

• DBI::Easy - seems to be a wrapper around DBI

• AnyData - interface between DBI and arbitrary data sources such as XML or HTML

• DBIx::ThinSQL - helpers for SQL statements

• DB::Evented - event-based wrapper for DBI-like behaviour, uses AnyEvent::DBI

INHERITED METHODS

Database::Async::DB

do, insert, prepare, query, quote_field_name, quote_table_name

IO::Async::Notifier

add_child, adopt_future, adopted_futures, can_event, children, configure_unknown, debug_printf, get_loop, invoke_error, invoke_event, loop, make_event_cb, maybe_invoke_event, maybe_make_event_cb, new, notifier_name, parent, remove_child, remove_from_parent

AUTHOR

Tom Molesworth <TEAM@cpan.org>

LICENSE

Copyright Tom Molesworth 2011-2023. Licensed under the same terms as Perl itself.

cpanm Database::Async

perl -MCPAN -e shell
install Database::Async