Several Symfony projects in one database but different Postgres schemas

Question

I'm trying to setup environment in which several Symfony REST API projects (microservices actually) live in single Postgres database but occupy different schemas in DB.

There is no problem to use @ORM\Table to define schema but I found out that migration_versions table is still living in public schema. And blindly hitting 'Yes' while applying migration in the second project just removes the table for the first one.

Of course, I could manually trim generated migration class not to allow execute DROP TABLE statement. But is it possible to setup Doctrine to use custom schema for storing migration_versions table thus completely isolating one project from the others within a single database?

Doctrine prefix does not apply to migrations table ? doctrine-project.org/projects/doctrine-orm/en/2.6/cookbook/… — Yoann MIR
– Yoann MIR, Commented Jul 16, 2019 at 14:55

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

According to symfony Documentation, you can name migrations table on each of your project, maybe using the same prefix you use to separate your tables.

 # config/packages/doctrine_migrations.yaml
doctrine_migrations:
    dir_name: '%kernel.project_dir%/src/Migrations'
    namespace: DoctrineMigrations
    table_name: 'migration_versions' // Use a custom name for each application ? 
    column_name: 'version'
    column_length: 14
    executed_at_column_name: 'executed_at'
    name: 'Application Migrations'
    # available in version >= 1.2. Possible values: "BY_YEAR", "BY_YEAR_AND_MONTH", false
    organize_migrations: false
    # available in version >= 1.3. Path to your custom migrations template
    custom_template: ~
    all_or_nothing: false

https://symfony.com/doc/master/bundles/DoctrineMigrationsBundle/index.html

Let's say your project tables start with myproject_ prefix. To prevent migrations from removing your other tables, you can use doctrine dbal schema_filter property in your config.yml/doctrine.yml

config.yml

doctrine:
    dbal:
        schema_filter: "/^myproject_/"

According to symfony documentation : # If set to "/^sf2_/" all tables not prefixed with "sf2_" will be ignored by the schema tool. This is for custom tables which should not be altered automatically.

So that should do the trick for your use case

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Jul 16, 2019 at 15:25

Yoann MIR

8215 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

erop Over a year ago

Thank you! I'll try to use dot-notation in table_name property to move migration_versions in desired schema. Thanks again!

erop Over a year ago

Hmm... It may be strange by Doctrine generates DROP TABLE statements for neighbor project. Both for entity table and migraion_versions_<project_name>. Again, it could be wiped out manually. But why does Doctrine care too much about tables not related to current project?

Yoann MIR Over a year ago

Look at the last section of the documentation about the doctrine dbal filter property. It may do the trick if you think of a regular expression that says : any table name not starting with my project prefix ...

erop Over a year ago

Looks like it works well only for the first time but when I switch back to first project and start adding properties to an entity and ./bin/console make:migration it could not generate it because Duplicate table: 7 ERROR: relation "migration_versions_first" already exists . "first" is a postfix for migration_versions table and prefix for entity tables created in "first" schema. Need some debugging trying to find out the actual reason.

erop Over a year ago

Same situation with the second experiment project: while make:migration it wants to re-create migration_versions_<project_postfix> table despite of table_name properties set in doctrine-migrations.yaml

|

Julien ARBEY · Accepted Answer · 2021-12-26 16:52:29Z

0

For Doctrine Migration v3.x take care configuration has changed !

table_name: your_table_name

as been replaced by

table_storage:
    table_name: your_table_name

Doctrine migration documentation here : https://www.doctrine-project.org/projects/doctrine-migrations/en/3.3/reference/configuration.html

If you use doctrine-migration-bundle it will looks like

doctrine_migrations:
    storage:
        table_storage:
            table_name: your_table_name

Doctrine migration bundle documentation here : https://symfony.com/bundles/DoctrineMigrationsBundle/current/index.html#configuration

edited Dec 26, 2021 at 16:52

answered Dec 26, 2021 at 16:41

Julien ARBEY

236 bronze badges

Collectives™ on Stack Overflow

Several Symfony projects in one database but different Postgres schemas

2 Answers 2

config.yml

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

config.yml

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related