3 DBIx::Class::Manual::Intro - Introduction to DBIx::Class
7 So, you are bored with SQL, and want a native Perl interface for your
8 database? Or you've been doing this for a while with L<Class::DBI>,
9 and think there's a 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
16 =head2 Tables become ResultSources
18 DBIx::Class needs to know what your Table structure looks like. You do that
19 by defining L<DBIx::Class::ResultSource>s. Each table get's a ResultSource,
20 which defines the Columns it has, along with any Relationships it has to
21 other tables. (And oh, so much more besides) The important thing to
24 A ResultSource == Table
26 (most of the time, but just bear with my simplification)
28 =head2 It's all about the ResultSet
30 So, we've got some ResultSources defined. Now, we want to actually use
31 those definitions to help us translate the queries we need into
34 Let's say we defined a ResultSource for an "album" table with three
35 columns: "albumid", "artist", and "title". Any time we want to query
36 this table, we'll be creating a L<DBIx::Class::ResultSet> from it's
37 ResultSource. For example, the results of:
39 SELECT albumid, artist, title FROM album;
41 Would be retrieved by creating a ResultSet object from the album
42 table's ResultSource, likely by using the "search" method.
44 DBIx::Class doesn't limit you to creating only simple ResultSets --
45 if you 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
59 from the database. If you are returning a ResultSet, the query won't
60 execute until you use a method that wants to access the data. (Such
61 as "next", or "first")
63 The important thing to understand:
65 Setting up a ResultSet does not execute the query; retrieving
68 =head1 SETTING UP DBIx::Class
70 Let's look at how you can set and use your first native L<DBIx::Class>
73 First we'll see how you can set up your classes yourself. If you want
74 them to be auto-discovered, just skip to the next section, which shows
75 you how to use L<DBIx::Class::Schema::Loader>.
77 =head2 Setting it up manually
79 First, you should create your base schema class, which inherits from
80 L<DBIx::Class::Schema>:
83 use base qw/DBIx::Class::Schema/;
85 In this class you load your result_source ("table", "model") classes, which
86 we will define later, using the load_classes() method. You can specify which
87 classes to load manually:
89 # load My::Schema::Album and My::Schema::Artist
90 __PACKAGE__->load_classes(qw/ Album Artist /);
92 Or load classes by namespace:
94 # load My::Schema::Album, My::Schema::Artist and My::OtherSchema::LinerNotes
95 __PACKAGE__->load_classes(
97 'My::Schema' => [qw/ Album Artist /],
98 'My::OtherSchema' => [qw/ LinerNotes /]
102 Or let your schema class load all classes in its namespace automatically:
105 __PACKAGE__->load_classes();
107 Next, create each of the classes you want to load as specified above:
109 package My::Schema::Album;
110 use base qw/DBIx::Class/;
112 Load any components required by each class with the load_components() method.
113 This should consist of "Core" plus any additional components you want to use.
114 For example, if you want serial/auto-incrementing primary keys:
116 __PACKAGE__->load_components(qw/ PK::Auto Core /);
118 C<PK::Auto> is supported for many databases; see
119 L<DBIx::Class::Storage::DBI> for more information.
121 Set the table for your class:
123 __PACKAGE__->table('album');
125 Add columns to your class:
127 __PACKAGE__->add_columns(qw/ albumid artist title /);
129 Each column can also be set up with its own accessor, data_type and other
130 pieces of information that it may be useful to have, just pass C<add_columns>
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,
157 Most of this data isn't yet used directly by DBIx::Class, but various related
158 modules such as L<DBIx::Class::WebForm> make use of it. Also it allows you
159 to create your database tables from your Schema, instead of the other way
160 around. See L<SQL::Translator> for details.
162 See L<DBIx::Class::ResultSource> for more details of the possible column
165 Accessors are created for each column automatically, so My::Schema::Album will
166 have albumid() (or album(), when using the accessor), artist() and title()
169 Define a primary key for your class:
171 __PACKAGE__->set_primary_key('albumid');
173 If you have a multi-column primary key, just pass a list instead:
175 __PACKAGE__->set_primary_key( qw/ albumid artistid / );
177 Define relationships that the class has with any other classes by using
178 either C<belongs_to> to describe a column which contains an ID of another
179 table, or C<has_many> to make a predefined accessor for fetching objects
180 that contain this tables foreign key in one of their columns:
182 __PACKAGE__->has_many('albums', 'My::Schema::Artist', 'album_id');
184 More information about the various types of relationships available, and
185 how you can design your own, can be found in L<DBIx::Class::Relationship>.
187 =head2 Using L<DBIx::Class::Schema::Loader>
189 This is an external module, and not part of the L<DBIx::Class>
190 distribution. Like L<Class::DBI::Loader>, it inspects your database,
191 and automatically creates classes for all the tables in your database.
192 Here's a simple setup:
195 use base qw/DBIx::Class::Schema::Loader/;
197 __PACKAGE__->loader_options( relationships => 1 );
201 The actual autoloading process will occur when you create a connected
202 instance of your schema below.
204 L<DBIx::Class::Schema::Loader> takes lots of other options. For more
205 information, consult its documentation.
209 To connect to your Schema, you also need to provide the connection details.
210 The arguments are the same as you would use for L<DBI/connect>:
212 my $schema = My::Schema->connect('dbi:SQLite:/home/me/myapp/my.db');
214 You can create as many different schema instances as you need. So if you have
215 a second database you want to access:
217 my $other_schema = My::Schema->connect( $dsn, $user, $password, $attrs );
219 Note that L<DBIx::Class::Schema> does not cache connections for you. If you
220 use multiple connections, you need to do this manually.
222 To execute some sql statements on every connect you can add them as an option
223 in a special fifth argument to connect, like so:
225 my $another_schema = My::Schema->connect(
230 { on_connect_do => \@on_connect_sql_statments }
233 For more information about this and other special C<connect()>-time options,
234 see L<DBIx::Class::Schema::Storage::DBI/connect_info>.
238 Once you've defined the basic classes, either manually or using
239 L<DBIx::Class::Schema::Loader>, you can start interacting with your database.
241 To access your database using your $schema object, you can fetch a L<DBIx::Class::Manual::Glossary/"ResultSet">
242 representing each of your tables by calling the ->resultset method.
244 The simplest way to get a record is by primary key:
246 my $album = $schema->resultset('Album')->find(14);
248 This will run a C<SELECT> with C<albumid = 14> in the C<WHERE> clause,
249 and return an instance of C<My::Schema::Album> that represents this
250 row. Once you have that row, you can access and update columns:
252 $album->title('Physical Graffiti');
253 my $title = $album->title; # $title holds 'Physical Graffiti'
255 If you prefer, you can use the C<set_column> and C<get_column>
258 $album->set_column('title', 'Presence');
259 $title = $album->get_column('title');
261 Just like with L<Class::DBI>, you call C<update> to commit your
262 changes to the database:
266 If needed, you can throw away your local changes like this:
268 $album->discard_changes if $album->is_changed;
270 As you can see, C<is_changed> allows you to check if there are local
271 changes to your object.
273 =head2 Adding and removing rows
275 To create a new record in the database, you can use the C<create>
276 method. It returns an instance of C<My::Schema::Album> that can be
277 used to access the data in the new record:
279 my $new_album = $schema->resultset('Album')->create({
280 title => 'Wish You Were Here',
281 artist => 'Pink Floyd'
284 Now you can add data to the new record:
286 $new_album->label('Capitol');
287 $new_album->year('1975');
290 Likewise, you can remove it from the database like this:
294 You can also remove records without retrieving them first, by calling
295 delete directly on a ResultSet object.
297 # Delete all of Falco's albums
298 $schema->resultset('Album')->search({ artist => 'Falco' })->delete;
300 =head2 Finding your objects
302 L<DBIx::Class> provides a few different ways to retrieve data from
303 your database. Here's one example:
305 # Find all of Santana's albums
306 my $rs = $schema->resultset('Album')->search({ artist => 'Santana' });
308 In scalar context, as above, C<search> returns a
309 L<DBIx::Class::ResultSet> object. It can be used to peek at the first
310 album returned by the database:
312 my $album = $rs->first;
315 You can loop over the albums and update each one:
317 while (my $album = $rs->next) {
318 print $album->artist . ' - ' . $album->title;
323 Or, you can update them all at once:
325 $rs->update({ year => 2001 });
327 For more information on what you can do with a
328 L<DBIx::Class::ResultSet>, see L<DBIx::Class::ResultSet/METHODS>.
330 In list context, the C<search> method returns all of the matching
333 # Fetch immediately all of Carlos Santana's albums
334 my @albums = $schema->resultset('Album')->search(
335 { artist => 'Carlos Santana' }
337 foreach my $album (@albums) {
338 print $album->artist . ' - ' . $album->title;
341 We also provide a handy shortcut for doing a C<LIKE> search:
343 # Find albums whose artist starts with 'Jimi'
344 my $rs = $schema->resultset('Album')->search_like({ artist => 'Jimi%' });
346 Or you can provide your own C<WHERE> clause, like:
348 # Find Peter Frampton albums from the year 1986
349 my $where = 'artist = ? AND year = ?';
350 my @bind = ( 'Peter Frampton', 1986 );
351 my $rs = $schema->resultset('Album')->search_literal( $where, @bind );
353 The preferred way to generate complex queries is to provide a
354 L<SQL::Abstract> construct to C<search>:
356 my $rs = $schema->resultset('Album')->search({
357 artist => { '!=', 'Janis Joplin' },
358 year => { '<' => 1980 },
359 albumid => [ 1, 14, 15, 65, 43 ]
362 This results in something like the following C<WHERE> clause:
364 WHERE artist != 'Janis Joplin'
366 AND albumid IN (1, 14, 15, 65, 43)
368 For more examples of complex queries, see
369 L<DBIx::Class::Manual::Cookbook>.
371 The search can also be modified by passing another hash with
374 my @albums = My::Schema->resultset('Album')->search(
375 { artist => 'Bob Marley' },
376 { rows => 2, order_by => 'year DESC' }
379 C<@albums> then holds the two most recent Bob Marley albums.
381 For a complete overview of the available attributes, see
382 L<DBIx::Class::ResultSet/ATTRIBUTES>.
388 =item * L<DBIx::Class::Manual::Cookbook>