CakePHP Tips Winter 2020

Over the last months, I collected some useful tips again for CakePHP apps and development you might find useful.

Data Integrity for your DB content

I usually also don’t take this into account when creating new table relations, even though I sure should.
Imagine a case where you add a new hasMany relation "pools" to existing records "users".
A user can be added to a single pool, each pool can have multiple users. In our case let’s add then a foreign key pool_id inside "users" table.
This way each user can only ever be assigned to one pool.
When you now add a pool and assign 2-3 users, those users will all have this foreign key populated.
If for some reason you now remove the pool, you sure don’t want to also remove the user. Now the users’ foreign keys keep those values, though. Those keys point now to dead/removed rows in the "pools" table.
This is especially a problem if you have logic that checks for NULL columns here to detect non-assigned users. They wouldn’t list those 3 users anymore despite not being properly assigned.

At this point, this is not a business logic issue anymore as much as it is data integrity.
For me business logic is maybe still if you want to remove related "hasMany" records (dependent true/false config) when removing a record (e.g. a deleted post "hasMany" comments that now become also removable).
But here we just need to make sure the "delete operation" of this belongsTo direction also is properly taken care of. And here the database’s constraints can help, they also do a way better and more performant job for larger datasets. Trying to get this into some "afterDelete" logic in all your Table classes could be more error-prone.

So how do we do this?

Check your current schema

Setup plugin contains a new useful CLI command to check your current schema for possible issues around nullable foreign key columns.
Run

bin/cake db_constraints check -v

And see if any warnings pop up.

Adjusting your schema

If so, you might want to add the missing ['delete' => 'SET_NULL'] constraints into your migrations as well as checking all existing rows here if that foreign key value might actually point to an already removed record as and such could distort your business logic.

$this->table('users')
    ->addForeignKey('pool_id', 'pools', ['id'], ['delete' => 'SET_NULL'])
    ->update();

This will now make sure your database is always matching your expectations in terms of queries properly finding what you want them to find.

Don’t forget to also run a check on all your records and set the foreign key to null if the matching record cannot be found.
Then you are good again here for all existing data.

Also note: Both integer columns must then be both either signed or unsigned ('signed' => false), or the additional will fail.

Further constraints

You could also do the opposite and block deleting on the DB level if any related record still exists (['delete' => 'RESTRICT']). But here often you can leverage the framework and have a softer "fail" than failing with exceptions. So depending on the usability you want to provide you have options here to either do this low level or inside your Table class and business logic.

Above, we talked about posts and comments.
If you want to remove dependent records always, you can move those to the DB level and make it also way faster:

$this->table('comments')
    ->addForeignKey('post_id', 'posts', ['id'], ['delete' => 'CASCADE'])
    ->update();

In case you have the application try the same thing in parallel, it should take precedence here. The app would first remove dependent records before removing the parent.
By that time the constraint should not be existing anymore, and it would just delete the main entry as if there was no cascading need.

Keep Fixtures in Sync with Tables

What happens quite often in application development: You modify tables using migrations (plugin) and you forget to also update the fixtures.
Your tests are still all green – as they don’t know about some new (possibly required) fields.
Once you deploy things break.
It is good to have some CI tooling, or at least basic local tools to make sure all is in sync and the tests reflect the actual database structure.

The TestHelper plugin offers a useful tool that can check this:

bin/cake fixture_check -v

It was basically an early spin-off from here as an improved version and shipped right away with other useful tools as per package principles. It contains a few more powerful add-ons in terms of comparisons.

Pro-tip:
Once in a while also hook up your live DB to this tool, to make sure not only your local (test) DB and the fixtures are in sync, but also the actual one in production usage.
Sometimes it can happen that an index, constraint or even field is slightly different or out of sync with your local DB.
Since these are read-only operations, this is safe to do so (through your app_local.php), as long as you switch it right back afterward.

More useful tooling for local development

The TestHelper plugin can quickly "bake" (generate) missing test classes for the respective src/ classes.
It lists the status of test coverage and you can easily see what’s missing.
One click and you got your baked test case to finalize.

The plugin also ships with a quick way to reverse a URL into the array or path element.
You copy and paste the URL /test-helper and you get e.g.

[
    'prefix' => null,
    'plugin' => 'TestHelper',
    'controller' => 'TestHelper',
    'action' => 'index',
]

Especially if you used a lot of custom routing, or want to quickly use this in a Html/Url helper call, this can be quite a time saver.

Since 4.0 it also supports path element building from such a URL ([Plugin].[Prefix]/[Controller]::[action]).
See docs for details.