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


Class::DBI::AutoIncrement - Emulate auto-incrementing columns on Class::DBI subclasses


$Id:,v 1.7 2006/06/08 08:58:32 erwan Exp $


Let's assume you have a project making use of Class::DBI. You have implemented a subclass of Class::DBI called MyProject::DBI that opens a connection towards your project's database. You also created a class called MyProject::Book that represents the table Book in your database:

    package MyProject::Book;
    use base qw(MyProject::DBI);

    MyProject::Book->columns(Primary => qw(seqid));
    MyProject::Book->table(Others => qw(author title isbn));

Now, you would like the column seqid of the table Book to be auto-incrementing, but your database unfortunately does not support auto-incrementing sequences. Instead, use Class::DBI::AutoIncrement to set the value of seqid automagically upon each insert():

    package MyProject::Book;
    use base qw(Class::DBI::AutoIncrement MyProject::DBI);

    MyProject::Book->columns(Primary => qw(seqid));
    MyProject::Book->table(Others => qw(author title isbn));

From now on, when you call:

    my $book = Book->insert({author => 'me', title => 'my life'});

$book gets its seqid field automagically set to the next available value for that column. If you had 3 rows in the table 'book' having seqids 1, 2 and 3, this new inserted row will get the seqid 4 (assuming a default setup).

That's it!


Class::DBI::AutoIncrement emulates an auto-incrementing sequence on a column of a table managed by a subclass of Class::DBI.

Class::DBI does not natively support self-incrementing sequences, but relies on the underlying database having support for it, which not all databases do have. Class::DBI::AutoIncrement provides an emulation layer that automagically sets a specified column to its next index value when the Class::DBI method insert is called.

Class::DBI::AutoIncrement does that by querying the table for the current highest value of the column, upon each call to insert (or only once if caching is on). You can also specify the increment step and start value of the sequence.

No more than one column per table can be auto-incremented.


A child class of Class::DBI that describes a table with 1 auto-incremented column must inherit from Class::DBI::AutoIncrement, and Class::DBI::AutoIncrement must be its first parent class in its @ISA array. The inheritance declaration should look like:

    package ChildOfClassDBI;

    use base qw(Class::DBI::AutoIncrement Some Other Classes);

This is necessary since Class::DBI::AutoIncrement uses the child class's @ISA during runtime to access the parent's implementations of the insert method.


autoincrement($column) or autoincrement($column, [Min => $min,] [Step => $step,] [Cache => 1,])

Tells Class::DBI::AutoIncrement which column should be auto-incremented, and how.

$column is the column name and must be defined.

$min is the sequence's start value. If $min is not defined, 0 is assumed as the start value.

$step is the step of increment. If not defined, a step of 1 is assumed.


    # seqid is automatically incremented by 1 at each insert, starting at 1
    MyProject::Book->autoincrement('seqid', Min => 1);    

    # seqid is automatically incremented by 3 at each insert, starting at 5
    MyProject::Book->autoincrement('seqid', Min => 5, Step => 3);    

By default Class::DBI::AutoIncrement queries the database for the current highest value of the auto-incremented column upon each call to insert. If you want to let Class::DBI::AutoIncrement cache this value, set the 'Cache' parameter to true:

    # seqid is automatically incremented by 1 at each insert, and query the database
    # only the first time we need to know the current index value
    MyProject::Book->autoincrement('seqid', Cache => 1);

Overrides Class::DBI's insert method. If $data->{$column} is undefined, it is automagically set to its next value. If $data->{$column} is defined, this value is used unchanged.


This method is supported for backward compatibility reasons. Do not use it. Same as insert.


"you must define a column name to autoincrement."

You tried to call 'autoincrement' without specifying a column name.

"class <class> already has one auto-incremented column"

You tried to call 'autoincrement' twice for the same table/class.

"parameter 'Min' of method 'autoincrement' must be a number."

You called the method 'autoincrement' with an invalid number beside the 'Min' attribute.

"no auto-incremented column has been specified for class <class>"

You most likely tried to call 'insert' without having first called 'autoincrement'.

"no database table has been specified for class <class>"

You most likely tried to call 'insert' without having first called the Class::DBI methods 'table'.

"BUG: 'require <class>' failed because of: ..."

Class::DBI::AutoIncrement failed to find the package <class> in @INC.

"Class::DBI::AutoIncrement expects class $caller to inherit from at least 1 more parent class"

A child class of Class::DBI::AutoIncrement must inherit from at least Class::DBI::AutoIncrement and an other class that subclasses Class::DBI (or Class::DBI itself).


Class::DBI::AutoIncrement silently modifies the class hierarchy of its children classes during runtime. You might get weird results if your code relies on a static class hierarchy.

If you are using caching, either let Class::DBI::AutoIncrement handle the computation of the next sequence index completly or do it all by yourself, but do not mix both ways or you will get weird results. Really.

Fetching the current highest value of the sequence from the database and inserting a new row is not done atomically. You will get race conditions if multiple threads are inserting into the same table.


Big thanks to David Westbrook for providing me with exemplar bug reports and usefull inspiration!


See Class::DBI.

See Class::DBI::AutoIncrement::Simple for a simpler alternative that should fit most of your needs.


Copyright (C) 2005 by Erwan Lemonnier <>

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


Because this software is licensed free of charge, there is no warranty for the software, to the extent permitted by applicable law. Except when otherwise stated in writing the copyright holders and/or other parties provide the software "as is" without warranty of any kind, either expressed or implied, including, but not limited to, the implied warranties of merchantability and fitness for a particular purpose. The entire risk as to the quality and performance of the software is with you. Should the software prove defective, you assume the cost of all necessary servicing, repair, or correction.

In no event unless required by applicable law or agreed to in writing will any copyright holder, or any other party who may modify and/or redistribute the software as permitted by the above licence, be liable to you for damages, including any general, special, incidental, or consequential damages arising out of the use or inability to use the software (including but not limited to loss of data or data being rendered inaccurate or losses sustained by you or third parties or a failure of the software to operate with any other software), even if such holder or other party has been advised of the possibility of such damages.