DBIx::C...::Intro(3)

💖 DBIx::Class::Manual::Intro(3) — the girl's guide 💖

okay so you're looking at DBIx::C...::Intro(3), which is the User Contributed Perl Documentation entry for DBIx::Class::Manual::Intro — literally Introduction to DBIx::Class. bestie, this is the doorway. walk through it. 🚪✨

---

🌸 INTRODUCTION 🌸

So you're bored with SQL and you want a native Perl interface for your database? Same. Or maybe you've been doing this a while with Class::DBI and you're lowkey like "there has got to be a better way"? You've come to the right place, no cap. 🫶

---

🦋 THE DBIx::Class WAY 🦋

Here are a few simple tips that'll help you get your bearings with DBIx::Class. Think of them as the vibe check before we get into the lore.

✨ Tables become Result classes ✨

DBIx::Class needs to know what your Table structure looks like, and you tell it by defining Result classes. Result classes are defined by calling methods proxied to DBIx::Class::ResultSource. Each Result class defines one Table, which defines the Columns it has, plus any Relationships it has to other tables. (And oh, so much more besides — truly the main character energy.)

The important thing to understand:

A Result class == Table

(most of the time, but just bear with my simplification 💅)

🎀 It's all about the ResultSet 🎀

So we've got some ResultSources defined. Now we want to actually use those definitions to help us translate the queries we need into handy perl objects!

Let's say we defined a ResultSource for an album table with three columns: albumid, artist, and title. Any time we want to query this table, we'll be creating a DBIx::Class::ResultSet from its ResultSource. For example, the results of:

SELECT albumid, artist, title FROM album;

would be retrieved by creating a ResultSet object from the album table's ResultSource — likely by using the search method.

DBIx::Class doesn't limit you to creating only simple ResultSets — if you wanted to do something like:

SELECT title FROM album GROUP BY title;

you could easily achieve it. iconic.

The important thing to understand:

Any time you would reach for a SQL query in DBI, you are
creating a DBIx::Class::ResultSet.

💜 Search is like "prepare" 💜

DBIx::Class tends to wait until it absolutely must fetch information from the database. If you're returning a ResultSet, the query won't execute until you use a method that actually wants to access the data. (Such as next, or first.) she's patient, she's strategic.

The important thing to understand:

Setting up a ResultSet does not execute the query; retrieving
the data does.

🌈 Search results are returned as Rows 🌈

Rows of the search from the database are blessed into Result objects. yes babe, blessed. 🧚

---

👑 SETTING UP DBIx::Class 👑

Let's look at how you can set up and use your first native DBIx::Class tree.

First we'll see how you can set up your classes yourself. If you want them to be auto-discovered, just skip to the next section, which shows you how to use DBIx::Class::Schema::Loader. (We're not mad at you, shortcuts are valid.)

🌸 Setting it up manually 🌸

First, you should create your base schema class, which inherits from DBIx::Class::Schema:

package My::Schema;
use base qw/DBIx::Class::Schema/;

In this class you load your result_source ("table", "model") classes — which we'll define later — using the load_namespaces() method:

# load My::Schema::Result::* and their resultset classes
__PACKAGE__->load_namespaces();

By default this loads all the Result (Row) classes in the My::Schema::Result:: namespace, and also any resultset classes in the My::Schema::ResultSet:: namespace (if missing, the resultsets are defaulted to be DBIx::Class::ResultSet objects). You can change the result and resultset namespaces by using options to the load_namespaces in DBIx::Class::Schema call.

It's also possible to do the same things manually by calling load_classes for the Row classes and defining in those classes any required resultset classes.

Next, create each of the classes you want to load as specified above:

package My::Schema::Result::Album;
use base qw/DBIx::Class::Core/;

Load any additional components you may need with the load_components() method, and provide component configuration if required. For example, if you want automatic row ordering:

__PACKAGE__->load_components(qw/ Ordered /);
__PACKAGE__->position_column('rank');

Ordered will refer to a field called 'position' unless otherwise directed. Here you're defining the ordering field to be named 'rank'.

⚠️ NOTE: Insert errors may occur if you use the Ordered component, but have not defined a position column or have a 'position' field in your row. so like… don't do that 🙈

Set the table for your class:

__PACKAGE__->table('album');

Add columns to your class:

__PACKAGE__->add_columns(qw/ albumid artist title rank /);

Each column can also be set up with its own accessor, data_type and other pieces of information that it may be useful to have — just pass add_columns a hash:

__PACKAGE__->add_columns(albumid =>
                          { accessor  => 'album',
                            data_type => 'integer',
                            size      => 16,
                            is_nullable => 0,
                            is_auto_increment => 1,
                          },
                        artist =>
                          { data_type => 'integer',
                            size      => 16,
                            is_nullable => 0,
                          },
                        title  =>
                          { data_type => 'varchar',
                            size      => 256,
                            is_nullable => 0,
                          },
                        rank =>
                          { data_type => 'integer',
                            size      => 16,
                            is_nullable => 0,
                            default_value => 0,
                          }
                       );

DBIx::Class doesn't directly use most of this data yet, but various related modules such as HTML::FormHandler::Model::DBIC make use of it. It also allows you to create your database tables from your Schema, instead of the other way around. See "deploy" in DBIx::Class::Schema for details. we love a reversible situation 💖

See DBIx::Class::ResultSource for more details of the possible column attributes.

Accessors are created for each column automatically, so My::Schema::Result::Album will have albumid() (or album(), when using the accessor), artist() and title() methods. free methods, literally no effort required.

Define a primary key for your class:

__PACKAGE__->set_primary_key('albumid');

If you have a multi-column primary key, just pass a list instead:

__PACKAGE__->set_primary_key( qw/ albumid artistid / );

Define this class' relationships with other classes using either belongs_to to describe a column which contains an ID of another Table, or has_many to make a predefined accessor for fetching objects that contain this Table's foreign key:

# in My::Schema::Result::Artist
__PACKAGE__->has_many('albums', 'My::Schema::Result::Album', 'artist');

See DBIx::Class::Relationship for more information about the various types of available relationships and how you can design your own.

🦋 Using DBIx::Class::Schema::Loader 🦋

This module (DBIx::Class::Schema::Loader) is an external module, and not part of the DBIx::Class distribution. Heads up on that one. It inspects your database, and automatically creates classes for all the tables in your schema. it's giving magic, honestly.

The simplest way to use it is via the dbicdump script from the DBIx::Class::Schema::Loader distribution. For example:

$ dbicdump -o dump_directory=./lib \
    -o components='["InflateColumn::DateTime"]' \
    MyApp::Schema dbi:mysql:mydb user pass

If you have a mixed-case database, use the preserve_case option, e.g.:

$ dbicdump -o dump_directory=./lib -o preserve_case=1 \
    -o components='["InflateColumn::DateTime"]' \
    MyApp::Schema dbi:mysql:mydb user pass

If you're using Catalyst, then you can use the helper that comes with Catalyst::Model::DBIC::Schema:

$ script/myapp_create.pl model MyModel DBIC::Schema MyApp::Schema \
    create=static moniker_map='{ foo => "FOO" }' dbi:SQLite:./myapp.db \
    on_connect_do='PRAGMA foreign_keys=ON' quote_char='"'

See Catalyst::Helper::Model::DBIC::Schema for more information on this helper.

See the DBIx::Class::Schema::Loader and DBIx::Class::Schema::Loader::Base documentation for more information on the many loader options. there are so many options bestie, go read them.

💅 Connecting 💅

To connect to your Schema, you need to provide the connection details or a database handle.

🌟 Via connection details 🌟

The arguments are the same as for "connect" in DBI:

my $schema = My::Schema->connect('dbi:SQLite:/home/me/myapp/my.db');

You can create as many different schema instances as you need. So if you have a second database you want to access:

my $other_schema = My::Schema->connect( $dsn, $user, $password, $attrs );

💀 Note that DBIx::Class::Schema does not cache connections for you. If you use multiple connections, you need to do this manually. nobody's doing it for you, babe.

To execute some SQL statements on every connect you can add them as an option in a special fifth argument to connect:

my $another_schema = My::Schema->connect(
    $dsn,
    $user,
    $password,
    $attrs,
    { on_connect_do => \@on_connect_sql_statments }
);

See "connect_info" in DBIx::Class::Storage::DBI for more information about this and other special connect-time options.

🍓 Via a database handle 🍓

The supplied coderef is expected to return a single connected database handle (e.g. a DBI $dbh)

my $schema = My::Schema->connect (
  sub { Some::DBH::Factory->connect },
  \%extra_attrs,
);

⭐ Basic usage ⭐

Once you've defined the basic classes — either manually or using DBIx::Class::Schema::Loader — you can start interacting with your database.

To access your database using your $schema object, you can fetch a "ResultSet" in DBIx::Class::Manual::Glossary representing each of your tables by calling the resultset method.

The simplest way to get a record is by primary key:

my $album = $schema->resultset('Album')->find(14);

This will run a SELECT with albumid = 14 in the WHERE clause, and return an instance of My::Schema::Result::Album that represents this row. Once you have that row, you can access and update columns:

$album->title('Physical Graffiti');
my $title = $album->title; # $title holds 'Physical Graffiti'

If you prefer, you can use the set_column and get_column accessors instead:

$album->set_column('title', 'Presence');
$title = $album->get_column('title');

Just like with Class::DBI, you call update to save your changes to the database (by executing the actual UPDATE statement):

$album->update;

If needed, you can throw away your local changes:

$album->discard_changes if $album->is_changed;

---

okay bestie that's the first half of the page — part 2 is right behind it, keep scrolling 💜

As you can see, is_changed lets you check whether there are local changes sitting on your object. vibe check for your row, basically 💅

Adding and removing rows

To create a new record in the database, hit up the create method. It hands you back an instance of My::Schema::Result::Album that you can use to get at the data in the new record:

my $new_album = $schema->resultset('Album')->create({
  title  => 'Wish You Were Here',
  artist => 'Pink Floyd'
});

Now you can pile data onto the new record:

$new_album->label('Capitol');
$new_album->year('1975');
$new_album->update;

And same energy in reverse — yeet it out of the database:

$new_album->delete;

You can also delete records without fetching them first, by calling delete straight-up on a ResultSet object.

# Delete all of Falco's albums
$schema->resultset('Album')->search({ artist => 'Falco' })->delete;

---

Finding your objects

DBIx::Class gives you a few different ways to pull data out of your database. Here's one example:

# Find all of Santana's albums
my $rs = $schema->resultset('Album')->search({ artist => 'Santana' });

In scalar context — like above — search returns a DBIx::Class::ResultSet object. You can use it to peek at the first album the database gives back:

my $album = $rs->first;
print $album->title;

You can loop over the albums and update each one:

while (my $album = $rs->next) {
  print $album->artist . ' - ' . $album->title;
  $album->year(2001);
  $album->update;
}

Or, lowkey efficient, update them all at once:

$rs->update({ year => 2001 });

In list context, the search method returns all of the matching rows:

# Fetch immediately all of Carlos Santana's albums
my @albums = $schema->resultset('Album')->search(
  { artist => 'Carlos Santana' }
);
foreach my $album (@albums) {
  print $album->artist . ' - ' . $album->title;
}

We also ship a handy shortcut for doing a LIKE search:

# Find albums whose artist starts with 'Jimi'
my $rs = $schema->resultset('Album')->search_like({ artist => 'Jimi%' });

Or you can bring your own WHERE clause:

# Find Peter Frampton albums from the year 1986
my $where = 'artist = ? AND year = ?';
my @bind  = ( 'Peter Frampton', 1986 );
my $rs    = $schema->resultset('Album')->search_literal( $where, @bind );

The preferred way to build complex queries is to hand search a SQL::Abstract::Classic-compatible construct:

my $rs = $schema->resultset('Album')->search({
  artist  => { '!=', 'Janis Joplin' },
  year    => { '<' => 1980 },
  albumid => { '-in' => [ 1, 14, 15, 65, 43 ] }
});

Which gives you something like this WHERE clause:

WHERE artist != 'Janis Joplin'
  AND year < 1980
  AND albumid IN (1, 14, 15, 65, 43)

For more examples of complex queries, go look at DBIx::Class::Manual::Cookbook. it's giving reference material, and it ate 🍓

The search can also be tweaked by passing another hash with attributes:

my @albums = My::Schema->resultset('Album')->search(
  { artist => 'Bob Marley' },
  { rows => 2, order_by => { -desc => 'year' } }
);

@albums then holds the two most recent Bob Marley albums. iconic behaviour, no notes ✨

For more info on what you can actually do with a DBIx::Class::ResultSet, see "METHODS" in DBIx::Class::ResultSet.

For a complete overview of the available attributes, see "ATTRIBUTES" in DBIx::Class::ResultSet.

---

🌸 NOTES 🌸

The Significance and Importance of Primary Keys

The whole concept of a primary key in DBIx::Class deserves its own little main-character moment. The formal definition (which kinda resembles a classic RDBMS one) is a unique constraint that's least likely to change after the initial row creation. Annnnd that's where the similarity ends, bestie.

Any time you call a CRUD operation on a row (e.g. delete, update, discard_changes, etc.) DBIx::Class uses the values of the primary key columns to populate the WHERE clause it needs to actually pull the operation off. That's exactly why it matters to declare a primary key on all your result sources — even if the underlying RDBMS doesn't have one. In a pinch you can always declare each row identifiable by all its columns:

__PACKAGE__->set_primary_key(__PACKAGE__->columns);

Good news: DBIx::Class is smart enough to store a copy of the PK values before any row-object changes happen, so even if you go and change the values of PK columns, the WHERE clause stays correct. 👑

If you elect not to declare a "primary key", DBIx::Class behaves correctly by throwing exceptions on any row operation that relies on uniquely identifiable rows. And if you inherited datasets with multiple identical rows in them, you can still operate on those sets — provided you only use DBIx::Class::ResultSet CRUD methods: search, update, delete

For example, the following would NOT work (assuming People has no declared PK):

my $result = $schema->resultset('People')
                  ->search({ last_name => 'Dantes' })
                   ->next;
$result->update({ children => 2 }); # <-- exception thrown because $result isn't
                                # necessarily unique

So instead, do the following:

$schema->resultset('People')
        ->search({ last_name => 'Dantes' })
         ->update({ children => 2 }); # <-- update's ALL Dantes to have children of 2

⚠️ yeah that second one updates every Dantes. fr fr read the comment before you run it 😭

Problems on RHEL5/CentOS5

There used to be an issue with the system perl on Red Hat Enterprise Linux 5, some versions of Fedora and derived systems. Further information on that can be found in DBIx::Class::Manual::Troubleshooting. ancient lore, but we keep it 🏺

---

🦋 SEE ALSO 🦋

• DBIx::Class::Manual::Cookbook

---

💜 FURTHER QUESTIONS? 💜

Check the list of additional DBIC resources. go forth and read, bestie 🫶

---

🎀 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. slay, legally speaking 💅

---

and that's the whole page!! brought to you by perl v5.42.0, dated 2025-01-22, living in DBIx::C...::Intro(3) — ty for reading, ily, go write some schemas 🌈⭐