Store Sessions in a Database

Store Sessions in a Database

Symfony stores sessions in files by default. If your application is served by multiple servers, you’ll need to use a database instead to make sessions work across different servers.

Symfony can store sessions in all kinds of databases (relational, NoSQL and key-value) but recommends key-value databases like Redis to get best performance.

Store Sessions in a key-value Database (Redis)

This section assumes that you have a fully-working Redis server and have also installed and configured the phpredis extension.

First, define a Symfony service for the connection to the Redis server:

  • YAML

    1. # config/services.yaml
    2. services:
    3.     # ...
    4.     Redis:
    5.         # you can also use \RedisArray, \RedisCluster or \Predis\Client classes
    6.         class: Redis
    7.         calls:
    8.             - connect:
    9.                 - '%env(REDIS_HOST)%'
    10.                 - '%env(int:REDIS_PORT)%'
    12.             # uncomment the following if your Redis server requires a password
    13.             # - auth:
    14.             #     - '%env(REDIS_PASSWORD)%'

  • XML

    1. <?xml version="1.0" encoding="UTF-8" ?>
    2. <container xmlns="http://symfony.com/schema/dic/services"
    3.     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    4.     xsi:schemaLocation="http://symfony.com/schema/dic/services https://symfony.com/schema/dic/services/services-1.0.xsd">
    6.     <services>
    7.         <!-- you can also use \RedisArray, \RedisCluster or \Predis\Client classes -->
    8.         <service id="Redis" class="Redis">
    9.             <call method="connect">
    10.                 <argument>%env(REDIS_HOST)%</argument>
    11.                 <argument>%env(int:REDIS_PORT)%</argument>
    12.             </call>
    14.             <!-- uncomment the following if your Redis server requires a password:
    15.             <call method="auth">
    16.                 <argument>%env(REDIS_PASSWORD)%</argument>
    17.             </call> -->
    18.         </service>
    19.     </services>
    20. </container>

  • PHP

    1. // ...
    2. $container
    3.     // you can also use \RedisArray, \RedisCluster or \Predis\Client classes
    4.     ->register('Redis', \Redis::class)
    5.     ->addMethodCall('connect', ['%env(REDIS_HOST)%', '%env(int:REDIS_PORT)%'])
    6.     // uncomment the following if your Redis server requires a password:
    7.     // ->addMethodCall('auth', ['%env(REDIS_PASSWORD)%'])
    8. ;

Now pass this \Redis connection as an argument of the service associated to the Symfony\Component\HttpFoundation\Session\Storage\Handler\RedisSessionHandler. This argument can also be a \RedisArray, \RedisCluster, \Predis\Client, and RedisProxy:

  • YAML

    1. # config/services.yaml
    2. services:
    3.     # ...
    4.     Symfony\Component\HttpFoundation\Session\Storage\Handler\RedisSessionHandler:
    5.         arguments:
    6.             - '@Redis'
    7.             # you can optionally pass an array of options. The only options are 'prefix' and 'ttl',
    8.             # which define the prefix to use for the keys to avoid collision on the Redis server
    9.             # and the expiration time for any given entry (in seconds), defaults are 'sf_s' and null:
    10.             # - { 'prefix': 'my_prefix', 'ttl': 600 }

  • XML

    1. <!-- config/services.xml -->
    2. <services>
    3.     <service id="Symfony\Component\HttpFoundation\Session\Storage\Handler\RedisSessionHandler">
    4.         <argument type="service" id="Redis"/>
    5.         <!-- you can optionally pass an array of options. The only options are 'prefix' and 'ttl',
    6.              which define the prefix to use for the keys to avoid collision on the Redis server
    7.              and the expiration time for any given entry (in seconds), defaults are 'sf_s' and null:
    8.         <argument type="collection">
    9.             <argument key="prefix">my_prefix</argument>
    10.             <argument key="ttl">600</argument>
    11.         </argument> -->
    12.     </service>
    13. </services>

  • PHP

    1. // config/services.php
    2. use Symfony\Component\DependencyInjection\Reference;
    3. use Symfony\Component\HttpFoundation\Session\Storage\Handler\RedisSessionHandler;
    5. $container
    6.     ->register(RedisSessionHandler::class)
    7.     ->addArgument(
    8.         new Reference('Redis'),
    9.         // you can optionally pass an array of options. The only options are 'prefix' and 'ttl',
    10.         // which define the prefix to use for the keys to avoid collision on the Redis server
    11.         // and the expiration time for any given entry (in seconds), defaults are 'sf_s' and null:
    12.         // ['prefix' => 'my_prefix', 'ttl' => 600],
    13.     );

Next, use the handler_id configuration option to tell Symfony to use this service as the session handler:

  • YAML

    1. # config/packages/framework.yaml
    2. framework:
    3.     # ...
    4.     session:
    5.         handler_id: Symfony\Component\HttpFoundation\Session\Storage\Handler\RedisSessionHandler

  • XML

    1. <!-- config/packages/framework.xml -->
    2. <framework:config>
    3.     <!-- ... -->
    4.     <framework:session handler-id="Symfony\Component\HttpFoundation\Session\Storage\Handler\RedisSessionHandler"/>
    5. </framework:config>

  • PHP

    1. // config/packages/framework.php
    2. use Symfony\Component\HttpFoundation\Session\Storage\Handler\RedisSessionHandler;
    4. // ...
    5. $container->loadFromExtension('framework', [
    6.     // ...
    7.     'session' => [
    8.         'handler_id' => RedisSessionHandler::class,
    9.     ],
    10. ]);

That’s all! Symfony will now use your Redis server to read and write the session data. The main drawback of this solution is that Redis does not perform session locking, so you can face race conditions when accessing sessions. For example, you may see an “Invalid CSRF token” error because two requests were made in parallel and only the first one stored the CSRF token in the session.

See also

If you use Memcached instead of Redis, follow a similar approach but replace RedisSessionHandler by Symfony\Component\HttpFoundation\Session\Storage\Handler\MemcachedSessionHandler.

Store Sessions in a Relational Database (MariaDB, MySQL, PostgreSQL)

Symfony includes a Symfony\Component\HttpFoundation\Session\Storage\Handler\PdoSessionHandler to store sessions in relational databases like MariaDB, MySQL and PostgreSQL. To use it, first register a new handler service with your database credentials:

  • YAML

    1. # config/services.yaml
    2. services:
    3.     # ...
    5.     Symfony\Component\HttpFoundation\Session\Storage\Handler\PdoSessionHandler:
    6.         arguments:
    7.             - '%env(DATABASE_URL)%'
    9.             # you can also use PDO configuration, but requires passing two arguments
    10.             # - 'mysql:dbname=mydatabase; host=myhost; port=myport'
    11.             # - { db_username: myuser, db_password: mypassword }

  • XML

    1. <!-- config/services.xml -->
    2. <?xml version="1.0" encoding="UTF-8" ?>
    3. <container xmlns="http://symfony.com/schema/dic/services"
    4.     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    5.     xmlns:framework="http://symfony.com/schema/dic/symfony"
    6.     xsi:schemaLocation="http://symfony.com/schema/dic/services
    7.         https://symfony.com/schema/dic/services/services-1.0.xsd
    8.         https://symfony.com/schema/dic/symfony/symfony-1.0.xsd">
    10.     <services>
    11.         <service id="Symfony\Component\HttpFoundation\Session\Storage\Handler\PdoSessionHandler" public="false">
    12.             <argument>%env(DATABASE_URL)%</argument>
    14.             <!-- you can also use PDO configuration, but requires passing two arguments: -->
    15.             <!-- <argument>mysql:dbname=mydatabase; host=myhost; port=myport</argument>
    16.                 <argument type="collection">
    17.                     <argument key="db_username">myuser</argument>
    18.                     <argument key="db_password">mypassword</argument>
    19.                 </argument> -->
    20.         </service>
    21.     </services>
    22. </container>

  • PHP

    1. // config/services.php
    2. use Symfony\Component\HttpFoundation\Session\Storage\Handler\PdoSessionHandler;
    4. $storageDefinition = $container->autowire(PdoSessionHandler::class)
    5.     ->setArguments([
    6.         '%env(DATABASE_URL)%',
    7.         // you can also use PDO configuration, but requires passing two arguments:
    8.         // 'mysql:dbname=mydatabase; host=myhost; port=myport',
    9.         // ['db_username' => 'myuser', 'db_password' => 'mypassword'],
    10.     ])
    11. ;

Next, use the handler_id configuration option to tell Symfony to use this service as the session handler:

  • YAML

    1. # config/packages/framework.yaml
    2. framework:
    3.     session:
    4.         # ...
    5.         handler_id: Symfony\Component\HttpFoundation\Session\Storage\Handler\PdoSessionHandler

  • XML

    1. <!-- config/packages/framework.xml -->
    2. <framework:config>
    3.     <!-- ... -->
    4.     <framework:session
    5.         handler-id="Symfony\Component\HttpFoundation\Session\Storage\Handler\PdoSessionHandler"/>
    6. </framework:config>

  • PHP

    1. // config/packages/framework.php
    2. use Symfony\Component\HttpFoundation\Session\Storage\Handler\PdoSessionHandler;
    4. // ...
    5. $container->loadFromExtension('framework', [
    6.     // ...
    7.     'session' => [
    8.         'handler_id' => PdoSessionHandler::class,
    9.     ],
    10. ]);

Configuring the Session Table and Column Names

The table used to store sessions is called sessions by default and defines certain column names. You can configure these values with the second argument passed to the PdoSessionHandler service:

  • YAML

    1. # config/services.yaml
    2. services:
    3.     # ...
    5.     Symfony\Component\HttpFoundation\Session\Storage\Handler\PdoSessionHandler:
    6.         arguments:
    7.             - '%env(DATABASE_URL)%'
    8.             - { db_table: 'customer_session', db_id_col: 'guid' }

  • XML

    1. <!-- config/services.xml -->
    2. <?xml version="1.0" encoding="UTF-8" ?>
    3. <container xmlns="http://symfony.com/schema/dic/services"
    4.     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    5.     xsi:schemaLocation="http://symfony.com/schema/dic/services
    6.         https://symfony.com/schema/dic/services/services-1.0.xsd">
    8.     <services>
    9.         <service id="Symfony\Component\HttpFoundation\Session\Storage\Handler\PdoSessionHandler" public="false">
    10.             <argument>%env(DATABASE_URL)%</argument>
    11.             <argument type="collection">
    12.                 <argument key="db_table">customer_session</argument>
    13.                 <argument key="db_id_col">guid</argument>
    14.             </argument>
    15.         </service>
    16.     </services>
    17. </container>

  • PHP

    1. // config/services.php
    2. use Symfony\Component\HttpFoundation\Session\Storage\Handler\PdoSessionHandler;
    3. // ...
    5. $container->autowire(PdoSessionHandler::class)
    6.     ->setArguments([
    7.         '%env(DATABASE_URL)%',
    8.         ['db_table' => 'customer_session', 'db_id_col' => 'guid'],
    9.     ])
    10. ;

These are parameters that you can configure:

db_table (default sessions):

The name of the session table in your database;

db_username: (default: '')

The username used to connect when using the PDO configuration (when using the connection based on the DATABASE_URL env var, it overrides the username defined in the env var).

db_password: (default: '')

The password used to connect when using the PDO configuration (when using the connection based on the DATABASE_URL env var, it overrides the password defined in the env var).

db_id_col (default sess_id):

The name of the column where to store the session ID (column type: VARCHAR(128));

db_data_col (default sess_data):

The name of the column where to store the session data (column type: BLOB);

db_time_col (default sess_time):

The name of the column where to store the session creation timestamp (column type: INTEGER);

db_lifetime_col (default sess_lifetime):

The name of the column where to store the session lifetime (column type: INTEGER);

db_connection_options (default: [])

An array of driver-specific connection options;

lock_mode (default: LOCK_TRANSACTIONAL)

The strategy for locking the database to avoid race conditions. Possible values are LOCK_NONE (no locking), LOCK_ADVISORY (application-level locking) and LOCK_TRANSACTIONAL (row-level locking).

Preparing the Database to Store Sessions

Before storing sessions in the database, you must create the table that stores the information. The session handler provides a method called [createTable()](https://github.com/symfony/symfony/blob/4.4/src/Symfony/Component/HttpFoundation/Session/Storage/Handler/PdoSessionHandler.php "Symfony\Component\HttpFoundation\Session\Storage\Handler\PdoSessionHandler::createTable()") to set up this table for you according to the database engine used:

  1. try {
  2.     $sessionHandlerService->createTable();
  3. } catch (\PDOException $exception) {
  4.     // the table could not be created for some reason
  5. }

If you prefer to set up the table yourself, it’s recommended to generate an empty database migration with the following command:

  1. $ php bin/console doctrine:migrations:generate

Then, find the appropriate SQL for your database below, add it to the migration file and run the migration with the following command:

  1. $ php bin/console doctrine:migrations:migrate

MariaDB/MySQL

  1. CREATE TABLE `sessions` (
  2.     `sess_id` VARBINARY(128) NOT NULL PRIMARY KEY,
  3.     `sess_data` BLOB NOT NULL,
  4.     `sess_lifetime` INTEGER UNSIGNED NOT NULL,
  5.     `sess_time` INTEGER UNSIGNED NOT NULL,
  6.     INDEX `sessions_sess_lifetime_idx` (`sess_lifetime`)
  7. ) COLLATE utf8mb4_bin, ENGINE = InnoDB;

Note

A BLOB column type (which is the one used by default by createTable()) stores up to 64 kb. If the user session data exceeds this, an exception may be thrown or their session will be silently reset. Consider using a MEDIUMBLOB if you need more space.

PostgreSQL

  1. CREATE TABLE sessions (
  2.     sess_id VARCHAR(128) NOT NULL PRIMARY KEY,
  3.     sess_data BYTEA NOT NULL,
  4.     sess_lifetime INTEGER NOT NULL,
  5.     sess_time INTEGER NOT NULL
  6. );
  7. CREATE INDEX sessions_sess_time_idx ON sessions (sess_lifetime);

Microsoft SQL Server

  1. CREATE TABLE sessions (
  2.     sess_id VARCHAR(128) NOT NULL PRIMARY KEY,
  3.     sess_data NVARCHAR(MAX) NOT NULL,
  4.     sess_lifetime INTEGER NOT NULL,
  5.     sess_time INTEGER NOT NULL,
  6.     INDEX sessions_sess_lifetime_idx (sess_lifetime)
  7. );

Store Sessions in a NoSQL Database (MongoDB)

Symfony includes a Symfony\Component\HttpFoundation\Session\Storage\Handler\MongoDbSessionHandler to store sessions in the MongoDB NoSQL database. First, make sure to have a working MongoDB connection in your Symfony application as explained in the DoctrineMongoDBBundle configuration article.

Then, register a new handler service for MongoDbSessionHandler and pass it the MongoDB connection as argument:

  • YAML

    1. # config/services.yaml
    2. services:
    3.     # ...
    5.     Symfony\Component\HttpFoundation\Session\Storage\Handler\MongoDbSessionHandler:
    6.         arguments:
    7.             - '@doctrine_mongodb.odm.default_connection'

  • XML

    1. <!-- config/services.xml -->
    2. <?xml version="1.0" encoding="UTF-8" ?>
    3. <container xmlns="http://symfony.com/schema/dic/services"
    4.     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    5.     xmlns:framework="http://symfony.com/schema/dic/symfony"
    6.     xsi:schemaLocation="http://symfony.com/schema/dic/services
    7.         https://symfony.com/schema/dic/services/services-1.0.xsd
    8.         https://symfony.com/schema/dic/symfony/symfony-1.0.xsd">
    10.     <services>
    11.         <service id="Symfony\Component\HttpFoundation\Session\Storage\Handler\MongoDbSessionHandler" public="false">
    12.             <argument type="service">doctrine_mongodb.odm.default_connection</argument>
    13.         </service>
    14.     </services>
    15. </container>

  • PHP

    1. // config/services.php
    2. use Symfony\Component\HttpFoundation\Session\Storage\Handler\MongoDbSessionHandler;
    4. $storageDefinition = $container->autowire(MongoDbSessionHandler::class)
    5.     ->setArguments([
    6.         new Reference('doctrine_mongodb.odm.default_connection'),
    7.     ])
    8. ;

Next, use the handler_id configuration option to tell Symfony to use this service as the session handler:

  • YAML

    1. # config/packages/framework.yaml
    2. framework:
    3.     session:
    4.         # ...
    5.         handler_id: Symfony\Component\HttpFoundation\Session\Storage\Handler\MongoDbSessionHandler

  • XML

    1. <!-- config/packages/framework.xml -->
    2. <framework:config>
    3.     <!-- ... -->
    4.     <framework:session
    5.         handler-id="Symfony\Component\HttpFoundation\Session\Storage\Handler\MongoDbSessionHandler"/>
    6. </framework:config>

  • PHP

    1. // config/packages/framework.php
    2. use Symfony\Component\HttpFoundation\Session\Storage\Handler\MongoDbSessionHandler;
    4. // ...
    5. $container->loadFromExtension('framework', [
    6.     // ...
    7.     'session' => [
    8.         'handler_id' => MongoDbSessionHandler::class,
    9.     ],
    10. ]);

Note

MongoDB ODM 1.x only works with the legacy driver, which is no longer supported by the Symfony session class. Install the alcaeus/mongo-php-adapter package to retrieve the underlying \MongoDB\Client object or upgrade to MongoDB ODM 2.0.

That’s all! Symfony will now use your MongoDB server to read and write the session data. You do not need to do anything to initialize your session collection. However, you may want to add an index to improve garbage collection performance. Run this from the MongoDB shell:

  1. use session_db
  2. db.session.createIndex( { "expires_at": 1 }, { expireAfterSeconds: 0 } )

Configuring the Session Field Names

The collection used to store sessions defines certain field names. You can configure these values with the second argument passed to the MongoDbSessionHandler service:

  • YAML

    1. # config/services.yaml
    2. services:
    3.     # ...
    5.     Symfony\Component\HttpFoundation\Session\Storage\Handler\MongoDbSessionHandler:
    6.         arguments:
    7.             - '@doctrine_mongodb.odm.default_connection'
    8.             - { id_field: '_guid', 'expiry_field': 'eol' }

  • XML

    1. <!-- config/services.xml -->
    2. <?xml version="1.0" encoding="UTF-8" ?>
    3. <container xmlns="http://symfony.com/schema/dic/services"
    4.     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    5.     xsi:schemaLocation="http://symfony.com/schema/dic/services
    6.         https://symfony.com/schema/dic/services/services-1.0.xsd">
    8.     <services>
    9.         <service id="Symfony\Component\HttpFoundation\Session\Storage\Handler\MongoDbSessionHandler" public="false">
    10.             <argument type="service">doctrine_mongodb.odm.default_connection</argument>
    11.             <argument type="collection">
    12.                 <argument key="id_field">_guid</argument>
    13.                 <argument key="expiry_field">eol</argument>
    14.             </argument>
    15.         </service>
    16.     </services>
    17. </container>

  • PHP

    1. // config/services.php
    2. use Symfony\Component\HttpFoundation\Session\Storage\Handler\MongoDbSessionHandler;
    3. // ...
    5. $container->autowire(MongoDbSessionHandler::class)
    6.     ->setArguments([
    7.         '...',
    8.         ['id_field' => '_guid', 'expiry_field' => 'eol'],
    9.     ])
    10. ;

These are parameters that you can configure:

id_field (default _id):

The name of the field where to store the session ID;

data_field (default data):

The name of the field where to store the session data;

time_field (default time):

The name of the field where to store the session creation timestamp;

expiry_field (default expires_at):

The name of the field where to store the session lifetime.

This work, including the code samples, is licensed under a Creative Commons BY-SA 3.0 license.