Perl DBI - Database connections with perl

What is Perl DBI? It is the Perl Database Interface which supports database programming with perl. As any good interface does, it provides a user facing set of methods and variables to make database connections and commands with. The interface hides the underlying details of the database being used, whether it be Oracle, MySQL, PostgreSQL, etc.

Perl DBI connect

How do I make a database connection in perl?

As described above, the DBI exposes methods for which you can make a database connection using perl. The method used to make the connection is simply, you guessed it, connect. The parameters required are the data source, username, and password.

For example, to connect to a database with perl using DBI, run the following command:

$dbh = DBI→connect($data_source, $username, $password) or die $DBI::errstr;

To disconnect from the database with DBI, run the following command:

$dbh→disconnect

Perl DBI install

How do I install DBI for Perl? The installation is rather complicated, in our humble opinion. There are many depending factors including the underlying OS (Operating System) it is installed on and the environment in which it is running. For the sake of this article, we will be using a Perl docker image and using an Oracle database.

Download an Oracle Instant Client from https://www.oracle.com.
Extract the Oracle client to /opt/instantclient_11_2 or the version of the instant client you are using.
Download the SDK (Software Development Kit) for the Oracle client you downloaded.
Extract the SDK to /opt/instantclient_11_2/sdk.
Download the DBD package to install. The Oracle download is located at https://metacpan.org/pod/DBD::Oracle.
Update your OS and make sure the following dependencies are installed:
1. libaio1
Install Bundle::DBI in with the following command: perl -MCPAN -e 'install Bundle::DBI'
Change to the directory of the previously extracted DBD package. In this case, /opt/DBD-Oracle-1.83
Run the following commands:
1. perl Makefile.PL -V 11.2.0
2. make install
Set the following environment variables:
1. ORACLE_HOME=/opt/instantclient_11_2/
2. LD_LIBRARY_PATH=/opt/instantclient_11_2/
3. C_INCLUDE_PATH=/opt/instantclient_11_2/sdk/include/

After following the previous instructions, you should have a working installation of DBI for perl. The installation can be challenging and you may run into different issues depending on which OS you are installing on. If you run across any issues in the process, feel free to leave us a comment below and we will attempt to help troubleshoot with you.

Perl DBI example

To query a database table using DBI, you must first prepare a select statement using the exposed prepare method in the interface. For example,

 $sth = $dbh→prepare("select * from table1");

Where $dbh is the connection made above in a previous section. A select statement may be executed in other ways than using prepare, but this is just one way. After creating the prepared statement, you must then execute that statement with:

$sth→execute();

After execution, assuming an array of rows was returned by your select statement, run the following command to fetch the rows:

$sth→fetchrow_array();

Perl DBI module

There is a DBI module in perl for each database type you are using, including Oracle, PostgreSQL, MySQL, and others. They can be found here: https://metacpan.org/search?q=DBD%3A%3A

Dockerfile for DBI with Perl

Finally, we have made it to the part that puts everything we’ve discussed together. We previously mentioned that installing and configuring DBI can be a complicated process, so doing it repeatedly on multiple servers or having to rebuild a server at a later date may cause us to have to relearn everything about DBI.

Creating your own Dockerfile to take care of the DBI installation will save you time and effort, and is self documenting. Your final version will likely be different than this one, but this will at least get you started.

The source can be found in our GitHub repo at: https://github.com/misterpki/docker-perl-dbi

FROM perl:latest

ENV ORACLE_HOME=/opt/instantclient_11_2/ENV LD_LIBRARY_PATH=/opt/instantclient_11_2/ENV C_INCLUDE_PATH=/opt/instantclient_11_2/sdk/include/
COPY dependencies/* /opt/
WORKDIR /opt
RUN apt-get update && \    
apt-get install -y libaio1 && \    
unzip instantclient-basic-linux.x64-11.2.0.4.0.zip && \    
unzip instantclient-sdk-linux.x64-11.2.0.4.0.zip -d /opt/instantclient_11_2 && \
mv /opt/instantclient_11_2/instantclient_11_2/sdk /opt/instantclient_11_2 && \    rm -r /opt/instantclient_11_2/instantclient_11_2/ && \    
tar -xzvf DBD-Oracle-1.83.tar.gz && \    
perl -MCPAN -e 'install Bundle::DBI' && \    
cd DBD-Oracle-1.83 && \    
perl Makefile.PL -V 11.2.0 && \    
make install

Conclusion

This post demonstrated how to use DBI to make database connections, queries, and other examples including installation. The Dockerfile provides a self documented way of installing DBI in perl. Leave a comment if you have any questions and check out our other articles.