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
13 Here are a few simple tips that will help you get your bearings with
16 =head2 Tables become Result classes
18 DBIx::Class needs to know what your Table structure looks like. You
19 do that by defining Result classes. Result classes are defined by
20 calling methods proxied to L<DBIx::Class::ResultSource>. Each Result
21 class defines one Table, which defines the Columns it has, along with
22 any Relationships it has to other tables. (And oh, so much more
23 besides) 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 So, we've got some ResultSources defined. Now, we want to actually use those
32 definitions to help us translate the queries we need into handy perl objects!
34 Let's say we defined a ResultSource for an "album" table with three columns:
35 "albumid", "artist", and "title". Any time we want to query this table, we'll
36 be creating a L<DBIx::Class::ResultSet> from its ResultSource. For example, the
39 SELECT albumid, artist, title FROM album;
41 Would be retrieved by creating a ResultSet object from the album table's
42 ResultSource, likely by using the "search" method.
44 DBIx::Class doesn't limit you to creating only simple ResultSets -- if you
45 wanted to do something like:
47 SELECT title FROM album GROUP BY title;
49 You could easily achieve it.
51 The important thing to understand:
53 Any time you would reach for a SQL query in DBI, you are
54 creating a DBIx::Class::ResultSet.
56 =head2 Search is like "prepare"
58 DBIx::Class tends to wait until it absolutely must fetch information from the
59 database. If you are returning a ResultSet, the query won't execute until you
60 use a method that wants to access the data. (Such as "next", or "first")
62 The important thing to understand:
64 Setting up a ResultSet does not execute the query; retrieving
67 =head2 Search results are returned as Rows
69 Rows of the search from the database are blessed into
70 L<DBIx::Class::Row> objects.
72 =head1 SETTING UP DBIx::Class
74 Let's look at how you can set and use your first native L<DBIx::Class> tree.
76 First we'll see how you can set up your classes yourself. If you want them to
77 be auto-discovered, just skip to the L<next section|/Using
78 DBIx::Class::Schema::Loader>, which shows you how to use
79 L<DBIx::Class::Schema::Loader>.
81 =head2 Setting it up manually
83 First, you should create your base schema class, which inherits from
84 L<DBIx::Class::Schema>:
87 use base qw/DBIx::Class::Schema/;
89 In this class you load your result_source ("table", "model") classes, which we
90 will define later, using the load_namespaces() method:
92 # load My::Schema::Result::* and their resultset classes
93 __PACKAGE__->load_namespaces();
95 By default this loads all the Result (Row) classes in the
96 My::Schema::Result:: namespace, and also any resultset classes in the
97 My::Schema::ResultSet:: namespace (if missing, the resultsets are
98 defaulted to be DBIx::Class::ResultSet objects). You can change the
99 result and resultset namespaces by using options to the
100 L<DBIx::Class::Schema/load_namespaces> call.
102 It is also possible to do the same things manually by calling
103 C<load_classes> for the Row classes and defining in those classes any
104 required resultset classes.
106 Next, create each of the classes you want to load as specified above:
108 package My::Schema::Result::Album;
109 use base qw/DBIx::Class::Core/;
111 Load any additional components you may need with the load_components() method,
112 and provide component configuration if required. For example, if you want
113 automatic row ordering:
115 __PACKAGE__->load_components(qw/ Ordered /);
116 __PACKAGE__->position_column('rank');
118 Ordered will refer to a field called 'position' unless otherwise directed. Here you are defining
119 the ordering field to be named 'rank'. (NOTE: Insert errors may occur if you use the Ordered
120 component, but have not defined a position column or have a 'position' field in your row.)
122 Set the table for your class:
124 __PACKAGE__->table('album');
126 Add columns to your class:
128 __PACKAGE__->add_columns(qw/ albumid artist title rank /);
130 Each column can also be set up with its own accessor, data_type and other pieces
131 of information that it may be useful to have -- just pass C<add_columns> a hash:
133 __PACKAGE__->add_columns(albumid =>
134 { accessor => 'album',
135 data_type => 'integer',
138 is_auto_increment => 1,
142 { data_type => 'integer',
145 is_auto_increment => 0,
149 { data_type => 'varchar',
152 is_auto_increment => 0,
156 { data_type => 'integer',
159 is_auto_increment => 0,
164 DBIx::Class doesn't directly use most of this data yet, but various related
165 modules such as L<DBIx::Class::WebForm> make use of it. Also it allows you to
166 create your database tables from your Schema, instead of the other way around.
167 See L<DBIx::Class::Schema/deploy> for details.
169 See L<DBIx::Class::ResultSource> for more details of the possible column
172 Accessors are created for each column automatically, so My::Schema::Result::Album will
173 have albumid() (or album(), when using the accessor), artist() and title()
176 Define a primary key for your class:
178 __PACKAGE__->set_primary_key('albumid');
180 If you have a multi-column primary key, just pass a list instead:
182 __PACKAGE__->set_primary_key( qw/ albumid artistid / );
184 Define this class' relationships with other classes using either C<belongs_to>
185 to describe a column which contains an ID of another Table, or C<has_many> to
186 make a predefined accessor for fetching objects that contain this Table's
189 # in My::Schema::Result::Artist
190 __PACKAGE__->has_many('albums', 'My::Schema::Result::Album', 'artist');
192 See L<DBIx::Class::Relationship> for more information about the various types of
193 available relationships and how you can design your own.
195 =head2 Using DBIx::Class::Schema::Loader
197 This module (L<DBIx::Class::Schema::Loader>) is an external module, and not part
198 of the L<DBIx::Class> distribution. It inspects your database, and automatically
199 creates classes for all the tables in your schema.
201 The simplest way to use it is via the L<dbicdump> script from the
202 L<DBIx::Class::Schema::Loader> distribution. For example:
204 $ dbicdump -o dump_directory=./lib \
205 -o components='["InflateColumn::DateTime"]' \
206 MyApp::Schema dbi:mysql:mydb user pass
208 If you have a mixed-case database, use the C<preserve_case> option, e.g.:
210 $ dbicdump -o dump_directory=./lib -o preserve_case=1 \
211 -o components='["InflateColumn::DateTime"]' \
212 MyApp::Schema dbi:mysql:mydb user pass
214 If you are using L<Catalyst>, then you can use the helper that comes with
215 L<Catalyst::Model::DBIC::Schema>:
217 $ script/myapp_create.pl model MyDB DBIC::Schema MyDB::Schema \
218 create=static moniker_map='{ foo => "FOO" }' dbi:SQLite:./myapp.db \
219 on_connect_do='PRAGMA foreign_keys=ON' quote_char='"'
221 See L<Catalyst::Helper::Model::DBIC::Schema> for more information on this
224 See the L<DBIx::Class::Schema::Loader> and L<DBIx::Class::Schema::Loader::Base>
225 documentation for more information on the many loader options.
229 To connect to your Schema, you need to provide the connection details or a
232 =head3 Via connection details
234 The arguments are the same as for L<DBI/connect>:
236 my $schema = My::Schema->connect('dbi:SQLite:/home/me/myapp/my.db');
238 You can create as many different schema instances as you need. So if you have a
239 second database you want to access:
241 my $other_schema = My::Schema->connect( $dsn, $user, $password, $attrs );
243 Note that L<DBIx::Class::Schema> does not cache connections for you. If you use
244 multiple connections, you need to do this manually.
246 To execute some SQL statements on every connect you can add them as an option in
247 a special fifth argument to connect:
249 my $another_schema = My::Schema->connect(
254 { on_connect_do => \@on_connect_sql_statments }
257 See L<DBIx::Class::Storage::DBI/connect_info> for more information about
258 this and other special C<connect>-time options.
260 =head3 Via a database handle
262 The supplied coderef is expected to return a single connected database handle
263 (e.g. a L<DBI> C<$dbh>)
265 my $schema = My::Schema->connect (
266 sub { Some::DBH::Factory->connect },
272 Once you've defined the basic classes, either manually or using
273 L<DBIx::Class::Schema::Loader>, you can start interacting with your database.
275 To access your database using your $schema object, you can fetch a
276 L<DBIx::Class::Manual::Glossary/"ResultSet"> representing each of your tables by
277 calling the C<resultset> method.
279 The simplest way to get a record is by primary key:
281 my $album = $schema->resultset('Album')->find(14);
283 This will run a C<SELECT> with C<albumid = 14> in the C<WHERE> clause, and
284 return an instance of C<My::Schema::Result::Album> that represents this row. Once you
285 have that row, you can access and update columns:
287 $album->title('Physical Graffiti');
288 my $title = $album->title; # $title holds 'Physical Graffiti'
290 If you prefer, you can use the C<set_column> and C<get_column> accessors
293 $album->set_column('title', 'Presence');
294 $title = $album->get_column('title');
296 Just like with L<Class::DBI>, you call C<update> to save your changes to the
297 database (by executing the actual C<UPDATE> statement):
301 If needed, you can throw away your local changes:
303 $album->discard_changes if $album->is_changed;
305 As you can see, C<is_changed> allows you to check if there are local changes to
308 =head2 Adding and removing rows
310 To create a new record in the database, you can use the C<create> method. It
311 returns an instance of C<My::Schema::Result::Album> that can be used to access the data
314 my $new_album = $schema->resultset('Album')->create({
315 title => 'Wish You Were Here',
316 artist => 'Pink Floyd'
319 Now you can add data to the new record:
321 $new_album->label('Capitol');
322 $new_album->year('1975');
325 Likewise, you can remove it from the database:
329 You can also remove records without retrieving them first, by calling delete
330 directly on a ResultSet object.
332 # Delete all of Falco's albums
333 $schema->resultset('Album')->search({ artist => 'Falco' })->delete;
335 =head2 Finding your objects
337 L<DBIx::Class> provides a few different ways to retrieve data from your
338 database. Here's one example:
340 # Find all of Santana's albums
341 my $rs = $schema->resultset('Album')->search({ artist => 'Santana' });
343 In scalar context, as above, C<search> returns a L<DBIx::Class::ResultSet>
344 object. It can be used to peek at the first album returned by the database:
346 my $album = $rs->first;
349 You can loop over the albums and update each one:
351 while (my $album = $rs->next) {
352 print $album->artist . ' - ' . $album->title;
357 Or, you can update them all at once:
359 $rs->update({ year => 2001 });
361 In list context, the C<search> method returns all of the matching rows:
363 # Fetch immediately all of Carlos Santana's albums
364 my @albums = $schema->resultset('Album')->search(
365 { artist => 'Carlos Santana' }
367 foreach my $album (@albums) {
368 print $album->artist . ' - ' . $album->title;
371 We also provide a handy shortcut for doing a C<LIKE> search:
373 # Find albums whose artist starts with 'Jimi'
374 my $rs = $schema->resultset('Album')->search_like({ artist => 'Jimi%' });
376 Or you can provide your own C<WHERE> clause:
378 # Find Peter Frampton albums from the year 1986
379 my $where = 'artist = ? AND year = ?';
380 my @bind = ( 'Peter Frampton', 1986 );
381 my $rs = $schema->resultset('Album')->search_literal( $where, @bind );
383 The preferred way to generate complex queries is to provide a L<SQL::Abstract>
384 construct to C<search>:
386 my $rs = $schema->resultset('Album')->search({
387 artist => { '!=', 'Janis Joplin' },
388 year => { '<' => 1980 },
389 albumid => { '-in' => [ 1, 14, 15, 65, 43 ] }
392 This results in something like the following C<WHERE> clause:
394 WHERE artist != 'Janis Joplin'
396 AND albumid IN (1, 14, 15, 65, 43)
398 For more examples of complex queries, see L<DBIx::Class::Manual::Cookbook>.
400 The search can also be modified by passing another hash with
403 my @albums = My::Schema->resultset('Album')->search(
404 { artist => 'Bob Marley' },
405 { rows => 2, order_by => 'year DESC' }
408 C<@albums> then holds the two most recent Bob Marley albums.
410 For more information on what you can do with a L<DBIx::Class::ResultSet>, see
411 L<DBIx::Class::ResultSet/METHODS>.
413 For a complete overview of the available attributes, see
414 L<DBIx::Class::ResultSet/ATTRIBUTES>.
418 =head2 The Significance and Importance of Primary Keys
420 The concept of a L<primary key|DBIx::Class::ResultSource/set_primary_key> in
421 DBIx::Class warrants special discussion. The formal definition (which somewhat
422 resembles that of a classic RDBMS) is I<a unique constraint that is least
423 likely to change after initial row creation>. However this is where the
424 similarity ends. Any time you call a CRUD operation on a row (e.g.
425 L<delete|DBIx::Class::Row/delete>,
426 L<update|DBIx::Class::Row/update>,
427 L<discard_changes|DBIx::Class::Row/discard_changes>,
428 etc.) DBIx::Class will use the values of of the
429 L<primary key|DBIx::Class::ResultSource/set_primary_key> columns to populate
430 the C<WHERE> clause necessary to accomplish the operation. This is why it is
431 important to declare a L<primary key|DBIx::Class::ResultSource/set_primary_key>
432 on all your result sources B<even if the underlying RDBMS does not have one>.
433 In a pinch one can always declare each row identifiable by all its columns:
435 __PACKAGE__->set_primary_keys (__PACKAGE__->columns);
437 Note that DBIx::Class is smart enough to store a copy of the PK values before
438 any row-object changes take place, so even if you change the values of PK
439 columns the C<WHERE> clause will remain correct.
441 If you elect not to declare a C<primary key>, DBIx::Class will behave correctly
442 by throwing exceptions on any row operation that relies on unique identifiable
443 rows. If you inherited datasets with multiple identical rows in them, you can
444 still operate with such sets provided you only utilize
445 L<DBIx::Class::ResultSet> CRUD methods:
446 L<search|DBIx::Class::ResultSet/search>,
447 L<update|DBIx::Class::ResultSet/update>,
448 L<delete|DBIx::Class::ResultSet/delete>
450 For example, the following would not work (assuming C<People> does not have
453 my $row = $schema->resultset('People')
454 ->search({ last_name => 'Dantes' })
456 $row->update({ children => 2 }); # <-- exception thrown because $row isn't
459 So instead the following should be done:
461 $schema->resultset('People')
462 ->search({ last_name => 'Dantes' })
463 ->update({ children => 2 }); # <-- update's ALL Dantes to have children of 2
465 =head2 Problems on RHEL5/CentOS5
467 There used to be an issue with the system perl on Red Hat Enterprise
468 Linux 5, some versions of Fedora and derived systems. Further
469 information on this can be found in L<DBIx::Class::Manual::Troubleshooting>
475 =item * L<DBIx::Class::Manual::Cookbook>