Fast Laravel Database Testing Without In-Memory SQLite

The In-Memory SQLite Trap

Every developer eventually hits the same wall. Your test suite takes two minutes to run, so you swap your database driver in phpunit.xml to sqlite with :memory:. Suddenly, your suite finishes in eight seconds. You commit, push, and feel like a genius until your CI pipeline fails or production throws an error that your tests never caught.

In-memory SQLite is fast because it bypasses disk I/O completely and ignores half the rules your production database enforces. If you run PostgreSQL 16 or MySQL 8.0 in production, testing against SQLite is lying to yourself. They treat JSON columns, case sensitivity, foreign key checks, and raw SQL queries entirely differently.

Here is a real example. Suppose you have a query searching inside a PostgreSQL jsonb column using Postgres-specific search operators in Laravel 12 on PHP 8.3:

namespace App\Services;

use App\Models\User;
use Illuminate\Database\Eloquent\Collection;

class UserSearchService
{
    public function findByPreferences(string $key, string $value): Collection
    {
        return User::query()
            ->whereRaw("settings->'preferences'->>? = ?", [$key, $value])
            ->get();
    }
}

If you test this method against an in-memory SQLite database, SQLite throws a QueryException with a syntax error near the ->> operator. SQLite doesn't support PostgreSQL jsonb path operators natively. To make the test pass, you end up writing conditional logic inside your application code just for testing, which completely defeats the purpose of automated testing.

How RefreshDatabase Actually Works

Laravel provides two main traits for managing database state in tests: RefreshDatabase and DatabaseTransactions. Understanding what happens behind the scenes helps you pick the right tool without destroying your suite's execution speed.

When you use RefreshDatabase, Laravel performs a single migration run at the start of the test process. Once the database schema is ready, Laravel wraps every individual test method inside a database transaction. Before running a test, Laravel calls DB::beginTransaction(). When the test finishes, it executes DB::rollBack(). Your test data never gets written to disk permanently, keeping the database clean for the next test.

DatabaseTransactions works almost identically during test execution, but skips the initial schema creation check. If your database schema is already migrated, DatabaseTransactions boots slightly faster, but it assumes you manually keep your test database updated when migrations change.

Where Transactions Break Down

Transactions are fast, but they break down in specific scenarios:

• Multiple Connections: If your application dispatches an HTTP request or queue job that opens a separate PDO connection, that second connection can't see uncommitted data inside the primary test transaction.

• Truncate Statements: Implicit commits occur in MySQL when running TRUNCATE TABLE or ALTER TABLE. Executing DDL inside a test immediately commits the active transaction, leaking test data into subsequent tests.

• Nested Application Transactions: If your code calls DB::transaction() with custom savepoint handling or isolation levels, rolling back the outer test transaction can sometimes interact unexpectedly with PDO driver state.

Fixing Slow Migrations with Schema Dumps

If you have 150 migration files built up over years, running RefreshDatabase on a fresh test database can spend 6 to 10 seconds running migrations before a single test executes. On PHP 8.3 with Laravel 12, running 200 migrations sequentially takes roughly 800ms per worker start.

Instead of deleting old migration files, use schema dumping. Run this command in your terminal:

php artisan schema:dump --prune

Laravel squashes all existing migrations into a single SQL schema file located at database/schema/postgres-schema.sql (or mysql-schema.sql). When RefreshDatabase boots up, Laravel executes that single SQL file in about 40ms, skipping the parsing and execution of hundreds of individual PHP migration files.

Parallel Database Testing with Real Postgres

The absolute fastest way to run Laravel database tests without sacrificing production accuracy is combining php artisan test --parallel with a real database running on a RAM disk.

When you run parallel tests, Laravel uses ParaTest under the hood. It creates dedicated databases for each worker thread, named test_db_test_1, test_db_test_2, and so on. Every process maintains its own isolated transaction boundary.

Here is how to set up your phpunit.xml environment variables to ensure your tests run against real PostgreSQL:

<php>
    <env name="APP_ENV" value="testing"/>
    <env name="DB_CONNECTION" value="pgsql"/>
    <env name="DB_HOST" value="127.0.0.1"/>
    <env name="DB_PORT" value="5432"/>
    <env name="DB_DATABASE" value="testing"/>
    <env name="DB_USERNAME" value="postgres"/>
    <env name="DB_PASSWORD" value="secret"/>
</php>

Speeding Up Postgres with Shared Memory

PostgreSQL writes Write-Ahead Logging (WAL) files to disk to guarantee data durability. In a test environment, you don't care about crash recovery; you only care about execution speed. By running PostgreSQL data directories on Linux /dev/shm (shared memory tmpfs), all disk I/O occurs entirely in RAM.

If you use Docker Compose for local development, mount a tmpfs volume for PostgreSQL like this:

version: '3.8'
services:
  postgres:
    image: postgres:16-alpine
    environment:
      POSTGRES_DB: testing
      POSTGRES_PASSWORD: secret
    tmpfs:
      - /var/lib/postgresql/data:rw,noexec,nosuid,size=1024m
    ports:
      - "5432:5432"

When we converted our suite of 450 database tests from standard disk storage to Docker tmpfs with 4 parallel workers, execution time dropped from 38 seconds down to 3.2 seconds. That's faster than SQLite on disk, while maintaining 100% fidelity with production PostgreSQL 16 features like JSON operators, partial indexes, and UUID generation.

Which Approach Should You Pick?

Stop defaulting to in-memory SQLite for non-trivial applications. Here is my recommendation matrix for database testing in Laravel:

1. Use Postgres or MySQL on tmpfs with RefreshDatabase: This is the best setup for almost every application. You get exact database parity, full support for native JSON indexes, and sub-5-second execution times when running parallel tests.

2. Use DatabaseTransactions: Choose this when you have an existing persistent local database and don't want Laravel running schema migrations during every test run. Make sure your schema is synced before running tests.

3. Avoid SQLite :memory: Only use SQLite if your application is deployed to SQLite in production. Otherwise, the risk of false positives and syntax incompatibilities far outweighs the microsecond speed advantage.