CakeDC Blog

TIPS, INSIGHTS AND THE LATEST FROM THE EXPERTS BEHIND CAKEPHP

Using Postgres as default database- hints, tricks and tips

Written by Yevgeny on September 07, 2020
8906 VIEWS

SQL language for different databases has some differences, which could cause problems after migrations between these databases. Here, we collected some hints, problems could appear during migration from MySQL to PostgreSQL. We focus on the CakePHP model layer and query generation layer.

 

Tables join in where expression

Often we want to join two tables using a condition like $query->where([‘Author.id = Article.author_id’]) which works fine till we dont need field alias quotes. In the case of migration to postgres, we might want to enable autoQuotes. In this case, we can use $query->newExpr()->equalFields(‘Author.id, Article.author_id’). 

 

Case sensitivity in like expressions

By default mysql does case insensitive search. Switching to postgres, you can note that some functionality works differently. Hopefully you have tests, which covers your code, and this will be detected during migration. Postgres uses custom syntax for such queries named ILIKE.The case of old style conditions where method arrays straight forward,  you’d just go with ILIKE instead of LIKE. But what if we want to use ILIKE in builder methods... Here is that example:

    return $query->where(

        function (QueryExpression $exp) use ($field, $value): QueryExpression {

            return $exp->add(new \Cake\Database\Expression\Comparison($field, $value, 'string', 'ILIKE'));

        }); 

 

Type casing

As postgres is much more strict with types, type casing is not a rare operation and may be needed.

Here is an example of how to perform it using FunctionExpression: $expr = (new FunctionExpression('CAST'))->setConjunction(' AS ')->add([$id, 'varchar' => 'literal']); which generates expression like :id AS varchar there :id is the placeholder for variable $id. This trick, used with literal, allows you to cast to any postgres type.

 

 Quotes of tables and fields

Sometimes it is critical to inform CakePHP that the field should be quoted. One recommendation is to avoid using plain strings in case of table joins, or using IS NULL as string. So if array syntax is used, all CakePHP conventions must be followed. However,  sometimes we should help the ORM and obviously wrap a field name with IdentifiedExpression. Let's take a look back to the previous example, but now we want to type cast not value, but a field. The only solution to quote field name correctly is using this code:

    $id = new IdentifierExpression($this->aliasField($field));

    $expr = (new FunctionExpression('CAST'))->setConjunction(' AS ')->add([$id, 'varchar' => 'literal']); 

 

Building complex arithmetic expressions

In case we want to generate expressions in query fields, and we don’t want to overcomplicate logic, we could use these next tricks. Here, I have created ListExpression, which could be used as a collection of expressions. Each of these are corrected, and generates a query with correct handling of each element. See: https://gist.github.com/skie/f6e4f1a1b61e0f902a507f7907c3bbf2

So, say we want to generate expressions like this: “Events”.”time_to” - “Events”.”time_from”...

With ListExpression, it can be done quite easy: 

$diff = new ListExpression([new IdentifierExpression('Events.time_to'), '-', new IdentifierExpression('Events.time_from')]);

 

Hopefully these tricks will be as useful for your baking as they have been for mine! 

Latest articles

15 Views

The new CakePHP RateLimitMiddleware

This article is part of the CakeDC Advent Calendar 2025 (December 21st 2025) Rate limiting a specific endpoint of your application can be a life saver. Sometimes you can't optimize the endpoint and it'll be expensive in time or CPU, or the endpoint has a business restriction for a given user. In the past, I've been using https://github.com/UseMuffin/Throttle a number of times to provide rate limiting features to CakePHP. Recently, I've been watching the addition of the RateLimitMiddleware to CakePHP 5.3, I think it was a great idea to incorporate these features into the core and I'll bring you a quick example about how to use it in your projects. Let's imagine you have a CakePHP application with an export feature that will take some extra CPU to produce an output, you want to ensure the endpoint is not abused by your users. In order to limit the access to the endpoint, add the following configuration to your config/app.php // define a cache configuration, Redis could be a good option for a fast and distributed approach 'rate_limit' => [ 'className' => \Cake\Cache\Engine\RedisEngine::class, 'path' => CACHE, 'url' => env('CACHE_RATE_LIMIT_URL', null), ], Then, in your src/Application.php middleware method, create one or many configurations for your rate limits. The middleware allows a lot of customization, for example to select the strategy, or how are you going to identify the owner of the rate limit. ->add(new RateLimitMiddleware([ 'strategy' => RateLimitMiddleware::STRATEGY_FIXED_WINDOW, 'identifier' => RateLimitMiddleware::IDENTIFIER_IP, 'limit' => 5, 'window' => 10, 'cache' => 'rate_limit', 'skipCheck' => function ($request) { return !( $request->getParam('controller') === 'Reports' && $request->getParam('action') === 'index' ); } ])) In this particular configuration we are going to limit the access to the /reports/index endpoint (we skip everything else) to 5 requests every 10 seconds. You can learn more about the middleware configuration here https://github.com/cakephp/docs/pull/8063 while the final documentation is being finished. This article is part of the CakeDC Advent Calendar 2025 (December 21st 2025)

Written by Jorge on December 21, 2025

56 Views

Real-Time Notifications? You Might Not Need WebSockets

This article is part of the CakeDC Advent Calendar 2025 (December 20th 2025) As PHP developers, when we hear "real-time," our minds immediately jump to WebSockets. We think of complex setups with Ratchet, long-running server processes, and tricky Nginx proxy configurations. And for many applications (like live chats or collaborative editing) WebSockets are absolutely the right tool. But, if you don't need all that complexity or if you just want to push data from your server to the client? Think of a new notification, a "users online" counter, or a live dashboard update. For these one-way-street use cases, WebSockets are often overkill. Enter Server-Sent Events (SSE). It's a simple, elegant, and surprisingly powerful W3C standard that lets your server stream updates to a client over a single, long-lasting HTTP connection.

SSE vs. WebSockets: The Showdown

The most important difference is direction.
  • WebSockets (WS): Bidirectional. The client and server can both send messages to each other at any time. It's a two-way conversation.
  • Server-Sent Events (SSE): Unidirectional. Only the server can send messages to the client. It's a one-way broadcast.
This single difference has massive implications for simplicity and implementation.
Feature Server-Sent Events (SSE) WebSockets (WS)
Direction Unidirectional (Server ➔ Client) Bidirectional (Client ⟺ Server)
Protocol Just plain HTTP/S A new protocol (ws://, wss://)
Simplicity High. simple API, complex ops at scale Low. Requires a special server.
Reconnection Automatic! The browser handles it. Manual. You must write JS to reconnect.
Browser API Native EventSource object. Native WebSocket object.
Best For Notifications, dashboards, live feeds. Live chats, multiplayer games, co-editing.
Pros for SSE:
  • It's just HTTP. No new protocol, no special ports.
  • Automatic reconnection is a life-saver.
  • The server-side implementation can be a simple controller action.
Cons for SSE:
  • Strictly one-way. The client can't send data back on the same connection.
  • Some older proxies or servers might buffer the response, which can be tricky.
Infrastructure Note: Since SSE keeps a persistent connection open, each active client will occupy one PHP-FPM worker. For high-traffic applications, ensure your server is configured to handle the concurrent load or consider a non-blocking server like RoadRunner. Additionally, using HTTP/2 is strongly recommended to bypass the 6-connection-per-domain limit found in older HTTP/1.1 protocols

The Implementation: A Smart, Reusable SSE System in CakePHP

We're not going to build a naive while(true) loop that hammers our database every 2 seconds. That's inefficient. Instead, we'll build an event-driven system. The while(true) loop will only check a cache key. This is lightning-fast. A separate "trigger" class will update that cache key's timestamp only when a new notification is actually created. This design is clean, decoupled, and highly performant.
Note: This example uses CakePHP, but the principles (a component, a trigger, and a controller) can be adapted to any framework like Laravel or Symfony.

1. The Explicit SseTrigger Class

First, we need a clean, obvious way to "poke" our SSE stream. We'll create a simple class whose only job is to update a cache timestamp. This is far better than a "magic" Cache::write() call hidden in a model. src/Sse/SseTrigger.php <?php namespace App\Sse; use Cake\Cache\Cache; /** * Provides an explicit, static method to "push" an SSE event. * This simply updates a cache key's timestamp, which the * SseComponent is watching. */ class SseTrigger { /** * Pushes an update for a given SSE cache key. * * @param string $cacheKey The key to "touch". * @return bool */ public static function push(string $cacheKey): bool { // We just write the current time. The content doesn't // matter, only the timestamp. return Cache::write($cacheKey, microtime(true)); } }

CRITICAL PERFORMANCE WARNING: The PHP-FPM Bottleneck

In a standard PHP-FPM environment, each SSE connection is synchronous and blocking. This means one active SSE stream = one locked PHP-FPM worker. If your max_children setting is 50, and 50 users open your dashboard, your entire website will stop responding because there are no workers left to handle regular requests. How to mitigate this: Dedicated Pool: Set up a separate PHP-FPM pool specifically for SSE requests. Go Asynchronous: Use a non-blocking server like RoadRunner, Swoole or FrankenPHP. These can handle thousands of concurrent SSE connections with minimal memory footprint. HTTP/2: Always serve SSE over HTTP/2 to bypass the browser's 6-connection limit per domain.

2. The SseComponent (The Engine)

This component encapsulates all the SSE logic. It handles the loop, the cache-checking, the CallbackStream, and even building the final Response object. The controller will be left perfectly clean. To handle the stream, we utilize CakePHP's CallbackStream. Unlike a standard response that sends all data at once, CallbackStream allows us to emit data in chunks over time. It wraps our while(true) loop into a PSR-7 compliant stream, enabling the server to push updates to the browser as they happen without terminating the request. src/Controller/Component/SseComponent.php <?php namespace App\Controller\Component; use Cake\Controller\Component; use Cake\Http\CallbackStream; use Cake\Cache\Cache; use Cake\Http\Response; class SseComponent extends Component { protected $_defaultConfig = [ 'poll' => 2, // How often to check the cache (in seconds) 'eventName' => 'message', // Default SSE event name 'heartbeat' => 30, // Keep-alive to prevent proxy timeouts ]; /** * Main public method. * Builds the stream and returns a fully configured Response. */ public function stream(callable $dataCallback, string $watchCacheKey, array $options = []): Response { $stream = $this->_buildStream($dataCallback, $watchCacheKey, $options); // Get and configure the controller's response $response = $this->getController()->getResponse(); $response = $response ->withHeader('Content-Type', 'text/event-stream') ->withHeader('Cache-Control', 'no-cache') ->withHeader('Connection', 'keep-alive') ->withHeader('X-Accel-Buffering', 'no') // For Nginx: disable response buffering ->withBody($stream); return $response; } /** * Protected method to build the actual CallbackStream. */ protected function _buildStream(callable $dataCallback, string $watchCacheKey, array $options = []): CallbackStream { $config = $this->getConfig() + $options; return new CallbackStream(function () use ($dataCallback, $watchCacheKey, $config) { set_time_limit(0); $lastSentTimestamp = null; $lastHeartbeat = time(); while (true) { if (connection_aborted()) { break; } // 1. THE FAST CHECK: Read the cache. $currentTimestamp = Cache::read($watchCacheKey); // 2. THE COMPARE: Has it been updated? if ($currentTimestamp > $lastSentTimestamp) { // 3. THE SLOW CHECK: Cache is new, so run the data callback. $data = $dataCallback(); // 4. THE PUSH: Send the data. echo "event: " . $config['eventName'] . "\n"; echo "data: " . json_encode($data) . "\n\n"; $lastSentTimestamp = $currentTimestamp; $lastHeartbeat = time(); } else if (time() - $lastHeartbeat > $config['heartbeat']) { // 5. THE HEARTBEAT: Send a comment to keep connection alive. echo ": \n\n"; $lastHeartbeat = time(); } if (ob_get_level() > 0) { ob_flush(); } flush(); // Wait before the next check sleep($config['poll']); } }); } }

3. Connecting the Logic (Model & Controller)

First, we use our SseTrigger in the afterSave hook of our NotificationsTable. This makes it clear: "After saving a notification, push an update." src/Model/Table/NotificationsTable.php (Partial) use App\Sse\SseTrigger; // Don't forget to import! public function afterSave(EventInterface $event, Entity $entity, ArrayObject $options) { // Check if the entity has a user_id if ($entity->has('user_id') && !empty($entity->user_id)) { // Build the user-specific cache key $userCacheKey = 'notifications_timestamp_user_' . $entity->user_id; // Explicitly trigger the push! SseTrigger::push($userCacheKey); } } Now, our controller action becomes incredibly simple. Its only jobs are to get the current user, define the data callback, and return the component's stream. src/Controller/NotificationsController.php <?php namespace App\Controller; use App\Controller\AppController; use Cake\Http\Exception\ForbiddenException; class NotificationsController extends AppController { public function initialize(): void { parent::initialize(); $this->loadComponent('Sse'); $this->loadComponent('Authentication.Authentication'); } public function stream() { $this->autoRender = false; // 1. Get authenticated user $identity = $this->Authentication->getIdentity(); if (!$identity) { throw new ForbiddenException('Authentication required'); } // 2. Define user-specific parameters $userId = $identity->get('id'); $userCacheKey = 'notifications_timestamp_user_' . $userId; // 3. Define the data callback (what to run when there's an update) $dataCallback = function () use ($userId) { return $this->Notifications->find() ->where(['user_id' => $userId, 'read' => false]) ->order(['created' => 'DESC']) ->limit(5) ->all(); }; // 4. Return the stream. That's it! return $this->Sse->stream( $dataCallback, $userCacheKey, [ 'eventName' => 'new_notification', // Custom event name for JS 'poll' => 2 ] ); } }

4. The Frontend (The Easy Part)

Thanks to the native EventSource API, the client-side JavaScript is trivial. No libraries. No complex connection management. <script> // 1. Point to your controller action const sseUrl = '/notifications/stream'; const eventSource = new EventSource(sseUrl); // 2. Listen for your custom event eventSource.addEventListener('new_notification', (event) => { console.log('New data received!'); const notifications = JSON.parse(event.data); // Do something with the data... // e.g., update a <ul> list or a notification counter updateNotificationBell(notifications); }); // 3. (Optional) Handle errors eventSource.onerror = (error) => { console.error('EventSource failed:', error); // The browser will automatically try to reconnect. }; // (Optional) Handle the initial connection eventSource.onopen = () => { console.log('SSE connection established.'); }; </script>

Ideas for Your Projects

You can use this exact pattern for so much more than just notifications:
  • Live Admin Dashboard: A "Recent Sales" feed or a "Users Online" list that updates automatically.
  • Activity Feeds: Show "John recently commented..." in real-time.
  • Progress Indicators: For a long-running background process (like video encoding), push status updates ("20% complete", "50% complete", etc.).
  • Live Sports Scores: Push new scores as they happen.
  • Stock or Crypto Tickers: Stream new price data from your server.

When NOT to Use SSE: Know Your Limits

While SSE is an elegant solution for many problems, it isn't a silver bullet. You should avoid SSE and stick with WebSockets or standard Polling when:
  • True Bidirectional Communication is Required: If your app involves heavy "back-and-forth" (like a fast-paced multiplayer game or a collaborative whiteboarding tool), WebSockets are the correct choice.
  • Binary Data Streams: SSE is a text-based protocol. If you need to stream raw binary data (like audio or video frames), WebSockets or WebRTC are better suited.
  • Legacy Browser Support (IE11): If you must support older browsers that lack EventSource and you don't want to rely on polyfills, SSE will not work.
  • Strict Connection Limits: If you are on a restricted shared hosting environment with very few PHP-FPM workers and no support for HTTP/2, the persistent nature of SSE will quickly exhaust your server's resources.

Conclusion

WebSockets are a powerful tool, but they aren't the only tool. For the wide array of use cases that only require one-way, server-to-client communication, Server-Sent Events are a simpler, more robust, and more maintainable solution. It integrates perfectly with the standard PHP request cycle, requires no extra daemons, and is handled natively by the browser. So the next time you need real-time updates, ask yourself: "Do I really need a two-way conversation?" If the answer is no, give SSE a try. This article is part of the CakeDC Advent Calendar 2025 (December 20th 2025)

Written by Arodriguez on December 19, 2025

61 Views

QA vs. Devs: a MEME tale of the IT environment

QA testing requires knowledge in computer science but still many devs think of us like  homer-simpson-meme   BUT... morpheus-meme   It is not like we want to detroy what you have created but... house-on-fire-meme   And we have to report it, it is our job... tom-and-jerry-meme   It is not like we think dev-vs-qa   I mean cat-meme   Plaeas do not consider us a thread :) willy-wonka-meme 0/0/0000 reaction-to-a-bug   Sometimes we are kind of lost seeing the application... futurama-meme   And sometimes your don't believe the crazy results we get... ironman-meme   I know you think aliens-meme   But remmember we are here to help xD the-office-meme   Happy Holidays to ya'll folks! the-wolf-of-wallstreet-meme   PS. Enjoy some more memes   feature-vs-user   hide-the-pain-harold-meme   idea-for-qa   peter-parker-meme   meme   dev-estimating-time-vs-pm    

Written by Rcardoso on December 17, 2025

We Bake with CakePHP