In the process of migrating from MySQL to Postgres we are running into this issue with our Laravel PHP Unit tests.

We use seeders, factories, or models in our setUp() or seeding functions to insert data into our db to test against.

We are using the RefreshDatabase trait in a super class for all of our tests.

Then within individual test functions we are creating more database records using the same methods above.

When the test functions run we get this error (the message varies slightly with the entities being referenced based on the current function we are testing).

IlluminateDatabaseQueryException 

SQLSTATE[23505]: Unique violation: 7 ERROR:  duplicate key value violates unique constraint "business_types_pkey"
DETAIL:  Key (id)=(1001) already exists. (SQL: insert into "business_types" ("tenant_id", "name", "slug", "updated_at", "created_at") values (1001, Business Type with Empty Slug, , 2024-05-16 13:20:47, 2024-05-16 13:20:47) returning "id")

Here is the seeder we are using for the BusinessTypes entity

<?php

namespace DatabaseSeeders;

use AppEnumsBusinessTypeSlugEnum;
use IlluminateDatabaseSeeder;
use IlluminateSupportFacadesDB;
use AppTenant;

class BusinessTypeSeeder extends Seeder
{

    public const B2C_ID = 1001;
    public const ECOMM_ID = 1002;
    public const B2B_ID = 1003;

    public const BUSINESS_TYPES = [
        self::B2C_ID => [
            'name' => 'B2C',
            'slug' => BusinessTypeSlugEnum::B2C->value
        ],
        self::ECOMM_ID => [
            'name' => 'eComm',
            'slug' => BusinessTypeSlugEnum::Ecomm->value
        ],
        self::B2B_ID => [
            'name' => 'B2B',
            'slug' => BusinessTypeSlugEnum::B2B->value
        ]
    ];

    /**
     * Run the database seeds.
     *
     * @return void
     */
    public function run()
    {
        foreach (self::BUSINESS_TYPES as $id => $columns) {
            DB::table('business_types')->insert([
                'id' => $id,
                'name' => $columns['name'],
                'slug' => $columns['slug'],
                'tenant_id' => Tenant::firstOrFail()->id
            ]);
        }
    }
}

Here is the test function that is failing

    public function testResolveWhenBusinessTypeHasEmptySlugDiscoveryShouldNotHaveAnyServices(): void
    {
        $this->set_auth();

        // Set the client's business type to one with an empty slug.
        $business_type = BusinessType::create([
            'tenant_id' => Tenant::firstOrFail()->id,
            'name' => 'Business Type with Empty Slug',
            'slug' => ''
        ]);
        $client = Client::findOrFail(SingleClientSeeder::CLIENT_ID);
        $client->business_type_id = $business_type->id;
        $client->save();

        $args = [
            'client_name' => SingleClientSeeder::NAME,
            'tier_id' => TiersSeeder::TIER_ID,
            'create_discovery' => 'yes'
        ];
        $audit = $this->resolve($args);
        $discovery = $audit->discovery;

        // Assert that a Discovery was created with no Departments/Services.
        $this->assertExactlyOneNotSoftDeletedModelInTable($discovery);
        $this::assertEmpty($discovery->departments);
        $this::assertEmpty($discovery->services);
        $this->assertDatabaseCount('discovery_department', 0);
        $this->assertDatabaseCount('discovery_service', 0);
    }

I believe it’s failing when we create the new BusinessType using the ::create() function.

Removing the specific id property from the seeder appears to fix that problem.

However this is causing us to refactor every single seeder and test to make sure we aren’t using hard coded id’s which is a very large effort.

Is there an easier way to resolve this issue?

Seems like it’s related to how postgres handles sequences? I’m not sure why harcoding the id’s during record creation doesn’t increment the sequence.

Is this a laravel issue?