Al Newkirk > ORMesque > ORMesque



Annotate this POD


Open  1
View/Report Bugs
Module Version: 1.110422   Source  


ORMesque - Lightweight To-The-Point ORM


version 1.110422


    my $db = ORMesque->new('dbi:mysql:foo', 'root');
    my $ta = $db->table_a
            ->page(1, 25)
            ->read({ column => 'value' });
    my $tb = $db->table_b
            ->page(1, 25)
            ->read({ column => 'value' });
    return $ta->join($tb); # returns an aggregated arrayref of hashefs

ORMesque is a lightweight ORM supporting any database listed under ORMesque::SchemaLoader making it a great alternative when you don't have the time, need or desire to learn/utilize DBIx::Class or the like. ORMesque is an object relational mapper that provides a database connection to the database of your choice and automatically creates objects and accessors for that database and its tables and columns. ORMesque uses SQL::Abstract querying syntax. More usage examples...

    my $db = ORMesque->new($dsn);
    my $user = $db->users;
    # Grab the first record, not neccessary if operating on only one record
    # SQL::Abstract where clause passed to the "read" method
        'column' => 'query'
    # How many records in collection
    for (0..$user->count) {
        print $user->column;
        $user->column('new stuff');
        $user->update($user->current, $user->id);
    # The database objects main accessors are CRUD (create, read, update, and delete)
    # Also, need direct access to the resultset?
    $user->collection; # returns an array of hashrefs
    $user->current;    # return a hashref of the current row in the collection

Occassionally you may want to create application Models using ORMesque and venture beyond the standard CRUD methods, creating classes for each table and extending its methods. The following is an example of how this should be done using ORMesque.

    package MyApp::Model;
    use base 'ORMesque';
    # create your base Model - lib/MyApp/
    package MyApp::Model::Cd;
    use base 'MyApp::Model';
    # create your table specific Model - lib/MyApp/Model/
    # note the model should be named after the table, the naming is as follows:
    # Schema Table Classes are CamelCased for convention, all class names are
    # titlecased, and have dashed and underscores removed.
    # e.g. table 'user_workplace' would generate a class named 'UserWorkspace'
    # with no special characters. If package name is one of the auto-generated
    # classes, all relevant methods and settings will be set automatically.
    sub write_cd {
        $self = shift;
        $self->create({ ... });
        return $self;


    The namespace() method returns the classname being used in the auto-generated
    database table classes. 
    my $a = ORMesque->new(...);
    my $b = ThisApp->new(...);
    $a->namespace; # ORMesque
    $b->namespace; # ThisApp


    Once the reset() method analyzes the specified database, the schema is cached
    to for speed and performance. Occassionally you may want to re-read the
    database schema.
    my $db = ORMesque->new(...);


    The next() method instructs the database object to continue to the next
    row if it exists.
    my $table = ORMesque->new(...)->table;
    while ($table->next) {


    The first() method instructs the database object to continue to return the first
    row in the resultset.
    my $table = ORMesque->new(...)->table;


    The last() method instructs the database object to continue to return the last
    row in the resultset.
    my $table = ORMesque->new(...)->table;


    The collection() method return the raw resultset object.
    my $table = ORMesque->new(...)->table;


    The current() method return the raw row resultset object of the position in
    the resultset collection.
    my $table = ORMesque->new(...)->table;


    The clear() method empties all resultset containers. This method should be used
    when your ready to perform another operation (start over) without initializing
    a new object.
    my $table = ORMesque->new(...)->table;


    The key() method finds the database objects primary key if its defined.
    my $table = ORMesque->new(...)->table;


    The select() method defines specific columns to be used in the generated
    SQL query. This useful for database tables that have lots of columns
    where only a few are actually needed.
    my $table = ORMesque->new(...)->table
    $table->select('foo', 'bar')->read();


    The return() method queries the database for the last created object(s).
    It is important to note that while return() can be used in most cases
    like the last_insert_id() to fetch the recently last created entry,
    function, you should not use it that way unless you know exactly what
    this method does and what your database will return.
    my $new = ORMesque->new(...)->table;
    my $rec = $new->current; # current row


    The count() method returns the number of items in the resultset of the
    object it's called on. Note! If you make changes to the database, you
    will need to call read() before calling count() to get an accurate
    count as count() operates on the current collection.
    my $db = ORMesque->new(...)->table;
    my $count = $db->read->count;
    Note! The count() method DOES NOT query the database, it merely counts
    the number of items in the existing resultset produced by read().
    Alternatively, to perform a type-of SQL COUNT() query you can use the
    count($where) syntax:
    my $db = ORMesque->new(...)->table;
    my $count = $db->count({ id => 12345});
    # notice there is no read() command


    Caveat 1: The create method will remove the primary key if the column
    is marked as auto-incremented ...
    The create method creates a new entry in the datastore.
    takes 1 arg: hashref (SQL::Abstract fields parameter)
        'column_a' => 'value_a',
    # create a copy of an existing record
    my $user = ORMesque->new(...)->users;
    $user->full_name_column('Copy of ' . $user->full_name);

    # get newly created record
    print $user->id; # new record id
    print $user->full_name;


    The read method fetches records from the datastore.
    Takes 2 arg.
    arg 1: hashref (SQL::Abstract where parameter) or scalar
    arg 2: arrayref (SQL::Abstract order parameter) - optional
        'column_a' => 'value_a',
    .. or read by primary key ..
    .. or read and limit the resultset ..
    ORMesque->new(...)->table->read({ 'column_a' => 'value_a' }, ['orderby_column_a'], $limit, $offset);
    .. or return a paged resultset ..
    ORMesque->new(...)->table->page(1, 25)->read;


    The update method alters an existing record in the datastore.
    Takes 2 arg.
    arg 1: hashref (SQL::Abstract fields parameter)
    arg 2: arrayref (SQL::Abstract where parameter) or scalar - optional
        'column_a' => 'value_a',
        'where_column_a' => '...'
        'column_a' => 'value_a',
    }, 1);


    The delete method is prohibited from deleting an entire database table and
    thus requires a where clause. If you intentionally desire to empty the entire
    database then you may use the delete_all method.
        'column_a' => 'value_a',


    The delete_all method is use to intentionally empty the entire database table.


If you have used ORMesque with a project of any sophistication you will have undoubtedly noticed that the is no mechanism for specifying joins and this is intentional. ORMesque is an ORM, and object relational mapper and that is its purpose, it is not a SQL substitute. Joins are neccessary in SQL as they are the only means of gathering related data. Such is not the case with Perl code, however, even in code the need to join related datasets exists and that is the need we address. The join method "Does Not Execute Any SQL", in-fact the join method is meant to be called after the desired resultsets have be gathered. The join method is merely an aggregator of result sets.

    my ($cd, $artist) = (ORMesque->new(...)->cd, ORMesque->new(...)->artist);

    $artist->read({ id => $aid });
    $cd->read({ artist => $aid });

Always use the larger dataset to initiate the join, in the following example, the list we want is "the list of cds" and we want to include the artist information with every "cd" entry so we use the persist option.

    my $resultset = $cd->join($artist, {
        persist => 1

The join configuration option "persist" when set true will instruct the aggregator to include the first entry of the associated table with each entry in the primary list which is the list (collection) within the object that initiated the join. Every table object may be passed an options join configuration object as follows:

    my $resultset = $cd->join($artist, {
        persist => 1
    .. which is the same as ..
    my $resultset = $cd->join({
    }, $artist, {
        persist => 1
    .. more complexity ..
    my $resultset = $track->join($cd, {
        persist => 1
    }, $artist, {
        persist => 1

By default, a joined resultset is returned as an arrayref of hashrefs with all table columns as keys which are in $table_$columnName format. This is not always ideal and so the "columns" join configuration option allows you to specify exactly which columns to include as well as supply an alias if desired. The following is an example of that:

    my $resultset = $track->join({
        columns => {
            track_name => 'track',
    }, $cd, {
        persist => 1
        columns => {
            cd_name => 'cd',
    }, $artist, {
        persist => 1,
        columns => {
            artist_name => 'artist'


    The page method creates a paged resultset and instructs the read() method to
    only return the resultset of the desired page.
    my $page = 1; # page of data to be returned
    my $rows = 100; # number of rows to return
    ORMesque->new(...)->table->page($page, $rows)->read;


    The pager method provides access to the Data::Page object used in pagination.
    Please see L<Data::Page> for more details...
    $pager = ORMesque->new(...)->table->pager;


ORMesque provides columns accessors to the current record in the resultset object which is accessible via current() by default, collection() returns an arrayref of hashrefs based on the last read() call. Alternatively you may use the following methods to further transform and manipulate the returned resultset.


    Returns a list of column names. In scalar context, returns an array reference.
    Column names are lower cased if lc_columns was true when the query was executed.


    Binds the columns returned from the query to variable(s)
    ORMesque->new(...)->table->read(1)->into(my ($foo, $bar));


    Fetches a single row and returns a list of values. In scalar context,
    returns only the last value.
    my @values = ORMesque->new(...)->table->read(1)->list;


    Fetches a single row and returns an array reference.
    my $row = ORMesque->new(...)->table->read(1)->array;
    print $row->[0];


    Fetches a single row and returns a hash reference.
    Keys are lower cased if lc_columns was true when the query was executed.
    my $row = ORMesque->new(...)->table->read(1)->hash;
    print $row->{id};


    Fetches all remaining rows and returns a flattened list.
    In scalar context, returns an array reference.
    my @records = ORMesque->new(...)->table->read(1)->flat;
    print $records[0];


    Fetches all remaining rows and returns a list of array references.
    In scalar context, returns an array reference.
    my $rows = ORMesque->new(...)->table->read(1)->arrays;
    print $rows->[0];


    Fetches all remaining rows and returns a list of hash references.
    In scalar context, returns an array reference.
    Keys are lower cased if lc_columns was true when the query was executed.
    my $rows = ORMesque->new(...)->table->read(1)->hashes;
    print $rows->[0]->{id};


    Constructs a hash of hash references keyed by the values in the chosen column.
    In scalar context, returns a hash reference.
    In list context, returns interleaved keys and values.
    my $customer = ORMesque->new(...)->table->read->map_hashes('id');
    # $customers = { $id => { name => $name, location => $location } }


    Constructs a hash of array references keyed by the values in the chosen column.
    In scalar context, returns a hash reference.
    In list context, returns interleaved keys and values.
    my $customer = ORMesque->new(...)->table->read->map_arrays(0);
    # $customers = { $id => [ $name, $location ] }


    Returns the number of rows affected by the last row affecting command,
    or -1 if the number of rows is not known or not available.
    For SELECT statements, it is generally not possible to know how many
    rows are returned. MySQL does provide this information. See DBI for a
    detailed explanation.
    my $changes = ORMesque->new(...)->table->insert(ORMesque->new(...)->table->current)->rows;


ORMesque has as its sub-classes DBIx::Simple and SQL::Abstract as its querying language, it also provides access to SQL::Interp for good measure. For an in-depth look at what you can do with these utilities, please check out DBIx::Simple::Examples.


The error function is used to access the $DBI::errstr variable.


The query function provides a simplified interface to DBI, Perl's powerful database interfacing module. This function provides auto-escaping/interpolation as well as resultset abstraction.

    $db->query('DELETE FROM foo WHERE id = ?', $id);
    $db->query('SELECT 1 + 1')->into(my $two);
    $db->query('SELECT 3, 2 + 2')->into(my ($three, $four));

        'SELECT name, email FROM people WHERE email = ? LIMIT 1',
    )->into(my ($name, $email));
    # One big flattened list (primarily for single column queries)
    my @names = $db->query('SELECT name FROM people WHERE id > 5')->flat;
    # Rows as array references
    for my $row ($db->query('SELECT name, email FROM people')->arrays) {
        print "Name: $row->[0], Email: $row->[1]\n";


The iquery function is used to interpolate Perl variables into SQL statements, it converts a list of intermixed SQL fragments and variable references into a conventional SQL string and list of bind values suitable for passing onto DBI

    my $result = $db->iquery('INSERT INTO table', \%item);
    my $result = $db->iquery('UPDATE table SET', \%item, 'WHERE y <> ', \2);
    my $result = $db->iquery('DELETE FROM table WHERE y = ', \2);

    # These two select syntax produce the same result
    my $result = $db->iquery('SELECT * FROM table WHERE x = ', \$s, 'AND y IN', \@v);
    my $result = $db->iquery('SELECT * FROM table WHERE', {x => $s, y => \@v});

    my $first_record = $result->hash;
    for ($result->hashes) { ... }


Access to the underlying DBIx::Simple object.


Access to the underlying DBI object.


Determine whether a database connection exists. Returns true or false.


Al Newkirk <>


This software is copyright (c) 2010 by awncorp.

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

syntax highlighting: