Testing database interactions is one of the most important parts of application testing. In Laravel, the database plays a central role in almost every feature—user authentication, orders, posts, products, payments, and much more. Ensuring that database operations work correctly is essential to building stable, predictable, and bug-free applications. Laravel provides powerful tools for testing database queries, insertions, updates, deletions, and model behavior using an in-memory SQLite database or a dedicated test database.

This extensive 3000-word guide will explain everything you need to know about testing database interactions in Laravel. You will learn how Laravel structures database tests, how to use testing traits like RefreshDatabase, how to make use of model factories, how to assert database state, how to test insertions and deletions, how to test relationships, how to fake transactions, how to test validation with the database, how to test migrations, how to use Pest or PHPUnit for database tests, how to isolate test cases, and best practices for building clean and reliable database test suites.

By the end of this post, you will be able to write professional-level database tests for any Laravel application.

Understanding Why Database Testing Is Important

Database interactions are often responsible for the most critical logic in your application. If the database layer is broken or unreliable, the entire system becomes unstable.

Database testing ensures that:

• records are inserted correctly
• fields are updated correctly
• records are deleted safely
• constraints work as expected
• relationships behave correctly
• queries return the expected results
• migrations create appropriate schemas
• validation prevents invalid data from being saved

Without database tests, bugs can silently corrupt data or return inconsistent results.

---

Laravel’s Built-In Tools for Database Testing

Laravel provides everything needed for database testing inside its testing suite. Tools include:

• RefreshDatabase
• DatabaseMigrations
• DatabaseTransactions
• model factories
• in-memory SQLite
• PHPUnit helpers
• Pest helpers
• database assertion methods

Laravel makes database testing easy, fast, and reliable.

---

Using RefreshDatabase Trait

The most commonly used trait for database testing is:

use RefreshDatabase;

This trait runs all migrations before each test and refreshes the database after each test.

Advantages:

• ensures a clean state before every test
• prevents leftover records interfering with tests
• runs quickly when using SQLite in-memory mode

Example:

class UserTest extends TestCase
{
use RefreshDatabase;
public function test_user_is_created()
{
    User::factory()->create(['email' => '[email protected]']);
    $this->assertDatabaseHas('users', [
        'email' => '[email protected]'
    ]);
}
}

---

Using SQLite In-Memory Database for Faster Testing

Laravel allows using an in-memory database for extremely fast tests.

Set the test environment in phpunit.xml:

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

In-memory databases run entirely in RAM:

• migrations run instantly
• inserts and queries execute quickly
• no persistent files are created

This speeds up large test suites dramatically.

---

Using DatabaseMigrations Trait

Another option is:

use DatabaseMigrations;

This runs the migrations only once per test class before tests start, then rolls them back after.

This can be faster for large databases.

---

Using DatabaseTransactions Trait

This trait wraps each test case inside a database transaction:

use DatabaseTransactions;

Every test rolls back all database changes automatically.

Advantages:

• extremely fast
• no need to re-run migrations

Limitation:

• does not work with SQLite in-memory databases
• does not work with multiple database connections

---

Asserting Database State

Laravel gives powerful testing helpers to inspect database records.

assertDatabaseHas

Checks if a record exists:

$this->assertDatabaseHas('users', [
'email' => '[email protected]'
]);

Example after creating user:

User::factory()->create(['email' => '[email protected]']);

---

assertDatabaseMissing

Checks that a record does not exist:

$this->assertDatabaseMissing('users', [
'email' => '[email protected]'
]);

---

assertDeleted

Checks that a model was deleted:

$user = User::factory()->create();

$user->delete();

$this->assertSoftDeleted($user);

For hard delete:

$this->assertDatabaseMissing('users', ['id' => $user->id]);

---

Testing Insert Operations

Database insert operations are frequently tested.

Example:

public function test_user_insertion()
{
User::factory()->create([
    'name' => 'John Doe'
]);
$this->assertDatabaseHas('users', [
    'name' => 'John Doe'
]);
}

You can also insert manually:

DB::table('users')->insert([
'name' => 'John Doe',
'email' => '[email protected]'
]);

Then assert:

$this->assertDatabaseHas('users', [
'email' => '[email protected]'
]);

---

Testing Update Operations

Updating records:

public function test_user_update()
{
$user = User::factory()->create();
$user->update(['name' => 'Updated Name']);
$this->assertDatabaseHas('users', [
    'id' => $user->id,
    'name' => 'Updated Name'
]);
}

You should also assert the old value is missing:

$this->assertDatabaseMissing('users', [
'name' => 'Old Name'
]);

---

Testing Delete Operations

Test hard delete:

public function test_user_delete()
{
$user = User::factory()->create();
$user->delete();
$this->assertDatabaseMissing('users', [
    'id' => $user->id
]);
}

Test soft delete:

$this->assertSoftDeleted($user);

---

Testing Soft Deletes

Soft deletes keep the record but fill the deleted_at field.

Check soft deletion:

$this->assertSoftDeleted('users', [
'id' => $user->id
]);

Restore record:

$user->restore();

Now assert:

$this->assertDatabaseHas('users', [
'id' => $user->id
]);

---

Testing Relationships

Testing relationships ensures Eloquent relations work correctly.

belongsTo relationship

public function test_post_belongs_to_user()
{
$user = User::factory()->create();
$post = Post::factory()->create(['user_id' => $user->id]);
$this->assertTrue($post->user->is($user));
}

---

hasMany relationship

public function test_user_has_many_posts()
{
$user = User::factory()->create();
Post::factory()->count(3)->create(['user_id' => $user->id]);
$this->assertCount(3, $user->posts);
}

---

belongsToMany relationship

public function test_role_assignment()
{
$role = Role::factory()->create();
$user = User::factory()->create();
$user->roles()->attach($role);
$this->assertDatabaseHas('role_user', [
    'role_id' => $role->id,
    'user_id' => $user->id
]);
}

---

Testing Model Factories

Factories make database tests easier.

Create a factory:

php artisan make:factory UserFactory

Factory example:

public function definition()
{
return [
    'name' => $this->faker->name,
    'email' => $this->faker->unique()->safeEmail
];
}

Use factory:

User::factory()->count(5)->create();

Factories produce reliable dummy data.

---

Testing Validation Against Database

Laravel validation often includes database rules.

Example: unique email validation.

Test:

public function test_email_must_be_unique()
{
User::factory()->create(['email' => '[email protected]']);
$response = $this->post('/register', [
    'email' => '[email protected]',
    'password' => 'password'
]);
$response->assertSessionHasErrors('email');
}

Database-backed validation ensures correctness.

---

Testing Authentication Against Database

Testing login:

public function test_user_login()
{
$user = User::factory()->create([
    'password' => bcrypt('secret')
]);
$response = $this->post('/login', [
    'email' => $user->email,
    'password' => 'secret'
]);
$response->assertRedirect('/home');
$this->assertAuthenticatedAs($user);
}

Database verifies user record.

---

Testing API Endpoints With Database

Example API test:

public function test_api_returns_users()
{
User::factory()->count(3)->create();
$response = $this->getJson('/api/users');
$response->assertStatus(200)
         ->assertJsonCount(3, 'data');
}

Testing ensures correct output.

---

Testing Pivot Tables

BelongsToMany relationships require pivot assertions.

$this->assertDatabaseHas('role_user', [
'user_id' => $user->id,
'role_id' => $role->id
]);

Detach:

$user->roles()->detach($role);

Assert missing:

$this->assertDatabaseMissing('role_user', [
'role_id' => $role->id
]);

---

Testing JSON Fields and Casting

Models often include JSON fields.

Example:

public function test_json_field_stored_properly()
{
$product = Product::factory()->create([
    'details' => ['color' => 'red', 'size' => 'M']
]);
$this->assertDatabaseHas('products', [
    'id' => $product->id
]);
}

---

Testing Database Transactions

Test that transaction rolls back:

DB::beginTransaction();

User::factory()->create();

DB::rollBack();

$this->assertDatabaseCount('users', 0);

---

Testing Seeders

Run seeder inside tests:

$this->seed(UserSeeder::class);

Verify:

$this->assertDatabaseHas('users', [
'email' => '[email protected]'
]);

---

Testing Migrations

You can test if columns exist:

$this->assertTrue(
Schema::hasColumn('users', 'email')
);

---

Testing Performance of Queries

You can inspect queries using DB::enableQueryLog()

DB::enableQueryLog();

User::all();

$log = DB::getQueryLog();

Assertions can verify query count.

---

Using Pest for Database Testing

Pest offers cleaner syntax.

Example:

uses(RefreshDatabase::class);

it('creates a user', function () {
User::factory()->create(['email' => '[email protected]']);
$this->assertDatabaseHas('users', ['email' => '[email protected]']);
});

Pest is expressive and modern.

---

Best Practices for Database Testing

• always use RefreshDatabase
• use SQLite in-memory for speed
• avoid testing Laravel internals
• isolate each test
• use factories for test data
• write descriptive test names
• test both success and failure cases
• test relationships
• test soft deletes
• test validation rules
• test edge cases like null values