.docs

Contributte Doctrine DBAL

Integration of Doctrine DBAL for Nette Framework.

Content

• Installation
• Configuration
  • Minimal configuration
  • Advanced configuration
  • Encoding
  • Replicas
  • Caching
  • Types
  • Debug
  • Middlewares
  • Logging
  • Console
• Examples

Installation

Install package using composer.

composer require nettrine/dbal

Register prepared compiler extension in your config.neon file.

extensions:
  nettrine.dbal: Nettrine\DBAL\DI\DbalExtension

For better integration with Nette Framework and Tracy, pass %debugMode% to the extension constructor. Learn more in debug chapter.

extensions:
  nettrine.dbal: Nettrine\DBAL\DI\DbalExtension(%debugMode%)

Note

This is just DBAL, for ORM please use nettrine/orm.

Configuration

Minimal configuration

PostgreSQL

nettrine.dbal:
  connections:
    default:
      driver: pdo_pgsql
      url: "postgresql://user:password@localhost:5432/dbname"

MySQL / MariaDB

nettrine.dbal:
  connections:
    default:
      driver: mysqli
      url: "mysql://user:passoword@localhost:3306/dbname"

SQLite

nettrine.dbal:
  connections:
    default:
      driver: pdo_sqlite
      url: "sqlite:///:memory:"

Advanced configuration

Here is the list of all available options with their types.

nettrine.dbal:
 debug:
   panel: <boolean> # optional, it's determined automatically or passed to constructor when extension is registered

 types: array<string, class-string>
 typesMapping: array<string, class-string>

 connections:
   <name>:
     # Connection
     application_name: <string>
     charset: <string>
     connectstring: <string>
     dbname: <string>
     defaultTableOptions: <array<string, mixed>>
     driver: <'pdo_sqlite', 'sqlite3', 'pdo_mysql', 'mysqli', 'pdo_pgsql', 'pgsql', 'pdo_oci', 'oci8', 'pdo_sqlsrv', 'sqlsrv', 'ibm_db2'>
     driverClass: <string>
     driverOptions: <array<string, mixed>>
     exclusive: <string>
     gssencmode: <string>
     host: <string>
     instancename: <string>
     keepReplica: <bool>
     memory: <bool>
     password: <string>
     path: <string>
     persistent: <bool>
     pooled: <string>
     port: <int>
     primary: <connection-config>
     protocol: <string>
     replica: array<string, connection-config>
     serverVersion: <string>
     service: <string>
     servicename: <string>
     sessionMode: <int>
     ssl_ca: <string>
     ssl_capath: <string>
     ssl_cert: <string>
     ssl_cipher: <string>
     ssl_key: <string>
     sslcert: <string>
     sslcrl: <string>
     sslkey: <string>
     sslmode: <string>
     sslrootcert: <string>
     unix_socket: <string>
     url: <string>
     user: <string>
     wrapperClass: <string>

     # Config
     middlewares: array<string, <service|class-name>>
     resultCache: <service|class-name>
     schemaAssetsFilter: <service|class-name>
     autoCommit: <boolean>

For example:

nettrine.dbal:
  types:
    uuid: Ramsey\Uuid\Doctrine\UuidType

  connections:
    # Short DSN configuration
    default:
      driver: pdo_pgsql
      url: "postgresql://user:password@localhost:5432/dbname?charset=utf8"

    # Explicit configuration
    second:
      driver: pdo_pgsql
      host: localhost
      port: 5432
      user: user
      password: password
      dbname: dbname
      charset: utf8

Supported drivers:

• pdo_mysql
• pdo_sqlite
• pdo_pgsql
• pdo_oci
• oci8
• ibm_db2
• pdo_sqlsrv
• mysqli
• pgsql
• sqlsrv
• sqlite3

Tip

Take a look at real Nettrine DBAL configuration example at contributte/doctrine-project.

Encoding

You can set encoding for your connection.

nettrine.dbal:
  connections:
    default:
      charset: utf8
      defaultTableOptions:
        charset: utf8
        collate: utf8_unicode_ci

Replicas

Doctrine DBAL supports read replicas. You can define them in the configuration.

nettrine.dbal:
  connections:
    default:
      driver: pdo_pgsql
      wrapperClass: Doctrine\DBAL\Connections\PrimaryReadReplicaConnection
      url: "postgresql://user:password@localhost:5432/table?charset=utf8&serverVersion=15.0"
      replica:
        read1:
          url: "postgresql://user:password@read-db1:5432/table?charset=utf8"
        read2:
          url: "postgresql://user:password@read-db2:5432/table?charset=utf8"

Caching

Tip

Take a look at more information in official Doctrine documentation:

• https://www.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/caching.html

A Doctrine DBAL can automatically cache result sets. The feature is optional though, and by default, no result set is cached. You can enable the result cache by setting the resultCache configuration option to an instance of a cache driver.

Warning

Cache adapter must implement Psr\Cache\CacheItemPoolInterface interface. Use any PSR-6 + PSR-16 compatible cache library like symfony/cache or nette/caching.

nettrine.dbal:
  connections:
    default:
      configuration:
        # Create cache manually
        resultCache: App\CacheService(%tempDir%/cache/dbal)

        # Use registered cache service
        resultCache: @cacheService

If you want to disable cache, you can use provided NullCacheAdapter.

nettrine.dbal:
    connections:
      default:
        resultCache: Nettrine\DBAL\Cache\NullCacheAdapter

If you like symfony/cache you can use it as well.

nettrine.dbal:
    connections:
      default:
        # Creat cache manually
        resultCache: Symfony\Component\Cache\Adapter\FilesystemAdapter(namespace: doctrine-dbal, defaultLifetime: 0, directory: %tempDir%/cache/dbal)

        # Use registered cache service
        resultCache: @cacheFilesystem

If you like nette/caching you can use it as well. Be aware that nette/caching is not PSR-6 + PSR-16 compatible, you need contributte/psr16-caching.

nettrine.dbal:
    connections:
      default:
        resultCache: Contributte\Psr6\CachePool(
          Nette\Caching\Cache(
            Nette\Caching\Storages\FileStorage(%tempDir%/cache)
            doctrine/dbal
          )
        )

Important

You should always use cache for production environment. It can significantly improve performance of your application. Pick the right cache adapter for your needs. For example from symfony/cache:

• FilesystemAdapter - if you want to cache data on disk
• ArrayAdapter - if you want to cache data in memory
• ApcuAdapter - if you want to cache data in memory and share it between requests
• RedisAdapter - if you want to cache data in memory and share it between requests and servers
• ChainAdapter - if you want to cache data in multiple storages

Types

Tip

Take a look at more information in official Doctrine documentation:

• http://docs.doctrine-project.org/projects/doctrine-dbal/en/latest/reference/types.html
• http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/cookbook/custom-mapping-types.html

Doctrine DBAL supports custom mapping types. You can define your own types in the configuration.

nettrine.dbal:
  types:
    # https://github.com/ramsey/uuid-doctrine
    uuid: Ramsey\Uuid\Doctrine\UuidType

You can also define type mapping for database columns.

nettrine.dbal:
  types:
    uuid_binary_ordered_time: Ramsey\Uuid\Doctrine\UuidBinaryOrderedTimeType
  typesMapping:
    uuid_binary_ordered_time: binary

Debug

This library provides Tracy panel(s) for debugging queries.

You can enable Tracy panels(s) multiple ways:

1. Autodetect in development mode (Tracy\Debugger::$productionMode = FALSE). Learn more about Nette & Tracy.

$configurator->enableTracy(...);

2. Manually provide %debugMode% to the extension.

extensions:
  nettrine.dbal: Nettrine\DBAL\DI\DbalExtension(%debugMode%)

3. Manually set debug.panel to true in NEON configuration.

nettrine.dbal:
  debug:
    panel: %debugMode%

Middlewares

Tip

Take a look at more information in official Doctrine documentation:

• https://www.doctrine-project.org/projects/doctrine-dbal/en/4.2/reference/architecture.html#middlewares
• doctrine/dbal#5784 Since Doctrine v3.6 you have to use middlewares instead of event system.

Middlewares are the way how to extend doctrine library or hook to special events.

nettrine.dbal:
  connections:
    default:
      middlewares:
        insight: App\InsightMiddleware

<?php declare(strict_types=1);

namespace App;

use Doctrine\DBAL\Driver;
use Doctrine\DBAL\Driver\Middleware;

final class InsightMiddleware implements Middleware
{

	public function wrap(Driver $driver): Driver
	{
		return new InsightDriverMiddleware($driver);
	}

}

<?php declare(strict_types=1);

namespace App;

use Doctrine\DBAL\Driver\Connection;
use Doctrine\DBAL\Driver\Middleware\AbstractDriverMiddleware;

final class InsightDriverMiddleware extends AbstractDriverMiddleware
{

	public function connect(array $params): Connection
	{
		return new InsightConnection(parent::connect($params));
	}

}

Logging

Tip

Take a look at more information in official Doctrine documentation:

• https://www.doctrine-project.org/projects/doctrine-dbal/en/4.2/reference/architecture.html#logging
• doctrine/dbal#5784 Since Doctrine v3.6 you have to use middlewares instead of event system.

Doctrine DBAL supports logging of SQL queries. You can enable logging by setting logger middleware.

nettrine.dbal:
  connections:
    default:
      middlewares:
        # Create logger manualy
        logger: Doctrine\DBAL\Logging\Middleware(
            Monolog\Logger(doctrine, [Monolog\Handler\StreamHandler(%tempDir%/doctrine.log)])
        )

        # Use registered logger service
        logger: Doctrine\DBAL\Logging\Middleware(@logger)

Unfortunately, Doctrine\DBAL\Logging\Middleware provides only basic logger. If you want to measure time or log queries to file, you have to implement your own logger.

<?php declare(strict_types=1);

namespace App;

use Doctrine\DBAL\Driver;
use Doctrine\DBAL\Driver\Middleware;

final class InspectorMiddleware implements Middleware
{
    public function __construct() {
    }

    public function wrap(Driver $driver): Driver
    {
        // Create your custom driver, wrap the original one and return it
        return new InspectorDriver($driver);
    }
}

nettrine.dbal:
  configuration:
    middlewares:
      inspector: App\InspectorMiddleware()

Tip

Inspiration for custom middleware can be found in Symfony (symfony/doctrine-bridge).

• https://github.com/symfony/doctrine-bridge/blob/09dbb7c731430335e9ae89ee5054b5f5580c49bf/Middleware/Debug/Middleware.php#L1-L37)

Console

Tip

Doctrine DBAL needs Symfony Console to work. You can use symfony/console or contributte/console.

composer require contributte/console

extensions:
  console: Contributte\Console\DI\ConsoleExtension(%consoleMode%)

  nettrine.dbal: Nettrine\DBAL\DI\DbalExtension

Since this moment when you type bin/console, there'll be registered commands from Doctrine DBAL.

Examples

Tip

Take a look at more examples in contributte/doctrine.