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 ResultSources
18 DBIx::Class needs to know what your Table structure looks like. You do that by
19 defining L<DBIx::Class::ResultSource>s. Each table gets a ResultSource, which
20 defines the Columns it has, along with any Relationships it has to other tables.
21 (And oh, so much more besides) The important thing to understand:
23 A ResultSource == Table
25 (most of the time, but just bear with my simplification)
27 =head2 It's all about the ResultSet
29 So, we've got some ResultSources defined. Now, we want to actually use those
30 definitions to help us translate the queries we need into handy perl objects!
32 Let's say we defined a ResultSource for an "album" table with three columns:
33 "albumid", "artist", and "title". Any time we want to query this table, we'll
34 be creating a L<DBIx::Class::ResultSet> from its ResultSource. For example, the
37 SELECT albumid, artist, title FROM album;
39 Would be retrieved by creating a ResultSet object from the album table's
40 ResultSource, likely by using the "search" method.
42 DBIx::Class doesn't limit you to creating only simple ResultSets -- if you
43 wanted to do something like:
45 SELECT title FROM album GROUP BY title;
47 You could easily achieve it.
49 The important thing to understand:
51 Any time you would reach for a SQL query in DBI, you are
52 creating a DBIx::Class::ResultSet.
54 =head2 Search is like "prepare"
56 DBIx::Class tends to wait until it absolutely must fetch information from the
57 database. If you are returning a ResultSet, the query won't execute until you
58 use a method that wants to access the data. (Such as "next", or "first")
60 The important thing to understand:
62 Setting up a ResultSet does not execute the query; retrieving
65 =head1 SETTING UP DBIx::Class
67 Let's look at how you can set and use your first native L<DBIx::Class> tree.
69 First we'll see how you can set up your classes yourself. If you want them to
70 be auto-discovered, just skip to the next section, which shows you how to use
71 L<DBIx::Class::Schema::Loader>.
73 =head2 Setting it up manually
75 First, you should create your base schema class, which inherits from
76 L<DBIx::Class::Schema>:
79 use base qw/DBIx::Class::Schema/;
81 In this class you load your result_source ("table", "model") classes, which we
82 will define later, using the load_classes() method. You can specify which
83 classes to load manually:
85 # load My::Schema::Album and My::Schema::Artist
86 __PACKAGE__->load_classes(qw/ Album Artist /);
88 Or load classes by namespace:
90 # load My::Schema::Album, My::Schema::Artist and My::OtherSchema::LinerNotes
91 __PACKAGE__->load_classes(
93 'My::Schema' => [qw/ Album Artist /],
94 'My::OtherSchema' => [qw/ LinerNotes /]
98 Or let your schema class load all classes in its namespace automatically:
101 __PACKAGE__->load_classes();
103 Next, create each of the classes you want to load as specified above:
105 package My::Schema::Album;
106 use base qw/DBIx::Class/;
108 Load any components required by each class with the load_components() method.
109 This should consist of "Core" plus any additional components you want to use.
110 For example, if you want serial/auto-incrementing primary keys:
112 __PACKAGE__->load_components(qw/ PK::Auto Core /);
114 C<PK::Auto> is supported for many databases; see L<DBIx::Class::Storage::DBI>
115 for more information.
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 /);
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,
152 DBIx::Class doesn't directly use most of this data yet, but various related
153 modules such as L<DBIx::Class::WebForm> make use of it. Also it allows you to
154 create your database tables from your Schema, instead of the other way around.
155 See L<SQL::Translator> for details.
157 See L<DBIx::Class::ResultSource> for more details of the possible column
160 Accessors are created for each column automatically, so My::Schema::Album will
161 have albumid() (or album(), when using the accessor), artist() and title()
164 Define a primary key for your class:
166 __PACKAGE__->set_primary_key('albumid');
168 If you have a multi-column primary key, just pass a list instead:
170 __PACKAGE__->set_primary_key( qw/ albumid artistid / );
172 Define this class' relationships with other classes using either C<belongs_to>
173 to describe a column which contains an ID of another Table, or C<has_many> to
174 make a predefined accessor for fetching objects that contain this Table's
177 __PACKAGE__->has_many('albums', 'My::Schema::Artist', 'album_id');
179 See L<DBIx::Class::Relationship> for more information about the various types of
180 available relationships and how you can design your own.
182 =head2 Using L<DBIx::Class::Schema::Loader>
184 This is an external module, and not part of the L<DBIx::Class> distribution.
185 Like L<Class::DBI::Loader>, it inspects your database, and automatically creates
186 classes for all the tables in your database. Here's a simple setup:
189 use base qw/DBIx::Class::Schema::Loader/;
191 __PACKAGE__->loader_options( relationships => 1 );
195 The actual autoloading process will occur when you create a connected instance
196 of your schema below.
198 See the L<DBIx::Class::Schema::Loader> documentation for more information on its
203 To connect to your Schema, you need to provide the connection details. The
204 arguments are the same as for L<DBI/connect>:
206 my $schema = My::Schema->connect('dbi:SQLite:/home/me/myapp/my.db');
208 You can create as many different schema instances as you need. So if you have a
209 second database you want to access:
211 my $other_schema = My::Schema->connect( $dsn, $user, $password, $attrs );
213 Note that L<DBIx::Class::Schema> does not cache connections for you. If you use
214 multiple connections, you need to do this manually.
216 To execute some sql statements on every connect you can add them as an option in
217 a special fifth argument to connect:
219 my $another_schema = My::Schema->connect(
224 { on_connect_do => \@on_connect_sql_statments }
227 See L<DBIx::Class::Schema::Storage::DBI/connect_info> for more information about
228 this and other special C<connect>-time options.
232 Once you've defined the basic classes, either manually or using
233 L<DBIx::Class::Schema::Loader>, you can start interacting with your database.
235 To access your database using your $schema object, you can fetch a
236 L<DBIx::Class::Manual::Glossary/"ResultSet"> representing each of your tables by
237 calling the C<resultset> method.
239 The simplest way to get a record is by primary key:
241 my $album = $schema->resultset('Album')->find(14);
243 This will run a C<SELECT> with C<albumid = 14> in the C<WHERE> clause, and
244 return an instance of C<My::Schema::Album> that represents this row. Once you
245 have that row, you can access and update columns:
247 $album->title('Physical Graffiti');
248 my $title = $album->title; # $title holds 'Physical Graffiti'
250 If you prefer, you can use the C<set_column> and C<get_column> accessors
253 $album->set_column('title', 'Presence');
254 $title = $album->get_column('title');
256 Just like with L<Class::DBI>, you call C<update> to commit your changes to the
261 If needed, you can throw away your local changes:
263 $album->discard_changes if $album->is_changed;
265 As you can see, C<is_changed> allows you to check if there are local changes to
268 =head2 Adding and removing rows
270 To create a new record in the database, you can use the C<create> method. It
271 returns an instance of C<My::Schema::Album> that can be used to access the data
274 my $new_album = $schema->resultset('Album')->create({
275 title => 'Wish You Were Here',
276 artist => 'Pink Floyd'
279 Now you can add data to the new record:
281 $new_album->label('Capitol');
282 $new_album->year('1975');
285 Likewise, you can remove it from the database:
289 You can also remove records without retrieving them first, by calling delete
290 directly on a ResultSet object.
292 # Delete all of Falco's albums
293 $schema->resultset('Album')->search({ artist => 'Falco' })->delete;
295 =head2 Finding your objects
297 L<DBIx::Class> provides a few different ways to retrieve data from your
298 database. Here's one example:
300 # Find all of Santana's albums
301 my $rs = $schema->resultset('Album')->search({ artist => 'Santana' });
303 In scalar context, as above, C<search> returns a L<DBIx::Class::ResultSet>
304 object. It can be used to peek at the first album returned by the database:
306 my $album = $rs->first;
309 You can loop over the albums and update each one:
311 while (my $album = $rs->next) {
312 print $album->artist . ' - ' . $album->title;
317 Or, you can update them all at once:
319 $rs->update({ year => 2001 });
321 In list context, the C<search> method returns all of the matching rows:
323 # Fetch immediately all of Carlos Santana's albums
324 my @albums = $schema->resultset('Album')->search(
325 { artist => 'Carlos Santana' }
327 foreach my $album (@albums) {
328 print $album->artist . ' - ' . $album->title;
331 We also provide a handy shortcut for doing a C<LIKE> search:
333 # Find albums whose artist starts with 'Jimi'
334 my $rs = $schema->resultset('Album')->search_like({ artist => 'Jimi%' });
336 Or you can provide your own C<WHERE> clause:
338 # Find Peter Frampton albums from the year 1986
339 my $where = 'artist = ? AND year = ?';
340 my @bind = ( 'Peter Frampton', 1986 );
341 my $rs = $schema->resultset('Album')->search_literal( $where, @bind );
343 The preferred way to generate complex queries is to provide a L<SQL::Abstract>
344 construct to C<search>:
346 my $rs = $schema->resultset('Album')->search({
347 artist => { '!=', 'Janis Joplin' },
348 year => { '<' => 1980 },
349 albumid => { '-in' => [ 1, 14, 15, 65, 43 ] }
352 This results in something like the following C<WHERE> clause:
354 WHERE artist != 'Janis Joplin'
356 AND albumid IN (1, 14, 15, 65, 43)
358 For more examples of complex queries, see L<DBIx::Class::Manual::Cookbook>.
360 The search can also be modified by passing another hash with
363 my @albums = My::Schema->resultset('Album')->search(
364 { artist => 'Bob Marley' },
365 { rows => 2, order_by => 'year DESC' }
368 C<@albums> then holds the two most recent Bob Marley albums.
370 For more information on what you can do with a L<DBIx::Class::ResultSet>, see
371 L<DBIx::Class::ResultSet/METHODS>.
373 For a complete overview of the available attributes, see
374 L<DBIx::Class::ResultSet/ATTRIBUTES>.
380 =item * L<DBIx::Class::Manual::Cookbook>