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).
Illuminate\Database\QueryException
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 Database\Seeders;
use App\Enums\BusinessTypeSlugEnum;
use Illuminate\Database\Seeder;
use Illuminate\Support\Facades\DB;
use App\Tenant;
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. We have hundreds of test classes that rely on hard coded id's which we seed in the database.
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?
Because postgres and laravel cannot handle the primary key sequence updates when you hard code id's, there are 2 solutions.
For now we are using option 2 using a helper class
<?php
namespace App;
use Illuminate\Support\Facades\DB;
use Illuminate\Database\Eloquent\Model;
class PostgresHelper
{
/**
* Reset the primary key sequence for a table given a model.
*
* We extract the table name from the model.
*
* Because postgres handles primary key sequences differently than MySQL, we need to reset the sequence for a table
* when we insert a record with a specific ID. This is because the sequence will not automatically increment to the next id.
*
* If the table has no records, the sequence will be set to 1001.
* If the table has records, the sequence will be set to the max id so that the next id upon insertion will be the max id + 1.
*
* @param string Model $model - The model to get the table name from for the sequence reset.
* @return void
*/
public static function resetPrimaryKeySequenceForTable(Model $model)
{
$tableName = $model->getTable();
$sql = "select setval(pg_get_serial_sequence('nova.{$tableName}', 'id'), (select greatest(max(id),1001) from nova.{$tableName}));";
DB::statement($sql);
}
}
Here is an example of it being used in our test function to reset the sequence prior to inserting data without hard coded id's
public function testResolveWhenClientIsEcommerceDiscoveryShouldHaveAllCacAndLtvAppraisalServices(): void
{
$this->set_auth();
// Ensure the client's business type is e-comm.
$client = Client::findOrFail(SingleClientSeeder::CLIENT_ID);
$client->tier_id = TiersSeeder::TIER_ID;
$ecomm_business_type = BusinessTypeSeeder::ECOMM_ID;
$client->business_type_id = $ecomm_business_type;
$client->save();
PostgresHelper::resetPrimaryKeySequenceForTable(new Department());
// Create a Department and Service; these should be excluded from the Appraisal.
DepartmentFactory::createDepartmentWithServices('Test', 1);
// Create Cac and LTV Departments/Services.
[$cac_department, $ltv_department] = $this->createEcommAuditDepartments();
$cac_appraisal_services_ids = $cac_department->services()
->whereHas('modules', fn ($q) => $q->where('slug', 'appraisal'))
->pluck('id')
->toArray();
$ltv_appraisal_services_ids = $ltv_department->services()
->whereHas('modules', fn ($q) => $q->where('slug', 'appraisal'))
->pluck('id')
->toArray();
$args = [
'client_name' => SingleClientSeeder::NAME,
'tier_id' => TiersSeeder::TIER_ID,
'create_discovery' => 'yes'
];
$result = $this->resolve($args);
$audit = Audit::findOrFail($result->id);
$discovery = $audit->discovery;
$expected_department_ids = [$cac_department->id, $ltv_department->id];
$actual_department_ids = $discovery->departments->pluck('id')->toArray();
$expected_service_ids = array_merge($cac_appraisal_services_ids, $ltv_appraisal_services_ids);
$actual_service_ids = $discovery->services->pluck('id')->toArray();
// Assert that a Discovery was created with CAC and LTV Depts. and all their Services.
$this::assertEquals($expected_service_ids, $actual_service_ids);
$this::assertEquals($expected_department_ids, $actual_department_ids);
}