---
name: "DBIx::Class::Manual::Features"
section: "3pm"
platform: "linux"
package: "libdbix-class-perl"
version: "0.082844-1"
release: "Debian 13 (Trixie)"
platforms: ["linux"]
url: "https://new.carta.tech/DBIx::Class::Manual::Features"
---

# DBIx::Class::Manual::Features(3pm)

A boatload of DBIx::Class features with links to respective documentation

## NAME

DBIx::Class::Manual::Features - A boatload of DBIx::Class features with links to respective documentation

## META

### Large Community

There are hundres of DBIC contributors listed in *AUTHORS*. That ranges from documentation help, to test help, to added features, to entire database support.

### Active Community

Currently (June 9, 2010) 6 active branches (committed to in the last two weeks) in git.  Last release (0.08122) had 14 new features, and 16 bug fixes.  Of course that ebbs and flows \<https://metacpan.org/changes/distribution/DBIx-Class\>.)

### Responsive Community

- I needed MSSQL order-by support; the community helped me add support

  - generally very welcoming of people willing to help

  ## General ORM

These are things that are in most other ORMs, but are still reasons to use DBIC over raw SQL.

### Cross DB

The vast majority of code should run on all databases without needing 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 );
```

### DBIC: Create

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

See "create" in DBIx::Class::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}";
 }
```

### DBIC: 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 DBIx::Class::ResultSet, "search" in DBIx::Class::ResultSet, "next" in DBIx::Class::ResultSet, and "all" in DBIx::Class::ResultSet

**TMTOWTDI!**

### SQL: Update

```
 my $update = $dbh->prepare('
    UPDATE books
    SET title = ?
    WHERE id = ?
 ');
 $update->execute( 'New title', $book_id );
```

### DBIC: Update

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

See "update" in DBIx::Class::Row

Will not update unless value changes

### SQL: Delete

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

### DBIC: Delete

```
 $book->delete
```

See "delete" in DBIx::Class::Row

### SQL: Search

```
 my $sth = $dbh->prepare('
   SELECT title,
   authors.name as author_name
   FROM books
   WHERE books.name LIKE "%monte cristo%" AND
   books.topic = "jailbreak"
 ');
```

### DBIC: Search

```
 my $book = $book_rs->search({
    'me.name'  => { -like => '%monte cristo%' },
    'me.topic' => 'jailbreak',
 })->next;
```

- See SQL::Abstract::Classic, "next" in DBIx::Class::ResultSet, and "search" in DBIx::Class::ResultSet

  - (kinda) introspectible

  - Prettier than SQL

  ### OO Overridability

- Override new if you want to do validation

  - Override delete if you want to disable deletion

  - and on and on

  ### Convenience Methods

- "find\_or\_create" in DBIx::Class::ResultSet

  - "update\_or\_create" in DBIx::Class::ResultSet

  ### Non-column methods

Need a method to get a user's gravatar URL?  Add a `"gravatar_url"` method to the Result class

### RELATIONSHIPS

- "belongs\_to" in DBIx::Class::Relationship

  - "has\_many" in DBIx::Class::Relationship

  - "might\_have" in DBIx::Class::Relationship

  - "has\_one" in DBIx::Class::Relationship

  - "many\_to\_many" in DBIx::Class::Relationship

  - SET AND FORGET

  ## DBIx::Class Specific Features

These things may be in other ORM's, but they are very specific, so doubtful

### -\>deploy

Create a database from your DBIx::Class schema.

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

See "deploy" in DBIx::Class::Schema.

See also: DBIx::Class::DeploymentHandler

### Schema::Loader

Create a DBIx::Class schema from your database.

```
 package Frew::Schema;
 use strict;
 use warnings;
 use base 'DBIx::Class::Schema::Loader';
 __PACKAGE__->loader_options({
    naming => 'v7',
    debug  => $ENV{DBIC_TRACE},
 });
 1;
 # elsewhere...
 my $schema = Frew::Schema->connect( $dsn, $user, $pass );
```

See DBIx::Class::Schema::Loader and "CONSTRUCTOR OPTIONS" in DBIx::Class::Schema::Loader::Base.

### 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 DBIx::Class::Schema

I use populate here \<http://blog.afoolishmanifesto.com/archives/1255\> to export our whole (200M~) db to SQLite

### 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 DBIx::Class::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

DBIx::Class 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: DBIx::Class::Helpers

- DBIx::Class::Helper::ResultSet::IgnoreWantarray

  - DBIx::Class::Helper::ResultSet::Random

  - DBIx::Class::Helper::ResultSet::SetOperations

  - DBIx::Class::Helper::Row::JoinTable

  - DBIx::Class::Helper::Row::NumifyGet

  - DBIx::Class::Helper::Row::SubClass

  - DBIx::Class::Helper::Row::ToJSON

  - DBIx::Class::Helper::Row::StorageValues

  - DBIx::Class::Helper::Row::OnColumnChange

  ### Extensibility example: DBIx::Class::TimeStamp

- See DBIx::Class::TimeStamp

  - Cross DB

  - set\_on\_create

  - set\_on\_update

  ### Extensibility example: Kioku

- See DBIx::Class::Schema::KiokuDB

  - Kioku is the new hotness

  - Mix RDBMS with Object DB

  ### 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 'DBIx::Class::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 DBIx::Class::Manual::Cookbook

- All searches should be ResultSet methods

  - Name has obvious meaning

  - "current\_source\_alias" in DBIx::Class::ResultSet helps things to work no matter what

  ### ResultSet method in Action

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

### ResultSet Chaining

```
 $schema->resultset('Book')
    ->good
    ->cheap
    ->recent
```

### search\_related

```
 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 DBIx::Class::ResultSet, "search\_related" in DBIx::Class::ResultSet, and "get\_column" in DBIx::Class::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 DBIx::Class::Relationship::Base and "add\_to\_$rel" in DBIx::Class::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 DBIx::Class::Schema, "txn\_begin" in DBIx::Class::Schema, and "txn\_commit" in DBIx::Class::Schema.

### InflateColumn

```
 package Frew::Schema::Result::Book;
 use strict;
 use warnings;
 use base 'DBIx::Class::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 DBIx::Class::InflateColumn, "inflate\_column" in DBIx::Class::InflateColumn, and DBIx::Class::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 'DBIx::Class::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 DBIx::Class::FilterColumn and "filter\_column" in DBIx::Class::FilterColumn

### ResultSetColumn

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

See DBIx::Class::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 DBIx::Class::ResultSet, "as" in DBIx::Class::ResultSet, and "group\_by" in DBIx::Class::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 => 'DBIx::Class::ResultClass::HashRefInflator',
 });
```

See "result\_class" in DBIx::Class::ResultSet and DBIx::Class::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 DBIx::Class::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::Classic

## FURTHER QUESTIONS?

Check the list of additional DBIC resources.

## COPYRIGHT AND LICENSE

This module is free software copyright by the DBIx::Class (DBIC) authors. You can redistribute it and/or modify it under the same terms as the DBIx::Class library.