/ 
DBIO-0.900000
/ DBIO::Manual::Features

NAME

DBIO::Manual::Features - Feature overview with links to the relevant DBIO documentation

VERSION

version 0.900000

PROJECT

Broad Contributor Base

DBIO has accumulated contributions across core, drivers, tests, and documentation. See "AUTHORS" in DBIO and AUTHORS for the current list.

Active Development

Development activity changes over time, but DBIO continues to evolve across core and driver distributions. For recent releases and changelog history, see https://metacpan.org/changes/distribution/DBIO.

Practical Feedback Loop

Users and maintainers regularly turn driver-specific gaps into concrete fixes
Documentation improvements, failing tests, and focused bug reports are all useful contributions

General ORM

These are familiar ORM features, but they are still good reasons to use DBIO instead of hand-writing every query.

Cross DB

Most schema and query code should remain portable across drivers with little or no backend-specific tweaking.

Basic CRUD

C - Create
R - Retrieve
U - Update
D - Delete

SQL: Create

my $sth = $dbh->prepare('
   INSERT INTO books
   (title, author_id)
   values (?,?)
');

$sth->execute( 'A book title', $author_id );

DBIO: Create

my $book = $book_rs->create({
   title     => 'A book title',
   author_id => $author_id,
});

See "create" in DBIO::ResultSet

No need to pair placeholders and values
Automatically gets autoincremented id for you
Transparently uses INSERT ... RETURNING for databases that support it

SQL: Read

my $sth = $dbh->prepare('
   SELECT title,
   authors.name as author_name
   FROM books, authors
   WHERE books.author = authors.id
');

while ( my $book = $sth->fetchrow_hashref ) {
  say "Author of $book->{title} is $book->{author_name}";
}

DBIO: Read

my $book = $book_rs->find($book_id);

or

my $book = $book_rs->search({ title => 'A book title' }, { rows => 1 })->next;

or

my @books = $book_rs->search({ author => $author_id })->all;

or

while( my $book = $books_rs->next ) {
  printf "Author of %s is %s\n", $book->title, $book->author->name;
}

See "find" in DBIO::ResultSet, "search" in DBIO::ResultSet, "next" in DBIO::ResultSet, and "all" in DBIO::ResultSet

DBIO intentionally supports several convenient query shapes here.

SQL: Update

my $update = $dbh->prepare('
   UPDATE books
   SET title = ?
   WHERE id = ?
');

$update->execute( 'New title', $book_id );

DBIO: Update

$book->update({ title => 'New title' });

See "update" in DBIO::Row

Will not update unless value changes

SQL: Delete

my $delete = $dbh->prepare('DELETE FROM books WHERE id = ?');

$delete->execute($book_id);

DBIO: Delete

$book->delete

See "delete" in DBIO::Row

my $sth = $dbh->prepare('
  SELECT title,
  authors.name as author_name
  FROM books
  WHERE books.name LIKE "%monte cristo%" AND
  books.topic = "jailbreak"
');
my $book = $book_rs->search({
   'me.name'  => { -like => '%monte cristo%' },
   'me.topic' => 'jailbreak',
})->next;
See SQL::Abstract, "next" in DBIO::ResultSet, and "search" in DBIO::ResultSet
Introspectable at the Perl level
Easier to compose than raw SQL in Perl code

Object-Level Overrides

Override new if you want to do validation
Override delete if you want to disable deletion
Domain logic can live next to the result classes it belongs to

Convenience Methods

"find_or_create" in DBIO::ResultSet
"update_or_create" in DBIO::ResultSet

Non-column methods

Need a method to build a user's gravatar URL? Add a gravatar_url method to the result class.

RELATIONSHIPS

"belongs_to" in DBIO::Relationship
"has_many" in DBIO::Relationship
"might_have" in DBIO::Relationship
"has_one" in DBIO::Relationship
"many_to_many" in DBIO::Relationship
SET AND FORGET

DBIO Specific Features

These features are especially DBIO-shaped, even when similar ideas exist in other ORMs.

->deploy

Create a database from your DBIO schema.

my $schema = Frew::Schema->connect( $dsn, $user, $pass );

$schema->deploy

See "deploy" in DBIO::Schema.

Generate

Create DBIO schema classes by introspecting an existing database.

dbiogen -o dump_directory=./lib MyApp::Schema $dsn $user $pass

See dbiogen and DBIO::Generate.

Populate

Made for inserting lots of rows very quickly into database

$schema->populate([ Users =>
   [qw( username password )],
   [qw( frew     >=4char$ )],
   [qw(      ...          )],
   [qw(      ...          )],
);

See "populate" in DBIO::Schema

See "populate" in DBIO::Schema for details on bulk loading data.

Multicreate

Create an object and its related objects all at once

$schema->resultset('Author')->create({
   name => 'Stephen King',
   books => [{ title => 'The Dark Tower' }],
   address => {
      street => '123 Turtle Back Lane',
      state  => { abbreviation => 'ME' },
      city   => { name => 'Lowell'     },
   },
});

See "create" in DBIO::ResultSet

books is a has_many
address is a belongs_to which in turn belongs to state and city each
for this to work right state and city must mark abbreviation and name as unique

Extensible

DBIO helped pioneer fast MI in Perl 5 with Class::C3, so it is made to allow extensions to nearly every part of it.

Extensibility example: Row helpers (integrated into DBIO::Row)

The following helpers from DBIx::Class::Helpers are integrated natively into DBIO::Row and require no load_components call:

"TO_JSON" in DBIO::Row — serializable hashref, excludes text/blob columns
"serializable_columns" in DBIO::Row — list of serializable columns
"self_rs" in DBIO::Row — ResultSet for a single row
"get_storage_value" in DBIO::Row — original DB value before in-memory changes
"before_column_change" in DBIO::Row — fire callback before a column is updated
"after_column_change" in DBIO::Row — fire callback after a column is updated
"around_column_change" in DBIO::Row — wrap update with a column-aware around hook
"proxy_resultset_method" in DBIO::Row — expose ResultSet with_ methods as row accessors

Extensibility example: DBIO::TimeStamp

See DBIO::TimeStamp
Cross DB
set_on_create
set_on_update

Result vs ResultSet

Result == Row
ResultSet == Query Plan
Internal Join Optimizer for all DB's (!!!)
(less important but...)
ResultSource == Queryable collection of rows (Table, View, etc)
Storage == Database
Schema == associates a set of ResultSources with a Storage

ResultSet methods

package MyApp::Schema::ResultSet::Book;

use strict;
use warnings;

use base 'DBIO::ResultSet';

sub good {
   my $self = shift;
   $self->search({
      $self->current_source_alias . '.rating' => { '>=' => 4 }
   })
};

sub cheap {
   my $self = shift;
   $self->search({
      $self->current_source_alias . '.price' => { '<=' => 5}
   })
};

# ...

1;

See "Predefined searches" in DBIO::Manual::Cookbook

All searches should be ResultSet methods
Name has obvious meaning
"current_source_alias" in DBIO::ResultSet helps things to work no matter what

ResultSet method in Action

$schema->resultset('Book')->good

ResultSet Chaining

$schema->resultset('Book')
   ->good
   ->cheap
   ->recent
my $score = $schema->resultset('User')
   ->search({'me.userid' => 'frew'})
   ->related_resultset('access')
   ->related_resultset('mgmt')
   ->related_resultset('orders')
   ->telephone
   ->search_related( shops => {
      'shops.datecompleted' => {
         -between => ['2009-10-01','2009-10-08']
      }
   })->completed
   ->related_resultset('rpt_score')
   ->search(undef, { rows => 1})
   ->get_column('raw_scores')
   ->next;

The SQL that this produces (with placeholders filled in for clarity's sake) on our system (Microsoft SQL) is:

SELECT raw_scores
  FROM (
    SELECT raw_scores, ROW_NUMBER() OVER (
        ORDER BY (
            SELECT (1)
          )
      ) AS rno__row__index
      FROM (
        SELECT rpt_score.raw_scores
          FROM users me
          JOIN access access
            ON access.userid = me.userid
          JOIN mgmt mgmt
            ON mgmt.mgmtid = access.mgmtid
          JOIN [order] orders
            ON orders.mgmtid = mgmt.mgmtid
          JOIN shop shops
            ON shops.orderno = orders.orderno
          JOIN rpt_scores rpt_score
            ON rpt_score.shopno = shops.shopno
        WHERE (
          datecompleted IS NOT NULL AND
          (
            (shops.datecompleted BETWEEN '2009-10-01' AND '2009-10-08')  AND
            (type = '1' AND me.userid = 'frew')
          )
        )
      ) rpt_score
  ) rpt_score
WHERE rno__row__index BETWEEN 1 AND 1

See: "related_resultset" in DBIO::ResultSet, "search_related" in DBIO::ResultSet, and "get_column" in DBIO::ResultSet.

bonus rel methods

my $book = $author->create_related(
   books => {
      title => 'Another Discworld book',
   }
);

my $book2 = $pratchett->add_to_books({
   title => 'MOAR Discworld book',
});

See "create_related" in DBIO::Relationship::Base and "add_to_$rel" in DBIO::Relationship::Base

Note that it automatically fills in foreign key for you

Excellent Transaction Support

$schema->txn_do(sub {
   ...
});

$schema->txn_begin; # <-- low level
# ...
$schema->txn_commit;

See "txn_do" in DBIO::Schema, "txn_begin" in DBIO::Schema, and "txn_commit" in DBIO::Schema.

InflateColumn

package Frew::Schema::Result::Book;

use strict;
use warnings;

use base 'DBIO::Core';

use DateTime::Format::MySQL;

# Result code here

__PACKAGE__->load_components('InflateColumn');

__PACKAGE__->inflate_column(
   date_published => {
      inflate => sub { DateTime::Format::MySQL->parse_date( shift ) },
      deflate => sub { shift->ymd },
   },
);

See DBIO::InflateColumn, "inflate_column" in DBIO::InflateColumn, and DBIO::InflateColumn::DateTime.

InflateColumn: deflation

$book->date_published(DateTime->now);
$book->update;

InflateColumn: inflation

say $book->date_published->month_abbr; # Nov

FilterColumn

package Frew::Schema::Result::Book;

use strict;
use warnings;

use base 'DBIO::Core';

# Result code here

__PACKAGE__->load_components('FilterColumn');

__PACKAGE__->filter_column(
   length => {
      to_storage   => 'to_metric',
      from_storage => 'to_imperial',
   },
);

sub to_metric   { $_[1] * .305 }
sub to_imperial { $_[1] * 3.28 }

See DBIO::FilterColumn and "filter_column" in DBIO::FilterColumn

ResultSetColumn

my $rsc = $schema->resultset('Book')->get_column('price');
$rsc->first;
$rsc->all;
$rsc->min;
$rsc->max;
$rsc->sum;

See DBIO::ResultSetColumn

Aggregates

my @res = $rs->search(undef, {
   select   => [
      'price',
      'genre',
      { max => price },
      { avg => price },
   ],
   as       => [
      qw(price genre max_price avg_price)
   ],
   group_by => [qw(price genre)],
});
for (@res) {
   say $_->price . ' ' . $_->genre;
   say $_->get_column('max_price');
   say $_->get_column('avg_price');
}

See "select" in DBIO::ResultSet, "as" in DBIO::ResultSet, and "group_by" in DBIO::ResultSet

Careful, get_column can basically mean three things
private in which case you should use an accessor
public for what there is no accessor for
public for get resultset column (prev example)

HRI

$rs->search(undef, {
  result_class => 'DBIO::ResultClass::HashRefInflator',
});

See "result_class" in DBIO::ResultSet and DBIO::ResultClass::HashRefInflator.

Easy on memory
Mega fast
Great for quick debugging
Great for performance tuning (we went from 2m to < 3s)

Subquery Support

my $inner_query = $schema->resultset('Artist')
   ->search({
    name => [ 'Billy Joel', 'Brittany Spears' ],
})->get_column('id')->as_query;

my $rs = $schema->resultset('CD')->search({
    artist_id => { -in => $inner_query },
});

See "Subqueries" in DBIO::Manual::Cookbook

Bare SQL w/ Placeholders

$rs->update({
   # !!! SQL INJECTION VECTOR
   price => \"price + $inc", # DON'T DO THIS
});

Better:

$rs->update({
   price => \['price + ?', [inc => $inc]],
});

See "Literal SQL with placeholders and bind values (subqueries)" in SQL::Abstract

AUTHOR

DBIO & DBIx::Class Authors

COPYRIGHT AND LICENSE

Copyright (C) 2026 DBIO Authors Portions Copyright (C) 2005-2025 DBIx::Class Authors Based on DBIx::Class, heavily modified.

This is free software; you can redistribute it and/or modify it under the same terms as the Perl 5 programming language system itself.