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.
10 Let's look at how you can set and use your first native L<DBIx::Class>
13 First we'll see how you can set up your classes yourself. If you want
14 them to be auto-discovered, just skip to the next section, which shows
15 you how to use L<DBIx::Class::Schema::Loader>.
17 =head2 Setting it up manually
19 First, you should create your base schema class, which inherits from
20 L<DBIx::Class::Schema>:
23 use base qw/DBIx::Class::Schema/;
25 In this class you load your result_source ("table", "model") classes, which
26 we will define later, using the load_classes() method. You can specify which
27 classes to load manually:
29 # load My::Schema::Album and My::Schema::Artist
30 __PACKAGE__->load_classes(qw/ Album Artist /);
32 Or load classes by namespace:
34 # load My::Schema::Album, My::Schema::Artist and My::OtherSchema::LinerNotes
35 __PACKAGE__->load_classes(
37 'My::Schema' => [qw/ Album Artist /],
38 'My::OtherSchema' => [qw/ LinerNotes /]
42 Or let your schema class load all classes in its namespace automatically:
45 __PACKAGE__->load_classes();
47 Next, create each of the classes you want to load as specified above:
49 package My::Schema::Album;
50 use base qw/DBIx::Class/;
52 Load any components required by each class with the load_components() method.
53 This should consist of "Core" plus any additional components you want to use.
54 For example, if you want serial/auto-incrementing primary keys:
56 __PACKAGE__->load_components(qw/ PK::Auto Core /);
58 C<PK::Auto> is supported for many databases; see
59 L<DBIx::Class::Storage::DBI> for more information.
61 Set the table for your class:
63 __PACKAGE__->table('album');
65 Add columns to your class:
67 __PACKAGE__->add_columns(qw/ albumid artist title /);
69 Each column can also be set up with its own accessor, data_type and other
70 pieces of information that it may be useful to have, just pass C<add_columns>
73 __PACKAGE__->add_columns(albumid =>
74 { accessor => 'album',
75 data_type => 'integer',
78 is_auto_increment => 1,
82 { data_type => 'integer',
85 is_auto_increment => 0,
89 { data_type => 'varchar',
92 is_auto_increment => 0,
97 Most of this data isn't yet used directly by DBIx::Class, but various related
98 modules such as L<DBIx::Class::WebForm> make use of it. Also it allows you
99 to create your database tables from your Schema, instead of the other way
100 around. See L<SQL::Translator> for details.
102 See L<DBIx::Class::ResultSource> for more details of the possible column
105 Accessors are created for each column automatically, so My::Schema::Album will
106 have albumid() (or album(), when using the accessor), artist() and title()
109 Define a primary key for your class:
111 __PACKAGE__->set_primary_key('albumid');
113 If you have a multi-column primary key, just pass a list instead:
115 __PACKAGE__->set_primary_key( qw/ albumid artistid / );
117 Define relationships that the class has with any other classes by using
118 either C<belongs_to> to describe a column which contains an ID of another
119 table, or C<has_many> to make a predefined accessor for fetching objects
120 that contain this tables foreign key in one of their columns:
122 __PACKAGE__->has_many('albums', 'My::Schema::Artist', 'album_id');
124 More information about the various types of relationships available, and
125 how you can design your own, can be found in L<DBIx::Class::Relationship>.
128 =head2 Using L<DBIx::Class::Schema::Loader>
130 This is an external module, and not part of the L<DBIx::Class>
131 distribution. Like L<Class::DBI::Loader>, it inspects your database,
132 and automatically creates classes for all the tables in your database.
133 Here's a simple setup:
136 use DBIx::Class::Schema::Loader;
138 my $loader = DBIx::Class::Loader->new(
139 dsn => 'dbi:SQLite:/home/me/myapp/my.db',
140 namespace => 'MyApp::DB'
145 This should be equivalent to the manual setup in the section above.
146 L<DBIx::Class::Schema::Loader> takes lots of other options. For more
147 information, consult its documentation.
151 L<DBIx::Class::Schema::Loader> already contains the connection info for the
152 database, so to get started all you need to do is create an instance of your
155 my $schema = MyApp::DB->new();
157 To connect to your manually created Schema, you also need to provide the
160 my $schema = My::Schema->connect('dbi:SQLite:/home/me/myapp/my.db');
162 You can create as many different schema instances as you need. So if you have
163 a second database you want to access:
165 my $other_schema = My::Schema->connect( $dsn, $user, $password, $attrs );
167 Note that L<DBIx::Class::Schema> does not cache connnections for you. If you
168 use multiple connections, you need to do this manually.
170 To execute some sql statements on every connect you can pass them to your schema after the connect:
172 $schema->storage->on_connect_do(\@on_connect_sql_statments);
176 Once you've defined the basic classes, either manually or using
177 L<DBIx::Class::Schema::Loader>, you can start interacting with your database.
179 To access your database using your $schema object, you can fetch a L<DBIx::Class::Manual::Glossary/"ResultSet">
180 representing each of your tables by calling the ->resultset method.
182 The simplest way to get a record is by primary key:
184 my $album = $schema->resultset('Album')->find(14);
186 This will run a C<SELECT> with C<albumid = 14> in the C<WHERE> clause,
187 and return an instance of C<MyApp::DB::Album> that represents this
188 row. Once you have that row, you can access and update columns:
190 $album->title('Physical Graffiti');
191 my $title = $album->title; # $title holds 'Physical Graffiti'
193 If you prefer, you can use the C<set_column> and C<get_column>
196 $album->set_column('title', 'Presence');
197 $title = $album->get_column('title');
199 Just like with L<Class::DBI>, you call C<update> to commit your
200 changes to the database:
204 If needed, you can throw away your local changes like this:
206 $album->discard_changes if $album->is_changed;
208 As you can see, C<is_changed> allows you to check if there are local
209 changes to your object.
211 =head2 Adding and removing rows
213 To create a new record in the database, you can use the C<create>
214 method. It returns an instance of C<MyApp::DB::Album> that can be
215 used to access the data in the new record:
217 my $new_album = $schema->resultset('Album')->create({
218 title => 'Wish You Were Here',
219 artist => 'Pink Floyd'
222 Now you can add data to the new record:
224 $new_album->label('Capitol');
225 $new_album->year('1975');
228 Likewise, you can remove it from the database like this:
232 You can also remove records without retrieving them first, by calling
233 delete directly on a ResultSet object.
235 # Delete all of Falco's albums
236 $schema->resultset('Album')->search({ artist => 'Falco' })->delete;
238 =head2 Finding your objects
240 L<DBIx::Class> provides a few different ways to retrieve data from
241 your database. Here's one example:
243 # Find all of Santana's albums
244 my $rs = $schema->resultset('Album')->search({ artist => 'Santana' });
246 In scalar context, as above, C<search> returns a
247 L<DBIx::Class::ResultSet> object. It can be used to peek at the first
248 album returned by the database:
250 my $album = $rs->first;
253 You can loop over the albums and update each one:
255 while (my $album = $rs->next) {
256 print $album->artist . ' - ' . $album->title;
261 Or, you can update them all at once:
263 $rs->update({ year => 2001 });
265 For more information on what you can do with a
266 L<DBIx::Class::ResultSet>, see L<DBIx::Class::ResultSet/METHODS>.
268 In list context, the C<search> method returns all of the matching
271 # Fetch immediately all of Carlos Santana's albums
272 my @albums = $schema->resultset('Album')->search(
273 { artist => 'Carlos Santana' }
275 foreach my $album (@albums) {
276 print $album->artist . ' - ' . $album->title;
279 We also provide a handy shortcut for doing a C<LIKE> search:
281 # Find albums whose artist starts with 'Jimi'
282 my $rs = MyApp::DB::Album->search_like({ artist => 'Jimi%' });
284 Or you can provide your own C<WHERE> clause, like:
286 # Find Peter Frampton albums from the year 1986
287 my $where = 'artist = ? AND year = ?';
288 my @bind = ( 'Peter Frampton', 1986 );
289 my $rs = $schema->resultset('Album')->search_literal( $where, @bind );
291 The preferred way to generate complex queries is to provide a
292 L<SQL::Abstract> construct to C<search>:
294 my $rs = $schema->resultset('Album')->search({
295 artist => { '!=', 'Janis Joplin' },
296 year => { '<' => 1980 },
297 albumid => [ 1, 14, 15, 65, 43 ]
300 This results in something like the following C<WHERE> clause:
302 WHERE artist != 'Janis Joplin'
304 AND albumid IN (1, 14, 15, 65, 43)
306 For more examples of complex queries, see
307 L<DBIx::Class::Manual::Cookbook>.
309 The search can also be modified by passing another hash with
312 my @albums = MyApp::DB::Album->search(
313 { artist => 'Bob Marley' },
314 { rows => 2, order_by => 'year DESC' }
317 C<@albums> then holds the two most recent Bob Marley albums.
319 For a complete overview of the available attributes, see
320 L<DBIx::Class::ResultSet/ATTRIBUTES>.
326 =item * L<DBIx::Class::Manual::Cookbook>
328 =item * L<DBIx::Class::Manual::FAQ>