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 next section, which shows you how to use
78 L<DBIx::Class::Schema::Loader>.
80 =head2 Setting it up manually
82 First, you should create your base schema class, which inherits from
83 L<DBIx::Class::Schema>:
86 use base qw/DBIx::Class::Schema/;
88 In this class you load your result_source ("table", "model") classes, which we
89 will define later, using the load_namespaces() method:
91 # load My::Schema::Result::* and their resultset classes
92 __PACKAGE__->load_namespaces();
94 By default this loads all the Result (Row) classes in the
95 My::Schema::Result:: namespace, and also any resultset classes in the
96 My::Schema::ResultSet:: namespace (if missing, the resultsets are
97 defaulted to be DBIx::Class::ResultSet objects). You can change the
98 result and resultset namespaces by using options to the
99 L<DBIx::Class::Schema/load_namespaces> call.
101 It is also possible to do the same things manually by calling
102 C<load_classes> for the Row classes and defining in those classes any
103 required resultset classes.
105 Next, create each of the classes you want to load as specified above:
107 package My::Schema::Result::Album;
108 use base qw/DBIx::Class::Core/;
110 Load any additional components you may need with the load_components() method,
111 and provide component configuration if required. For example, if you want
112 automatic row ordering:
114 __PACKAGE__->load_components(qw/ Ordered /);
115 __PACKAGE__->position_column('rank');
117 Set the table for your class:
119 __PACKAGE__->table('album');
121 Add columns to your class:
123 __PACKAGE__->add_columns(qw/ albumid artist title rank /);
125 Each column can also be set up with its own accessor, data_type and other pieces
126 of information that it may be useful to have -- just pass C<add_columns> a hash:
128 __PACKAGE__->add_columns(albumid =>
129 { accessor => 'album',
130 data_type => 'integer',
133 is_auto_increment => 1,
137 { data_type => 'integer',
140 is_auto_increment => 0,
144 { data_type => 'varchar',
147 is_auto_increment => 0,
151 { data_type => 'integer',
154 is_auto_increment => 0,
159 DBIx::Class doesn't directly use most of this data yet, but various related
160 modules such as L<DBIx::Class::WebForm> make use of it. Also it allows you to
161 create your database tables from your Schema, instead of the other way around.
162 See L<DBIx::Class::Schema/deploy> for details.
164 See L<DBIx::Class::ResultSource> for more details of the possible column
167 Accessors are created for each column automatically, so My::Schema::Result::Album will
168 have albumid() (or album(), when using the accessor), artist() and title()
171 Define a primary key for your class:
173 __PACKAGE__->set_primary_key('albumid');
175 If you have a multi-column primary key, just pass a list instead:
177 __PACKAGE__->set_primary_key( qw/ albumid artistid / );
179 Define this class' relationships with other classes using either C<belongs_to>
180 to describe a column which contains an ID of another Table, or C<has_many> to
181 make a predefined accessor for fetching objects that contain this Table's
184 # in My::Schema::Result::Artist
185 __PACKAGE__->has_many('albums', 'My::Schema::Result::Album', 'artist');
187 See L<DBIx::Class::Relationship> for more information about the various types of
188 available relationships and how you can design your own.
190 =head2 Using L<DBIx::Class::Schema::Loader>
192 This is an external module, and not part of the L<DBIx::Class> distribution.
193 Like L<Class::DBI::Loader>, it inspects your database, and automatically creates
194 classes for all the tables in your database. Here's a simple setup:
197 use base qw/DBIx::Class::Schema::Loader/;
199 __PACKAGE__->loader_options( relationships => 1 );
203 The actual autoloading process will occur when you create a connected instance
204 of your schema below.
206 See the L<DBIx::Class::Schema::Loader> documentation for more information on its
211 To connect to your Schema, you need to provide the connection details or a
214 =head3 Via connection details
216 The arguments are the same as for L<DBI/connect>:
218 my $schema = My::Schema->connect('dbi:SQLite:/home/me/myapp/my.db');
220 You can create as many different schema instances as you need. So if you have a
221 second database you want to access:
223 my $other_schema = My::Schema->connect( $dsn, $user, $password, $attrs );
225 Note that L<DBIx::Class::Schema> does not cache connections for you. If you use
226 multiple connections, you need to do this manually.
228 To execute some SQL statements on every connect you can add them as an option in
229 a special fifth argument to connect:
231 my $another_schema = My::Schema->connect(
236 { on_connect_do => \@on_connect_sql_statments }
239 See L<DBIx::Class::Schema::Storage::DBI/connect_info> for more information about
240 this and other special C<connect>-time options.
242 =head3 Via a database handle
244 The supplied coderef is expected to return a single connected database handle
245 (e.g. a L<DBI> C<$dbh>)
247 my $schema = My::Schema->connect (
248 sub { Some::DBH::Factory->connect },
254 Once you've defined the basic classes, either manually or using
255 L<DBIx::Class::Schema::Loader>, you can start interacting with your database.
257 To access your database using your $schema object, you can fetch a
258 L<DBIx::Class::Manual::Glossary/"ResultSet"> representing each of your tables by
259 calling the C<resultset> method.
261 The simplest way to get a record is by primary key:
263 my $album = $schema->resultset('Album')->find(14);
265 This will run a C<SELECT> with C<albumid = 14> in the C<WHERE> clause, and
266 return an instance of C<My::Schema::Result::Album> that represents this row. Once you
267 have that row, you can access and update columns:
269 $album->title('Physical Graffiti');
270 my $title = $album->title; # $title holds 'Physical Graffiti'
272 If you prefer, you can use the C<set_column> and C<get_column> accessors
275 $album->set_column('title', 'Presence');
276 $title = $album->get_column('title');
278 Just like with L<Class::DBI>, you call C<update> to save your changes to the
279 database (by executing the actual C<UPDATE> statement):
283 If needed, you can throw away your local changes:
285 $album->discard_changes if $album->is_changed;
287 As you can see, C<is_changed> allows you to check if there are local changes to
290 =head2 Adding and removing rows
292 To create a new record in the database, you can use the C<create> method. It
293 returns an instance of C<My::Schema::Result::Album> that can be used to access the data
296 my $new_album = $schema->resultset('Album')->create({
297 title => 'Wish You Were Here',
298 artist => 'Pink Floyd'
301 Now you can add data to the new record:
303 $new_album->label('Capitol');
304 $new_album->year('1975');
307 Likewise, you can remove it from the database:
311 You can also remove records without retrieving them first, by calling delete
312 directly on a ResultSet object.
314 # Delete all of Falco's albums
315 $schema->resultset('Album')->search({ artist => 'Falco' })->delete;
317 =head2 Finding your objects
319 L<DBIx::Class> provides a few different ways to retrieve data from your
320 database. Here's one example:
322 # Find all of Santana's albums
323 my $rs = $schema->resultset('Album')->search({ artist => 'Santana' });
325 In scalar context, as above, C<search> returns a L<DBIx::Class::ResultSet>
326 object. It can be used to peek at the first album returned by the database:
328 my $album = $rs->first;
331 You can loop over the albums and update each one:
333 while (my $album = $rs->next) {
334 print $album->artist . ' - ' . $album->title;
339 Or, you can update them all at once:
341 $rs->update({ year => 2001 });
343 In list context, the C<search> method returns all of the matching rows:
345 # Fetch immediately all of Carlos Santana's albums
346 my @albums = $schema->resultset('Album')->search(
347 { artist => 'Carlos Santana' }
349 foreach my $album (@albums) {
350 print $album->artist . ' - ' . $album->title;
353 We also provide a handy shortcut for doing a C<LIKE> search:
355 # Find albums whose artist starts with 'Jimi'
356 my $rs = $schema->resultset('Album')->search_like({ artist => 'Jimi%' });
358 Or you can provide your own C<WHERE> clause:
360 # Find Peter Frampton albums from the year 1986
361 my $where = 'artist = ? AND year = ?';
362 my @bind = ( 'Peter Frampton', 1986 );
363 my $rs = $schema->resultset('Album')->search_literal( $where, @bind );
365 The preferred way to generate complex queries is to provide a L<SQL::Abstract>
366 construct to C<search>:
368 my $rs = $schema->resultset('Album')->search({
369 artist => { '!=', 'Janis Joplin' },
370 year => { '<' => 1980 },
371 albumid => { '-in' => [ 1, 14, 15, 65, 43 ] }
374 This results in something like the following C<WHERE> clause:
376 WHERE artist != 'Janis Joplin'
378 AND albumid IN (1, 14, 15, 65, 43)
380 For more examples of complex queries, see L<DBIx::Class::Manual::Cookbook>.
382 The search can also be modified by passing another hash with
385 my @albums = My::Schema->resultset('Album')->search(
386 { artist => 'Bob Marley' },
387 { rows => 2, order_by => 'year DESC' }
390 C<@albums> then holds the two most recent Bob Marley albums.
392 For more information on what you can do with a L<DBIx::Class::ResultSet>, see
393 L<DBIx::Class::ResultSet/METHODS>.
395 For a complete overview of the available attributes, see
396 L<DBIx::Class::ResultSet/ATTRIBUTES>.
400 =head2 Problems on RHEL5/CentOS5
402 There used to be an issue with the system perl on Red Hat Enterprise
403 Linux 5, some versions of Fedora and derived systems. Further
404 information on this can be found in L<DBIx::Class::Manual::Troubleshooting>
410 =item * L<DBIx::Class::Manual::Cookbook>