Phinx: Multiple Databases

[wpillar]

It would be cool if Phinx could handle migrations for different databases all from the same instance. In our company setup we have one application which uses n databases, with different migrations for each that need to be handled.

What are you thoughts on handling multiple databases? I might fork and see if I can get it working but I want to know if you'd ever merge something like that?

[robmorgan]

Hi @wpillar, in it's present state you can "hack" Phinx to support multiple databases, but only using raw SQL. We would have to do work to improve the native PHP functions. Feel free to fork, if enough people wanted this feature then I would consider adding it.

[wpillar]

Ah I see, I'll implement a nice way to specify the database from each migration.

There's a step further we can take to really make this the only migrations library you'll ever need, the ability to override default environments on a per-database basis. For example, with our application we have n databases across n servers, would be amazing if Phinx could handle migrations for all of that.

What do you think?

[ivigasin]

It is really necessary option

[farnoux]

+1 for this feature.

[eko3alpha]

+1 for this feature!

[kornrunner]

+1 for this feature!

[Spadbob]

+1, would love this feature!

[dragoonis]

As per our email @robmorgan - I would like to see multiple databases per environment, and have migrations only executed for a specific environment.

I'd like to see environment sub-folders as an option, such as:

migrations/users/file.php
migrations/products/file.php
migrations/file.php

Where 'users' and 'products' are different environments and thus have different databases, however if you look at the third option it's global implying it will be executed for all environments.

What are your thoughts here @robmorgan ?

[robmorgan]

Ok guys the hardest thing for me to visualize with this feature is how we manage multiple db's across multiple environments? I see environments as 'testing', 'staging' & 'production'. 99% of the time I want to run the same migrations on each environment to develop, test and finally release it. I believe people want to introduce the concept of conditional migrations that may/may not run depending on the env?

[wpillar]

So I actually ended up writing our own migration library with multiple DB support, it's not public yet though. The way we handled multiple DBs is with config files for each environment.

config_dev.yml
config_staging.yml
config_live.yml

Then we passed those config files to the console application:

/bin/migrate migrate config_dev.yml

And use our build tool (Jake) to wrap that command for each environment so we run one command and it figures out what environment it is.

And in each of those files it the connection details for all the databases. I don't think migrations should be conditional on environment and I don't see why that would be the desired functionality? Surely if you're making a schema change and going from dev -> live it needs to go through the release pipeline and be the same for each environment?

[eko3alpha]

I was thinking maybe this could work. Allow for a directory structure like so:

/migrations/ [ database name ] / [ phinx yml config]

/migrations/db-1/phinx.yml
/migrations/db-2/phinx.yml
/migrations/db-3/phinx.yml

and when creating a new migration for db-1 you could do something like this

/bin/phinx db-1 create RenameLabelToAttribute

this would create a new file

/migrations/db-1/20140330133629_rename_label_to_attribute.php

And to migrate

/bin/phinx db-1 migrate -e staging

Just an idea, I was thinking about this last. A project im working on requires 3 db's and was trying to find easier ways to do this. The only solution I found that works right now is to create 3 composer projects for the 3 databases with only phinx. And in each project directory do this

php vendor/bin/phinx migrate -e testing

it works, not ideal but it works.

[CowChris]

Create an alias for each database and reference the alias in the SQL. At least this'd work for SQL (believe flywaydb.org uses this and it's possible using Phing's DBDeploy using placeholders).

The resulting SQL looks something like this:

placeholder.my_db_alias = biscuits;

USE ${my_db_alias}; DROP TABLE test_table;

[baversjo]

+1

[oniric85]

+1

[adam-paterson]

+1

[BardiaAfshin]

+1 This is exactly what I'm implementing, since it's on the issue. Here's a question - I tried posting this as a new Issue but the submit button was disabled (Github bug?)

I'm bamboozled by this issue I'm having.

I'm writing a method that consumes the phinx applicaiton, and passing arguments to it. It works!!!

What doesn't work is stubbing/mocking the method in my test so I can TDD it.

class.MyCommand.php looks like this (something like this):

class MyCommand extends Command {
    public function runMigrations(InputInterface $input, OutputInterface $output, $target, $app, $environment, $configuration)
    {
        $phinx_application = new PhinxApplication('1');
        $migrate_command = $phinx_application->find('migrate');


        $configuration = (strlen($configuration) > 0 ? $configuration :__DIR__ . "/../apps/$app/phinx.yml");

        if ($target == '' || is_null($target)) {
            $arguments = array(
                'command' => 'migrate',
                '--configuration' => $configuration,
                '--environment' => "$environment"
            );
        } else {
            $arguments = array(
                'command' => 'migrate',
                '--configuration' => $configuration,
                '--environment' => "$environment",
                '--target' => "$target"
            );
        }

        $input = new ArrayInput($arguments);
        $returncode = $migrate_command->run($input, $output);


        return $returncode;
    }
}

class.MyCommandTest.php looks like this:

require_once '../src/MyCommand.php'
class MyApplicationTest extends PHPUnit_Framework_TestCase {

    protected $application;
    protected $applicationTester;

    public function setUp() {
        $this->application = new SQLUp\Console\SQLUpApplication('testing','testing');
        $this->application->setAutoExit(false);
        $this->application->setCatchExceptions(false);

        $this->applicationTester = new ApplicationTester($this->application);

    }

      public function test_enough_arguments() {


        $stub = $this->getMock('\\MyApplication\\Console\\MyCommand');
        $stub->expects($this->any())
            ->method('runMigrations')
            ->will($this->returnValue(0));



        $this->applicationTester->run(
            array(  'command' => 'update',
                    'app' => 'web',
                    'environment' => 'testing',
                    '--configuration' => __DIR__.'/fixtures/phinx.yml')
        );

        $this->assertContains('Finished with no errors.',
            $this->applicationTester->getDisplay());

    }
}

I have exhausted all resources, even looked through Github instances mentioning applicationTester and Phinx to see if anyone else has written the solution for this problem. When I run my tests (PHPUNIT 4.3.1) I get

There was a problem connecting to the database: SQLSTATE[HY000] [2002] No such file or directory.
Time: 41 ms, Memory: 6.00Mb


FAILURES!
Tests: 1, Assertions: 0, Errors: 1.

Process finished with exit code 2

The key mock/stub I want to accomplish is to have runMigrations return the value 0, this is a success, which means I don't care what it does, just return 0 so I can move forward with the System Under Test, and verify it's unit test.

[BardiaAfshin]

@wpillar have you seen https://github.com/Pajk/PHPDeploy

[alekseyp]

+1

[jamespsterling]

I think something like the below configuration would let us define multiple databases to apply the migrations to, but then log to some global database.

  environments: 
    default_database: Global
    default_migration_table: PhinxLog
    development: 
      adapter: mysql
      charset: utf8
      host: localhost
      name: 
        - one
        - two
        - three

[BardiaAfshin]

The configuration you are proposing doesn't fit being able to drive multiple databases with different revisions. Since sharing the same PhinxLog, you'd be dependent on collapsing the sequencing migrations after another instead hence removing a major flexibility. Unless, that's not a requirement. Then, why not.

[laurin1]

I know this is kind of a hijack, and apologies....

https://github.com/robmorgan/phinx/issues/515

Actually, having the phinxlog table as part of the same database is a problem for us as well and if we moved instead had a structure like this, maybe it would that part of this issue as well:

• schema: db1
• schema: db2
• schema: phinxlog
  ** table: db1 (phinxlog table for db1)
  ** table: db2 (phinxlog table for db2)

[davidjdixon]

Phinx already has the ability to run migrations across different databases without any kind of hack (although the process to set up and use is a little manual/ugly). The solution is simply to use a different config file for each database, and set the migration path to a different sub folder for each DB. Each DB will then have its own migration path and phinxlog table for migration history.

To reduce the amount of code duplication for the configs, I switched my example to use PHP config files. My example also uses $_SERVER vars, as the config in my examples comes from a capistrano integration, but you should get the gist.

file structure:

migrations/
| - db1/
| - db2/
| - (etc)

db1.php:

$_SERVER['PHINX_DBNAME'] = 'castle';
// set other params here

return include 'phinx.php';

phinx.php

return [
    'paths' => [
        'migrations' => 'migrations/' . (isset($_SERVER['PHINX_DBNAME']) ? $_SERVER['PHINX_DBNAME'] : ''),
    ],
    'environments' => [
        'default_migration_table' => 'phinxlog',
        'default_database' => 'default',
        'default' => [
            'adapter' => 'mysql',
            'host' => isset($_SERVER['PHINX_DBHOST']) ? $_SERVER['PHINX_DBHOST'] : '',
            'name' => isset($_SERVER['PHINX_DBNAME']) ? $_SERVER['PHINX_DBNAME'] : '',
            'user' => isset($_SERVER['PHINX_DBUSER']) ? $_SERVER['PHINX_DBUSER'] : '',
            'pass' => isset($_SERVER['PHINX_DBPASS']) ? $_SERVER['PHINX_DBPASS'] : '',
            'port' => 3306,
            'charset' => 'utf8',
        ],
    ],
];

This way you can just run your migrations (or other commands) with:

phinx migrate -c db1.php

[rbalik]

+1

[kimsyversen]

+1

[amfriedman]

I think the simplest and most elegant way for phinx to accomplish managing multiple databases is to allow me to couple the migration path with each one of my 'environments' in my config file instead of one global migration path per config file. This would allow me to continue using just one config file to handle multiple databases. For instance (YML style):

paths:
    # Have this change from "migrations" to "default_migrations"
    default_migrations: /path/to/phinx/migrations/db1

environments:

    default_migration_table: phinxlog
    default_database: sandbox

    production:
        adapter: mysql
        host: localhost
        name: db1

    staging:
        adapter: mysql
        host: localhost
        name: db1_staging

     production_other_thing:
        # Allow me to add "migrations" path here to override the default
        migrations: /path/to/phinx/migrations/db2
        adapter: mysql
        host: localhost
        name: db2

    staging_other_thing:
        # Allow me to again add "migrations" path here to override the default
        migrations: /path/to/phinx/migrations/db2
        adapter: mysql
        host: localhost
        name: db2_staging

[VeryStrongFingers]

+1 @amfriedman
Per-environment configuration of 'migrations' path would be great.

environments:
    default_migration_table: phinxlog
    default_database: app1
    app1:
        adapter: mysql
        host: localhost
        name: production_db
        user: root
        pass: ''
        port: 3306
        charset: utf8
        paths: %%PHINX_CONFIG_DIR%%/migrations/app1
    app2:
        adapter: mysql
        host: localhost
        name: production_db
        user: root
        pass: ''
        port: 3306
        charset: utf8
        paths: %%PHINX_CONFIG_DIR%%/migrations/app2

[niksamokhvalov]

+1 for this feature!

[cdburgess]

For multi-tenant databases, we need this feature as well. Interestingly, we cannot manage our various environments outside of their own environment. (i.e. production cannot be accessed outside of the production servers etc.)

To manage the multi-tenancy, I used the environment variable to store all of the databases and their connections. It works like a charm!

• Used a .php file configuration to connect to the system that contains the database connection information and loaded each database config in the environment section of the config

return [
    "paths" => [
        "migrations" => "{PATH_TO_MIGRATIONS}"
    ],
    "environments" => [
        "default_migration_table" => "{MIGRATION_TABLE_NAME}",
        "{DB1}" => [
            "adapter" => "mysql",
            "host" => $host,
            "name" => {DB1},
            "user" => $username,
            "pass" => $password,
        ],
        "{DB2}" => [
            "adapter" => "mysql",
            "host" => $host,
            "name" => {DB2},
            "user" => $username,
            "pass" => $password,
        ],
               ...
    ],
];

• I leave out the default_database setting in the config so no database will migrate without specifying it
• Added a snippet of code to the Migrate.php file to recursively loop over the execute to run this on every database in the environment when you run phinx migrate -e all

around line 74 of Migrate.php right after $environment = $input->getOption('environment');

if ($environment == 'all') {
            $startAll = microtime(true);
            foreach (array_keys($this->config['environments']) as $environmentName) {
                if ($environmentName == 'default_migration_table') {
                    continue;
                }
                $input->setOption('environment', $environmentName);
                $this->execute($input, $output);
            }
            $endAll = microtime(true);
            $output->writeln('<comment>All databases complete. Took ' . sprintf('%.4fs', $endAll - $startAll) . '</comment>');
            return;
 }

• to update any database one at a time, simple pass the name of the database in the environment option in the command line.

phinx migrate -e {DB1}

If you don't want to have to think about the code, you can get the updated file from my fork here: https://github.com/cdburgess/phinx/blob/0.4.x-dev/src/Phinx/Console/Command/Migrate.php

_note:_ This is just a quick hack for migrations. It will not be merged into the main code... ever.

[m1ome]

How should be consistency done in this?
When a DB2 failed transaction and DB1 already commited it?
What should we do? We will break a db structure/data after migration?

[cdburgess]

Hey @robmorgan as I look through the code, I am seeing that environment is pretty much synonymous with database. In fact, the getEnvironment() method refers the the default_database in the environment section of the configuration. Does this mean that if we want to extend phinx to run migrations on n multi-tenant databases, we should set each database as an environment? Or would you prefer to have a new variable under each environment called databases that correspond to a list of databases for that environment?

Right now it's hard to tell which direction one would go to extend the code because environment and database are used somewhat interchangeably. I have a few days of development cycles to dedicate to this issue. I think I can achieve something worth while, but I would love your direction.

[cdburgess]

@m1ome I have updated the code so it will manage status, rollback, and migration as I described above. In addition, it will try/catch any error on migrate/rollback so the they will continue as normal for the rest of the databases. see: https://github.com/cdburgess/phinx-mt

[ralphschindler]

Another note to point out: not all commands accept an environment flag. For example create does not take environment into account, so you couldn't use that to create a specific migration for a specific database/environment.

[cdburgess]

You are correct @ralphschindler! However, that is already inherent in the system. The current way to work around that is to use a different config for the databases that need a different set of migrations.
phinx create {MigrationName} -c {config_file}

However, in a multi-tenancy environment, you are likely to want all of the databases to reflect the same schema as they will likely be running from the same app code. If this is not the case, you will need a separate config file for each one anyway.

[eko3alpha]

I know I'm one of the people that requested a multiple db feature. However, after using Phinx for a while, its easy to do migrations on multiple databases without any hacks or updates.

Here is my working directory structure:

/migrations/
            /db1/
                   /migrations/
                   /phinx.yml
            /db2/
                   /migrations/
                   /phinx.yml
            /db3/
                   /migrations/
                   /phinx.yml

Open terminal in each one of the db directories and execute the migration.

eko3alpha:/migrations/db1/$ phinx migrate -e testing

I've grown to love this approach for its clear separation. Don't forget to install Phinx globally and run the migrate command inside the respective directory.

Thanks @robmorgan for your awesome work!

[robmorgan]

@eko3alpha thx for your suggestion. Indeed its probably the best work around for now. I'll refer people to this until we consider something more integrated.

[niksamokhvalov]

I use Phinx migrations for multiple databases. My structure directory:

migrations/
     .db1/
     .db2/
     .db3/
     db1.yml
     db2.yml
     db3.yml

Console commands for migrate one DB:

php phinx:migrate -c migrations/db1.yml

Migrate all DB:

php phinx:migrate all

Console command for executing migrations of the all shards: https://gist.github.com/niksamokhvalov/3dace9bee42bc76c5f8a

[daverogers]

@eko3alpha - great solution. That worked for me :+1:

[mnpenner]

I need this feature too, but I think my setup is different than everyone else's.

I want to run _the same_ migrations across _many_ databases (~400), except in some circumstances where a few DBs should be excluded.

The way I see this working is that each migration file should have a shouldRun($databases) method wherein you can filter through the list of databases before the migration is run. Phinx would need to keep track of which databases it has been ran on/which ones it's skipped.

I would want this migration data stored in its own DB so that it doesn't have to connect to 400 different DBs to figure it out.

[JCMais]

Have same setup than @mnpenner, I'm currently building a SaaS application, and each client has their own database, the databases have the same structure, so there is only one migration that should run in all databases.

[janvandijk]

@mnpenner & @JCMais Same setup here. One master database and 100s+ tenant databases (on the same mysql instance).

We need to be able to both migrate the master database as the tenant databases.

Any progress in making this work? And/or nice hacks / workarounds people have found?

[mnpenner]

@janvandijk I started my own library. I'm not sure I'll be able to open-source it though; it's kinda specific to our platform.

[JCMais]

@robmorgan Any plans for that feature? I see that you got the milestone assigned to it, do you already know how you plan to implement it? If yes, can you share.

[ahmadarif]

+1 for this feature :+1:

[Henk8]

Same setup here. +1 for this feature

[wzrigui]

+1

[nitesh-fiestel]

+1

[thorne51]

+1

Our application uses one master database which is copied for every client added to the system. So when we do a migration on the master database, we would need to apply the same changes to the client system.

Maybe we could possibly specify database/schema as an option to the $this->table() function?

[JCMais]

the best option in my opinion is to pass the databases directly to the migrate command.

[thorne51]

@JCMais I'd need to be able to execute that migrate command from my migration in that case. Would you be able to help out with a code example of how to accomplish this please?

The list of client databases is around 50 and is programmatically created, and retrieved. So what I'd like to do is be able to execute the current migration on an array of database names. Originally I thought of just having the phinxlog table on the main master database and just executing the queries on each of the client databases. But if I can programmatically run the actual migration on the client database with its own phinxlog table that would be fine as well.

Thanks!

[JCMais]

@thorne51 your use case is identical to mine, I misread your initial comment.

offtopic: What a great avatar!

[thorne51]

@JCMais I ended up just using our own database class and executing the queries manually, and essentially using phinx as a wrapper for migrations, with the migrations only being recorded in the main database.

offtopic: Thanks! Got it from the fan site kit a while ago! Yours is great too! Some info on yours ;)

[JCMais]

@thorne51 how are you handling failed migrations?

[thorne51]

@JCMais I only wrap all my DB statements in a try..catch block and output on which client, which migration failed and manually sort it out from there. Not exactly sure of a better way to handle it to be honest?

[webdevbyjoss]

+1

[atippett]

I'm seeing a lot of talk about multiple databases with a sprinkle of multiple schemas. My situation is we have the same schema for each of our customers that we have to push updates to. There might be 100+ schemas but we want to keep them all the same. Has anyone figure how to accomplish this with phinx without having to specify configuration for each schema?

[thorne51]

@atippett Currently I just create SQL scripts and use our own system's database adapter class to run the scripts on multiple databases. In this scenario, I only use phinx to track (on my main database) which migrations have been run and which haven't.

[ghost]

+1

[duraki]

Any update to not using workaround? Pull request? Anything? Jesus, 4 year passed and this issue is still open. Great. Just great.

Edit (for the sake of no-rant only comment):
For a (yet another) bad solution, one can just put several environments flags depending on the database. Unfortunately, this means that some database have unused and unnecessary tables executed while migration.

Example of what I'm talking about:

environments:
  dev_db1:
      adapter: mysql
      ...
      name: database_1

  dev_db2:
      adapter: mysql
      ...
      name: database_2

[lorenzo]

@duraki Maybe the lack of interest in sending a pull request for this feature shows that it is actually not necessary to have it in this repo.

One simple way of having multiple configurations per environment is to have a script swapping the files and then run the executable.

That option also makes phinx a lot more secure, as you won't be committing these files with credentials into git by mistake.

[duraki]

@lorenzo Thank you for the input. I agree this feature did not attract many, maybe since some architectures (eg. microservice) is yet to arise in a PHP-based environments. You can actually see the comments being more regular lately on the issue with the applied tenancy on different projects.

On other hand, I've seen cases of uninterested, since no pull was proposed, or no proper solution was commented for a long time, therefore no change would be made even if commenting/requesting the feature.

Anyway, I hope to see this sometimes bundled with phinx.

Regards.

[rohithb]

Found a workaround
Create migrationRunner.php as follows

require_once __DIR__ . "/../vendor/autoload.php";

$dbs = ['db1', 'db2', 'db3'];  // name of the databases

foreach($dbs as $db){

    $app = new \Phinx\Console\PhinxApplication();

    $_SERVER['PHINX_DBNAME'] = $db;
    $wrap = new \Phinx\Wrapper\TextWrapper($app, array(
                'parser' => 'yaml'
        ));
    $response = $wrap->getMigrate();

    print_r($response);
    unset($wrap); 
    unset($app);
    unset($response);
}

Run

php migrationRunner.php

To execute migrations on all databases in array $db .

phinx.yml

paths:
    migrations: %%PHINX_CONFIG_DIR%%/db/migrations
    seeds: %%PHINX_CONFIG_DIR%%/db/seeds

environments:
    default_migration_table: phinxlog
    default_database: development
    development:
        adapter: mysql
        host: localhost
        name: %%PHINX_DBNAME%%
        user: root
        pass: 'PASSWORD'
        port: 3306
        charset: utf8

version_order: creation

[friebe]

Nice to see your post. Can u post your folder structure pls ?

[rohithb]

@friebe Sure.

project/
├── db/
│   ├── migrations/
│   ├── seeds/
│   └── migrationRunner.php
├── src/
├── vendor/
├── composer.json
└── phinx.yml

the exact command is php db/migrationRunner.php while running from project root.

You need to put %%PHINX_DBNAME%% as name in environment for loading it from $_SERVER variable.

[friebe]

@rohithb thx. it helps a lot. I noticed that the autoloader which load the \Phinx\Console\PhinxApplication() object is missing.

I tryed to put $autoloader = require __DIR__ . '/../robmorgan/phinx/src/composer_autoloader.php'; at the beginning of the migrationRunner. But it dont helps me. Maybe any idea ?

[rohithb]

@friebe I edited the actual post and added the line which imports autoload.php. Please check it.
In order to use composer dependencies, you should include vendor/autoload.php in your project.
You are importing the wrong file.

[friebe]

@rohithb It works find. Save my day.

[ghost]

If we all look at this from the config outwards for now, to tackle multiple databases inside multiple environments:

paths:
  migrations: /migrations
  seeds: /seeds

environments:
  default_migration_table: _migrations
  default_database: development
  production:
  - adapter: mysql
    host: localhost
    name: production_db
    user: root
    pass: ''
    port: 3306
    charset: utf8

  testing:
  - adapter: mysql
    host: localhost
    name: testing_db
    user: root
    pass: ''
    port: 3306
    charset: utf8

  development:
  - adapter: mysql
    host: localhost
    name: development_db
    user: root
    pass: ''
    port: 3306
    charset: utf8

  version_order: creation

This would provide an 'environment' array of database instances.

If this is coupled with the multiple folder setups as described in use cases above:

/databases
    /tenant
        /migrations
        /seeds
        /phinx.yml
    /system
        /migrations
        /seeds
        /phinx.yml

This would allow multiple 'collections' of environments and databases.

---

So, code wise, we put the folder structure to one side. This gives us the following rules to work towards:

• [x] Must be able to migrate multiple environments. (done in phinx already!)
• [ ] Must be able to migrate multiple databases within each environment

Once that is solved, basically the problem is solved. If after that, a wrapper wants to be made to sit outside a folder structure, that can't be too complicated.

I'm starting to look into this, so if anyone knows any caveats I might encounter, please let me know!

[broberts-mrmc]

+1

In the YAML configuration file example, did you intend to provide a multi-environment and database example?

[ghost]

That is the multi environment multi database example, not the dash before the adapter key, this makes anything below it a complete item, so if you were to add another one, it would be separate.

paths:
  migrations: /migrations
  seeds: /seeds

environments:
  default_migration_table: _migrations
  default_database: development
  production:
  - adapter: mysql
    host: localhost
    name: production_db
    user: root
    pass: ''
    port: 3306
    charset: utf8
  - adapter: mysql
    host: localhost
    name: production_db_2
    user: root
    pass: ''
    port: 3306
    charset: utf8

  testing:
  - adapter: mysql
    host: localhost
    name: testing_db
    user: root
    pass: ''
    port: 3306
    charset: utf8

  development:
  - adapter: mysql
    host: localhost
    name: development_db
    user: root
    pass: ''
    port: 3306
    charset: utf8

  version_order: creation

Above is a better example.

[ghost]

This is in fact a lot more complicated than I first imagined.

My intent was to move the functionality of Environment into a new class Database and then run loops inside Environment or Manager to get the correct data. I've already hit the wall

What happens if a migration fails on one database and not another?

I suppose it would be logged in that database, but how would one re-run a single database? I haven't got sufficient an answer for questions like that, so I may have to hold off on this and use Phinx alongside Deployer to get the functionality I need quickly.

[broberts-mrmc]

Some thoughts...

1. Seems commands with support for "-e" (environment) option would need support for a new option, maybe "-n" ([database] name) since "-d" is already used for "date". The new option could be used to specify the specific database [name] within an environment if, for example, with the MIGRATE command, all databases in an environment (default or otherwise) should not to be migrated.

2. I'd assumed the use case for multiple databases was "multiple dissimilar databases" used by a single project (e.g. ERP database, HR database) rather than "multiple similar or identical databases" (e.g. HR database # 1, HR database # 2). In the the former case, it seems it would be less problematic if a migration failed on one database and not the other as the migrations would likely also be dissimilar. You may have a different use case in mind.

[broberts-mrmc]

Also, if you're already bootstrapping your own database configuration using the PHP format phinx configuration, I think a recent pull/merge makes multiple databases possible, though not quite in the same way: #1070

[mvrhov]

Just do multiple environments and prefix each environment with the database name. IMO having multiple databases in one app is not that common.

[ghost]

It is in a multi-tenant application; we have one system database, and n client databases which would need migrating. We would duplicate one or more of these into say development environment, so would need to handle multiple environments with multiple databases inside these.

I wouldn't want to migrate 100+ environments ;P

[duraki]

For some reason, phinx contributors really try hard to avoid this feature request. I mean, multi-tenancy app isn't something new these days. Therefore, we can all agree that this issue became a hot topic since the last update from @lorenzo (ignored it, since the lack of priority and PR).

I'd gladly send a pull request, the problem is, we can't describe a proper design pattern for this case. Can we switch current topic from an actual "issue" (for which we got notice from year ago), and move towards to engineering discussion so we can finally see this in master.

A few of really interesting solution approaches are in comments for this issue, yet we all "hack" the way around it.

[broberts-mrmc]

I think the problem (or need) is simple and clearly defined, but seems there's more than one use case mentioned throughout the comments in this issue. What follows is an attempt to define the use cases (in no particular order):

1. Multi-Tenancy DBMS (i.e. one common database and/or multiple similar/identical databases in one project and for each environment) -- I see this as a reasonable/valid use case, though the original description provided by @wpillar in 2014 doesn't seem to contemplate this.

2. Multiple, Dissimilar Databases (i.e. different databases in one project and for each environment) -- I see this as another reasonable/valid use case. It's the one I have in mind (e.g. one project with an ERP database and HR database; multiple environments too, of course).

These two use cases seem to have some divergent needs. For example, it seems "migrations failures" require more delicate treatment in one case (# 1) and not the other. I think a good starting point would be to clearly define the use case(s) that would/should be addressed in an engineering discussion, design, etc. There may be other uses cases not readily apparent to me. Thoughts?

[dig412]

Thanks @broberts-mrmc, excellent summary. I made a couple of diagrams of these use cases, mostly for my own benefit to help visualise the problem.

Multiple, Dissimilar Databases

As always naming is an issue. Phinx gives us "environment" which works well, but what is the other grouping called? It's called database in this issue, but that's an overloaded term, especially as the Phinx config calls the default environment default_database.

I call this grouping database, with each instance of a database in an environment (app in dev, app in prod, etc) called a schema. As this wording isn't used elsewhere in Phinx I'm not sure how useful this is.

Each coloured band has a separate table structure, separate migrations, and a separate migration log table.

This is the use case that's handled fairly well by having multiple distinct Phinx directories with a config file for each as @eko3alpha suggested a while back.

It probably wouldn't be too difficult to internalise this structure within Phinx, with per-environment and per-database config values for migration directory and migration table, as in this fantasy example:

environments:
  default_migration_table: phinx_migrations
  default_environment: development
  development:
    application_db:
      host: localhost
      schema: application_db
      migration_table: phinx_migrations_application
    user_db:
      host: localhost
      schema: user_db
      migration_table: phinx_migrations_user
  production:
    application_db:
      host: prod.server
      schema: application_db
      migration_table: phinx_migrations_application
    user_db:
      host: prod_user.server
      schema: user_db
      migration_table: phinx_migrations_user

Issues with this - it's quite verbose, as the config is effectively 2D we end up duplicating per-database values for each environment, or per-environment values per DB if we were to flip the structure. Any suggestions?

Multi Tenancy

I'm less clear about what to do with this, as it's not the way our app works or the way I use Phinx.

@johnrobertporter has suggested a config format that makes sense to me - an array of multiple schema connection details to be used. As many have pointed out the issue with this use-case is commit/rollback behaviour across multiple schemas/servers. This looks like the hardest decision to make to resolve this issue.

Should Phinx proceed across all schemas regardless of failure, or should it roll back all in case of a single failure? Perhaps this could be configurable behaviour?

[fengxw]

sad about this issue still is opened! i think it's a common issue for a project composed of many modules. whatever, maybe @eko3alpha 's solution is best for now, and we can make a command to run all the migrations which in different db automatically. anyway, hope to have an official solution.

[JayPHP]

@lorenzo @dereuromark Any thoughts on an approach for this?

I've seen a few PR's which directly relate to this, but i've lost track.

[lorenzo]

@JapSeyz My personal take on this is that we should remove the idea of environments. We could happily transform that into databases

[JapSeyz]

@lorenzo What do you mean? I think you tagged me by mistake instead of JayPHP?

[lorenzo]

@JapSeyz sorry, I did mean to tag him.

[ghost]

@lorenzo I think that's not the right solution. Environments and databases has been confounded due to the fact that Phinx is a single database application; environments is the correct term to use as it talks about local vs production for example. We need to have databases added as a term, and the merging of terms in the codebase needs to be split out properly.

[njake]

@johnrobertporter Agree, environment is not way, environment != databases
We have over 1000 databases dynamically created, environments is not way at all.

[lorenzo]

Would renaming environments to database help? What's being discussed here is the ability to use different configurations, we already have that feature.

[ghost]

Would renaming environments to database help?

This is what I'm trying to say is the wrong approach.

What's being discussed here is the ability to use different configurations, we already have that feature.

The general topic of this thread is about having multiple databases within the environments we use like local, testing, production et al.

I understand that using multiple configuration files is a solution, but for me it's a temporary one at best. @broberts-mrmc and @dig412 have summed it up the best so far.

There needs to be a new concept inside the codebase Database alongside Environment. That will pave the way to the separation of concerns and slowly allow the ability to add multiple databases.

I really wish I had time to offer to really look into this as I would love to give it a go.

[lorenzo]

maybe those who really understand the need for this and why having multiple configuration files is not a solution can contribute the code for it?

I really cannot see how multiple files or templting a single config file using an external script fails to solve this problem.

[rohithb]

Hi, We have been using a wrapper for Phinx to migrate hundreds of databases for our application little over a year.
Check https://github.com/rohithb/phinx-multiple-database .

This is not a neat solution, its just a hack. But I hope this will be helpful.

[jszoja]

I started working on a very simple concept in jszoja/phinx in multidb branch if anybody is interested. So far it worked for both migrations and rollbacks. Testing on mysql only.
It assumes that the all databases resides on the same server, but I believe it can be easily modified to suit distributed databases through multiple servers.

[piotr-cz]

Guys, look at #1241

[lorenzo]

Unfortunately that branch is not up to date and did not pass the tests @piotr-cz

[keithy]

I have looked at these issues, and incuded some solutions in my own PDO library available here:

https://github.com/keithy/primo-pdo-php

The solution I have come up with starts by enabling my code to access Phinx as normal code, not as a wrapped up command line tool. This allows the primo-pdo library to prepare a bespoke $config array, and invoke phinx with it directly, supplying a custom PDO instance.

[I think that this approach could be documented and Phinx refactored so that this interface to Phinx is a typical use case (removing as many dependencies i.e. Console I/O) from the core code.]

Features:

The custom PDO instance has per-adapter helpers, which can iron out some of the differences between database sql implementations. e.g CONCAT vs ||.

Secondly the config file has been adapted to provide lots of cool features, such as:

• merging in settings from a defaults file
• merging global settings into each of the environments
• merging per-adapter settings into environments

This enables other features to be applied on a, for-all, per-adapter or per-environment basis including:

• a specified migration state per adapter/environment (for fixtures)
• the option of per-environment/per-adapter paths
• optional overrides, e.g. 'backup', 'snapshot' (i.e. use each environment with a different database host) for backups.
• and last minute %%PER_CLIENT%% variable substitution.

Example Use:

$test_fixture = $reader
     ->choose('empty')
     ->which('snapshots')
     ->create( $isCleanStart )
     ->copyTo( $reader->choose('empty') );

Selecting the "empty" environment, apply the optional configuration "snapshots", which changes the working directory to a cache of pre-prepared fixtures. In that folder, (optionally) delete any existing database, and create a new one by applying migrations, and seeders as specified within the "empty" environment. Copy the result to overwrite the normal "empty" database, returning the result as the fixture... then we want to use the database in a test case!

$query = ""
$pdo = $test_fixture->pdo()->addLog( function( $sql ) use(&$query) { $query = $sql } );
// prepared stmt
assert( $pdo->run("SELECT count(*) WHERE name IS ?", "Rob")->fetchColumn() == 1 );
assert( $query == "SELECT count(*) WHERE name IS `Rob`" );

This is pretty handy, and requires no changes to Phinx. I think that this approach (or similar) potentially frees Phinx from more complex and use-case specific feature requests.

Phinx could be improved by either adopting some of these ideas, by simplifing or both.

I would suggest a little refactoring, so that internally "ManagerBasic" only accepts a single environment with all of the parameters that it needs, called or subclassed by "Manager", which processes the existing Phinx file formats as it does currently. Then my library and others like it can use ManagerBasic, knowing that the interface will remain stable. Manager can then include improvements that are backwards compatible, or a future Manager2 can break the mold.

If the command line tool is improved to accept a parameter, that selects the Manager* implementation class, it can be used with a range of configuration files, with "ManagerBasic" (single environment array and no frills), "Manager" (default backwards compatible), "Manager2" (including new ideas), or "MyOwnManagerClass". The result being, that now I could have a means for the commandline tool to keep pace with any new features of the custom pdo library I am using.

[keithy]

I have implemented the initial refactoring to which I referred above and have attempted to run the tests. If you are interested the code is available for review at

https://github.com/keithy/phinx/tree/refactored_manager/src/Phinx/Migration

tests status:
https://travis-ci.com/keithy/phinx/builds

[dereuromark]

PR welcome.

[ihommani]

Hi @dereuromark , I didn't get the status of the feature. This thread is closed and points to an enhancement that is closed but unmerged. Is this feature put aside for the moment?
thx

[ihommani]

My personnal opinion on the feature is although it answers direct needs of many users (including myself) I can't stop to think that we may open a pandora's box by including it in the phinx core.
Phinx is designed to be mono DB. More precisely, a Phinx execution/run is monoDB.
That allows the tool to be driven by KISS principles that I really appreciate. Previous comments proposed several quickwins that I find acceptable. As far as I am concerned, I define a phinx.yml per Database (each one in a folder named after de database) and orchestrate the overall seeding/migration through a simple script.

[keithy]

My suggestion would be, to look a bit more closely at the utility that you can get if you implement a few simple suggestions above, particularly accepting a connection array instead of the connection string.

Which is introdiuced here https://github.com/keithy/primo-pdo-php/wiki/ConfigReader-returns-an-Environment

I found the end result satisfying, particularly the ability to use phinx for generating fixtures to test against. (Unfortunately I will not be working with PHP for a good while)

The full refactoring suggested was not difficult but neither was it trival to finish in the time I had available.

example configuration file using the enhancements:

https://github.com/keithy/primo-pdo-php/blob/master/okay/_fixtures/phinx.php