I'm currently learning how to test database's in the PHPUnit framework, and ran across the problem that I don't want to connect to a real database in my tests. This because when I run the tests on another computer, this computer might not have the same database.
I implemented the \PHPUnit\DbUnit\TestCaseTrait trait and set up the following methods:
/**
* Returns the test database connection.
*
* @return \PHPUnit\DbUnit\Database\Connection
*/
protected function getConnection()
{
$pdo = new PDO('sqlite::memory:');
return $this->createDefaultDBConnection($pdo, ':memory:');
}
/**
* Returns the test dataset.
*
* @return \PHPUnit\DbUnit\DataSet\IDataSet
*/
protected function getDataSet()
{
return $this->createXMLDataSet(dirname(__FILE__) . '/test-dataset.xml');
}
The dataset file is present, and is found properly.
In my setUp method from my test, I set a variable in the object to a \PDO instance.
/**
* @var PDO $databaseServerConnection
*/
private $databaseServerConnection;
public function setUp()
{
$this->databaseServerConnection = $this->getConnection()->getConnection();
}
I expected I could now use that PDO connection with the data that came from the dataset file in the getDataSet() method.
For my own try, I tried comparing those with the following code:
# Specify the tables we want to have in our connection dataset
$tables = ['users'];
# Create the dataset in the connection with the tables
$dataset = $this->getConnection()->createDataSet($tables);
# Query all results from the user table in the connection
$queryTable = $this->getConnection()->createQueryTable(
'users', 'SELECT * FROM users'
);
# Get the raw table data from the dataset file
$expectedTable = $this->getDataSet()->getTable('users');
# Check if theyre equal
$this->assertTablesEqual($queryTable, $expectedTable);
When debugging this, I noticed that the $tables array variable inside the $dataset is just a empty. Here a var_dump of the $dataset variable.
class PHPUnit\DbUnit\Database\FilteredDataSet#18 (3) {
protected $tableNames =>
array(1) {
[0] =>
string(5) "users"
}
protected $tables =>
array(0) {
}
protected $databaseConnection =>
class PHPUnit\DbUnit\Database\DefaultConnection#16 (2) {
protected $connection =>
class PDO#15 (0) {
}
protected $metaData =>
class PHPUnit\DbUnit\Database\Metadata\Sqlite#17 (6) {
protected $columns =>
array(0) {
...
}
protected $keys =>
array(0) {
...
}
protected $truncateCommand =>
string(11) "DELETE FROM"
protected $pdo =>
class PDO#15 (0) {
...
}
protected $schema =>
string(8) ":memory:"
protected $schemaObjectQuoteChar =>
string(1) """
}
}
}
Also the $data array inside the $queryTable variable is null. Here a var_dump of he $queryTable variable.
class PHPUnit\DbUnit\DataSet\QueryTable#22 (6) {
protected $query =>
string(19) "SELECT * FROM users"
protected $databaseConnection =>
class PHPUnit\DbUnit\Database\DefaultConnection#20 (2) {
protected $connection =>
class PDO#19 (0) {
}
protected $metaData =>
class PHPUnit\DbUnit\Database\Metadata\Sqlite#21 (6) {
protected $columns =>
array(0) {
...
}
protected $keys =>
array(0) {
...
}
protected $truncateCommand =>
string(11) "DELETE FROM"
protected $pdo =>
class PDO#19 (0) {
...
}
protected $schema =>
string(8) ":memory:"
protected $schemaObjectQuoteChar =>
string(1) """
}
}
protected $tableName =>
string(5) "users"
protected $tableMetaData =>
NULL
protected $data =>
NULL
private $other =>
NULL
}
This while the $data array inside the $expectedTable variable is full of the data created in the dataset file.
class PHPUnit\DbUnit\DataSet\DefaultTable#30 (3) {
protected $tableMetaData =>
class PHPUnit\DbUnit\DataSet\DefaultTableMetadata#34 (3) {
protected $columns =>
array(3) {
[0] =>
string(2) "id"
[1] =>
string(4) "name"
[2] =>
string(5) "email"
}
protected $primaryKeys =>
array(0) {
}
protected $tableName =>
string(5) "users"
}
protected $data =>
array(4) {
[0] =>
array(3) {
'id' =>
string(1) "1"
'name' =>
string(3) "test1"
'email' =>
string(9) "[email protected]"
}
[1] =>
array(3) {
'id' =>
string(1) "2"
'name' =>
string(3) "test2"
'email' =>
string(9) "[email protected]"
}
[2] =>
array(3) {
'id' =>
string(1) "3"
'name' =>
string(6) "test3"
'email' =>
string(12) "[email protected]"
}
[3] =>
array(3) {
'id' =>
string(1) "4"
'name' =>
string(4) "test4"
'email' =>
string(10) "[email protected]"
}
}
private $other =>
NULL
}
I also tried to execute 2 queries on the pdo connection object inside the getConnection() method to create the table with values inside them:
protected function getConnection()
{
$pdo = new PDO('sqlite::memory:');
$pdo->exec("CREATE TABLE users (id PRIMARY KEY, name VARCHAR(50), email VARCHAR(50))");
$pdo->exec("INSERT INTO users (id, name, email) VALUES (20, 'Bas', 'aa@me')");
return $this->createDefaultDBConnection($pdo, ':memory:');
}
How does it come that my there isn't any of the data available in my connection and how can I import the data from the dataset file into here to make the test pass?
Also, is this a good practice for doing this?
As far as I see setUp() in you TestCase overrides \PHPUnit\DbUnit\TestCaseTrait's setUp() which contains logic responsible for setting up and tearing down.
If you need to have different setUp's in TestCases might be better idea to make your own base class as described in docs and extend your TestCase's from it, and call parent::setUp() from child TestCase
UPDATE:
The Database, tables, sequences, triggers and views have to be created before you run the test suite.
it is taken from here, and there are couple more helpful hints there.
Basically that means that all tables, columns, indices, constraints, and other stuff that db-related code under tests deals with and depends upon, should be in place before running the tests. Any content in that db before running will not matter, all tables that are in dataset for the TestCase will be truncated and filled with data from that dataset.
UPDATE 2: (disclaimer: following is my personal preference)
Usually access to db is implemented via some kind of gateways. And they are the last thing I implement (at least in package scope). That gives me an opportuninty to have real knowledge about data that needs to be stored in database when I start doing those gateways. So when starting to write some TestCase on some gateway I just go for the management tool (usually some GUI like phpMyAdmin) and create table (tables) and maybe columns that I think need to be present to store the data that gateway deals with. Then writing tests and running them maybe in between changing tables structure how it fits better.
With such an approach all the structure of db is created manually (not by testing code) and it grows together with the code that works on db. I find it convinient by several reasons.
First, that is simplier since I don't have to manage creating (or recreating) structure before each TestCase. Especially if I have several TestCases that work with same tables.
Second, I always have db structure that is suitable for tests to be passing. Any unsuitable changes in the stucture will be caught. Also I can always generate an sql export instructions for this proper structure to initiate the real life database with all needed tables, columns, indices, keys, etc.
Third, sometimes a look needs to be (or even must be) taken on database having only db-specific issues in mind. So I can always open current test db and cleanly see what it is made of.
Note on in-memory db. In this case structure needs to be created in code. And here could be two obvious options -- setting a db with specific structure for the whole test suite or for specific test case (or group of them). As for me I would do the first one based on the reasons stated above.
The simplest way to implement would be making a connection and creating structure in bootrstrap file. But I would invest some time and add a little bit of dynamics like this:
<?xml version="1.0" encoding="UTF-8" ?>
<phpunit>
<php>
<var name="MAKE_IN_MEMORY_DB" value="yes" />
</php>
</phpunit>
and:
abstract class AbstractDbTestCase extends \PHPUnit\DbUnit\TestCase
{
private $connection;
protected function getConnection() {
if($this->connection === NULL){
$this->connection = $this->createConnection();
}
return $this->connection;
}
private function createConnection(){
if($GLOBALS['MAKE_IN_MEMORY_DB'] == 'yes'){
return $this->createInMemory();
}
else {
return $this->createRealDbConnection();
}
}
private function createInMemory(){
// create connection and set up db;
}
private function createRealDbConnection(){
// create connection using some data from phpunit.xml
}
}
That would make tests more decoupled from the environment constraints -- only config adjustment would be needed to run the tests. Actually I would do even more and use a file with sql statements to load those inside createInMemory() (would need some extra work but I think it's worth).