Browse documentation

#Symfony PostgreSQL Bundle

Symfony bundle integrating Flow PHP's PostgreSQL library with Symfony applications, providing database management, schema migrations, catalog-driven schema diffing, and optional telemetry for query tracing and metrics.

#Installation

For detailed installation instructions, see the installation page.

#Overview

This bundle is built on top of flow-php/postgresql and flow-php/pg-query-ext — see those pages for the underlying client, query builders, catalog, and migration engine.

For telemetry support (tracing, metrics, query logging per connection), the Symfony Telemetry Bundle is required and must expose a configured Telemetry service referenced via telemetry.service_id.

This bundle integrates Flow PHP's PostgreSQL library with Symfony applications. It provides:

• Multiple database connections - Configure and manage several PostgreSQL connections independently
• Database management commands - Create, drop databases and execute SQL from the console
• Migration framework - Generate, execute, and track schema migrations
• Schema diffing - Automatically generate migrations by comparing the database to catalog definitions
• Rollback support - Generate reversible migrations with automatic rollback files
• Telemetry integration - Distributed tracing, query logging, and metrics per connection
• Catalog provider system - Define target schemas via PHP attributes, service references, or inline YAML

#Configuration Reference

#Connections

At least one connection is required. Each connection has a DSN and optional telemetry and migrations blocks.

flow_postgresql:
  connections:
    default:
      dsn: '%env(DATABASE_URL)%'

#Telemetry

Enable telemetry per connection to get distributed tracing, query logging, and metrics.

flow_postgresql:
  connections:
    default:
      dsn: '%env(DATABASE_URL)%'

      telemetry:
        service_id: "flow.telemetry"         # Required: Telemetry service ID
        clock_service_id: null               # Optional: PSR-20 clock service (defaults to SystemClock)
        trace_queries: true                  # Record query execution in traces
        trace_transactions: true             # Record transaction boundaries
        collect_metrics: true                # Collect query metrics (duration, row count)
        log_queries: false                   # Log all queries
        max_query_length: 1000               # Truncate queries longer than this (chars)
        include_parameters: false            # Include query parameters in traces
        max_parameters: 10                   # Max number of parameters to include
        max_parameter_length: 100            # Max length of each parameter value (chars)

#Migrations

Migrations are configured at the top level, not per connection. Use --connection to target a specific connection when running migration commands.

flow_postgresql:
  connections:
    default:
      dsn: '%env(DATABASE_URL)%'

  migrations:
    enabled: true
    directory: "%kernel.project_dir%/migrations"  # Where migration files are stored
    namespace: "App\\Migrations"                   # PHP namespace for generated migrations
    table_name: "flow_migrations"                  # Database table tracking executed migrations
    table_schema: "public"                         # Schema for the migrations table
    migration_file_name: "migration.php"           # Name of the migration file in each version directory
    rollback_file_name: "rollback.php"             # Name of the rollback file in each version directory
    all_or_nothing: false                          # Wrap all migrations in a single transaction
    generate_rollback: true                        # Generate rollback files automatically

#Catalog Providers

Catalog providers define the target database schema. When you run flow:migrations:diff, the bundle compares the current database state to the catalog and generates migration SQL to reconcile the differences.

There are three ways to define catalog providers.

#Attribute-Based Discovery

Annotate a class implementing CatalogProvider with #[AsCatalogProvider] for automatic discovery:

<?php

namespace App\Database;

use Flow\Bridge\Symfony\PostgreSqlBundle\Attribute\AsCatalogProvider;
use Flow\PostgreSql\Catalog\Catalog;
use Flow\PostgreSql\Catalog\CatalogProvider;

#[AsCatalogProvider]
final class UsersCatalogProvider implements CatalogProvider
{
    public function get(): Catalog
    {
        return Catalog::create(
            // Define your tables, columns, indexes...
        );
    }
}

#Service Reference

Reference an existing service by its ID:

flow_postgresql:
  catalog_providers:
    - catalog_provider_id: "app.my_catalog_provider"

#Inline YAML

Define the catalog directly in configuration:

flow_postgresql:
  catalog_providers:
    - catalog:
        schemas:
          - name: "public"
            tables:
              - name: "users"
                columns:
                  - name: "id"
                    type: { name: "int4", schema: "pg_catalog" }
                    nullable: false
                  - name: "email"
                    type: { name: "varchar", schema: "pg_catalog" }
                    nullable: false
                  - name: "created_at"
                    type: { name: "timestamptz", schema: "pg_catalog" }
                    nullable: false

Multiple catalog providers can be combined. They are merged via ChainCatalogProvider into a single catalog.

#Console Commands

#Database Commands

These commands are always available, regardless of migration configuration.

All commands accept --connection (-c) to target a specific connection.

#Migration Commands

These commands are available when migrations.enabled: true for at least one connection.

Common options:

#Migration Workflow

#Directory Structure

Migrations are organized as directories, one per version:

migrations/
  20260401120000_create_users/
    migration.php
    rollback.php
  20260402150000_add_orders/
    migration.php
    rollback.php

Each directory name follows the pattern {version} or {version}_{name}, where version is a numeric timestamp (e.g. 20260401120000) and name is an optional description.

#Generating Migrations from Schema Diff

Compare the current database to your catalog providers and generate migration SQL automatically:

php bin/console flow:migrations:diff

This creates a new versioned directory with migration.php containing the SQL to bring the database in sync with the catalog, and rollback.php with the reverse operations (if generate_rollback is enabled).

Use --from-empty-schema to generate a migration as if the database were empty (useful for initial setup).

#Generating Blank Migrations

Create an empty migration for manual SQL (data migrations, custom operations):

php bin/console flow:migrations:generate

#Executing Migrations

Run all pending migrations:

php bin/console flow:migrations:migrate

Preview without applying:

php bin/console flow:migrations:migrate --dry-run

Execute a single version:

php bin/console flow:migrations:execute 20260401120000 --up

#Checking Status

php bin/console flow:migrations:status
php bin/console flow:migrations:up-to-date

#Multi-Connection Support

The bundle supports multiple independent PostgreSQL connections:

flow_postgresql:
  connections:
    default:
      dsn: '%env(DATABASE_URL)%'

    reporting:
      dsn: '%env(REPORTING_DATABASE_URL)%'

    analytics:
      dsn: '%env(ANALYTICS_DATABASE_URL)%'

  migrations:
    enabled: true
    directory: "%kernel.project_dir%/migrations"

Each connection gets its own set of services (flow.postgresql.{name}.*). The first connection is automatically aliased to the base interfaces (Client, ConnectionParameters, Migrator, etc.), allowing direct type-hint injection without specifying a connection name.

When migrations are enabled, migration services are registered for every connection. The same migration directory and configuration is shared across all connections.

Target a specific connection with any command:

php bin/console flow:migrations:migrate --connection=reporting

#Symfony Messenger Integration

The bundle integrates with flow-php/symfony-postgresql-messenger-bridge to provide a Symfony Messenger transport backed by Flow's native PostgreSQL client — no Doctrine DBAL required.

#Setup

1. Install the messenger bridge:

composer require flow-php/symfony-postgresql-messenger-bridge:~0.37.0

2. Enable messenger:

flow_postgresql:
  connections:
    default:
      dsn: '%env(DATABASE_URL)%'

  messenger:
    enabled: true
    table_name: messenger_messages  # default
    schema: public                  # default

3. Configure the Symfony Messenger transport:

# config/packages/messenger.yaml
framework:
  messenger:
    transports:
      async:
        dsn: 'flow-pgsql://default'
    routing:
      App\Message\MyMessage: async

4. Generate and run the migration to create the messenger table:

php bin/console flow:migrations:diff
php bin/console flow:migrations:migrate

The MessengerCatalogProvider is registered automatically when messenger.enabled: true, so the messenger_messages table appears in schema diffs alongside your other catalog-managed tables.

#Configuration Options

For full documentation, see the Symfony PostgreSQL Messenger Bridge.

#Symfony Cache Integration

The bundle integrates with flow-php/symfony-postgresql-cache-bridge to provide PSR-6 / Symfony Cache pools backed by Flow's native PostgreSQL client — no Doctrine DBAL required. The adapter implements PruneableInterface, so cache:pool:prune works out of the box.

Each pool is wired with the named connection's ConnectionParameters and opens its own pg_connect, isolated from the Client services used by application code. This isolation is what keeps $cache->save(...) from accidentally participating in (and being rolled back by) a transaction held by the caller.

#Setup

1. Install the cache bridge:

composer require flow-php/symfony-postgresql-cache-bridge:~0.37.0

2. Define one or more pools under flow_postgresql.cache.pools:

flow_postgresql:
  connections:
    default:
      dsn: '%env(DATABASE_URL)%'

  cache:
    pools:
      app:
        connection: default            # optional; defaults to the first connection
        table_name: cache_app          # default: cache_items
        default_lifetime: 3600         # default: 0 (no expiry)

      sessions:
        table_name: cache_sessions
        namespace: 'sess.'
        default_lifetime: 86400

Each pool registers two services:

• flow.postgresql.cache.pool.<name> — the cache adapter (public).
• flow.postgresql.cache.pool.<name>.catalog_provider — tagged flow.postgresql.catalog_provider, so the table is included in flow:migrations:diff.

3. Wire the pools into Symfony's cache framework via cache.adapter.psr6:

# config/packages/framework.yaml
framework:
  cache:
    pools:
      cache.app:
        adapter: cache.adapter.psr6
        provider: flow.postgresql.cache.pool.app

      cache.sessions:
        adapter: cache.adapter.psr6
        provider: flow.postgresql.cache.pool.sessions

The cache.adapter.psr6 wrapper is required because Symfony's CachePoolPass overwrites the first constructor argument of any service used directly as adapter:, which conflicts with this bridge's strict Client typing on argument 0.

4. Generate and run the migration to create the cache tables:

php bin/console flow:migrations:diff
php bin/console flow:migrations:migrate

#Configuration Options (per pool)

#Sharing the Connection

By default each pool opens its own pg_connect derived from the named connection's parameters. That isolation is what keeps $cache->save(...) from being rolled back if the caller's transaction fails.

Set share_connection: true to reuse the existing flow.postgresql.<connection>.client service instead. Trade-offs:

• Pro: one fewer pg_connect per worker per pool. Useful when connection budget is tight.
• Con: cache writes participate in whatever transaction the calling code happens to be inside. A rollback on the caller's transaction also rolls back the cache write.

Use share_connection: true only when you have an explicit reason to share state (e.g. transactional outbox, cache writes that should logically belong to the caller's unit of work).

#Pruning

Schedule the standard Symfony command on a cron to remove expired rows:

php bin/console cache:pool:prune

Without pruning, expired rows accumulate in the table. They are filtered out on read but are not deleted until either the same key is fetched again or cache:pool:prune runs.

For full documentation, see the Symfony PostgreSQL Cache Bridge.

#Symfony Session Integration

The bundle integrates with flow-php/symfony-postgresql-session-bridge to provide a Symfony session handler backed by Flow's native PostgreSQL client — no PDO or Doctrine DBAL required. The table layout is byte-compatible with PdoSessionHandler, so existing session rows can be reused as-is.

The handler is wired with the named connection's ConnectionParameters and opens its own pg_connect, isolated from the Client services used by application code. This is what makes LOCK_TRANSACTIONAL safe by default — the session's transaction lives on a dedicated connection and never wraps unrelated request work.

#Setup

1. Install the session bridge:

composer require flow-php/symfony-postgresql-session-bridge:~0.37.0

2. Enable the session handler:

flow_postgresql:
  connections:
    default:
      dsn: '%env(DATABASE_URL)%'

  session:
    enabled: true
    connection: default            # optional; defaults to the first connection
    table_name: sessions           # default
    schema: public                 # default
    lock_mode: transactional       # one of: none | advisory | transactional
    ttl: 86400                     # optional; falls back to session.gc_maxlifetime

Enabling the section registers three services:

• flow.postgresql.session.handler — the FlowPostgreSqlSessionHandler (public, also aliased to \SessionHandlerInterface).
• flow.postgresql.session.catalog_provider — tagged flow.postgresql.catalog_provider, so the sessions table is included in flow:migrations:diff.
• flow.postgresql.session.purge_command — see Purging Sessions.

3. Wire the handler into Symfony's session framework:

# config/packages/framework.yaml
framework:
  session:
    handler_id: flow.postgresql.session.handler
    cookie_secure: auto
    cookie_samesite: lax

4. Generate and run the migration to create the sessions table:

php bin/console flow:migrations:diff
php bin/console flow:migrations:migrate

#Configuration Options

#Sharing the Connection

By default the handler opens its own pg_connect derived from the named connection's parameters. That isolation is what keeps the session handler's transactions / locks from leaking into the connection used by application code.

Set share_connection: true to reuse the existing flow.postgresql.<connection>.client service instead. Use only when you have an explicit reason — tight connection budget or a deliberate need to keep session state on the same connection as application code.

#Purging Sessions

PHP's normal probabilistic GC (session.gc_probability) already triggers expired-row cleanup at request close, so most applications don't need extra wiring. For deterministic cleanup on a cron, the bundle exposes:

# Delete only sessions whose sess_lifetime is in the past (default behavior)
php bin/console flow:postgresql:session:purge

# Or explicitly:
php bin/console flow:postgresql:session:purge --expired

# Wipe every session (logs everyone out — destructive)
php bin/console flow:postgresql:session:purge --all

--expired and --all are mutually exclusive; passing both returns a non-zero exit code.

For full documentation, see the Symfony PostgreSQL Session Bridge.

#Test Transaction Rollback

The bundle integrates with flow-php/phpunit-postgresql-bridge to automatically wrap each PHPUnit test in a database transaction and roll it back after the test finishes — keeping your test database clean without manual teardown.

#Setup

1. Install the PHPUnit bridge:

composer require --dev flow-php/phpunit-postgresql-bridge:~0.37.0

2. Register the PHPUnit extension in phpunit.xml.dist:

<extensions>
    <bootstrap class="Flow\Bridge\PHPUnit\PostgreSQL\PostgreSQLExtension"/>
</extensions>

3. Enable transaction rollback per connection in a test-environment config:

# config/packages/test/flow_postgresql.yaml
flow_postgresql:
  connections:
    default:
      test_transaction_rollback: true
    readonly:
      test_transaction_rollback: false  # default, no wrapping

When test_transaction_rollback is true, the bundle replaces PgSqlClient::connect with StaticClient::connect for that connection. This ensures the exact same Client instance is reused across kernel reboots within the same test, so the transaction started by the PHPUnit extension covers all database operations performed by your services.

#How It Works

1. PHPUnit starts → extension enables StaticClient caching
2. Before each test → extension rolls back the previous transaction and begins a new one
3. Symfony kernel boots → bundle creates the client via StaticClient::connect() → returns the cached instance with an active transaction
4. Test runs → all queries go through the same cached client, inside the transaction
5. Next test starts → extension rolls back all changes from the previous test

PostgreSQL supports transactional DDL, so even CREATE TABLE and ALTER TABLE statements are rolled back.

#Skipping Rollback

Use the #[SkipTransactionRollback] attribute to opt out for specific tests, classes, or abstract parent classes. See the PHPUnit PostgreSQL Bridge documentation for details.

#Complete Example

# config/packages/flow_postgresql.yaml
flow_postgresql:
  connections:
    default:
      dsn: '%env(DATABASE_URL)%'

      telemetry:
        service_id: "flow.telemetry"
        trace_queries: true
        trace_transactions: true
        collect_metrics: true
        log_queries: false
        max_query_length: 1000

  messenger:
    enabled: true

  cache:
    pools:
      app:
        table_name: cache_app
        default_lifetime: 3600

  migrations:
    enabled: true
    directory: "%kernel.project_dir%/migrations"
    namespace: "App\\Migrations"
    table_name: "flow_migrations"
    table_schema: "public"
    all_or_nothing: false
    generate_rollback: true

  catalog_providers:
    - catalog:
        schemas:
          - name: "public"
            tables:
              - name: "users"
                columns:
                  - name: "id"
                    type: { name: "int4", schema: "pg_catalog" }
                    nullable: false
                  - name: "email"
                    type: { name: "varchar", schema: "pg_catalog" }
                    nullable: false
                  - name: "created_at"
                    type: { name: "timestamptz", schema: "pg_catalog" }
                    nullable: false

Found a typo or an outdated section? Edit this page on GitHub ↗