=head1 NAME DBIx::Class::Manual::Intro - Introduction to DBIx::Class =head1 INTRODUCTION You're bored with SQL, and want a native Perl interface for your database? Or you've been doing this for a while with L, and think there's a better way? You've come to the right place. =head1 THE DBIx::Class WAY Here are a few simple tips that will help you get your bearings with DBIx::Class. =head2 Tables become Result classes DBIx::Class needs to know what your Table structure looks like. You do that by defining Result classes. Result classes are defined by calling methods proxied to L. Each Result class defines one Table, which defines the Columns it has, along with any Relationships it has to other tables. (And oh, so much more besides) The important thing to understand: A Result class == Table (most of the time, but just bear with my simplification) =head2 It's all about the ResultSet So, we've got some ResultSources defined. Now, we want to actually use those definitions to help us translate the queries we need into handy perl objects! Let's say we defined a ResultSource for an "album" table with three columns: "albumid", "artist", and "title". Any time we want to query this table, we'll be creating a L from its ResultSource. For example, the results of: SELECT albumid, artist, title FROM album; Would be retrieved by creating a ResultSet object from the album table's ResultSource, likely by using the "search" method. DBIx::Class doesn't limit you to creating only simple ResultSets -- if you wanted to do something like: SELECT title FROM album GROUP BY title; You could easily achieve it. The important thing to understand: Any time you would reach for a SQL query in DBI, you are creating a DBIx::Class::ResultSet. =head2 Search is like "prepare" DBIx::Class tends to wait until it absolutely must fetch information from the database. If you are returning a ResultSet, the query won't execute until you use a method that wants to access the data. (Such as "next", or "first") The important thing to understand: Setting up a ResultSet does not execute the query; retrieving the data does. =head2 Search results are returned as Rows Rows of the search from the database are blessed into L objects. =head1 SETTING UP DBIx::Class Let's look at how you can set and use your first native L 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 L, which shows you how to use L. =head2 Setting it up manually First, you should create your base schema class, which inherits from L: package My::Schema; use base qw/DBIx::Class::Schema/; In this class you load your result_source ("table", "model") classes, which we will define later, using the load_namespaces() method: # load My::Schema::Result::* and their resultset classes __PACKAGE__->load_namespaces(); By default this loads all the Result (Row) classes in the My::Schema::Result:: namespace, and also any resultset classes in the My::Schema::ResultSet:: namespace (if missing, the resultsets are defaulted to be DBIx::Class::ResultSet objects). You can change the result and resultset namespaces by using options to the L call. It is also possible to do the same things manually by calling C for the Row classes and defining in those classes any required resultset classes. Next, create each of the classes you want to load as specified above: package My::Schema::Result::Album; use base qw/DBIx::Class::Core/; Load any additional components you may need with the load_components() method, and provide component configuration if required. For example, if you want automatic row ordering: __PACKAGE__->load_components(qw/ Ordered /); __PACKAGE__->position_column('rank'); Ordered will refer to a field called 'position' unless otherwise directed. Here you are defining the ordering field to be named 'rank'. (NOTE: Insert errors may occur if you use the Ordered component, but have not defined a position column or have a 'position' field in your row.) Set the table for your class: __PACKAGE__->table('album'); Add columns to your class: __PACKAGE__->add_columns(qw/ albumid artist title rank /); Each column can also be set up with its own accessor, data_type and other pieces of information that it may be useful to have -- just pass C a hash: __PACKAGE__->add_columns(albumid => { accessor => 'album', data_type => 'integer', size => 16, is_nullable => 0, is_auto_increment => 1, }, artist => { data_type => 'integer', size => 16, is_nullable => 0, }, title => { data_type => 'varchar', size => 256, is_nullable => 0, }, rank => { data_type => 'integer', size => 16, is_nullable => 0, default_value => 0, } ); DBIx::Class doesn't directly use most of this data yet, but various related modules such as L make use of it. Also it allows you to create your database tables from your Schema, instead of the other way around. See L for details. See L for more details of the possible column attributes. Accessors are created for each column automatically, so My::Schema::Result::Album will have albumid() (or album(), when using the accessor), artist() and title() methods. Define a primary key for your class: __PACKAGE__->set_primary_key('albumid'); If you have a multi-column primary key, just pass a list instead: __PACKAGE__->set_primary_key( qw/ albumid artistid / ); Define this class' relationships with other classes using either C to describe a column which contains an ID of another Table, or C to make a predefined accessor for fetching objects that contain this Table's foreign key: # in My::Schema::Result::Artist __PACKAGE__->has_many('albums', 'My::Schema::Result::Album', 'artist'); See L for more information about the various types of available relationships and how you can design your own. =head2 Using DBIx::Class::Schema::Loader This module (L) is an external module, and not part of the L distribution. It inspects your database, and automatically creates classes for all the tables in your schema. The simplest way to use it is via the L script from the L distribution. For example: $ dbicdump -o dump_directory=./lib \ -o components='["InflateColumn::DateTime"]' \ MyApp::Schema dbi:mysql:mydb user pass If you have a mixed-case database, use the C option, e.g.: $ dbicdump -o dump_directory=./lib -o preserve_case=1 \ -o components='["InflateColumn::DateTime"]' \ MyApp::Schema dbi:mysql:mydb user pass If you are using L, then you can use the helper that comes with L: $ script/myapp_create.pl model MyModel DBIC::Schema MyApp::Schema \ create=static moniker_map='{ foo => "FOO" }' dbi:SQLite:./myapp.db \ on_connect_do='PRAGMA foreign_keys=ON' quote_char='"' See L for more information on this helper. See the L and L documentation for more information on the many loader options. =head2 Connecting To connect to your Schema, you need to provide the connection details or a database handle. =head3 Via connection details The arguments are the same as for L: my $schema = My::Schema->connect('dbi:SQLite:/home/me/myapp/my.db'); You can create as many different schema instances as you need. So if you have a second database you want to access: my $other_schema = My::Schema->connect( $dsn, $user, $password, $attrs ); Note that L does not cache connections for you. If you use multiple connections, you need to do this manually. To execute some SQL statements on every connect you can add them as an option in a special fifth argument to connect: my $another_schema = My::Schema->connect( $dsn, $user, $password, $attrs, { on_connect_do => \@on_connect_sql_statments } ); See L for more information about this and other special C-time options. =head3 Via a database handle The supplied coderef is expected to return a single connected database handle (e.g. a L C<$dbh>) my $schema = My::Schema->connect ( sub { Some::DBH::Factory->connect }, \%extra_attrs, ); =head2 Basic usage Once you've defined the basic classes, either manually or using L, you can start interacting with your database. To access your database using your $schema object, you can fetch a L representing each of your tables by calling the C method. The simplest way to get a record is by primary key: my $album = $schema->resultset('Album')->find(14); This will run a C