Sessions | Naidim

Now we're going to work on storing our Session information in the database.

When working with multiple horizontally-scaled servers, the user cannot control which server responds to their request. A good load-balancer will attempt to maintain the connection on a single server, but it is highly likely that a request will be served by any of the balanced web servers. By storing the session data in the database instead of in the PHP session folder on the server, the user will not lose their login authentication if they get passed to a different server than the one they initially signed in on.

Create Database Table

Like most things in CakePHP, there are many options to each configuration setting that you can control so that things work exactly as you need them to. In our case the default works fine.

Create the sessions table in the database:

CREATE TABLE `sessions` (
  `id` char(40) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `data` blob DEFAULT NULL, -- for PostgreSQL use bytea instead of blob
  `expires` int(10) unsigned DEFAULT NULL,
  `created` datetime DEFAULT CURRENT_TIMESTAMP, -- Optional
  `modified` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, -- Optional
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Or you can use a migration:

$ bin/cake bake migration CreateSessions

Then update /config/Migrations/##############_CreateSessions.php

...
public function change(): void
{
  $table = $this->table('sessions', ['collation' => 'ascii_bin',
    'id' => false, 'primary_key' => ['id']]);
  $table->addColumn('id', 'char', ['limit' => 40, 'null' => false])
    ->addColumn('data', 'blob')
    ->addColumn('expires', 'integer', ['signed' => false])
    ->addTimestamps();
  $table->create();
}

And don't forget to run the migration:

bin/cake migrations migrate

Update the Application Configuration

Edit /config/app_local.php and change your Session default from 'php' to 'database' and that's all there is to it. If you were signed in, you'll need to sign in again because the application is looking in the application database for a record.