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>.
188 =head2 Using L<DBIx::Class::Schema::Loader>
190 This is an external module, and not part of the L<DBIx::Class>
191 distribution. Like L<Class::DBI::Loader>, it inspects your database,
192 and automatically creates classes for all the tables in your database.
193 Here's a simple setup:
196 use base qw/DBIx::Class::Schema::Loader/;
198 __PACKAGE__->load_from_connection(
199 connect_info = [ 'dbi:SQLite:/home/me/myapp/my.db' ]
204 This should be equivalent to the manual setup in the section above.
205 L<DBIx::Class::Schema::Loader> takes lots of other options. For more
206 information, consult its documentation.
210 L<DBIx::Class::Schema::Loader> already contains the connection info for the
211 database, so to get started all you need to do is create an instance of your
214 my $schema = My::Schema->new();
216 To connect to your manually created Schema, you also need to provide the
219 my $schema = My::Schema->connect('dbi:SQLite:/home/me/myapp/my.db');
221 You can create as many different schema instances as you need. So if you have
222 a second database you want to access:
224 my $other_schema = My::Schema->connect( $dsn, $user, $password, $attrs );
226 Note that L<DBIx::Class::Schema> does not cache connections for you. If you
227 use multiple connections, you need to do this manually.
229 To execute some sql statements on every connect you can pass them to your schema after the connect:
231 $schema->storage->on_connect_do(\@on_connect_sql_statments);
235 Once you've defined the basic classes, either manually or using
236 L<DBIx::Class::Schema::Loader>, you can start interacting with your database.
238 To access your database using your $schema object, you can fetch a L<DBIx::Class::Manual::Glossary/"ResultSet">
239 representing each of your tables by calling the ->resultset method.
241 The simplest way to get a record is by primary key:
243 my $album = $schema->resultset('Album')->find(14);
245 This will run a C<SELECT> with C<albumid = 14> in the C<WHERE> clause,
246 and return an instance of C<My::Schema::Album> that represents this
247 row. Once you have that row, you can access and update columns:
249 $album->title('Physical Graffiti');
250 my $title = $album->title; # $title holds 'Physical Graffiti'
252 If you prefer, you can use the C<set_column> and C<get_column>
255 $album->set_column('title', 'Presence');
256 $title = $album->get_column('title');
258 Just like with L<Class::DBI>, you call C<update> to commit your
259 changes to the database:
263 If needed, you can throw away your local changes like this:
265 $album->discard_changes if $album->is_changed;
267 As you can see, C<is_changed> allows you to check if there are local
268 changes to your object.
270 =head2 Adding and removing rows
272 To create a new record in the database, you can use the C<create>
273 method. It returns an instance of C<My::Schema::Album> that can be
274 used to access the data in the new record:
276 my $new_album = $schema->resultset('Album')->create({
277 title => 'Wish You Were Here',
278 artist => 'Pink Floyd'
281 Now you can add data to the new record:
283 $new_album->label('Capitol');
284 $new_album->year('1975');
287 Likewise, you can remove it from the database like this:
291 You can also remove records without retrieving them first, by calling
292 delete directly on a ResultSet object.
294 # Delete all of Falco's albums
295 $schema->resultset('Album')->search({ artist => 'Falco' })->delete;
297 =head2 Finding your objects
299 L<DBIx::Class> provides a few different ways to retrieve data from
300 your database. Here's one example:
302 # Find all of Santana's albums
303 my $rs = $schema->resultset('Album')->search({ artist => 'Santana' });
305 In scalar context, as above, C<search> returns a
306 L<DBIx::Class::ResultSet> object. It can be used to peek at the first
307 album returned by the database:
309 my $album = $rs->first;
312 You can loop over the albums and update each one:
314 while (my $album = $rs->next) {
315 print $album->artist . ' - ' . $album->title;
320 Or, you can update them all at once:
322 $rs->update({ year => 2001 });
324 For more information on what you can do with a
325 L<DBIx::Class::ResultSet>, see L<DBIx::Class::ResultSet/METHODS>.
327 In list context, the C<search> method returns all of the matching
330 # Fetch immediately all of Carlos Santana's albums
331 my @albums = $schema->resultset('Album')->search(
332 { artist => 'Carlos Santana' }
334 foreach my $album (@albums) {
335 print $album->artist . ' - ' . $album->title;
338 We also provide a handy shortcut for doing a C<LIKE> search:
340 # Find albums whose artist starts with 'Jimi'
341 my $rs = $schema->resultset('Album')->search_like({ artist => 'Jimi%' });
343 Or you can provide your own C<WHERE> clause, like:
345 # Find Peter Frampton albums from the year 1986
346 my $where = 'artist = ? AND year = ?';
347 my @bind = ( 'Peter Frampton', 1986 );
348 my $rs = $schema->resultset('Album')->search_literal( $where, @bind );
350 The preferred way to generate complex queries is to provide a
351 L<SQL::Abstract> construct to C<search>:
353 my $rs = $schema->resultset('Album')->search({
354 artist => { '!=', 'Janis Joplin' },
355 year => { '<' => 1980 },
356 albumid => [ 1, 14, 15, 65, 43 ]
359 This results in something like the following C<WHERE> clause:
361 WHERE artist != 'Janis Joplin'
363 AND albumid IN (1, 14, 15, 65, 43)
365 For more examples of complex queries, see
366 L<DBIx::Class::Manual::Cookbook>.
368 The search can also be modified by passing another hash with
371 my @albums = My::Schema->resultset('Album')->search(
372 { artist => 'Bob Marley' },
373 { rows => 2, order_by => 'year DESC' }
376 C<@albums> then holds the two most recent Bob Marley albums.
378 For a complete overview of the available attributes, see
379 L<DBIx::Class::ResultSet/ATTRIBUTES>.
385 =item * L<DBIx::Class::Manual::Cookbook>