Integrity Constraint Violation Errors in Feature Tests for Build A Voting App

@Tray2 Thanks! There are places where I am specifying specific statuses, but I think these are being done within a variable:

    public function filtering_works_when_query_string_in_place()
    {
        $statusOpen = Status::factory()->create(['name' => 'Open']);
        $statusConsidering = Status::factory()->create(['name' => 'Considering']);
        $statusInProgress = Status::factory()->create(['name' => 'In Progress']);
        $statusImplemented = Status::factory()->create(['name' => 'Implemented']);
        $statusClosed = Status::factory()->create(['name' => 'Closed']);

        Idea::factory()->create([
            'status_id' => $statusConsidering->id,
        ]);

        Idea::factory()->create([
            'status_id' => $statusConsidering->id,
        ]);

        Idea::factory()->create([
            'status_id' => $statusInProgress->id,
        ]);

        Idea::factory()->create([
            'status_id' => $statusInProgress->id,
        ]);

        Idea::factory()->create([
            'status_id' => $statusInProgress->id,
        ]);

        Livewire::withQueryParams(['status' => 'In Progress'])
            ->test(IdeasIndex::class)
            ->assertViewHas('ideas', function ($ideas) {
                return $ideas->count() === 3
                    && $ideas->first()->status->name === 'In Progress';
            });
    }

I went through all places where I am assigning some type of status in my tests, put them in a group, and then excluded that group from the testing, but I still end up with the same integrity constraint violation errors when I run the rest of the test suite.

@georgetown74 the transaction is rolled back in every test. So there probably isn’t any status in the table, or they have changed id. You need to recreate them for each test.

@Tray2 Thanks for your patience. I am still confused. Here's an example of a test that fails when I run it as part of the full test suite, but runs fine when I run it on its own:

    public function add_comment_form_works()
    {
        $user = User::factory()->create();
        $idea = Idea::factory()->create();

        Notification::fake();

        Notification::assertNothingSent();

        Livewire::actingAs($user)
            ->test(AddComment::class, [
                'idea' => $idea,
            ])
            ->set('comment', 'This is my first comment')
            ->call('addComment')
            ->assertEmitted('commentWasAdded');

        Notification::assertSentTo(
            [$idea->user],
            CommentAdded::class
        );

        $this->assertEquals(1, Comment::count());
        $this->assertEquals('This is my first comment', $idea->comments->first()->body);
    }

In my Idea factory it creates a status as part of creating the idea...

<?php

namespace Database\Factories;

use App\Models\Category;
use App\Models\Idea;
use App\Models\Status;
use App\Models\User;
use Illuminate\Database\Eloquent\Factories\Factory;

class IdeaFactory extends Factory
{
    /**
     * The name of the factory's corresponding model.
     *
     * @var string
     */
    protected $model = Idea::class;

    /**
     * Define the model's default state.
     *
     * @return array
     */
    public function definition()
    {
        return [
            'user_id' => User::factory(),
            'category_id' => Category::factory(),
            'status_id' => Status::factory(),
            'title' => ucwords($this->faker->words(4, true)),
            'description' => $this->faker->paragraph(5),
        ];
    }

    public function existing()
    {
        return $this->state(function (array $attributes) {
            return [
                'user_id' => $this->faker->numberBetween(1, 20),
                'category_id' => $this->faker->numberBetween(1, 4),
                'status_id' => $this->faker->numberBetween(1, 5),
            ];
        });
    }
}

... so what I am confused about is why does the test run successfully when I run it on its own but has an error when I run it with all the other tests?

Here's what I have for the Status factory:

<?php

namespace Database\Factories;

use App\Models\Status;
use Illuminate\Database\Eloquent\Factories\Factory;

/**
 * @extends \Illuminate\Database\Eloquent\Factories\Factory<\App\Models\Status>
 */
class StatusFactory extends Factory
{
    /**
     * Define the model's default state.
     *
     * @return array<string, mixed>
     */

    protected $model = Status::class;

    public function definition()
    {
        return [
            'name' => $this->faker->words(2, true),
        ];
    }
}

@georgetown74 My guess is that the status is pretty static, I have a similar table with media types. What I did to make it more consistent, is using a seeder in my tests.

<?php

namespace Database\Seeders;

use App\Models\MediaType;
use Illuminate\Database\Seeder;

class MediaTypeSeeder extends Seeder
{
    public function run(): void
    {
        MediaType::factory()
            ->count(4)
            ->sequence(
                ['name' => 'book'],
                ['name' => 'record'],
                ['name' => 'movie'],
                ['name' => 'game']
            )->create();
    }
}

And then I seed the table before each test

beforeEach(function () {
    $this->seed(MediaTypeSeeder::class);
    $this->mediaTypeId = MediaType::query()
        ->where('name', 'book')
        ->value('id');
});

And since the id can differ I then look for the name of the media type to get the id. That makes the id completely dynamic.

@Tray2 I will look into this. I am surprised because the tests where the error occurs only require that one status exist and, since I am using Refresh Database, the single status created by the factory should always have an ID value of 1. Thanks for your help!

@Tray2 I found one solution to this issue, which is to use sqlite as the DB_CONNECTION and :memory: for DB_DATABASE in my phpunit.xml file:

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

Once I made that change I had 0 errors when running the full test suite. BTW, this is what was suggested at 0:39 in the Episode 10 video https://laracasts.com/series/build-a-voting-app/episodes/10.

@georgetown74 Not necessarily since it uses transactions, and rolls back the changes. Not sure how and incrementing id works in MySQL though.

In Oracle the sequence would increase for each record inserted.

@georgetown74 Using SQLite is one option for testing, and it's fast, however I feel that using the same kind of RDBMS in test as I will in prod is a good idea. While SQLite is very good, it lacks some features that MySQL/MariaDB and PostgreSQL has.

@Tray2 Got it. It doesn't seem like simply switching to sqlite should be the solution since using use Refresh Database should clear out the data in the MySQL database tables before each test so I am not sure why it seems to work for sqlite but not MySQL. Thanks all your help with this.

And agreed, I would prefer to use MySQL for the tests as well since that is what will be used in production.

@georgetown74 It doesn't clear anything, it does a rollback.

The first time it runs, it migrates the database, and then it starts a transaction, performs the test, rolls back the data to its initial state, and then creates a new transaction and performs the next test.

I just did a test with this code in Tinkerwell and each time I run it the id increases.

use App\Models\Author;

DB::transaction(function () {
  $id = Author::factory() ->create();
  echo $id;
  DB::rollback();
});

{"first_name":"Ella","last_name":"Connelly","updated_at":"2022-11-29T17:36:25.000000Z","created_at":"2022-11-29T17:36:25.000000Z","id":507}

{"first_name":"Bennett","last_name":"Spinka","updated_at":"2022-11-29T17:38:00.000000Z","created_at":"2022-11-29T17:38:00.000000Z","id":508}

So that is why your tests fail when using MySQL.

@Tray2 Got it, thanks for that extra bit of detail. When I look here (https://laravel.com/docs/9.x/database-testing#resetting-the-database-after-each-test) it seems I could try using the Illuminate\Foundation\Testing\DatabaseMigrations trait instead (though it cautions that it is slower). I will try that later and see if that resolves it when using MySQL.

@georgetown74 You should use RefreshDatabase , just make sure that you fetch the id:s of any foreign key that you use in your test, and don't hard code any of them.

@Tray2 I switched back to MySQL and then actually experimented with the DatabaseMigrations trait and I got through all of my tests without any errors, so that does work, but it was significantly slower, as expected. If I revert back to using RefreshDatabase can you please confirm if it is sufficient to place it once at the top of each test file, or do I need to place it before each individual test within that file? For example, here is my CommentNotificationsTest.php file:

<?php

namespace Tests\Feature;

use App\Http\Livewire\AddComment;
use App\Http\Livewire\CommentNotifications;
use App\Models\Comment;
use App\Models\Idea;
use App\Models\User;
use Illuminate\Foundation\Testing\RefreshDatabase;
use Illuminate\Foundation\Testing\WithFaker;
use Illuminate\Notifications\DatabaseNotification;
use Livewire\Livewire;
use Tests\TestCase;

class CommentNotificationsTest extends TestCase
{
    use RefreshDatabase;

	/** @test */ 
    public function comment_notifications_livewire_component_renders_when_user_logged_in()
    {
        $user = User::factory()->create();

        $response = $this->actingAs($user)->get(route('idea.index'));

        $response->assertSeeLivewire('comment-notifications');
    }

	/** @test */ 
    public function comment_notifications_livewire_component_does_not_render_when_user_not_logged_in()
    {
        $response = $this->get(route('idea.index'));

        $response->assertDontSeeLivewire('comment-notifications');
    }

    /** @test
     * @group error
     */

    public function notifications_show_for_logged_in_user()
    {
        $user = User::factory()->create();
        $idea = Idea::factory()->create([
            'user_id' => $user->id,
        ]);

        $userACommenting = User::factory()->create();
        $userBCommenting = User::factory()->create();

        Livewire::actingAs($userACommenting)
            ->test(AddComment::class, [ 'idea' => $idea, ])
            ->set('comment', 'This is the first comment')
            ->call('addComment');

        Livewire::actingAs($userBCommenting)
            ->test(AddComment::class, [ 'idea' => $idea, ])
            ->set('comment', 'This is the second comment')
            ->call('addComment');

DatabaseNotification::first()->update([ 'created_at' => now()->subMinute() ]);

        Livewire::actingAs($user)
            ->test(CommentNotifications::class)
            ->call('getNotifications')
            ->assertSeeInOrder(['This is the second comment', 'This is the first comment'])
            ->assertSet('notificationCount', 2);
    }

    /** @test
     * @group error
     */
    public function notification_count_greater_than_threshold_shows_for_logged_in_user()
    {
        $user = User::factory()->create();
        $idea = Idea::factory()->create([
            'user_id' => $user->id,
        ]);

        $userACommenting = User::factory()->create();
        $threshold = CommentNotifications::NOTIFICATION_THRESHOLD;

        foreach (range(1, $threshold + 1) as $item) {
            Livewire::actingAs($userACommenting)
                ->test(AddComment::class, [ 'idea' => $idea, ])
                ->set('comment', 'This is the first comment')
                ->call('addComment');
        }

        Livewire::actingAs($user)
            ->test(CommentNotifications::class)
            ->call('getNotifications')
            ->assertSet('notificationCount', $threshold.'+')
            ->assertSee($threshold.'+');
    }

        /** @test
     * @group error
     */
    public function can_mark_all_notifications_as_read()
    {
        $user = User::factory()->create();
        $idea = Idea::factory()->create([
            'user_id' => $user->id,
        ]);

        $userACommenting = User::factory()->create();
        $userBCommenting = User::factory()->create();

        Livewire::actingAs($userACommenting)
            ->test(AddComment::class, [ 'idea' => $idea, ])
            ->set('comment', 'This is the first comment')
            ->call('addComment');

        Livewire::actingAs($userBCommenting)
            ->test(AddComment::class, [ 'idea' => $idea, ])
            ->set('comment', 'This is the second comment')
            ->call('addComment');

        Livewire::actingAs($user)
            ->test(CommentNotifications::class)
            ->call('getNotifications')
            ->call('markAllAsRead');

        $this->assertEquals(0, $user->fresh()->unreadNotifications->count());
    }

        /** @test
     * @group error
     */
    public function can_mark_individual_notification_as_read()
    {
        $user = User::factory()->create();
        $idea = Idea::factory()->create([
            'user_id' => $user->id,
        ]);

        $userACommenting = User::factory()->create();
        $userBCommenting = User::factory()->create();

        Livewire::actingAs($userACommenting)
            ->test(AddComment::class, [ 'idea' => $idea, ])
            ->set('comment', 'This is the first comment')
            ->call('addComment');

        Livewire::actingAs($userBCommenting)
            ->test(AddComment::class, [ 'idea' => $idea, ])
            ->set('comment', 'This is the second comment')
            ->call('addComment');

        Livewire::actingAs($user)
            ->test(CommentNotifications::class)
            ->call('getNotifications')
            ->call('markAsRead', DatabaseNotification::first()->id)
            ->assertRedirect(route('idea.show', [
                'idea' => $idea,
                'page' => 1,
            ]));

        $this->assertEquals(1, $user->fresh()->unreadNotifications->count());
    }

        /** @test
     * @group error
     */
    public function notification_idea_deleted_redirects_to_index_page()
    {
        $user = User::factory()->create();
        $idea = Idea::factory()->create([
            'user_id' => $user->id,
        ]);

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

        Livewire::actingAs($userACommenting)
            ->test(AddComment::class, [ 'idea' => $idea, ])
            ->set('comment', 'This is the first comment')
            ->call('addComment');

        $idea->delete();

        Livewire::actingAs($user)
            ->test(CommentNotifications::class)
            ->call('getNotifications')
            ->call('markAsRead', DatabaseNotification::first()->id)
            ->assertRedirect(route('idea.index'));
    }

        /** @test
     * @group error
     */
    public function notification_comment_deleted_redirects_to_index_page()
    {
        $user = User::factory()->create();
        $idea = Idea::factory()->create([
            'user_id' => $user->id,
        ]);

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

        Livewire::actingAs($userACommenting)
            ->test(AddComment::class, [ 'idea' => $idea, ])
            ->set('comment', 'This is the first comment')
            ->call('addComment');

        $idea->comments()->delete();

        Livewire::actingAs($user)
            ->test(CommentNotifications::class)
            ->call('getNotifications')
            ->call('markAsRead', DatabaseNotification::first()->id)
            ->assertRedirect(route('idea.index'));
    }
}

@georgetown74 You only need it once per test file.

@Tray2 Confirmed, thanks!