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

Source: Dev.to

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 the 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.

Test‑type breakdown

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.

I recommend splitting database‑related tests into three categories:

1. Fast model and query tests – SQLite in‑memory or a dedicated 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. A stable test setup is more important than the test code itself.

Laravel’s default test database

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.

Key rule: tests should fail for the same reasons in CI as in production.

1️⃣ Enforcing uniqueness

Migration

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

Test (focus on the database, not validation)

public function test_user_email_must_be_unique()
{
    User::factory()->create([
        'email' => 'test@example.com',
    ]);

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

    User::factory()->create([
        'email' => 'test@example.com',
    ]);
}

Why this matters: the test asserts a hard guarantee—the database will never allow duplicate emails—regardless of how validation is implemented. These tests are cheap, fast, and extremely valuable during refactors.

2️⃣ Relationships and cascade rules

Migration

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

Test (behaviour‑focused)

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,
    ]);
}

Why this matters: it protects you against accidental changes to foreign keys or cascade rules—something that happens more often than people admit.

3️⃣ Avoiding the mocking anti‑pattern

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 behaviour
• actual persisted state

…then do not mock it.

Example: testing a transactional operation

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

Correct test (verifies final state)

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.

4️⃣ Migration tests – testing the risk, not every column

Migration tests are often skipped or tested unrealistically. You don’t need to test every column; you need to test the 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);
}

Why this matters: it protects against a very real production issue—broken deployments due to invalid existing data.

5️⃣ Speeding up database tests

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

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 ignored.

TL;DR

By structuring your database test suite this way, you get fast feedback, high confidence, and maintainable code—exactly what you need when working with Laravel’s powerful Eloquent ORM. Happy testing!

Testing Philosophy: What Not to Test

Skipped, and skipped tests are worse than no tests.

What to Avoid Testing

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

What to Focus On

• Business guarantees, not mechanical implementation.

Testing database logic and migrations isn’t about achieving 100 % coverage; it’s 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 as the codebase evolves.

Takeaway

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

Adopting this mindset will significantly improve both your test suite and your confidence in the system.