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 <https://github.com/vti/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 <https://github.com/genome/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

AUTHOR

    Tom Molesworth <TEAM@cpan.org>

LICENSE

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