CakePHP 3: Model Unit Test fails - "duplicate key value"

Question

I'm using Postgres (which I think is related to the problem), and CakePHP 3.

I have the following unit test to just check to make sure that a valid dataset can get saved by the model. When I run the following test, with a standard "bake'd" Model unit test, I get the error below.

I think this is the problem:

We are using fixtures to add some base data. This is the only place that I think might be causing a problem. To add credence to this, while the unit tests were running I ran the following command to get the next auto-incrementing id value and it returned 1, even though it returned the proper number in non-test DB. Select nextval(pg_get_serial_sequence('agencies', 'id')) as new_id;

Unit Test:

public function testValidationDefault()
{
    $agencyData = [
        'full_name' => 'Agency Full Name',
        'mode' => 'transit',
        'request_api_class' => 'Rest\Get\Json',
        'response_api_class' => 'NextBus\Generic',
        'realtime_url_pattern' => 'http://api.example.com',
        'routes' => '{"123": {"full_route": "123 Full Route", "route_color": "#123456"}}'
    ];

    $agency = $this->Agencies->newEntity($agencyData);
    $saved = $this->Agencies->save($agency);
    $this->assertInstanceOf('App\Model\Entity\Agency', $saved);
}

Error:

PDOException: SQLSTATE[23505]: Unique violation: 7 ERROR:  duplicate key value violates unique constraint "agencies_pkey"
DETAIL:  Key (id)=(1) already exists.

Things I've tried

• Copied that same code into a controller, and it successfully added the entity in the table.
• Adding an id of 200. Same error appears.

Update 1

The fixture for this does have the ID field set each record. Deleting them from the fixture does work, but it breaks other unit tests that rely on some relational data.

Answer 1

I don't like this solution, but adding the following before saving the entity does work.

$this->Agencies->deleteAll('1=1');

Answer 2

[UPDATE: My other answer is the real solution to this problem.! You don't have to do this anymore...]

Here is a less dirty workaround that doesn't require deleting all the records:

use Cake\Datasource\ConnectionManager;
...
$connection = ConnectionManager::get('test');
$results = $connection->execute('ALTER SEQUENCE <tablename>_id_seq RESTART WITH 999999');
//TEST WHICH INSERTS RECORD(s)...

It appears that the auto-incrementing doesn't get properly set/reset during the setUp() or tearDown()... so manually setting it to something really high (greater than the number of existing records) prevents the "duplicate key..." error.

The benefit of this hack (over deleteAll('1=1')) is that you can still subsequently run tests that reference existing DB data.

Answer 3

It might be a problem in your fixture definition. The Cake PHP documentation uses a _constraints field specifying that the id field is a primary key:

    '_constraints' => [
        'primary' => ['type' => 'primary', 'columns' => ['id']],
    ]