3 DBIx::Class::Manual::SchemaIntro - Introduction to DBIx::Class::Schema
7 This document describes how to set up DBIx::Class using the recommended
12 First, you should create your base schema class, which inherits from
13 L<DBIx::Class::Schema>:
16 use base qw/DBIx::Class::Schema/;
18 In this class you load your result_source ("table", "model") classes, which
19 we will define later, using the load_classes() method. You can specify which
20 classes to load manually:
22 # load My::Schema::Album and My::Schema::Artist
23 __PACKAGE__->load_classes(qw/ Album Artist /);
25 Or load classes by namespace:
27 # load My::Schema::Album, My::Schema::Artist and My::OtherSchema::LinerNotes
28 __PACKAGE__->load_classes(
30 'My::Schema' => [qw/ Album Artist /],
31 'My::OtherSchema' => [qw/ LinerNotes /]
35 Or let your schema class load all classes in its namespace automatically:
38 __PACKAGE__->load_classes();
40 Next, create each of the classes you want to load as specified above:
42 package My::Schema::Album;
43 use base qw/DBIx::Class/;
45 Load any components required by each class with the load_components() method.
46 This should consist of "Core" plus any additional components you want to use.
47 For example, if you use SQLite and want serial/auto-incrementing primary keys:
49 __PACKAGE__->load_components(qw/ PK::Auto::SQLite Core /);
51 C<PK::Auto> classes exist for many databases; see
52 L<DBIx::Class::PK::Auto> for more information.
54 Set the table for your class:
56 __PACKAGE__->table('album');
58 Add columns to your class:
60 __PACKAGE__->add_columns(qw/ albumid artist title /);
62 Accessors are created for each column automatically, so My::Schema::Album will
63 have albumid(), artist() and title() methods.
65 Define a primary key for your class:
67 __PACKAGE__->set_primary_key('albumid');
69 If you have a multi-column primary key, just pass a list instead:
71 __PACKAGE__->set_primary_key( qw/ albumid artistid / );
75 You can define relationships for any of your classes. L<DBIx::Class> will
76 automatically fill in the correct namespace, so if you want to say
77 "a My::Schema::Album object belongs to a My::Schema::Artist object" you do not
78 need to include the namespace when declaring the relationship:
80 __PACKAGE__->belongs_to('artist' => 'Artist');
84 That's all you need in terms of setup.
88 In your application code, you should first create a connected schema object:
90 my $schema = My::Schema->connect( $dsn, $user, $password, $attrs );
92 You can create as many different schema instances as you need. So if you have
93 a second database you want to access:
95 my $other_schema = My::Schema->connect( $dsn, $user, $password, $attrs );
97 Note that L<DBIx::Class::Schema> does not cache connnections for you. If you
98 use multiple connections, you need to do this manually.
100 To execute some sql statements on every connect you can pass them to your schema after the connect:
102 $schema->storage->on_connect_do(\@on_connect_sql_statments);
104 The simplest way to get a record is by primary key:
106 my $schema = My::Schema->connect( ... );
107 my $album = $schema->resultset('Album')->find(14);
109 This will run a C<SELECT> with C<albumid = 14> in the C<WHERE> clause,
110 and return an instance of C<My::Schema::Album> that represents this
111 row. Once you have that row, you can access and update columns:
113 $album->title('Physical Graffiti');
114 my $title = $album->title; # holds 'Physical Graffiti'
116 If you prefer, you can use the C<set_column> and C<get_column>
119 $album->set_column('title', 'Presence');
120 $title = $album->get_column('title');
122 You use C<update> to commit your changes to the database:
126 If needed, you can throw away your local changes like this:
128 $album->discard_changes() if $album->is_changed();
130 As you can see, C<is_changed> allows you to check if there are local
131 changes to your object.
133 =head2 Adding and removing rows
135 To create a new record in the database, you can use the C<create>
136 method. It returns an instance of C<My::Schema::Album> that can be
137 used to access the data in the new record:
139 my $new_album = $schema->resultset('Album')->create({
140 title => 'Wish You Were Here',
141 artist => 'Pink Floyd'
144 Now you can add data to the new record:
146 $new_album->label('Capitol');
147 $new_album->year('1975');
150 Likewise, you can remove it from the database like this:
154 You can also remove records without retrieving them first, by calling
155 delete directly on a ResultSet object.
157 # Delete all of Falco's albums
158 $schema->resultset('Album')->search({ artist => 'Falco' })->delete;
160 =head2 Finding your objects
162 L<DBIx::Class> provides a few different ways to retrieve data from
163 your database. Here's one example:
165 # Find all of Santana's albums
166 my $rs = $schema->resultset('Album')->search({ artist => 'Santana' });
168 In scalar context, as above, C<search> returns a
169 L<DBIx::Class::ResultSet> object. It can be used to peek at the first
170 album returned by the database:
172 my $album = $rs->first;
175 Or, you can loop over the albums and update each one:
177 while (my $album = $rs->next) {
178 print $album->artist . ' - ' . $album->title;
183 For more information on what you can do with a
184 L<DBIx::Class::ResultSet>, see L<DBIx::Class::ResultSet/METHODS>.
186 In list context, the C<search> method returns all of the matching
189 # Fetch immediately all of Carlos Santana's albums
190 my @albums = @{ $schema->resultset('Album')->search(
191 { artist => 'Carlos Santana' }
193 foreach my $album (@albums) {
194 print $album->artist . ' - ' . $album->title;
197 We also provide a handy shortcut for doing a C<LIKE> search:
199 # Find albums whose artist starts with 'Jimi'
200 my $rs = $schema->resultset('Album')->search_like({ artist => 'Jimi%' });
202 Or you can provide your own handmade C<WHERE> clause, like:
204 # Find Peter Frampton albums from the year 1986
205 my $where = 'artist = ? AND year = ?';
206 my @bind = ( 'Peter Frampton', 1986 );
207 my $rs = $schema->resultset('Album')->search_literal( $where, @bind );
209 The preferred way to generate complex queries is to provide a
210 L<SQL::Abstract> construct to C<search>:
212 my $rs = $schema->resultset('Album')->search({
213 artist => { '!=', 'Janis Joplin' },
214 year => { '<' => 1980 },
215 albumid => [ 1, 14, 15, 65, 43 ]
218 This results in something like the following C<WHERE> clause:
220 WHERE artist != 'Janis Joplin'
222 AND albumid IN (1, 14, 15, 65, 43)
224 For more examples of complex queries, see
225 L<DBIx::Class::Manual::Cookbook>.
227 The search can also be modified by passing another hash with
230 my @albums = $schema->resultset('Album')->search(
231 { artist => 'Bob Marley' },
232 { rows => 2, order_by => 'year DESC' }
235 C<@albums> then holds the two most recent Bob Marley albums.
237 For a complete overview of the available attributes, see
238 L<DBIx::Class::ResultSet/ATTRIBUTES>.
244 =item * L<DBIx::Class::Manual::Cookbook>
246 =item * L<DBIx::Class::Manual::FAQ>