DBI

Chapter 7 - Great CPAN Modules
by?Sam Tregar?
Apress ? 2002
Companion Web Site

The DBI module, by Tim Bunce, provides Perl with an object-oriented interface to virtually every relational database system under the sun (MySQL, PostgreSQL, Oracle, Sybase, and many more). DBI accomplishes this Olympian feat by relying on database driver (DBD) modules to interface with individual databases. For example, when you use DBI to talk to a PostgreSQL database, DBI loads the DBD::Pg^[10] module. If you want to connect to MySQL, the DBD::mysql^[11] module is loaded. Even CSV^[12] files can be accessed through the DBD::CSV^[13] module. There are DBD modules available for all of the commonly used databases, and many of the less common ones too.

Before DBI, uneven database connection libraries were the norm—each database had its own modules and they all worked (or didn't work) differently. Perl was struggling to be useful for serious database software development. DBI provided a great platform for database interface libraries, and turned Perl into a serious database application programming environment.

DBI defines two interfaces—a front-end interface for users and a back-end interface for DBD module programmers. Regardless of the underlying DBD module being used, the DBI front-end interface remains the same. The benefit of this system is that users can learn to use the DBI module and employ those skills to access any database with a DBD driver. Also, code written to the DBI interface can be easily ported between databases.^[14]

The DBI back-end interface for DBD creation is a rather complicated mix of composition and inheritance. Learning to create a DBD module is an arduous task, but that hasn't stopped the brigade of DBD developers from supporting nearly every database available today! This is an interesting point—front-end interfaces must be kept simple, but a back-end extension interface can be challenging and yet it will still be used if the module is successful enough. All DBD authors have to go through the difficult task of mastering the back-end interface, but once they do, their work immediately benefits from the elegance of the front- end interface. And their users are none the wiser.

If DBD modules can be viewed as back-end extensions, DBI also supports front-end extensions through the DBIx namespace. DBIx modules often work through composition. They proxy normal DBI methods to an internally held object and add new methods to perform common tasks. Other DBIx modules provide entirely new interfaces to DBI through tying.

DBI contains excellent documentation. The SYNOPSIS section shows an example of every available method call, all using a common convention in variable naming. This naming convention has become pervasive in DBI programming— virtually all database handles are called $dbh, statement handles $sth, and so on. This makes learning DBI and reading other programmers' DBI code easier.

The DBI user and developer community is perhaps one of the most active in all of Perldom. They maintain the [e-mail] mailing list where users and developers discuss problems and future development. See http://www.dbi.perl.org for more information.

The DBI and DBD modules have been responsible for turning Perl into the best platform for rapid database application development in existence today. They demonstrate the best results (if not the cleanest implementation) that modular and object-oriented programming has to offer. DBI was designed with reusability and extensibility as top priorities, and the results speak for themselves.

^[10]Written by Jeffrey Baker

^[11]Written by Jochen Wiedmann

^[12]Stands for Comma Separated Values, a text format commonly used to store tabular data

^[13]Written by Jeff Zucker

^[14]This would be much easier if databases all spoke the same dialect of SQL, but unfortunately that is not the case.