DBIx::Class::Manual::Intro - Introduction to DBIx::Class
So, you are bored with SQL, and want a native Perl interface for your database? Or you've been doing this for a while with Class::DBI, and think there's a better way? You've come to the right place. Let's look at how you can set and use your first native DBIx::Class tree.
First we'll see how you can set up your classes yourself. If you want them to be auto-discovered, just skip to the next section, which shows you how to use DBIx::Class::Loader.
First, you'll need a base class. It should inherit from DBIx::Class like this:
package MyApp::DB; use base qw/DBIx::Class/;
You will also want to load some of the DBIx::Class components. DBIx::Class::Core provides a good starter set. In addition you'll have to use either DBIx::Class::Schema or DBIx::Class::DB. We'll use DB in this introduction, since it involves less magic. Schema is mostly useful if you want to use multiple database connections.
DB
Schema
__PACKAGE__->load_components(qw/Core DB/);
If you want serial/auto-incrementing primary keys, you should use the DBIx::Class::PK::Auto component for your database. For example, if you're using SQLite add PK::Auto::SQLite to the list:
PK::Auto::SQLite
__PACKAGE__->load_components(qw/PK::Auto::SQLite Core DB/);
PK::Auto classes exist for many databases; see DBIx::Class::PK::Auto for more information.
PK::Auto
Once you've loaded the components, it's time to set up your connection:
__PACKAGE__->connection('dbi:SQLite:/home/me/myapp/my.db');
This method is similar to the normal DBI connect method, and can take username, password, and DBI attribute hash as well as the DSN.
connect
With that out of the way, we can define our first table class:
package MyApp::DB::Album; use base qw/MyApp::DB/;
Then we specify which table it uses,
__PACKAGE__->table('album');
and specify which columns it has.
__PACKAGE__->add_columns(qw/albumid artist title label year/);
This will automatically create accessors for each of the columns, so that you can read/update the values in rows you've retrieved.
Also, you need to tell it which column is the primary key:
__PACKAGE__->set_primary_key('albumid');
If you have a primary key composed of multiple columns, just pass a list instead.
That's pretty much all you need for a basic setup. If you have more advanced needs like using more than one database connection for the same class, see DBIx::Class::Schema.
This is an additional class, and not part of the DBIx::Class distribution. Like Class::DBI::Loader, it inspects your database, and automatically creates classes for all the tables in your database. Here's a simple setup:
package MyApp::DB; use DBIx::Class::Loader; my $loader = DBIx::Class::Loader->new( dsn => 'dbi:SQLite:/home/me/myapp/my.db', namespace => 'MyApp::DB' ); 1;
This should be equivalent to the manual setup in the section above. DBIx::Class::Loader takes lots of other options. For more information, consult its documentation.
Once you've defined the basic classes, either manually or using DBIx::Class::Loader, you can start interacting with your database. The simplest way to get a record is by primary key:
my $album = MyApp::DB::Album->find(14);
This will run a SELECT with albumid = 14 in the WHERE clause, and return an instance of MyApp::DB::Album that represents this row. Once you have that row, you can access and update columns:
SELECT
albumid = 14
WHERE
MyApp::DB::Album
$album->title('Physical Graffiti'); my $title = $album->title; # $title holds 'Physical Graffiti'
If you prefer, you can use the set_column and get_column accessors instead:
set_column
get_column
$album->set_column('title', 'Presence'); $title = $album->get_column('title');
Just like with Class::DBI, you do an update to commit your changes to the database:
update
$album->update;
If needed, you can throw away your local changes like this:
$album->discard_changes if $album->is_changed;
As you can see, is_changed allows you to check if there are local changes to your object.
is_changed
To create a new record in the database, you can use the create method. It returns an instance of MyApp::DB::Album that can be used to access the data in the new record:
create
my $new_album = MyApp::DB::Album->create({ title => 'Wish You Were Here', artist => 'Pink Floyd' });
Now you can add data to the new record:
$new_album->label('Capitol'); $new_album->year('1975'); $new_album->update;
Likewise, you can remove it from the database like this:
$new_album->delete;
You can also remove records without or retrieving first. This operation takes the same kind of arguments as a search.
# Delete all of Falco's albums MyApp::DB::Album->delete({ artist => 'Falco' });
DBIx::Class provides a few different ways to retrieve data from your database. Here's one example:
# Find all of Santana's albums my $rs = MyApp::DB::Album->search({ artist => 'Santana' });
In scalar context, as above, search returns a DBIx::Class::ResultSet object. It can be used to peek at the first album returned by the database:
search
my $album = $rs->first; print $album->title;
Or, you can loop over the albums and update each one:
while (my $album = $rs->next) { print $album->artist . ' - ' . $album->title; $album->year(2001); $album->update; }
For more information on what you can do with a DBIx::Class::ResultSet, see "METHODS" in DBIx::Class::ResultSet.
In list context, the search method returns all of the matching rows:
# Fetch immediately all of Carlos Santana's albums my @albums = MyApp::DB::Album->search({ artist => 'Carlos Santana' }); foreach my $album (@albums) { print $album->artist . ' - ' . $album->title; }
We also provide a handy shortcut for doing a LIKE search:
LIKE
# Find albums whose artist starts with 'Jimi' my $rs = MyApp::DB::Album->search_like({ artist => 'Jimi%' });
Or you can provide your own handmade WHERE clause, like:
# Find Peter Frampton albums from the year 1986 my $where = 'artist = ? AND year = ?'; my @bind = ( 'Peter Frampton', 1986 ); my $rs = MyApp::DB::Album->search_literal( $where, @bind );
The preferred way to generate complex queries is to provide a SQL::Abstract construct to search:
my $rs = MyApp::DB::Album->search({ artist => { '!=', 'Janis Joplin' }, year => { '<' => 1980 }, albumid => [ 1, 14, 15, 65, 43 ] });
This results in something like the following WHERE clause:
WHERE artist != 'Janis Joplin' AND year < 1980 AND albumid IN (1, 14, 15, 65, 43)
For more examples of complex queries, see DBIx::Class::Manual::Cookbook.
The search can also be modified by passing another hash with attributes:
my @albums = MyApp::DB::Album->search( { artist => 'Bob Marley' }, { rows => 2, order_by => 'year DESC' } );
@albums then holds the two most recent Bob Marley albums.
@albums
For a complete overview of the available attributes, see "ATTRIBUTES" in DBIx::Class::ResultSet.
DBIx::Class::Manual::Cookbook
DBIx::Class::Manual::FAQ
To install DBIx::Class, copy and paste the appropriate command in to your terminal.
cpanm
cpanm DBIx::Class
CPAN shell
perl -MCPAN -e shell install DBIx::Class
For more information on module installation, please visit the detailed CPAN module installation guide.