The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.


Coat::Persistent -- Simple Object-Relational mapping for Coat objects


Coat::Persistent is an object to relational-databases mapper, it allows you to build instances of Coat objects and save them into a database transparently.

You basically define a mapping rule, either global or per-class and play with your Coat objects without bothering with SQL for simple cases (selecting, inserting, updating).

Coat::Peristent lets you use SQL if you want to, considering SQL is the best language when dealing with compelx queries.


There are already very good ORMs for Perl available in the CPAN so why did this module get added?

Basically for one reason: I wanted a very simple way to build persistent objects for Coat and wanted something near the smart design of Rails'ORM (ActiveRecord). Moreover I wanted my ORM to let me send SQL requests if I wanted to (so I can do basic actions without SQL and complex queries with SQL).

This module is the result of my experiments of mixing DBI and Coat together, although it is a developer release, it works pretty well and fit my needs.

This module is expected to change in the future (don't consider the API to be stable at this time), and to grow (hopefully).

The underlying target of this module is to port the whole ActiveRecord::Base API to Perl. If you find the challenge and the idea interesting, feel free to contact me for giving a hand.

This is still a development version and should not be used in production environment.


The concept behing this module is the same behind the ORM of Rails : there are conventions that tell how to translate a model meta-information into a SQL one :

The conventions implemented in Coat::Persistent are the following:

The primary key of the tables mapped should be named 'id'.
Your table names must be named like the package they map, with the following rules applied : lower case, replace "::" by "_". For instance a class Foo::Bar should be mapped to a table named "foo_bar".
All foreign keys must be named "<table>_id" where table is the name if the class mapped formated like said above.

You can overide those conventions at import time:

    package My::Model;
    use Coat;
    use Coat::Persistent 
            table_name  => 'mymodel', # default would be 'my_model'
            primary_key => 'mid';     # default would be 'id'


Even if your table does not have a primary key, you can still use a Coat::Persistent model over it. You just have to tell Coat::Persistent that this table/model doesn't have a primary key :

   use Coat::Persistent primary_key => undef;

Note that instances of such a model cannot be saved like regular ones: there's no primary key, so it's impossible to build UPDATE SQL queries properly. That's why you'll have to give a condition whenver you call save().

For the same reason, it's impossible to use find() with numeric values (whi are assumed to be primary key values).

Example : package Model; ... use Coat::Persistent primary_key => undef; ...

    package  main;

    my $obj = Model->find(43); # FAIL : there's no primary key known for Model
    my $obj = Model->find_by_some_attribute(25); # OK

    $obj->save(); # FAIL : the SQL query cannot be built without a primary key
                  # defined

    $obj->save({some_attribute => 25}); # OK

Note that it's not recommended to use tables whithout primary keys, the support is only provided to support existing/border-line database schemas we can find in real-world.

Use that feature with caution!


You have two options for setting a database handle to your class. Either you already have a dbh an you set it to your class, or you don't and you let Coat::Persistent initialize it.

If you already have a database handle, use:

    # $driver is the driver name of the database handle (mysql, sqlite, ...)
    # $dbh is the database handle previously inititalized
    Coat::Persistent->set_dbh( $driver => $dbh);

Otherwise, use the DBI mapping explained below.


You may want to tell Coat::Persistent to use a $dbh you already have in hands, then you can use the set_dbh() method.

set_dbh($driver = $dbh)>

Set the given database handle for the calling class (set it by default if class is Coat::Persistent).


You have to tell Coat::Persistent how to map a class to a DBI driver. You can either choose to define a default mapper (in most of the cases this is what you want) or define a mapper for a specific class.

In order for your mapping to be possible, the driver you use must be known by Coat::Persistent, you can modify its driver mapping matrix if needed.

drivers( )

Return a hashref representing all the drivers mapped.

get_driver( $name )

Return the Perl module of the driver defined for the given driver name.

  MyClass->get_driver( 'mysql' );
add_driver( $name, $module )

Add or replace a driver mapping rule.

  MyClass->add_driver( sqlite => 'dbi:SQLite' );

Then, you can use your driver in mapping rules. Basically, the mapping will generate a DBI->connect() call.

Coat::Persistent->map_to_dbi $driver, @options

This will set the default mapper. Every class that hasn't a specific mapper set will use this one.

__PACKAGE__->map_to_dbi $driver, @options

This will set a mapper for the current class.

Supported values for $driver are the following :

csv : this will use DBI's "DBD:CSV" driver to map your instances to a CSV file. @options must contains a string as its first element being like the following: "f_dir=<DIRECTORY>" where DIRECTORY is the directory where to store de CSV files.


    packahe Foo;
    use Coat::Persistent;
    __PACKAGE__->map_to_dbi('csv', 'f_dir=./t/csv-directory');
mysql : this will use DBI's "dbi:mysql" driver to map your instances to a MySQL database. @options must be a list that contains repectively: the database name, the database user, the database password.


    package Foo;
    use Coat::Persistent;
    __PACKAGE__->map_to_dbi('mysql' => 'dbname', 'dbuser', 'dbpass' );


When using MySQL, you can choose either to let Coat::Persistent set itself primary key values for new entries, or use MySQL auto_increment mechanism.

This is done by calling Coat::Persistent->disable_internal_sequence_engine(); before any call to map_to_dbi() or set_dbh().

Currently, this is only tested to work with MySQL, patches for supporting other database engines are welcome.

Make sure you disable the internal sequence engine before initializing the $dbh, otherwise the two tables needed by DBIx::Sequence will be created in your DB (dbix_sequence_release and dbix_sequence_state).

A typical use of a MySQL database with auto_increment primary keys woudl like the following:

    # $dbh is an hanlde to a MySQL DB
    Coat::Persistent->set_dbh(mysql => $dbh);


Since version 0.0_0.2, Coat::Persistent provides a simple way to cache the results of underlying SQL requests. By default, no cache is performed.

You can either choose to enable the caching system for all the classes (global cache) or for a specific class. You could also define different cache configurations for each class.

When the cache is enabled, every SQL query generated by Coat::Persistent is first looked through the cache collection. If the query is found, its cached result is returned; if not, the query is executed with the appropriate DBI mapper and the result is cached.

The backend used by Coat::Persistent for caching is Cache::FastMmap which is able to expire the data on his own. Coat::Persistent lets you access the Cache::FastMmap object through a static accessor :

Coat::Persistent->cache : return the default cache object
__PACKAGE__->cache : return the cache object for the class __PACKAGE__

To set a global cache system, use the static method enable_cache. This method receives a hash table with options to pass to the Cache::FastMmap constructor.

Example :

        expire_time => '1h',
        cache_size  => '50m',
        share_file  => '/var/cache/myapp.cache',

It's possible to disable the cache system with the static method disable_cache.

See Cache::FastMmap for details about available constructor's options.



The following pragma are provided to configure the mapping that will be done between a table and the class.

has_p $name => %options

Coat::Persistent classes have the keyword has_p to define persistent attributes. Attributes declared with has_p are valid Coat attributes and take the same options as Coat's has method. (Refer to Coat for details).

All attributes declared with has_p must exist in the mapped data backend (they are a column of the table mapped to the class).

has_one $class

Tells that current class owns a subobject of the class $class. This will allow you to set and get a subobject transparently.

The backend must have a foreign key to the table of $class.


    package Foo;
    use Coat::Persistent;

    has_one 'Bar';

    package Bar;
    use Coat::Persistent;

    my $foo = new Foo;
    $foo->bar(new Bar);
has_many $class

This is the same as has_one but says that many items are bound to one instance of the current class. The backend of class $class must provide a foreign key to the current class.


The following methods are inherited by Coat::Persistent classes, they provide features for accessing and touching the database below the abstraction layer. Those methods must be called in class-context.

Find by id: This can either be a specific id or a list of ids (1, 5, 6)
Find in scalar context: This will return the first record matched by the options used. These options can either be specific conditions or merely an order. If no record can be matched, undef is returned.
Find in list context: This will return all the records matched by the options used. If no records are found, an empty array is returned.

The following options are supported :

select: By default, this is * as in SELECT * FROM, but can be changed.
from: By default, this is the table name of the class, but can be changed to an alternate table name (or even the name of a database view).
order: An SQL fragment like "created_at DESC, name".
group: An attribute name by which the result should be grouped. Uses the GROUP BY SQL-clause.
limit: An integer determining the limit on the number of rows that should be returned.

Examples without options:

    my $obj = Class->find(23);
    my @list = Class->find(1, 23, 34, 54);
    my $obj = Class->find("field = 'value'");
    my $obj = Class->find(["field = ?", $value]);

Example with options:

    my @list = Class->find($condition, { order => 'field1 desc' })
find_by_sql($sql, @bind_values

Executes a custom sql query against your database and returns all the results if in list context, only the first one if in scalar context.

If you call a complicated SQL query which spans multiple tables the columns specified by the SELECT that aren't real attributes of your model will be provided in the hashref of the object, but you won't have accessors.

The sql parameter is a full sql query as a string. It will be called as is, there will be no database agnostic conversions performed. This should be a last resort because using, for example, MySQL specific terms will lock you to using that particular database engine or require you to change your call if you switch engines.


    my $obj = Class->find_by_sql("select * from class where $cond");
    my @obj = Class->find_by_sql("select * from class where col = ?", 34);

Creates an object (or multiple objects) and saves it to the database.

The attributes parameter can be either be a hash or an array of hash-refs. These hashes describe the attributes on the objects that are to be created.


  # Create a single new object
  User->create(first_name => 'Jamie')
  # Create an Array of new objects
  User->create([{ first_name => 'Jamie'}, { first_name => 'Jeremy' }])


The following methods are provided by objects created from the class. Those methods must be called in instance-context.


If no record exists, creates a new record with values matching those of the object attributes. If a record does exist, updates the record with values matching those of the object attributes.

Returns the id of the object saved.


See Coat for all the meta-class documentation. See Cache::FastMmap for details about the cache objects provided.


This module was written by Alexis Sukrieh <>. Quite everything implemented in this module was inspired from ActiveRecord::Base's API (from Ruby on Rails).

Parts of the documentation are also taken from ActiveRecord::Base when appropriate.


Copyright 2007 by Alexis Sukrieh.

This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself.

2 POD Errors

The following errors were encountered while parsing the POD:

Around line 1252:

=back without =over

Around line 1254:

'=item' outside of any '=over'