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

[ghost]

+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.

[ghost]

@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

[ghost]

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.

[laurinkeithdavis]

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

#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?

[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-dev]

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