The Perl Toolchain Summit needs more sponsors. If your company depends on Perl, please support this very important event.

NAME

DBIx::Class::Manual::Intro

Introduction.

So, you are bored with SQL, and want a native perl interface for your classes? 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.

Setting it up manually

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 DBIx::Class's components. DBIx::Class::Core provides a good basic 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.

  __PACKAGE__->load_components(qw/Core DB/);

If you want serial/auto-incremental primary keys, you'll need to add the apropriate component for your db as well, for example

  __PACKAGE__->load_components(qw/PK::Auto::SQLite Core DB/);

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, and can take user/pass/dbi attribute hash as well as the dsn.

With that out of the way, we can define our first table class:

  package MyApp::DB::Frob

  use base qw/MyApp::DB/;

Then we specify which table it uses,

  __PACKAGE__->table('frob');

and specify which columns it has.

  __PACKAGE__->add_columns(qw/id foo bar/);

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('id');

If you have multiple primary keys, 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 1 database connections for the same class, see DBIx::Class::Schema.

Using DBIx::Class::Loader.

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 in the section above. DBIx::Class::Loader takes lots of other options. For more information, consult the reference documentation.

Basic Usage

Once you've defined the basic classes, you can start interacting with your database. The simplest way to get a column is by primary key:

  my $frob=MyApp::DB::Frob->find(14);

This will run a select with id=14 in the WHERE clause, and return an instance of MyApp::DB::Frob that represents this row. Once you have that row, you can access and update columns

    my $val=$frob->bar;
    $frob->bar(14);

or if you prefer, you can use the set_column/get_column accessors instead of the autogenerated accessors based on your column names.

Just like with Class::DBI, you do an 'update' to commit your changes to the database:

   $frob->update;

If needed, you can drop your local changes instead like this:

  $frob->discard_changes if $frob->is_changed;

As you can see, is_changed allows you to check if there are local changes to your object.

Adding and removing rows.

To make a new row, and put it into the database, you can use the 'create' method from DBIx::Class::Row

  my $new_thingie=MyApp::DB::Frob->create({
    foo=>'homer',
    bar=>'bart' });

likewise, you can remove if from the database like this:

  $new_thingie->delete();

or even without retrieving first. This operation takes the same kind of arguments as a search.

  MyApp::DB::Frob->delete({foo=>'bart'});

Finding your objects.

DBIx::Class provides a few different ways to retrieve data from your database. The simplest looks something like this:

  $rs=MyApp::DB::Frob->search(foo=>'bart');

note that all the search methods return a recordset in scalar context or a list containing all the elements in list context.

We also provide a handy shortcut for doing a like search:

  $rs=MyApp::DB::Frob->search_like(foo=>'bar%');

Or you can provide your own handmade WHERE clause, like

  $rs=MyApp::DB::Frob->search_literal('foo=?','bart');

The other way to provide more complex queries, is to provide a SQL::Abstract construct to search:

  $rs=MyApp::DB::Frob->search({
    bar=>{'>' => 10 },
    foo=>{'!=','bart'},
    id => [1,14,15,65,43]
  });

The search can also be modifyed by passing another hash with attributes:

  $rs=MyApp::DB::Frob->search( {foo=>'bart'},
      { page=>1, rows=>2, order_by=>'bar' } ); 

For a complete overview over the available attributes, see DBIx::Class::ResultSet