DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

The software you build is only as secure as the code that powers it. Learn how malicious code creeps into your software supply chain.

Apache Cassandra combines the benefits of major NoSQL databases to support data management needs not covered by traditional RDBMS vendors.

Generative AI has transformed nearly every industry. How can you leverage GenAI to improve your productivity and efficiency?

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Related

  • Micronaut With Relation Database and...Tests
  • Designing a Blog Application Using Document Databases
  • Relational DB Migration to S3 Data Lake Via AWS DMS, Part I
  • NoSQL for Relational Minds

Trending

  • Metrics at a Glance for Production Clusters
  • Data Quality: A Novel Perspective for 2025
  • Endpoint Security Controls: Designing a Secure Endpoint Architecture, Part 2
  • MySQL to PostgreSQL Database Migration: A Practical Case Study
  1. DZone
  2. Data Engineering
  3. Databases
  4. Practical PHP Testing Patterns: Stored Procedure Test

Practical PHP Testing Patterns: Stored Procedure Test

By 
Giorgio Sironi user avatar
Giorgio Sironi
·
May. 04, 11 · Interview
Likes (0)
Comment
Save
Tweet
Share
2.6K Views

Join the DZone community and get the full member experience.

Join For Free

It happened in the day before the advent of DDD and the Hexagonal architecture, that you had code that lived inside the database, such as Stored Procedures, constraints, and triggers.
Back in the day, the relational database was considered the single source of truth instead of a Domain Model written in a language like PHP or Java. Today the picture is different - but there are still scenarios where pushing code in the database make sense.

One of the reasons for having logic expressed as SQL and in other database languages is their power, and their performance. SQL operators, especially when augmented by proprietary extensions, let you declare pieces of logic that you would instead have to code by hand.

SQL that is executed directly on the database can accomplish operations too onerous to perform over a reconstituted object graph with a subsequent saving. In fact, every decent ORM include a language for batch updates that translates to SQL, like Doctrine with DQL; and also a mechanism for providing hints for the underlying database, like indexes definitions.

The problem with SQL derivates and other database-specific embedded logic is that we cannot execute it and test it in isolation - we need a real copy of the database to perform our tests. Thus the Stored Procedure Test is an umbrella term for tests that encompasses database code, even when they're not actually stored procedures. When I'll use the term stored procedure in this article, it will be to signify any database-specific code, such as complex queries, triggers and so on.

Implementation

The pattern prescribes to write unit tests for the stored procedure, to test it in isolation from the rest of application a first simplification. These tests cover nontrivial logic in database code - probably you don't need them for indexes definition, but more for queries with aggregate functions.

In the PHP world, Sqlite often suffices for testing queries - as long as you have an intermediate layer like Doctrine DBAL (part of Doctrine 2) which smooths out the differences between vendors. You use MySQL in production, Sqlite in the test suite, and you can write queries in Doctrine's DQL being confident that it will translate them to the right SQL dialect.

These tests should be executed in a sandbox - a database with just enough structure and data to test the stored procedure at hand. This sandbox should run by definition on the production dbms. The most difficult aspect of the pattern is integrating with the dbms: it should be running and listening on the right port. A sandbox should be created in setUp() or setUpBeforeClass(), and destroyed during teardown. In case the database is not available, the tests should be marked as skipped or incomplete.

Variations

In In-Database Stored Procedure Tests, the test is written in the same language as the database code. I cannot imagine something more boring for a PHP programmer.

In Remoted Stored Procedure Tests, which is the variation of interest for us, the tests are written in PHPUnit and integrated with the suite (slowing it down a bit).

The logic is that whatever SQL logic you're going to add to your application, is already encapsulated in some PHP class: for example, complex queries are encapsulated in Repositories or DAO. So it's going to be feasible to build a sandbox via PHP code, and test the stored procedure as a black box. It will be encapsulated for a unique execution, like schema creation, or for executing multiple times in case of queries.

Example

The example shows you how to test a query with a real database - supposing a surrogate database does not support all the needed functions - from inside a test suite. I thought it would be difficult to write this test, but instead it required less than a Pomodoro.

<?php
/**
 * This is a kind of integration test: we mock out the database from every 
 * other test, but still we have to hit it in one place to ensure our Gateway
 * (in this case a class containing queries) work.
 */
class StoredProcedureTest extends PHPUnit_Framework_TestCase
{
    private $connection;
    private $repository;

    /**
     * I'm using my local instance of MySQL: the only requirement is the 
     * presence of a database named 'sandbox'. You may want to parametrize 
     * everything here from database name to user and password.
     */
    public function setUp()
    {
        $this->connection = new PDO("mysql:host=localhost;dbname=sandbox", 'root', '');
        $this->connection->exec("CREATE TABLE users (name VARCHAR(255) NOT NULL PRIMARY KEY, year YEAR)");
        $this->repository = new UserRepository($this->connection, 2011);
    }

    public function testAverageAgeIsCalculated()
    {
        $this->insertUser('Giorgio', 1942);
        $this->insertUser('Isaac', 1920);
        $this->assertEquals(80, $this->repository->getAverageAge());
    }

    private function insertUser($name, $year)
    {
        $stmt = $this->connection->prepare("INSERT INTO users (name, year) VALUES (:name, :year)");
        $stmt->bindValue('name', $name, PDO::PARAM_STR);
        $stmt->bindValue('year', $year, PDO::PARAM_INT);
        return $stmt->execute();
    }

    public function tearDown()
    {
        $this->connection->exec('DROP TABLE users');
    }
}

class UserRepository
{
    private $connection;
    private $currentYear;

    public function __construct(PDO $connection, $currentYear)
    {
        $this->connection = $connection;
        $this->currentYear = $currentYear;
    }

    /**
     * We suppose AVG() cannot be correctly implemented by Sqlite or
     * another surrogate database (substitute another vendor feature
     * for the same effect). 
     * We also suppose reconstituting millions of User objects to calculate
     * their average age isn't feasible: that's why we used SQL directly.
     */
    public function getAverageAge()
    {
        $stmt = $this->connection->prepare('SELECT AVG(:year - year) AS average_age FROM users');
        $stmt->bindValue('year', $this->currentYear, PDO::PARAM_INT);
        $stmt->execute();
        $row = $stmt->fetch();
        return $row['average_age'];
    }
}
unit test PHP Database Relational database

Opinions expressed by DZone contributors are their own.

Related

  • Micronaut With Relation Database and...Tests
  • Designing a Blog Application Using Document Databases
  • Relational DB Migration to S3 Data Lake Via AWS DMS, Part I
  • NoSQL for Relational Minds

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!