Setting up a Postgresql Database Adapter with ZF2

Setting up Database access with Zend Framework 2 can feel a lot more intimidating then it really is. I partially blame the confusing nature of complex documentation along with the lack of good code samples. I am hoping to help with the second part of this equation.

Assuming you are using an MVC structure and placing most of your code in the module structure, you may find that centralizing your database connections is not only essential but also helps in reducing code redundancy. Thankfully, once you understand some of the core features of the ZF2 structure, overcoming obstacles like dependency injection, adapter factories, and global database access can become much less daunting.

So lets dive right in!

The first step to global database domination is setting up the global configurations. In your project's config/autoload directory open or create the file global.php. As with most other ZF2 configuration files, you will be simply returning an array of settings. The first is the "db" configuration array which will look something like this:

'db' => array(
    'driver' => 'Pdo_Pgsql',
    'database' => 'uHackedMe',
    'username'=>'obscureFictitiousName',
    'password'=>'likeIWouldTellYou'
),

The "driver" parameter represents the database driver you plan on using. Official options include: Mysqli, Pgsql, Sqlsrv, Pdo_Mysql, Pdo_Sqlite, and Pdo_Pgsql. The other settings should be pretty self explanatory. For the security conscious among us, you can always implement this logic outside your version control system and include the file. In my case, I simply return include('../../mySecretDbConfigFile.php');

The next step is wiring up the dependency injection that will prevent you from having to manually step through all of the hoops every time you want to run a simple query. That should look something like this:

'service_manager' => array(
    'factories' => array(
        'Zend\Db\Adapter\Adapter' => 'Zend\Db\Adapter\AdapterServiceFactory',
    ),
),

Essentially, this is setting up a database adapter factory that will pull in it's depended resources based on the db configuration settings, and store it all in the service manager for your happy consumption later on. It is amazing how much these few lines of code help simplify the whole process!

Now depending on your desired level of data abstraction,  you can easily create a table model that extends Zend\Db\TableGateway\AbstractTableGateway or simply access the Adapter you created earlier directly in your model. For the first option, you will want to define a second factory to automate the injection of the Adapter into your table model. You can do this by taping into the getServiceConfig() method of your Module.php files like this:

public function getServiceConfig(){
    return array(
        'factories' => array(
            'YourModule\Model\YourTableModel' => function($serviceManager){
                return new YourTableModel($serviceManager->get('Zend\Db\Adapter\Adapter'));
            }
        )
    );
}

At this point, inside your table model file, you now have direct access to the database as easily as this:

public function getRecord($id) {
    $row = $this->select(array('id' => (int) $id))->current();
    if (!$row){
        return false;
    }
    return array(
        'id' => $row->id,
        'name' => $row->name,
        'created' => $row->created,
    );
}

Finally, wherever you are accessing your table model (inside a controller or even another model), just use the service manager to get YourModule\Model\YourTableModel and your data will await you!

$serviceManager = $this->getServiceLocator();
$yourTableModel = $serviceManager->get('YourModule\Model\YourTableModel');
$yourRecord = $yourTableModel->getRecord($recordId);

If creating table gateways is simply not your cup of tea, you can skip the whole AbstractTableGateway step and simply add this into your Model:

$serviceManager = $this->getServiceLocator();
$adapter = $serviceManager->get('Zend\Db\Adapter\Adapter');
$result = $adapter->query('SELECT * FROM `record` WHERE `id` = ?', array($recordId));

When you first start developing like this, it does seem like a whole lot of setup just to get a basic database connection established. But once you understand the process, tools like the serviceManager & pre-baked factory classes sure do make life so much easier!