Testing Slow Queries in PHPUnit

Published in on Mar 16, 2020

Have you ever wanted to test slow queries in your app?

I've regularly wanted to see what would happen if a query took longer than expected to completed. Does it break my app?

Wouldn't it be nice if databases had a sleep() function like PHP?

Well look no further:

Sleep Function

In this example, I'm using an "in-memory" SQLite database in my PHPUnit tests.

Config Settings

Inspiration

I originally built this for my Slow Query Notifier package, which notifies you of any slow queries in your Laravel application. I was forced to build this "sleep" functionality in order to test that the package actually worked based on dynamic thresholds as well as sensible defaults for query response length. It took a lot of source-diving the Laravel code base, as well as some help from the brilliant Caleb Porzio.

Let's break down what's happening in this code:

  1. Create the SQLite connection:
app()['config']->set('database.connections.'.$name, [
  'driver'   => 'sqlite',
  'database' => ':memory:',
  'prefix'   => '',
]);

Here I am dynamically creating a named connection in my Laravel config. This is similar to how you set up connections in your config/database.php file. Specifically, I am creating an "in memory" SQLite connection. However, you can use any driver that has a "create function" command. That includes MySQL and PostgreSQL which probably covers at least 90% of all PHP applications.

  1. Fetch the connection by name:
$connection = \DB::connection($name);

Here I simply use the DB facade to reach into the container and get my connection instance.

  1. Fetch the connection's PDO instance:
$pdo = $connection->getPdo();

I had to source-dive the Laravel codebase to find this, but you can see the function in Illuminate\Database\Connection.php. You can learn more generally about PDOs HERE.

  1. Create the sleep() function:
$pdo->sqliteCreateFunction(
  'sleep',
  function ($milliseconds) {
    return usleep($milliseconds * 1000);
  }
);

Here we call the sqliteCreateFunction() which accepts two parameters:

  • The function name
    • Here we chose "sleep" but it can be anything.
  • The function code
    • Our function accepts one parameter called "milliseconds"

Inside of the the function closure, you can run any PHP code you want. We are running usleep() here.

Interesting note: I originally used sleep() which accepts "seconds" instead of "microseconds". I would then allow users to pass in milliseconds and multiplied by 1000, but unfortunately, the PHP Sleep function doesn't allow decimals (or partial seconds). So if you passed in less than 1,000 milliseconds, it would return sleep(0). Bummer, but oh well.

  1. Use the new function:
$connection->select(\DB::raw("SELECT sleep(5000)"));

Use the DB facade again to access the raw() function. This allows you to pass in any SQL code you'd like. We can just use our new sleep() function as though it's a native database function. Pretty cool.

In practice, I usually return the connection from a function with a really specific name. Something like this:

    $connection = app(Example::class)->getTemporaryConnectionWithSleepFunction();
    $result = $connection->select(\DB::raw("SELECT sleep($sleep)"));

I hope this comes in handy for you. Please let me know if you think of any other cool ways to use PHP functions in your database connections.

Onboard to my newsletter if you like the articles. It makes me feel good...😉