Testing & TDD

Testing Database Operations

32 min Lesson 18 of 35

Database Testing Fundamentals

Database testing is critical for applications that interact with persistent storage. Unlike unit tests that run in isolation, database tests verify that your application correctly reads, writes, updates, and deletes data. Well-designed database tests ensure data integrity, validate complex queries, and catch schema-related bugs early in development.

Key Principle: Database tests should be isolated, repeatable, and fast. Each test should start with a known state, execute operations, verify results, and clean up afterward. Tests should never depend on each other or leave behind data that affects subsequent tests.

Database Testing Strategies

There are several approaches to testing code that interacts with databases:

1. In-Memory Database

Use an in-memory database (like SQLite :memory:) for fast, isolated tests:

Pros: Extremely fast, no cleanup needed, perfect isolation
Cons: May have different SQL dialect than production, limited feature parity
Best for: Unit tests, CI/CD pipelines, quick feedback loops

2. Test Database

Use a separate database instance with the same engine as production:

Pros: Production-like behavior, full feature support, accurate query testing
Cons: Slower than in-memory, requires cleanup, needs infrastructure
Best for: Integration tests, complex queries, production validation

3. Database Transactions

Wrap each test in a transaction that rolls back after completion:

Pros: Fast cleanup, maintains consistency, no leftover data
Cons: Can't test transaction logic itself, some operations can't be rolled back
Best for: Most database tests, especially with Laravel/Rails

Laravel Database Testing

Laravel provides excellent tools for database testing with factories, seeders, and transaction management:

Database Migrations in Tests

<?php

namespace Tests\Feature;

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

class UserTest extends TestCase
{
    use RefreshDatabase; // Migrates and rolls back database

    public function test_user_can_be_created()
    {
        $user = User::create([
            'name' => 'John Doe',
            'email' => 'john@example.com',
            'password' => Hash::make('password')
        ]);

        $this->assertDatabaseHas('users', [
            'email' => 'john@example.com'
        ]);
    }
}

RefreshDatabase Trait: This trait migrates your database before each test and rolls back changes after. It's faster than fully migrating between tests because it uses transactions when possible. Use RefreshDatabase for most tests, and DatabaseMigrations only when you need full migrations (e.g., testing migrations themselves).

Database Factories

Factories generate test data with realistic, customizable attributes:

<?php

// database/factories/UserFactory.php
namespace Database\Factories;

use Illuminate\Database\Eloquent\Factories\Factory;

class UserFactory extends Factory
{
    public function definition()
    {
        return [
            'name' => fake()->name(),
            'email' => fake()->unique()->safeEmail(),
            'email_verified_at' => now(),
            'password' => Hash::make('password'),
            'remember_token' => Str::random(10),
        ];
    }

    public function unverified()
    {
        return $this->state(fn (array $attributes) => [
            'email_verified_at' => null,
        ]);
    }

    public function admin()
    {
        return $this->state(fn (array $attributes) => [
            'role' => 'admin',
        ]);
    }
}

// Using factories in tests
$user = User::factory()->create(); // Creates and saves to database
$unverified = User::factory()->unverified()->create();
$admin = User::factory()->admin()->create();

// Create multiple users
$users = User::factory()->count(10)->create();

// Make without saving
$user = User::factory()->make(); // In-memory only, not persisted

Factory Relationships

<?php

// database/factories/PostFactory.php
class PostFactory extends Factory
{
    public function definition()
    {
        return [
            'user_id' => User::factory(), // Creates related user
            'title' => fake()->sentence(),
            'content' => fake()->paragraphs(3, true),
            'published_at' => now(),
        ];
    }
}

// Creating related models
$post = Post::factory()->create(); // Creates post AND user

$user = User::factory()->create();
$post = Post::factory()->for($user)->create(); // Post for existing user

// Has many relationships
$user = User::factory()
    ->has(Post::factory()->count(5))
    ->create(); // User with 5 posts

// Many-to-many relationships
$post = Post::factory()
    ->hasAttached(Tag::factory()->count(3))
    ->create(); // Post with 3 tags

Database Assertions

Laravel provides powerful assertions for verifying database state:

Basic Database Assertions

<?php

// Assert record exists
$this->assertDatabaseHas('users', [
    'email' => 'john@example.com',
    'name' => 'John Doe'
]);

// Assert record doesn't exist
$this->assertDatabaseMissing('users', [
    'email' => 'deleted@example.com'
]);

// Assert record count
$this->assertDatabaseCount('posts', 10);

// Soft deletes
$this->assertSoftDeleted('posts', [
    'id' => $post->id
]);

// Not soft deleted
$this->assertNotSoftDeleted('posts', [
    'id' => $activePost->id
]);

// Model assertions
$this->assertModelExists($user);
$this->assertModelMissing($deletedUser);

Advanced Assertions

<?php

public function test_order_creates_invoice()
{
    $user = User::factory()->create();
    $order = Order::factory()->for($user)->create();

    // Multiple related assertions
    $this->assertDatabaseHas('orders', [
        'id' => $order->id,
        'user_id' => $user->id,
        'status' => 'pending'
    ]);

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

public function test_user_deletion_cascades()
{
    $user = User::factory()
        ->has(Post::factory()->count(3))
        ->create();

    $postIds = $user->posts->pluck('id');

    $user->delete();

    $this->assertModelMissing($user);

    foreach ($postIds as $id) {
        $this->assertDatabaseMissing('posts', ['id' => $id]);
    }
}

Database Seeders in Tests

Seeders populate the database with predefined data for tests:

<?php

// database/seeders/TestSeeder.php
namespace Database\Seeders;

class TestSeeder extends Seeder
{
    public function run()
    {
        // Create admin user
        User::factory()->admin()->create([
            'email' => 'admin@example.com'
        ]);

        // Create regular users with posts
        User::factory()
            ->count(10)
            ->has(Post::factory()->count(3))
            ->create();

        // Create categories
        Category::factory()->count(5)->create();
    }
}

// Using seeders in tests
class FeatureTest extends TestCase
{
    use RefreshDatabase;

    public function setUp(): void
    {
        parent::setUp();
        $this->seed(TestSeeder::class);
    }

    public function test_admin_can_delete_any_post()
    {
        $admin = User::where('email', 'admin@example.com')->first();
        $post = Post::first();

        $this->actingAs($admin)
            ->delete("/posts/{$post->id}")
            ->assertStatus(204);

        $this->assertModelMissing($post);
    }
}

Transaction Management

Transactions ensure tests don't leave behind data and run faster:

Automatic Transactions (Laravel)

<?php

use Illuminate\Foundation\Testing\RefreshDatabase;

class OrderTest extends TestCase
{
    use RefreshDatabase; // Automatically uses transactions

    public function test_order_calculates_total()
    {
        $order = Order::factory()->create();
        $order->items()->create(['price' => 10, 'quantity' => 2]);
        $order->items()->create(['price' => 15, 'quantity' => 1]);

        $this->assertEquals(35, $order->fresh()->total);

        // Transaction automatically rolls back after test
    }
}

Manual Transactions

<?php

use Illuminate\Support\Facades\DB;

public function test_payment_processing()
{
    DB::beginTransaction();

    try {
        $order = Order::factory()->create();
        $payment = $order->processPayment(100);

        $this->assertDatabaseHas('payments', [
            'order_id' => $order->id,
            'amount' => 100
        ]);

        DB::rollBack(); // Manual cleanup
    } catch (\Exception $e) {
        DB::rollBack();
        throw $e;
    }
}

Transaction Limitations: Some operations cannot be rolled back:

DDL statements (CREATE, ALTER, DROP tables)
Truncate operations in some databases
Operations that commit implicitly
External system calls (emails, API requests)

For these cases, use DatabaseMigrations trait instead or mock external dependencies.

JavaScript Database Testing

JavaScript applications often test databases using ORMs like Sequelize, Prisma, or TypeORM:

Prisma Testing Example

// tests/setup.ts
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

export async function resetDatabase() {
    await prisma.$transaction([
        prisma.post.deleteMany(),
        prisma.user.deleteMany(),
    ]);
}

export { prisma };

// tests/user.test.ts
import { prisma, resetDatabase } from './setup';

describe('User Model', () => {
    beforeEach(async () => {
        await resetDatabase();
    });

    afterAll(async () => {
        await prisma.$disconnect();
    });

    test('creates user with posts', async () => {
        const user = await prisma.user.create({
            data: {
                name: 'John Doe',
                email: 'john@example.com',
                posts: {
                    create: [
                        { title: 'First Post', content: 'Content 1' },
                        { title: 'Second Post', content: 'Content 2' }
                    ]
                }
            },
            include: { posts: true }
        });

        expect(user.posts).toHaveLength(2);

        const dbUser = await prisma.user.findUnique({
            where: { id: user.id },
            include: { posts: true }
        });

        expect(dbUser?.posts).toHaveLength(2);
    });

    test('deletes user cascades to posts', async () => {
        const user = await prisma.user.create({
            data: {
                name: 'Jane Doe',
                email: 'jane@example.com',
                posts: {
                    create: [{ title: 'Post', content: 'Content' }]
                }
            }
        });

        await prisma.user.delete({ where: { id: user.id } });

        const posts = await prisma.post.findMany({
            where: { userId: user.id }
        });

        expect(posts).toHaveLength(0);
    });
});

Sequelize Testing Example

// tests/models/user.test.js
const { sequelize, User, Post } = require('../../models');

describe('User Model', () => {
    beforeAll(async () => {
        await sequelize.sync({ force: true }); // Drop and recreate tables
    });

    beforeEach(async () => {
        await User.destroy({ where: {}, truncate: true, cascade: true });
    });

    afterAll(async () => {
        await sequelize.close();
    });

    test('creates user with validation', async () => {
        const user = await User.create({
            name: 'John Doe',
            email: 'john@example.com',
            password: 'password123'
        });

        expect(user.id).toBeDefined();
        expect(user.email).toBe('john@example.com');
    });

    test('validates email uniqueness', async () => {
        await User.create({
            name: 'John',
            email: 'john@example.com',
            password: 'password'
        });

        await expect(
            User.create({
                name: 'Jane',
                email: 'john@example.com',
                password: 'password'
            })
        ).rejects.toThrow();
    });

    test('has many posts', async () => {
        const user = await User.create({
            name: 'John',
            email: 'john@example.com',
            password: 'password'
        });

        await Post.bulkCreate([
            { userId: user.id, title: 'Post 1', content: 'Content 1' },
            { userId: user.id, title: 'Post 2', content: 'Content 2' }
        ]);

        const posts = await user.getPosts();
        expect(posts).toHaveLength(2);
    });
});

Testing Complex Queries

Test queries that involve joins, aggregations, and subqueries:

<?php

public function test_finds_top_authors_by_views()
{
    // Create users with posts
    $author1 = User::factory()->create();
    Post::factory()->for($author1)->create(['views' => 1000]);
    Post::factory()->for($author1)->create(['views' => 500]);

    $author2 = User::factory()->create();
    Post::factory()->for($author2)->create(['views' => 2000]);

    // Query: Top authors by total views
    $topAuthors = User::query()
        ->withCount('posts')
        ->withSum('posts', 'views')
        ->having('posts_sum_views', '>', 1000)
        ->orderByDesc('posts_sum_views')
        ->get();

    $this->assertCount(2, $topAuthors);
    $this->assertEquals($author2->id, $topAuthors->first()->id);
    $this->assertEquals(2000, $topAuthors->first()->posts_sum_views);
}

public function test_searches_posts_with_tags()
{
    $tag1 = Tag::factory()->create(['name' => 'laravel']);
    $tag2 = Tag::factory()->create(['name' => 'php']);

    $post1 = Post::factory()->create(['title' => 'Laravel Testing']);
    $post1->tags()->attach([$tag1->id, $tag2->id]);

    $post2 = Post::factory()->create(['title' => 'Vue.js Guide']);

    // Search posts by tag
    $laravelPosts = Post::whereHas('tags', function ($query) {
        $query->where('name', 'laravel');
    })->get();

    $this->assertCount(1, $laravelPosts);
    $this->assertEquals($post1->id, $laravelPosts->first()->id);
}

Best Practices for Database Testing

1. Isolate Each Test

Use transactions or migrations to ensure each test starts with a clean slate. Tests should never depend on data from previous tests.

2. Use Factories Over Hard-coded Data

Factories make tests more maintainable and realistic. Avoid hard-coding user data in every test.

3. Test Business Logic, Not ORM Features

Don't test that Eloquent can save records—that's Laravel's job. Test your application's queries, relationships, and data transformations.

4. Keep Tests Fast

Use in-memory databases for unit tests. Reserve full database tests for integration tests. Minimize factory usage to only necessary data.

5. Assert on Multiple Levels

Check both the model state and database state. Verify relationships, constraints, and side effects.

Practical Exercise

Exercise 1: Test an e-commerce order system

<?php

// Create factories and tests for:

// Order model with:
// - belongsTo User
// - hasMany OrderItem
// - total() method that sums item prices

// OrderItem model with:
// - belongsTo Order
// - belongsTo Product
// - subtotal property (price * quantity)

// Write tests for:
// 1. Creating an order with multiple items
// 2. Calculating correct order total
// 3. Updating item quantity updates total
// 4. Deleting order cascades to items
// 5. Order belongs to correct user

Exercise 2: Test a blog post publishing workflow

// Tests to write:

test('draft post is not visible to guests', () => {
    // Create draft post
    // Query published posts
    // Assert draft not included
});

test('publishing post sets published_at', () => {
    // Create draft
    // Call publish()
    // Assert published_at is set
    // Assert status is 'published'
});

test('author can see their drafts', () => {
    // Create author with drafts
    // Query author.posts()
    // Assert includes drafts
});

test('guest sees only published posts', () => {
    // Create mix of draft/published
    // Query Post::published()
    // Assert only published returned
});

Exercise 3: Test a tagging system

// Many-to-many: posts <-> tags

test('attaches tags to post', () => {
    // Create post and tags
    // Attach tags
    // Assert relationship exists
});

test('finds posts by tag', () => {
    // Create posts with different tags
    // Query by specific tag
    // Assert correct posts returned
});

test('detaching tag doesn\'t delete post', () => {
    // Create post with tag
    // Detach tag
    // Assert post still exists
    // Assert tag still exists
});