Testing Database Logic: What to Test, What to Skip, and Why It Matters

Database logic is one of the hardest parts of an application to test properly. Not because it is exotic or complex, but because it sits at the intersection of business rules, data consistency, performance, and evolution over time. In real projects, database tests are often either ignored completely or written in a way that makes the test suite slow, brittle, and painful to maintain.

In this article, I want to share a practical approach to testing database logic and migrations, based on real-world Laravel projects—not theory, not toy examples. The goal is simple: tests that give you confidence when refactoring, adding features, or deploying schema changes.

Previous article of this category: https://codecraftdiary.com/2025/12/13/testing-legacy-php-code-practical-strategies/

What “Database Logic” Really Means

When developers say “database logic,” they usually mean more than just CRUD operations. In practice, this includes:

• Model-level rules (computed fields, state transitions)
• Constraints enforced by the database (unique indexes, foreign keys)
• Side effects triggered by persistence (events, observers, jobs)
• Migrations that evolve schema safely over time
• Queries that encode business assumptions

Testing database logic is not about testing the database engine itself. It is about verifying that your application behaves correctly when real data is involved.

Choosing the Right Level of Testing

One of the most common mistakes is trying to test everything with unit tests. Pure unit tests are great, but they fall short when logic depends on the database.

In practice, I recommend splitting database-related tests into three categories:

1. Fast model and query tests (SQLite in memory or test database)
2. Integration tests for relationships and constraints
3. Migration tests focused on safety, not perfection

You do not need to test everything at every level. You need to test what can realistically break.

Setting Up a Reliable Test Database

A stable test setup is more important than the test code itself.

In Laravel, the default approach works well:

DB_CONNECTION=sqlite
DB_DATABASE=:memory:

This gives you fast feedback and clean isolation. However, be aware of one important limitation: SQLite behaves differently from MySQL/PostgreSQL, especially with foreign keys and JSON columns.

If your production logic depends heavily on database-specific behavior, consider running tests against the same engine using Docker or CI.

The key rule is consistency:
tests should fail for the same reasons in CI as in production.

Testing Models with Real Constraints

Let’s start with something simple but meaningful: enforcing uniqueness.

Imagine a users table where email must be unique.

Migration:

Schema::create('users', function (Blueprint $table) {
    $table->id();
    $table->string('email')->unique();
    $table->timestamps();
});

Instead of testing validation only, test the actual database behavior:

public function test_user_email_must_be_unique()
{
    User::factory()->create([
        'email' => '[e-mail]',
    ]);

    $this->expectException(QueryException::class);

    User::factory()->create([
        'email' => '[e-mail]',
    ]);
}

This test does not care how validation is implemented. It asserts a hard guarantee: the database will never allow duplicate emails.

These tests are cheap, fast, and extremely valuable during refactors.

Testing Relationships and Data Integrity

Relationships are another frequent source of subtle bugs.

Example: an Order must always belong to a User.

Schema::create('orders', function (Blueprint $table) {
    $table->id();
    $table->foreignId('user_id')->constrained()->cascadeOnDelete();
});

A practical test focuses on behavior, not structure:

public function test_orders_are_deleted_when_user_is_deleted()
{
    $user = User::factory()->create();
    $order = Order::factory()->create([
        'user_id' => $user->id,
    ]);

    $user->delete();

    $this->assertDatabaseMissing('orders', [
        'id' => $order->id,
    ]);
}

This test protects you against accidental changes to foreign keys or cascade rules—something that happens more often than people admit.

Avoiding Over-Mocking Database Behavior

A common anti-pattern is mocking Eloquent models or repositories for database logic. This usually leads to tests that pass while production breaks.

If logic depends on:

• database constraints
• transaction behavior
• actual persisted state

then do not mock it.

For example, testing a transactional operation:

DB::transaction(function () {
    $order->markAsPaid()
    $invoice->generate();
});

The correct test verifies the final state, not method calls:

public function test_order_is_paid_and_invoice_is_created()
{
    $order = Order::factory()->create();

    $service = new OrderPaymentService();
    $service->pay($order);

    $this->assertDatabaseHas('orders', [
        'id' => $order->id,
        'status' => 'paid',
    ]);

    $this->assertDatabaseHas('invoices', [
        'order_id' => $order->id,
    ]);
}

This kind of test survives refactoring far better than mocks.

Testing Migrations Without Overengineering

Migration tests are often skipped entirely, or tested in unrealistic ways. You do not need to test every column. You need to test risk.

Good candidates for migration tests:

• Data transformations
• Column renames
• Backfilled values
• Dropping or tightening constraints

Example: adding a non-null column with a default.

Migration:

Schema::table('users', function (Blueprint $table) {
    $table->boolean('is_active')->default(true);
});

Test:

public function test_existing_users_are_active_after_migration()
{
    $user = User::factory()->create([
        'is_active' => null,
    ]);

    $this->artisan('migrate');

    $user->refresh();

    $this->assertTrue($user->is_active);
}

This test protects against a very real production issue: broken deployments due to invalid existing data.

Keeping Tests Fast as the Project Grows

Database tests have a reputation for being slow. In most projects, this is not because of the database—it is because of test design.

A few pragmatic rules:

• Use factories with minimal defaults
• Avoid unnecessary seeding
• Reset the database using transactions when possible
• Do not test the same constraint in ten different tests

Speed is not just convenience. Slow tests get skipped, and skipped tests are worse than no tests.

What Not to Test

Equally important is knowing what not to test:

• Laravel’s internal Eloquent behavior
• Database engine implementation details
• Framework-provided migrations
• Simple getters/setters with no logic

Focus on business guarantees, not mechanical implementation.

Final Thoughts

Testing database logic and migrations is not about achieving 100% coverage. It is about reducing fear—fear of refactoring, fear of deployments, fear of touching old code.

Well-written database tests act as executable documentation. They tell future you (or your teammates) what must never break, even when the codebase evolves.

If there is one takeaway, it is this:

Test the database as a collaborator, not as an external dependency.

That mindset alone will significantly improve both your test suite and your confidence in the system.