Symfony 4 sqlite database creation for test environment

Question

For integration test purpose I am trying to create an sqlite db for test env but when I run php bin/console doctrine:schema:create --env=test I get errors that tables already exists. I suppose because it is not really creating a new test db but going on the existing db.

Looks like it is not reading from env.test.

I created a doctrine.yaml in config/test like this:

doctrine:
dbal:
    driver: 'pdo_sqlite'
    url: 'sqlite:///%kernel.project_dir%/var/data/test.sqlite'

What I am missing to create an sqlite test db?

Strange thing I get in the error:

In PDOConnection.php line 90:

 SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'user_menu' already exists 

Why SQL? Should not be IT for sqlite, am I right?

In config/packages/doctrine.yaml, I have:

parameters:
# Adds a fallback DATABASE_URL if the env var is not set.
# This allows you to run cache:warmup even if your
# environment variables are not available yet.
# You should not need to change this value.
env(DATABASE_URL): ''

doctrine:
dbal:
    # configure these for your database server
    connections:
            gui:
                driver: 'pdo_mysql'
                server_version: '5.7'
                charset: utf8mb4
                default_table_options:
                    charset: utf8mb4
                    collate: utf8mb4_unicode_ci
                url: '%env(resolve:DATABASE_GUI_URL)%'
            upv6:
                driver: 'pdo_mysql'
                server_version: '5.7'
                charset: utf8mb4
                default_table_options:
                    charset: utf8mb4
                    collate: utf8mb4_unicode_ci
                url: '%env(resolve:DATABASE_UPV6_URL)%'
orm:
    auto_generate_proxy_classes:  true # <- change to true
    proxy_dir:            '%kernel.cache_dir%/doctrine/orm/Proxies'
    proxy_namespace:      Proxies

    entity_managers:
        gui:
            connection: gui
            mappings:
                Gui:
                    is_bundle: false
                    type: annotation
                    dir: '%kernel.project_dir%/src/Entity/Gui'
                    prefix: 'App\Entity\Gui'
                    alias: Gui
        upv6:
            connection: upv6
            mappings:
                Upv6:
                    is_bundle: false
                    type: annotation
                    dir: '%kernel.project_dir%/src/Entity/Upv6'
                    prefix: 'App\Entity\Upv6'
                    alias: Upv6

My doctrine settings:

doctrine:
dbal:
    connections:
        gui:
            driver: pdo_mysql
            server_version: '5.7'
            charset: utf8mb4
            default_table_options:
                charset: utf8mb4
                collate: utf8mb4_unicode_ci
            url: '%env(resolve:DATABASE_GUI_URL)%'
            host: localhost
            port: null
            user: root
            password: null
            logging: true
            profiling: true
            options: {  }
            mapping_types: {  }
            slaves: {  }
            shards: {  }
        upv6:
            driver: pdo_mysql
            server_version: '5.7'
            charset: utf8mb4
            default_table_options:
                charset: utf8mb4
                collate: utf8mb4_unicode_ci
            url: '%env(resolve:DATABASE_UPV6_URL)%'
            host: localhost
            port: null
            user: root
            password: null
            logging: true
            profiling: true
            options: {  }
            mapping_types: {  }
            slaves: {  }
            shards: {  }
        default:
            driver: pdo_sqlite
            url: '%env(resolve:DATABASE_URL)%'
            host: localhost
            port: null
            user: root
            password: null
            logging: true
            profiling: true
            options: {  }
            mapping_types: {  }
            default_table_options: {  }
            slaves: {  }
            shards: {  }
    default_connection: default
    types: {  }
orm:
    auto_generate_proxy_classes: true
    proxy_dir: /var/www/symfony/var/cache/test/doctrine/orm/Proxies
    proxy_namespace: Proxies
    entity_managers:
        gui:
            connection: gui
            mappings:
                Gui:
                    is_bundle: false
                    type: annotation
                    dir: /var/www/symfony/src/Entity/Gui
                    prefix: App\Entity\Gui
                    alias: Gui
                    mapping: true
            query_cache_driver:
                type: array
                namespace: null
                cache_provider: null
            metadata_cache_driver:
                type: array
                namespace: null
                cache_provider: null
            result_cache_driver:
                type: array
                namespace: null
                cache_provider: null
            class_metadata_factory_name: Doctrine\ORM\Mapping\ClassMetadataFactory
            default_repository_class: Doctrine\ORM\EntityRepository
            auto_mapping: false
            naming_strategy: doctrine.orm.naming_strategy.default
            quote_strategy: doctrine.orm.quote_strategy.default
            entity_listener_resolver: null
            repository_factory: doctrine.orm.container_repository_factory
            hydrators: {  }
            filters: {  }
        upv6:
            connection: upv6
            mappings:
                Upv6:
                    is_bundle: false
                    type: annotation
                    dir: /var/www/symfony/src/Entity/Upv6
                    prefix: App\Entity\Upv6
                    alias: Upv6
                    mapping: true
            query_cache_driver:
                type: array
                namespace: null
                cache_provider: null
            metadata_cache_driver:
                type: array
                namespace: null
                cache_provider: null
            result_cache_driver:
                type: array
                namespace: null
                cache_provider: null
            class_metadata_factory_name: Doctrine\ORM\Mapping\ClassMetadataFactory
            default_repository_class: Doctrine\ORM\EntityRepository
            auto_mapping: false
            naming_strategy: doctrine.orm.naming_strategy.default
            quote_strategy: doctrine.orm.quote_strategy.default
            entity_listener_resolver: null
            repository_factory: doctrine.orm.container_repository_factory
            hydrators: {  }
            filters: {  }
    resolve_target_entities: {  }

Answer 1

The issue is caused by the way Symfony merges configuration files.

/config/packages/*.yml > /config/packages/<env>/*.yml

This results in all of the connections and entity managers defined in packages/doctrine.yml being added to your packages/test/doctrine.yml

To see the merged configuration settings Symfony will use, run:

php bin/console --env=test debug:config doctrine

Because of this running bin/console --env=test doctrine:schema:create will attempt to create the schema for ALL of the entity managers present in the resulting configuration.

To resolve the issue you will need to segregate your environment configs into prod, test, and dev or alternatively use .env.test to change the URLs used by Syfmony.

Depending on how you need to access your entity managers

Example

This is only an example of how to segregate the environment configurations, be sure to change any needed values for your application requirements

config/packages/doctrine.yml

parameters:
    # Adds a fallback DATABASE_URL if the env var is not set.
    # This allows you to run cache:warmup even if your
    # environment variables are not available yet.
    # You should not need to change this value.
    env(DATABASE_URL): ''

doctrine:
    orm:
        auto_generate_proxy_classes:  true # <- change to true
        proxy_dir:            '%kernel.cache_dir%/doctrine/orm/Proxies'
        proxy_namespace:      Proxies

config/packages/dev/doctrine.yml

doctrine:
    dbal:
        # configure these for your database server
        connections:
                gui:
                    driver: 'pdo_mysql'
                    server_version: '5.7'
                    charset: utf8mb4
                    default_table_options:
                        charset: utf8mb4
                        collate: utf8mb4_unicode_ci
                    url: '%env(resolve:DATABASE_GUI_URL)%'
                upv6:
                    driver: 'pdo_mysql'
                    server_version: '5.7'
                    charset: utf8mb4
                    default_table_options:
                        charset: utf8mb4
                        collate: utf8mb4_unicode_ci
                    url: '%env(resolve:DATABASE_UPV6_URL)%'        
        entity_managers:
            gui:
                connection: gui
                mappings:
                    Gui:
                        is_bundle: false
                        type: annotation
                        dir: '%kernel.project_dir%/src/Entity/Gui'
                        prefix: 'App\Entity\Gui'
                        alias: Gui
            upv6:
                connection: upv6
                mappings:
                    Upv6:
                        is_bundle: false
                        type: annotation
                        dir: '%kernel.project_dir%/src/Entity/Upv6'
                        prefix: 'App\Entity\Upv6'
                        alias: Upv6

config/packages/test/doctrine.yml

doctrine:
    dbal:
        driver: 'pdo_sqlite'
        url: 'sqlite:///%kernel.project_dir%/var/data/test.sqlite'

    #DEFINE THE ENTITY MANAGERS TO USE THE default CONNECTION
    #orm:
        #since the connections are not the same
        #you need to define your entity managers here...
        #entity_managers:
     #      gui:
     #          connection: default #<---- NOTICE DEFAULT and not gui 
     # (not sure what entity managers are needed for sqlite)
     #...

config/packages/prod/doctrine.yml

imports:
    - { resource: '../dev/doctrine.yaml' }

doctrine:
    orm:
        auto_generate_proxy_classes: false #<-- change to false
        metadata_cache_driver:
            type: service
            id: doctrine.system_cache_provider
        query_cache_driver:
            type: service
            id: doctrine.system_cache_provider
        result_cache_driver:
            type: service
            id: doctrine.result_cache_provider
     #...