NAME

    MojoX::MojoDbWrap - Light wrapper around Mojo::Pg/SQLite/...

VERSION

    This document describes MojoX::MojoDbWrap version 0.100.

SYNOPSIS

       use MojoX::MojoDbWrap;
    
       # anything good for the Mojo::Pg or Mojo::SQLite constructor
       my $wrap = MojoX::MojoDbWrap->new(
          db_url         => $db_url,  # e.g. 'postgresql://...' or a file
          migrations_for => {
             'Mojo::Pg'     => $pg_migrations,
             'Mojo::SQLite' => $sqlite_migrations,
          },
       );
    
       # run the migrations for the selected class, if any
       $wrap->init;
    
       say $wrap->mdb_class;  # "Mojo::Pg" or "Mojo::SQLite" or...
       say $wrap->mdb_module; # ditto, it's an alias
    
       # get the underlying Mojo::Pg or Mojo::SQLite or ... object
       my $mojodb = $wrap->mdb;
    
       # get the `db` for actual queries (same as `$wrap->mdb->db`)
       my $db = $wrap->db;
    
       # assuming an identifier column called `id`, get the identifier of
       # the record matching a condition (good for a `select` on `$db`)
       my $id_existing = $wrap->id_of($table, $condition);
    
       # if the identifier column has another name, pass it with the table
       # name like this (works for all methods taking a table)
       my $id_existing = $wrap->id_of([$table, $id_column], $condition);
    
       # either get the identifier of an existing record, or insert a new
       # record and get its identifier
       my $id_ensure = $wrap->id_or_insert($table, $condition, $default);
    
       # insert a new record, get its identifier back
       my $id_new = $wrap->insert($table, \%data);
    
       # insert a new record, or update the existing one with the same
       # value(s) in the "conflicting" column(s); get the identifier back
       my $id_up = $wrap->upsert($table, \%data, { conflicting => 'name' });
       my $id_up2 = $wrap->upsert($table, \%data, { conflicting => [qw< a b >] });
    
       # all of the above accept db in the final options, e.g. to work
       # inside a transaction started on that db
       my $db = $wrap->db;
       my $tx = $db->begin;
       my $id_tx = $wrap->insert($table, \%data, { db => $db });
       my $id_tx2 = $wrap->upsert($table, \%data, { conflicting => 'name', db => $db });
       $tx->commit;
    
       # extensibility
       my $wrap = MojoX::MojoDbWrap->new(
          db_url => $whatever,
          wrappers => [
             {
                class  => 'My::Class1',
                create => sub ($class, $db_url) { ... },
                insert => sub ($wrap, $tablish, $data, $opts) {
                   my ($db, $db_opts) = $wrap->db_and_opts($opts, 'My::Class1');
                   ...
                },
             },
             {  # without insert, default_inserter is used
                class  => 'My::Class2',
                create => sub ($class, $db_url) { ... },
             },
             ...
          ],
       );

DESCRIPTION

    This module acts as a light wrapper around classes like Mojo::Pg or
    Mojo::SQLite, which provide a largely overlapping interface, so that
    the same code can work with either of them. On top of this, it provides
    a few handy methods for common operations, like getting the identifier
    of a record ("id_of"), inserting a record and getting its identifier
    back ("insert"), doing both as needed ("id_or_insert"), and inserting
    or updating a record ("upsert").

    Identifiers of inserted records are taken via RETURNING, which is
    supported by PostgreSQL and by SQLite 3.35.0 or later. This is correct
    also when an insertion turns into an update due to on_conflict, which
    would not be the case with SQLite's last_insert_id.

    The wrapped object is created lazily, i.e. upon the first call to a
    method needing it.

    Methods taking a table also accept a tablish, i.e. either a string with
    the name of the table, or an array reference holding the name of the
    table and the name of the identifier column. When only the name of the
    table is provided (as a string, or as the only item of the array
    reference), the identifier column is assumed to be id.

    All methods operating on records ("id_of", "id_or_insert", "insert" and
    "upsert") accept option db in their final options hash reference, to
    run their queries on a specific database handle. This is how to work
    inside a transaction: start it with my $tx = $db->begin and pass the
    same $db, keeping it in a variable until the transaction ends
    (transaction objects only hold a weak reference to their handle). It
    also pins a connection without any transaction, e.g. for temporary
    tables, per-connection settings or an in-memory SQLite database.
    Without db, a handle is taken via "db". This is important, because each
    call to "db" can return a different connection, outside of any
    transaction; with SQLite and an in-memory database, a different
    connection is even a different (empty) database! See "db_and_opts".

    Errors are thrown as Ouch exceptions. In particular, passing final
    options that are neither undef nor a hash reference to any of the
    methods above (or to "default_inserter") throws an exception with code
    400, whose message starts with the name of the method. The same happens
    for options holding key -asc or -desc, i.e. an old-style sort
    specification that SQL::Abstract::Pg would still accept in place of
    options: put it inside option order_by instead, e.g. { order_by => {
    -desc => 'id' } }.

    Make sure to read "SECURITY CONSIDERATIONS" too.

INTERFACE

 db

       my $db = $wrap->db;

    Same as $wrap->mdb->db.

 db_and_opts

       my ($db, $db_opts) = $wrap->db_and_opts($opts); # OR
       my ($db, $db_opts) = $wrap->db_and_opts($opts, $name);

    Get the database handle to use for the operation, along with a copy of
    the options in $opts (a hash reference, or undef) without key db. The
    handle is the value for key db if present, or a fresh one from "db"
    otherwise.

    An exception with code 400 is thrown, whose message is prefixed with
    $name, if $opts:

      * is neither undef nor a hash reference;

      * holds key -asc or -desc (an old-style sort specification, see
      "DESCRIPTION");

      * holds key db with an undefined value (which would otherwise
      silently lead to a fresh connection, outside of any transaction).

    By default, $name is the name of the calling sub (or db_and_opts when
    called outside of any sub, including directly from an eval block),
    which can be __ANON__; pass something meaningful when calling from an
    anonymous sub, e.g. a custom inserter (see "new").

 db_url

       my $url = $wrap->db_url;

    The URL provided in "new".

 default_inserter

       my $id = $wrap->default_inserter($tablish, $data, $opts);

    Insert a new record based on hash reference $data, returning the value
    of the identifier column of the inserted (or updated) record, using
    RETURNING. This is the inserter used for Mojo::Pg, Mojo::SQLite and any
    wrapper in wrappers (see "new") that does not provide its own insert.

    $opts is an optional hash reference with options for the insert method
    of the db object (e.g. on_conflict), plus option db (see
    "db_and_opts"); option returning is always overridden.

    Returns undef if no record is returned, e.g. when on_conflict is undef
    (i.e. DO NOTHING) and there is a conflict.

    It can also be called as a plain function from a custom insert sub,
    with the same arguments it receives:

       insert => sub ($wrap, $tablish, $data, $opts) {
          ...
          return MojoX::MojoDbWrap::default_inserter(@_);
       },

 id_of

       my $id = $wrap->id_of($tablish, $cond);  # OR
       my $id = $wrap->id_of($tablish, $cond, $opts);

    Get the identifier of a record matching condition $cond, optionally
    with options in hash reference $opts (e.g. order_by), both passed to
    the select method of the db object. Option db selects the database
    handle, see "db_and_opts". Option limit is set to 1 when the wrapper
    supports it (see can_limit in "new"; it is supported for Mojo::Pg and
    Mojo::SQLite), as only the first matching record is used; use order_by
    to choose which one. Otherwise, the options are passed as they are,
    including any limit provided by the caller. Returns undef if no record
    matches.

    An undefined $cond leads to an exception with code 400, as it would
    match every record, i.e. return an arbitrary identifier. The same
    happens for a $cond that is not a reference: it would be bound as WHERE
    ?, so that e.g. "1" would match every record too. With Mojo::Pg and
    Mojo::SQLite (more precisely, when the wrapped object has method
    abstract), the same happens for any $cond generating no WHERE clause,
    e.g. {}, [] or { -or => [] }. See "SECURITY CONSIDERATIONS".

    $opts must be an options hash: a sort specification on its own, i.e. a
    string, an array reference or a hash reference with key -asc or -desc
    (which the select method of the db object would accept in place of
    options), leads to an exception with code 400. Pass it as option
    order_by instead:

       my $id = $wrap->id_of($table, $cond, { order_by => { -desc => 'id' } });

 id_or_insert

       my $id = $wrap->id_or_insert($tablish, $cond, $default); # OR
       my $id = $wrap->id_or_insert($tablish, $cond, $default, $opts);

    Retrieve the identifier of the record matching condition $cond,
    inserting a record with values in $default in case it is missing. $cond
    is checked as in "id_of".

    $opts is an optional hash reference that can contain options associated
    to keys select (for searching the identifier using "id_of") and insert
    (for inserting a new record using $default), as well as db (see
    "db_and_opts"). The same database handle is used for all queries in the
    call, overriding any db inside the select and insert options, which
    must be hash references if present. Any other key in $opts leads to an
    exception with code 400 (as does a non-hash value for select or
    insert): e.g. on_conflict must go inside insert, not at the top level.
    The select options follow the same rules as the options of "id_of", so
    a sort specification goes inside order_by.

    Unless on_conflict is set in the insert options, the insertion is done
    with on_conflict set to undef (i.e. DO NOTHING). This way, if another
    process inserts the same record between the search and the insertion,
    no exception is thrown; the search is tried again instead. After three
    unsuccessful rounds (i.e. three searches and three insertions), an
    exception with code 409 is thrown. In practice this means that $default
    conflicts (on some unique key) with an existing record that does not
    match $cond: make sure $cond covers the unique keys of $default. The
    exception carries no data, and the conflicting constraint is not known,
    because DO NOTHING does not report it.

 init

       $wrap->init; # OR
       $wrap->init($name);

    If available via "migrations_for", runs the migrations for the specific
    class (see "mdb_class"), using $name as the name of the migrations.
    $name defaults to the string migrations.

    Returns the object itself.

 insert

       my $id = $wrap->insert($tablish, $data); # OR
       my $id = $wrap->insert($tablish, $data, $opts);

    Insert a new record based on hash reference $data, returning the
    identifier of that new record.

    $opts is an optional hash reference with options for the insert method
    of the db object, e.g. on_conflict, plus option db (see "db_and_opts").
    With the default inserter (see "default_inserter"), if on_conflict
    leads to an update of an existing record, the identifier of that record
    is returned; if it leads to doing nothing, undef is returned.

    The insertion is done by the insert sub of the selected wrapper, see
    "new".

 mdb

       my $mdb = $wrap->mdb;

    Get the wrapped object (i.e. an instance of Mojo::Pg, Mojo::SQLite, or
    whatever else was instantiated through any of the wrappers provided in
    "new").

 mdb_class

       my $class = $wrap->mdb_class;

    The class of the wrapped object, as set in the class slot of the
    selected wrapper (see "new"). Technically, the wrapped object might be
    of another class.

    The value of this method is used to select the relevant migrations when
    calling "init".

 mdb_module

       my $class = $wrap->mdb_module;

    Alias for "mdb_class".

 migrations_for

       my $hashref = $wrap->migrations_for;

    Retrieve a hash reference with keys corresponding to classes of wrapped
    objects (e.g. Mojo::Pg, Mojo::SQLite, see "mdb_class"), and values
    corresponding to the migrations as strings (see e.g. "from_string" in
    Mojo::Pg::Migrations).

 new

       my $wrap = MojoX::MojoDbWrap->new(%args); # OR
       my $wrap = MojoX::MojoDbWrap->new(\%args);

    Create a new instance. Keys in %args can be used to set "db_url"
    (required) and "migrations_for", as well as an array reference for
    wrappers as explained below.

    Parameter db_url is in general supposed to be a string that can be used
    to instantiate a Mojo::Pg object (if it starts with postgres:// or
    postgresql://, in any case) or a Mojo::SQLite object (any other string
    accepted by "new" in Mojo::SQLite, e.g. a file path or :memory:). These
    are the two classes wrapped by default.

    Parameter wrappers allows providing a list of wrappers in case you want
    to support further classes too. This list is given as an array
    reference holding hash references with the following keys:

    can_limit

      a true value if the select method of the db object supports option
      limit, as with Mojo::Pg and Mojo::SQLite (which have it set). When
      true, "id_of" sets limit to 1. Optional, defaults to false, so a
      custom wrapper (even one for a subclass of Mojo::Pg or Mojo::SQLite)
      has to set it explicitly to get LIMIT 1.

    class

      the name of the class, which in case of selection will also be used
      to select the migrations (see "init"). Required.

    create

      a sub reference for creating a new instance, it will be called like
      this:

         $sub->($class, $db_url)

      with $class the same as class above and $db_url the same as the
      parameter db_url.

      The sub can return either a false value, in case it is not capable of
      handling $db_url, or an object that becomes the wrapped instance (as
      returned by "mdb"). Required.

      The object must provide a db method returning a database handle, as
      Mojo::Pg and Mojo::SQLite do; it is called by "db", which is used by
      all methods operating on records, including "insert".

    insert

      a sub reference for inserting a new record. It will be called like
      this:

         $sub->($wrap, $tablish, $data, $opts)

      where $wrap is the MojoX::MojoDbWrap object, $tablish is the name of
      the table or an array reference with the name of the table and the
      name of the identifier column (see "DESCRIPTION"), $data is the data
      to insert and $opts is a hash reference with insert options, derived
      from the ones passed to "insert", "upsert" (which turns conflicting
      into on_conflict) or "id_or_insert".

      When called by the methods of this module, $opts always holds key db
      with the database handle to use, which must be honored to work inside
      transactions and must not reach the insert method of the db object:
      use "db_and_opts" to get both the handle and the remaining options.
      Also note that "id_or_insert" sets on_conflict to undef (i.e. DO
      NOTHING) by default, which a custom inserter for a backend not
      supporting it has to ignore or translate.

      The return value must be the identifier of the newly inserted (or
      updated) record, or undef if no record was inserted.

      Optional, defaults to "default_inserter", which requires support for
      RETURNING.

    The hash references in wrappers are copied, so they are not modified.
    An exception with code 400 is thrown if wrappers is not an array
    reference, or any of its items is not a hash reference or lacks a
    required key, or has a non-code value for create or insert.

    When the wrapped object is needed, the create subs of the items in
    wrappers are called in order, until one returns a true value, which is
    taken as the wrapped object. Mojo::Pg and Mojo::SQLite are always tried
    last, in this order; Mojo::SQLite declines URLs it cannot parse, e.g.
    mysql://....

    If no item accepts db_url, an exception with code 400 and message
    unsupported DB URL is thrown. The URL is neither part of the message
    nor attached as exception data, as it might hold a password and
    exceptions often end up in logs.

    Mojo::Pg and Mojo::SQLite are not hard dependencies, and each is loaded
    only when its turn comes. If the needed one is not installed, the error
    from require is thrown as is (not as an Ouch exception). As
    Mojo::SQLite is the last resort, this means that without it any db_url
    not accepted by previous items leads to that error, even one that would
    be unsupported anyway (e.g. mysql://...).

 upsert

       my $id = $wrap->upsert($tablish, $data, $opts);

    Insert a new record, or update it if already present, returning its
    identifier (see "insert"). Option db is supported as in "insert".

    The assumption is that a conflict in the insertion can arise on a
    single column, or on a set of columns (e.g. a UNIQUE constraint). Pass
    its name, or an array reference with their names, as the value of key
    conflicting in $opts, like this:

       $wrap->upsert($tablish, $data, { conflicting => 'name', ... });
       $wrap->upsert($tablish, $data, { conflicting => [qw< a b >], ... });

    This generates an on_conflict option for "insert", setting all values
    in $data except the conflicting ones. If $data only holds the
    conflicting columns, the update sets the (first) conflicting column to
    its own value, so that the identifier of the existing record is still
    returned. Note that this is an update anyway, e.g. for triggers.

    All conflicting columns must have a defined value in $data: as NULL
    values never conflict, a new record would be inserted every time.

    If you need a more nuanced upsert, generate the on_conflict option
    yourself and pass it in $opts without conflicting (it's still advisable
    to call this method instead of "insert", so that you're explicit about
    your goal).

    An exception with code 400 is thrown when:

      * neither conflicting nor on_conflict is provided, as this would be a
      plain "insert";

      * both conflicting and on_conflict are provided;

      * conflicting is an empty array reference;

      * conflicting is provided and $data is not a hash reference (the
      column names are needed);

      * any conflicting column is missing or undef in $data.

SECURITY CONSIDERATIONS

    First of all, read the "COPYRIGHT AND LICENSE" section below very
    carefully, then read on here.

    With that as a basic foundation, this module tries to do its best to
    avoid introducing any security issue, as well as trying to catch some
    issues that might arise from the inputs it receives.

    Some notes that you should keep in mind:

      * You are supposed to validate all input parameters, especially those
      that will be passed down to other modules that do what I mean, like
      SQL::Abstract or its derivatives.

      * The condition parameter in "id_of" and "id_or_insert" should be
      used to pinpoint at most one record. If it matches multiple ones, you
      are probably doing something wrong; at the very least, you can't be
      sure to receive a consistent response from your queries, unless you
      also set option order_by (in "id_of", or in the select options of
      "id_or_insert").

      * Again on the condition parameter, an undefined value or a value
      that is not a reference leads to an exception with code 400: the
      latter would be bound as WHERE ?, so that e.g. "1" would match every
      record. Moreover, if the wrapped object supports method abstract (as
      Mojo::Pg and Mojo::SQLite do), the object it returns is assumed to
      support SQL::Abstract's interface, including a where method. In this
      case, the module calls it to generate a WHERE clause and checks
      whether it is empty, throwing an exception with code 400 if it is.
      This still leaves you wide margins to shoot yourself in the foot,
      though, like providing an always-true condition. Again, read the
      bullet immediately before this.

      * Conditions, data and options are passed to SQL::Abstract::Pg as
      they are. Table and column names are quoted, and plain values are
      bound as placeholders, so values can safely come from users. The
      structure must not: nested hashes from untrusted inputs (e.g. from
      decoded JSON) are read as operators like { name => { -like => '%' }
      }, and SQL::Abstract lets almost any operator text through to the
      query, i.e. this is SQL injection. Build conditions and data in your
      code, from scalar values, e.g. { email => "$email" }, and never pass
      an untrusted hash reference as it is.

      * Exceptions thrown by this module never hold the DB URL, which might
      hold a password (see "new"); connection errors from DBI show the
      database, host and user name, but not the password. The URL is still
      available via "db_url", and the password via the wrapped object (e.g.
      "password" in Mojo::Pg): beware of dumping them, e.g. in logs. The
      create subs of custom wrappers receive the URL too: keep it out of
      their exceptions.

      * Errors from the database are thrown as they are, and can hold
      values from your data, e.g. PostgreSQL's Key (email)=(...) already
      exists: beware of exposing them, e.g. in HTTP responses or logs.

      * Parameters "db_url", "migrations_for" and wrappers (see "new") must
      come from trusted sources, like your configuration or code:
      migrations are run as SQL, wrappers are code, and both Mojo::Pg and
      Mojo::SQLite turn the query string of the URL into options for DBI,
      e.g. ?RaiseError=0.

BUGS AND LIMITATIONS

    Minimum perl version 5.24.

    Getting identifiers uses RETURNING, so SQLite 3.35.0 or later is
    needed. Please send requests/patches if you need support for something
    older; most probably you will have to code a custom inserter anyway.

    The test suite runs tests against PostgreSQL only if environment
    variable TEST_PG is set to a connection URL, e.g.:

       TEST_PG=postgresql://user@localhost:5432/test prove -l t

    The tests create a schema with a unique name, and drop it at the end.

    Please report any bug or feature request through the repository of the
    project, available at https://codeberg.org/polettix/MojoX-MojoDbWrap.

AI ASSISTANCE

    This module was developed with the assistance of artificial
    intelligence (Anthropic Claude Opus 5.5, through Claude Code). The AI
    was used after release 0.006 to:

      * Write and run new tests (including the test library module
      t/lib/MojoDbWrapTests.pm).

      * Conduct code and security reviews, assisting in understanding the
      issues and checking their implementation.

      * Write most of the documentation: POD, Changes, and many commit
      messages. Some parts were written with the "AUTHOR".

    Code has generally been written by the "AUTHOR" or, in small parts, by
    the AI, then reviewed and corrected by the "AUTHOR", who is responsible
    for the module, within the terms of "COPYRIGHT AND LICENSE".

    Contributions by the AI can be found in the code repository, where
    AI-assisted commits carry a Co-Authored-By trailer; see "BUGS AND
    LIMITATIONS".

AUTHOR

    Flavio Poletti <flavio@polettix.it>

COPYRIGHT AND LICENSE

    Copyright 2026 by Flavio Poletti <flavio@polettix.it>

    Licensed under the Apache License, Version 2.0 (the "License"); you may
    not use this file except in compliance with the License. You may obtain
    a copy of the License at

        http://www.apache.org/licenses/LICENSE-2.0

    Unless required by applicable law or agreed to in writing, software
    distributed under the License is distributed on an "AS IS" BASIS,
    WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or
    implied. See the License for the specific language governing
    permissions and limitations under the License.

