PHP

PHP Integration Testing with Real Databases

HelpMeTest

10 Aug 2026 — 3 min read

Unit tests with mocks are fast and isolated, but they don't catch the class of bugs that only appear when real SQL runs against a real schema — constraint violations, wrong joins, missing indexes, transaction rollbacks that don't behave as expected. Integration testing with real databases finds those bugs before production does.

This guide covers three approaches: Laravel's DatabaseMigrations trait with SQLite in-memory, raw PHPUnit with PDO and SQLite, and full MySQL containers via Docker Compose for production-parity testing.

Approach 1: Laravel DatabaseMigrations with SQLite

Laravel ships with first-class database testing support. The fastest setup uses SQLite in-memory — no external process, no cleanup, sub-millisecond teardown.

Configure config/database.php and set the testing connection in phpunit.xml:

<php>
    <env name="DB_CONNECTION" value="sqlite"/>
    <env name="DB_DATABASE" value=":memory:"/>
</php>

Then use the RefreshDatabase trait in your test class:

use Illuminate\Foundation\Testing\RefreshDatabase;
use Tests\TestCase;

class OrderRepositoryTest extends TestCase
{
    use RefreshDatabase;

    public function testCreateOrderPersistsCorrectly(): void
    {
        $user = User::factory()->create();
        $product = Product::factory()->create(['price' => 2999]);

        $order = Order::create([
            'user_id' => $user->id,
            'product_id' => $product->id,
            'quantity' => 3,
        ]);

        $this->assertDatabaseHas('orders', [
            'user_id' => $user->id,
            'product_id' => $product->id,
            'quantity' => 3,
        ]);

        $this->assertSame(8997, $order->fresh()->total_cents);
    }
}

RefreshDatabase runs all migrations before your first test and wraps each test in a transaction that rolls back at teardown — no leftover data between tests.

Use DatabaseMigrations instead of RefreshDatabase when you need the full migrate/rollback cycle (e.g., testing migrations themselves):

use Illuminate\Foundation\Testing\DatabaseMigrations;

class MigrationTest extends TestCase
{
    use DatabaseMigrations;

    public function testMigrationAddsIndexOnEmail(): void
    {
        $indexes = DB::select("PRAGMA index_list(users)");
        $indexNames = array_column($indexes, 'name');
        
        $this->assertContains('users_email_unique', $indexNames);
    }
}

Approach 2: Raw PHPUnit with SQLite In-Memory

Outside Laravel, you can set up a lightweight PDO-based test database yourself:

class ProductRepositoryTest extends \PHPUnit\Framework\TestCase
{
    private \PDO $pdo;
    private ProductRepository $repository;

    protected function setUp(): void
    {
        $this->pdo = new \PDO('sqlite::memory:');
        $this->pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);

        $this->pdo->exec('
            CREATE TABLE products (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                price_cents INTEGER NOT NULL,
                stock INTEGER NOT NULL DEFAULT 0
            )
        ');

        $this->repository = new ProductRepository($this->pdo);
    }

    public function testFindByPriceRangeReturnsCorrectResults(): void
    {
        $this->pdo->exec("
            INSERT INTO products (name, price_cents, stock) VALUES
            ('Cheap Widget', 999, 10),
            ('Mid Widget', 2999, 5),
            ('Expensive Widget', 9999, 2)
        ");

        $results = $this->repository->findByPriceRange(1000, 5000);

        $this->assertCount(1, $results);
        $this->assertSame('Mid Widget', $results[0]->name);
    }

    public function testFindByPriceRangeReturnsEmptyForNoMatch(): void
    {
        $results = $this->repository->findByPriceRange(50000, 100000);
        $this->assertEmpty($results);
    }
}

This approach works with any PHP project regardless of framework. Each test gets a fresh in-memory database via setUp() — no shared state, no cleanup needed.

Approach 3: MySQL Containers for Production Parity

SQLite is convenient but not always accurate — MySQL behavior differs on JSON columns, full-text search, stored procedures, and strict mode. For full confidence, run tests against a real MySQL container.

docker-compose.yml for testing:

version: '3.8'
services:
  mysql-test:
    image: mysql:8.0
    environment:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: testdb
    ports:
      - "3307:3306"
    tmpfs:
      - /var/lib/mysql   # store DB in RAM for speed
    healthcheck:
      test: ["CMD", "mysqladmin", "ping", "-h", "localhost"]
      interval: 5s
      timeout: 3s
      retries: 10

Using tmpfs for MySQL data makes the container roughly 3x faster and eliminates I/O bottlenecks. The tradeoff is data doesn't survive restarts — which is exactly what you want for test isolation.

phpunit.xml for MySQL:

<php>
    <env name="DB_CONNECTION" value="mysql"/>
    <env name="DB_HOST" value="127.0.0.1"/>
    <env name="DB_PORT" value="3307"/>
    <env name="DB_DATABASE" value="testdb"/>
    <env name="DB_USERNAME" value="root"/>
    <env name="DB_PASSWORD" value="root"/>
</php>

CI integration (GitHub Actions):

services:
  mysql:
    image: mysql:8.0
    env:
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: testdb
    ports:
      - 3307:3306
    options: >-
      --health-cmd="mysqladmin ping"
      --health-interval=5s
      --health-retries=10

steps:
  - name: Run integration tests
    run: php artisan test --testsuite=Integration

Organizing Integration Tests

Keep integration tests in a separate suite to avoid slowing down your unit test feedback loop:

<!-- phpunit.xml -->
<testsuites>
    <testsuite name="Unit">
        <directory>tests/Unit</directory>
    </testsuite>
    <testsuite name="Integration">
        <directory>tests/Integration</directory>
    </testsuite>
</testsuites>

Run unit tests on every save. Run integration tests on every push to CI. This gives you fast local feedback without sacrificing database-level confidence.

Testing Transactions and Rollbacks

One pattern integration testing catches that mocks never will — transactional integrity:

public function testFailedOrderRollsBackInventoryDeduction(): void
{
    $product = Product::factory()->create(['stock' => 5]);

    DB::beginTransaction();
    try {
        $product->decrement('stock', 3);
        throw new \RuntimeException('Payment failed');
        DB::commit();
    } catch (\RuntimeException $e) {
        DB::rollBack();
    }

    $this->assertSame(5, $product->fresh()->stock);
}

This test verifies your rollback logic actually works. A mock-based test would never catch a missing DB::rollBack() call.

For continuous confidence beyond local integration tests, HelpMeTest monitors your application 24/7 — catching database-related failures in production flows the moment they happen. Sign up free and keep your data layer honest around the clock.