Behat and Databases

How about database driven tests? The traditional way to do this is to setup fixture data once and then test against it. But as time goes by, fixture data gets old and messed up. Wouldn’t it be nice to create the fixture data as part of your test? You can see exactly what is being created and test against it? No more trusting the data created by someone and quite possibly outdated. Boom! This article will show you how to achieve this;

We’ll be using the genesis/sql-api-wrapper (https://github.com/forceedge01/sql-api-wrapper) which has some awesome shiny tools that you’ll get for free along with a context file you can use out of the box. Here is a sample of what to expect:

...
Given I have a "User" fixture with the following data set:
  | email | example@abc.com |
  | name  | example name    |
And I have an "Address" fixture
...

The above will create a user, and then an address record that will be associated with the user all ready to go. You won’t need to manually set values on objects, it’ll be all handled automatically at the end.

Interested? Here we go.

Steps to cover:

1. Install genesis/sql-api-wrapper
2. Setup database connection
3. Create DataMod
4. Write steps in feature files and chill!

First require the package:

composer require --dev genesis/sql-api-wrapper

Next setup your behat.yml file:

# behat.yml
default:
    suites:
        default:
            contexts:
                - Genesis\SQLExtensionWrapper\DataModSQLContext
    extensions:
        Genesis\SQLExtensionWrapper\Extension:
            connection:
                engine: mysql # mssql, pgsql, sqlite
                host: localhost
                port: 1234
                dbname: mydb
                username: root
                password: root

Obviously you’ll have to set the details up yourself. Next up, create some DataMods.  A DataMod represents a table view. Here is an example User DataMod:

# features/bootstrap/DataMod/User.php
<?php

namespace DataMod;

use Genesis\SQLExtensionWrapper\BaseProvider;
use Genesis\SQLExtensionWrapper\DataModInterface;

class User extends BaseProvider implements DataModInterface
{
    /**
     * @return string
     */
    public static function getBaseTable()
    {
        return 'User';
    }

    /**
     * @return array
     */
    public static function getDataMapping()
    {
        return [
            'id' => 'id',
            'name' => 'full_name',
            'email' => 'email_address'
        ];
    }
}

At this stage, you can go ahead and run the following in your feature file:

Given I have a "User" fixture with the following data set:
  | name  | your name   |
  | email | abc@xyz.com |

Or any of the other step definitions that the `DataModSQLContext` provides. CONGRATULATIONS YOU CAN NOW CREATE FIXTURE USERS. But the fun doesn’t stop here – lets create an address DataMod and magically associate the User DataMod with it.

# features/bootstrap/DataMod/Address.php
<?php

namespace DataMod;

use Genesis\SQLExtensionWrapper\BaseProvider;
use Genesis\SQLExtensionWrapper\DataModInterface;

class Address extends BaseProvider implements DataModInterface
{
    /**
     * @return string
     */
    public static function getBaseTable()
    {
        return 'Address';
    }

    /**
     * @return array
     */
    public static function getDataMapping()
    {
        return [
            'id' => 'user_id',
            'user_id' => 'user_id',
            'address' => 'full_address'
        ];
    }

    /**
     * @param array $data
     *
     * @return array
     */
    public static function getDefaults(array $data = [])
    {
        return [
            'user_id' => User::getRequiredValue('id')
        ];
    }
}

The above Address DataMod is exactly the same except the getDefaults() method. This method receives your original $data array passed in so you can set some default values based on the original values or otherwise. In this case, we’re saying that the user_id field will be set to the value contained in the User object. The getRequiredValue() method makes sure that you can’t create an address object without having a User object created, and once you do – it will retrieve the value of the id automatically and assign it to the user_id. Simple, beautiful.

That’s it! Now you can do:

...
Given I have a "User" fixture with the following data set:
  | email | example@abc.com |
  | name  | example name    |
And I have an "Address" fixture
...

Magic. Explore more tools you can use with genesis/sql-api-wrapper.

ENJOY!