3 DBIx::Class::Manual::Intro - Introduction to DBIx::Class
7 You're bored with SQL, and want a native Perl interface for your database? Or
8 you've been doing this for a while with L<Class::DBI>, and think there's a
9 better way? You've come to the right place.
11 =head1 THE DBIx::Class WAY (CLIFF NOTES)
13 Here are a few simple tips that will help you get your bearings with
16 =head2 Tables become Result classes
18 L<DBIC|DBIx::Class> needs to know what your Table structure looks like. You
19 do that by defining L<Result classes|DBIx::Class::Manual::ResultClass>. Each
20 Result class defines one Table, which defines the Columns it has, any
21 L<Relationships|DBIx::Class::Relationship> it has to other tables, and much more.
23 The important thing to understand:
25 A Result class ~~ Table
27 (most of the time, but just bear with my simplification)
29 =head2 It's all about the ResultSet
31 Let's say we defined a L<Result class|DBIx::Class::Manual::ResultClass> (called
32 C<MyApp::Schema::Result::Artist>) for an C<album> table with three columns:
33 C<albumid>, C<artist>, and C<title>. Any time we want to query this table, we'll
34 be creating a L<ResultSet|DBIx::Class::ResultSet> from its
35 L<Schema|DBIx::Class::Schema>. For example, the results of:
37 SELECT albumid, artist, title FROM album;
39 Would be represented like so:
41 my $rs = $schema->resultset('Album')->search(undef, {
42 columns => [qw{ albumid artist title }]
45 L<DBIC|DBIx::Class> doesn't limit you to creating only simple ResultSets -- if you
46 wanted to do something like:
48 SELECT title FROM album GROUP BY title;
50 You could easily achieve it, like this:
52 my $rs = $schema->resultset('Album')->search(undef, {
54 group_by => ['title'],
57 The important thing to understand:
59 Instead of writing SQL queries manually, you ask a ResultSet object to
62 =head2 Search results are returned as "Rows"
64 Rows of the search from the database are blessed into
65 L<Result objects|DBIx::Class::Manual::ResultClass>. This might seem conflicting,
66 but this is because a Result class is supposed to be customizable "toolset" for
67 both result management and table definition.
69 The important thing to understand:
71 When -defining- a Result class, the purpose of the class is to:
72 * Define the table, columns, and constraints
73 * Define relationships to other tables
75 When -using- a Result object, the purpose of the object is to:
76 * Read data from a result (like a row)
77 * Perform CRUD operations, based on that data
79 * Use custom methods defined in the class by you
81 =head2 Search is like "prepare"
83 L<DBIC|DBIx::Class> tends to wait until it absolutely must fetch information from the
84 database. If you are returning a L<ResultSet|DBIx::Class::ResultSet>, the
85 query won't execute until you use a method that wants to access the data, such
86 as C<next> or C<first>.
88 # Does not run any SQL statements yet
89 my $rs = $schema->resultset('Album')->search(undef, {
90 columns => [qw{ albumid artist title }]
93 # Will trigger the SQL query once and loop through the results
94 while (my $result = $rs->next) {
95 my $artist = $result->artist;
99 =head1 SETTING UP DBIx::Class
101 Let's look at how you can set and use your first native L<DBIC|DBIx::Class> tree.
103 =head2 Which deployment method to take
105 There are a few different ways to create your L<DBIC|DBIx::Class> tree. Which
106 recommended method to take depends on how your database currently exists:
108 * If you are creating a brand new database, set up the tree manually, and then
109 create the tables via $schema->deploy (or another deployment tool).
110 * If the database already exists, use DBIx::Class::Schema::Loader or another
113 Since L<DBIC|DBIx::Class> first came into the scene, there have been a number of
114 useful deployment tools (schema builders) that ease the process (from oldest to
121 L<DBIx::Class::Schema::Loader> (existing DB only)
125 L<DBIx::Class::DeploymentHandler> (new or existing DB)
129 L<DBIx::Class::Migration> (new or existing DB)
133 This document only covers the manual method and L<DBICSL|/Using
134 DBIx::Class::Schema::Loader>. However, users with complex schemas might want
135 to check out the latest deployment tech to make life easier.
137 =head2 Setting it up manually
141 First, you should create your base schema class, which inherits from
142 L<DBIx::Class::Schema>:
145 use base qw/DBIx::Class::Schema/;
147 In this class, you load your C<result_source> ("table", "model") classes, which
148 we will define later, using the L<load_namespaces|DBIx::Class::Schema/load_namespaces>
151 # load My::Schema::Result::* and their resultset classes
152 __PACKAGE__->load_namespaces();
154 By default, this loads all the L<Result classes|DBIx::Class::Manual::ResultClass>
155 in the C<My::Schema::Result::> namespace, and also any ResultSet classes in the
156 C<My::Schema::ResultSet::> namespace. (If missing, the ResultSets are
157 defaulted to be L<DBIx::Class::ResultSet> objects.) You can change the Result
158 and ResultSet namespaces by using options to the
159 L<load_namespaces|DBIx::Class::Schema/load_namespaces> call.
161 It is also possible to do the same things manually by calling
162 L<load_classes|DBIx::Class::Schema/load_classes> for the
163 L<Result classes|DBIx::Class::Manual::ResultClass> and
164 defining in those classes any required ResultSet classes.
170 Next, create each of the classes you want to load as specified above:
172 package My::Schema::Result::Album;
173 use base qw/DBIx::Class::Core/;
175 Load any additional components you may need with the
176 L<load_components|Class::C3::Componentised/load_components( @comps )> method,
177 and provide component configuration if required. For example, if you want
178 automatic row ordering:
180 __PACKAGE__->load_components(qw/ Ordered /);
181 __PACKAGE__->position_column('rank');
183 (See L<DBIx::Class::Ordered> for more information.)
185 Set the table for your class:
187 __PACKAGE__->table('album');
191 Add columns to your class:
193 __PACKAGE__->add_columns(qw/ albumid artist title rank /);
195 Each column can also be set up with its own accessor, data_type and other pieces
196 of information that it may be useful to have -- just pass C<add_columns> a hash:
198 __PACKAGE__->add_columns(
201 data_type => 'integer',
203 is_auto_increment => 1,
206 data_type => 'integer',
210 data_type => 'varchar',
215 data_type => 'integer',
221 L<DBIC|DBIx::Class> doesn't directly use most of this data, but various
222 related modules, such as L<HTML::FormHandler::Model::DBIC>, make use of it. Also,
223 it allows you to create your database tables from your Schema, instead of the
224 other way around. See L<DBIx::Class::Schema/deploy> for details.
226 See L<DBIx::Class::ResultSource/add_columns> for more details of the possible column
229 Accessors are created for each column automatically, so C<My::Schema::Result::Album>
230 will have C<albumid> (or C<album>, when using the accessor shown above), C<artist>
231 and C<title> methods.
235 Define a primary key for your class:
237 __PACKAGE__->set_primary_key('albumid');
239 If you have a multi-column primary key, just pass a list instead:
241 __PACKAGE__->set_primary_key( qw/ albumid artistid / );
243 See also L</The Significance and Importance of Primary Keys>.
247 Define this class' relationships with other classes to make predefined accessors
248 for your L<Result class|DBIx::Class::Manual::ResultClass>. Use either
249 L<belongs_to|DBIx::Class::Relationship/belongs_to> to describe columns which
250 contain an ID of another Table, or
251 L<has_many|DBIx::Class::Relationship/has_many> to fetch objects that contain this
254 # in My::Schema::Result::Artist
255 __PACKAGE__->has_many('albums', 'My::Schema::Result::Album', 'artist');
257 See L<DBIx::Class::Relationship> for more information about the various types of
258 available relationships and how you can design your own.
260 =head2 Using DBIx::Class::Schema::Loader
262 L<DBICSL|DBIx::Class::Schema::Loader> is an external module, and not part
263 of the L<DBIC|DBIx::Class> distribution. It inspects your database, and
264 automatically creates classes for all the tables in your schema.
266 The simplest way to use it is via the L<dbicdump> script from the
267 L<DBIx::Class::Schema::Loader> distribution. For example:
269 $ dbicdump -o dump_directory=./lib \
270 -o components='["InflateColumn::DateTime"]' \
271 MyApp::Schema dbi:mysql:mydb user pass
273 If you have a mixed-case database, use the C<preserve_case> option, e.g.:
275 $ dbicdump -o dump_directory=./lib -o preserve_case=1 \
276 -o components='["InflateColumn::DateTime"]' \
277 MyApp::Schema dbi:mysql:mydb user pass
279 If you are using L<Catalyst>, then you can use the helper that comes with
280 L<Catalyst::Model::DBIC::Schema>:
282 $ script/myapp_create.pl model MyDB DBIC::Schema MyDB::Schema \
283 create=static moniker_map='{ foo => "FOO" }' dbi:SQLite:./myapp.db \
284 on_connect_do='PRAGMA foreign_keys=ON' quote_char='"'
286 See L<Catalyst::Helper::Model::DBIC::Schema> for more information on this
289 See the L<DBIx::Class::Schema::Loader> and L<DBIx::Class::Schema::Loader::Base>
290 documentation for more information on the many loader options.
294 To connect to your Schema, you need to provide the connection details or a
297 =head3 Via connection details
299 The arguments are the same as for L<DBI/connect>:
301 my $schema = My::Schema->connect('dbi:SQLite:/home/me/myapp/my.db');
303 You can create as many different schema instances as you need. So if you have a
304 second database you want to access:
306 my $other_schema = My::Schema->connect( $dsn, $user, $password, $attrs );
308 Note that L<DBIx::Class::Schema> does not cache connections for you. If you use
309 multiple connections, you need to do this manually.
311 To execute some SQL statements on every connect you can add them as an option in
312 a special fifth argument to connect:
314 my $another_schema = My::Schema->connect(
319 { on_connect_do => \@on_connect_sql_statments }
322 See L<DBIx::Class::Storage::DBI/connect_info> for more information about
323 this and other special C<connect>-time options.
325 =head3 Via a database handle
327 The supplied coderef is expected to return a single connected database handle
328 (e.g. a L<DBI> C<$dbh>)
330 my $schema = My::Schema->connect (
331 sub { Some::DBH::Factory->connect },
337 Once you've defined the basic classes, either manually or using
338 L<DBIx::Class::Schema::Loader>, you can start interacting with your database.
340 To access your database using your $schema object, you can fetch a
341 L<DBIx::Class::Manual::Glossary/"ResultSet"> representing each of your tables by
342 calling the C<resultset> method.
344 The simplest way to get a record is by primary key:
346 my $album = $schema->resultset('Album')->find(14);
348 This will run a C<SELECT> with C<albumid = 14> in the C<WHERE> clause, and
349 return an instance of C<My::Schema::Result::Album> that represents this row. Once you
350 have that row, you can access and update columns:
352 $album->title('Physical Graffiti');
353 my $title = $album->title; # $title holds 'Physical Graffiti'
355 If you prefer, you can use the C<set_column> and C<get_column> accessors
358 $album->set_column('title', 'Presence');
359 $title = $album->get_column('title');
361 Just like with L<Class::DBI>, you call C<update> to save your changes to the
362 database (by executing the actual C<UPDATE> statement):
366 If needed, you can throw away your local changes:
368 $album->discard_changes if $album->is_changed;
370 As you can see, C<is_changed> allows you to check if there are local changes to
373 =head2 Adding and removing rows
375 To create a new record in the database, you can use the C<create> method. It
376 returns an instance of C<My::Schema::Result::Album> that can be used to access the data
379 my $new_album = $schema->resultset('Album')->create({
380 title => 'Wish You Were Here',
381 artist => 'Pink Floyd'
384 Now you can add data to the new record:
386 $new_album->label('Capitol');
387 $new_album->year('1975');
390 Likewise, you can remove it from the database:
394 You can also remove records without retrieving them first, by calling delete
395 directly on a ResultSet object.
397 # Delete all of Falco's albums
398 $schema->resultset('Album')->search({ artist => 'Falco' })->delete;
400 =head2 Finding your objects
402 L<DBIC|DBIx::Class> provides a few different ways to retrieve data from your
403 database. Here's one example:
405 # Find all of Santana's albums
406 my $rs = $schema->resultset('Album')->search({ artist => 'Santana' });
408 In scalar context, as above, C<search> returns a L<DBIx::Class::ResultSet>
409 object. It can be used to peek at the first album returned by the database:
411 my $album = $rs->first;
414 You can loop over the albums and update each one:
416 while (my $album = $rs->next) {
417 print $album->artist . ' - ' . $album->title;
422 Or, you can update them all at once:
424 $rs->update({ year => 2001 });
426 In list context, the C<search> method returns all of the matching rows:
428 # Fetch immediately all of Carlos Santana's albums
429 my @albums = $schema->resultset('Album')->search(
430 { artist => 'Carlos Santana' }
432 foreach my $album (@albums) {
433 print $album->artist . ' - ' . $album->title;
436 We also provide a handy shortcut for doing a C<LIKE> search:
438 # Find albums whose artist starts with 'Jimi'
439 my $rs = $schema->resultset('Album')->search_like({ artist => 'Jimi%' });
441 Or you can provide your own C<WHERE> clause:
443 # Find Peter Frampton albums from the year 1986
444 my $where = 'artist = ? AND year = ?';
445 my @bind = ( 'Peter Frampton', 1986 );
446 my $rs = $schema->resultset('Album')->search_literal( $where, @bind );
448 The preferred way to generate complex queries is to provide a L<SQL::Abstract>
449 construct to C<search>:
451 my $rs = $schema->resultset('Album')->search({
452 artist => { '!=', 'Janis Joplin' },
453 year => { '<' => 1980 },
454 albumid => { '-in' => [ 1, 14, 15, 65, 43 ] }
457 This results in something like the following C<WHERE> clause:
459 WHERE artist != 'Janis Joplin'
461 AND albumid IN (1, 14, 15, 65, 43)
463 For more examples of complex queries, see L<DBIx::Class::Manual::Cookbook>.
465 The search can also be modified by passing another hash with
468 my @albums = My::Schema->resultset('Album')->search(
469 { artist => 'Bob Marley' },
470 { rows => 2, order_by => { -desc => 'year' } }
473 C<@albums> then holds the two most recent Bob Marley albums.
475 For more information on what you can do with a L<DBIx::Class::ResultSet>, see
476 L<DBIx::Class::ResultSet/METHODS>.
478 For a complete overview of the available attributes, see
479 L<DBIx::Class::ResultSet/ATTRIBUTES>.
483 =head2 The Significance and Importance of Primary Keys
485 The concept of a L<primary key|DBIx::Class::ResultSource/set_primary_key> in
486 L<DBIC|DBIx::Class> warrants special discussion. The formal definition (which somewhat
487 resembles that of a classic RDBMS) is I<a unique constraint that is least
488 likely to change after initial row creation>. However, this is where the
489 similarity ends. Any time you call a CRUD operation on a row (e.g.
490 L<delete|DBIx::Class::Row/delete>,
491 L<update|DBIx::Class::Row/update>,
492 L<discard_changes|DBIx::Class::Row/discard_changes>,
493 etc.), L<DBIC|DBIx::Class> will use the values of of the
494 L<primary key|DBIx::Class::ResultSource/set_primary_key> columns to populate
495 the C<WHERE> clause necessary to accomplish the operation. This is why it is
496 important to declare a L<primary key|DBIx::Class::ResultSource/set_primary_key>
497 on all your result sources B<even if the underlying RDBMS does not have one>.
498 In a pinch, one can always declare each row identifiable by all its columns:
500 __PACKAGE__->set_primary_key(__PACKAGE__->columns);
502 Note that L<DBIC|DBIx::Class> is smart enough to store a copy of the PK values before
503 any row-object changes take place, so even if you change the values of PK
504 columns, the C<WHERE> clause will remain correct.
506 If you elect not to declare a C<primary key>, L<DBIC|DBIx::Class> will behave correctly
507 by throwing exceptions on any row operation that relies on unique identifiable
508 rows. If you inherited datasets with multiple identical rows in them, you can
509 still operate with such sets provided you only utilize
510 L<DBIx::Class::ResultSet> CRUD methods:
511 L<search|DBIx::Class::ResultSet/search>,
512 L<update|DBIx::Class::ResultSet/update>,
513 L<delete|DBIx::Class::ResultSet/delete>
515 For example, the following would not work (assuming C<People> does not have
518 my $row = $schema->resultset('People')
519 ->search({ last_name => 'Dantes' })
521 $row->update({ children => 2 }); # <-- exception thrown because $row isn't
524 So, instead the following should be done:
526 $schema->resultset('People')
527 ->search({ last_name => 'Dantes' })
528 ->update({ children => 2 }); # <-- update's ALL Dantes to have children of 2
530 =head2 Problems on RHEL5/CentOS5
532 There used to be an issue with the system perl on Red Hat Enterprise
533 Linux 5, some versions of Fedora and derived systems. Further
534 information on this can be found in L<DBIx::Class::Manual::Troubleshooting>
540 =item * L<DBIx::Class::Manual::Cookbook>
542 =item * L<DBIx::Class::Manual::ResultClass>