./phinx init or ./phinx setup --- A automatic way to create the databases

[ghost]

I think it'd be a useful feature to have phinx create the databases for testing, and development at least, if they are not already there.

@robmorgan

thoughts?

[robmorgan]

maybe if it was a seperate command or an extra parameter. Definitely not the default behaviour though. Its one step developers should normally provision themselves.

[ghost]

I agree as having a implicit behavior become somewhat confusing. I think having a ./bin/phinx setup is a valid approach on increasing the verbosity of the feature and giving the developer power to consume the tool feature.

[adamski]

Yes this would a good feature to have, in conjunction with detook:schema-dump branch (which we are using here)

[rakshazi]

Hello! Any update on this?

[fncmedia]

Hi @robmorgan, are there any timings for this feature? Thx for Phinx. Best

[robmorgan]

@fncmedia thanks for kind words. No I don't particularly want this feature, but if somebody else wants to work on it then I'd be happy to accept.

[christhomas]

How is this not a feature you'd want? Do you prefer creating the databases by hand? So now on top of creating migrations, I also need to whilst testing use what tool to create the database, then perform the migrations, then test my code?

I'm unclear why this feature is not so blatantly obvious that it should be considered a part of the base feature set? What is the reasoning here?

[josegonzalez]

@christhomas Hi!

While this seems like a nice addition, every feature does have some initial cost. For instance, in this case it's probably just not something the existing maintainers have needed. I personally use TravisCI and they pre-create a database for me - and if not, I'll just use the mysql or psql binary to do so - before I run my migrations, so I would have never needed this "blatantly obvious" feature.

If you want to see this feature implemented in Phinx, I encourage you to submit a pull request with the changes - ideally with tests and documentation! - so that we might review it. If you see an existing pull request with this changeset in it, feel free to ping me and I can take a look and review it.

Thanks for understanding, and I look forward to your contribution!

[ldonis]

Hi, I think this feature can be really useful for test purposes.

Example: while I am creating migrations and seeders I also write foreign keys, so, to run tests again I have to drop tables because I cannot just truncate or delete due foreign keys and drop database is faster than drop tables manually. I know I can write the delete function in the migration itself, but that is a little bit dirty for me.

I can work on this and send a pull request once I have something ready, but due I do not know this project so much then probably it will take some time.

if you have a better solution for this scenario I will really appreciate your comments.

[halfer]

I tried to do this semi-manually, but I am not sure if there are enough hooks in Phinx at present. I am using 0.10.6 presently. My use case is deploying a microservice in five AWS regions - if we want to recreate the whole service we can drop the database for a region and have the migrations recreate the database, roles and grants (in Postgres).

I tried using the up migration, but that won't work because Phinx tries to connect to the database specified in the YAML environments section, so it won't reach this code.

I tried using the init method, and I wanted to get the database name from here, but it is not yet populated:

$databaseName = $this->getAdapter()->getOption('name');

This returns:

PHP Fatal error: Uncaught error: Call to a member function getOption() on null ...

For the time being I can work around this - I will read the YAML file manually in init() and then call $this->getAdapter()->createDatabase(), but it would be nice to have a pre-connect hook to make this easier. Is that already available, or would that be a feature request?

[curtiscarlson]

Hi, just came across this issue looking to do the same thing. On my local machine I can use phpmyadmin to create the database, but once I deploy to a sever, I don't want to install that. Before, I would use a "command" that works like so:

if(!checkDB()) {
  if(ask("Database " . DB . " not found.  Would you like to create it?")) {
      echo createDatabase();
  }
  else {
    die("Have a nice day");
  }
}

function createDatabase() {
  $con = mysqli_connect(DB_SERVER, DB_LOGIN, DB_PASS);
  $result = mysqli_query($con, "CREATE DATABASE IF NOT EXISTS ". DB);
  if(!$result) {
    die(mysqli_error($con));
  }
  return "database " . DB . " created successfully.\n";
}

function dropDatabase() {
  $con = mysqli_connect(DB_SERVER, DB_LOGIN, DB_PASS);
  $result = mysqli_query($con, "DROP DATABASE ". DB);
  if(!$result) {
    die(mysqli_error($con));
  }
  return "database " . DB . " dropped.\n";
}

function ask($question) {
  echo $question . " (Y/N): ";
  $stdin = fopen('php://stdin', 'r');
  $response = fgetc($stdin);
  if (strtoupper($response) == 'Y') {
    return true;
  }
  return false;
}

function checkDBServer($args=null) {
  $con = mysqli_connect(DB_SERVER, DB_LOGIN, DB_PASS);
  if(!$con) {
    return false;
  }
  return true;
}

function checkDB($args=null) {
  if(!checkDBServer()) die("database server connection failed\n");
  $con = mysqli_connect(DB_SERVER, DB_LOGIN, DB_PASS);
  $result = mysqli_query($con, "SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '". DB ."'");
  if($result->num_rows>0) {
    return true;
  }
  return false;
}

This would be a lot simpler to just do in the migrations portion, in my opinion.

<?php

use Phinx\Migration\AbstractMigration;

class CreateDatabase extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $this->execute('CREATE DATABASE IF NOT EXISTS `slimproject`');
    }

    /**
     * Migrate Down.
     */
    public function down()
    {
      $this->execute('DROP DATABASE `slimproject`');
    }
}

but obviously this doesn't work. I think ideally, if you want to keep the ability to switch adapters, it could look like so:

<?php

use Phinx\Migration\AbstractMigration;

class CreateDatabase extends AbstractMigration
{
    /**
     * Migrate Up.
     */
    public function up()
    {
        $db->create('development');  // or 'slimproject' ?
    }

    /**
     * Migrate Down.
     */
    public function down()
    {
      $db->drop('development');  // or 'slimproject' ?
    }
}

just some thoughts from someone who is digging through this for the first time and trying to think wayyy ahead to deployment already. My final idea on how to tackle this would be to put my original "command" script into your phinx script to check the db for existence every time and if it doesn't exist, instead of straight failing, simply ask " Database " . DB . " not found. Would you like to create it?" I suppose one answer would be to port that script over to a "bootsrap" file but to be brutally honest I am having trouble following the docs to do that.

[curtiscarlson]

OK so I just tried to implement this inside of a phinx.php config file.

<?php

use Symfony\Component\Dotenv\Dotenv;

require __DIR__ . '/vendor/autoload.php';

$dotenv = new Dotenv();
$dotenv->load(__DIR__.'/.env');

if(!checkDB()) {
  if(ask("Database " . $_ENV['DB_DATABASE'] . " not found.  Would you like to create it?")) {
    echo createDatabase();
  }
  else {
  die("Have a nice day");
  }
}

return [
    "paths" => [
        "migrations" => "app/db/migrations"
    ],
    "environments" => [
       ....

it doesn't work, my guess is phinx has an output buffer turned on so I can't read the text. I will probably just install synfony console and create a command to create the database at this point outside of phinx entirely. But I do think you should pursue this as it's a great functionality to include without very many lines of code really.

[curtiscarlson]

OK, I don't want to spam this thread, but I feel this is important. I thought, yet again, that I had solved this thing but I have not.

file "phinx" in root:

use Symfony\Component\Dotenv\Dotenv;

require __DIR__ . '/vendor/autoload.php';

$dotenv = new Dotenv();
$dotenv->load(__DIR__.'/.env');


if(!checkDB()) {
  if(ask("Database " . $_ENV['DB_DATABASE'] . " not found.  Would you like to create it?")) {
    echo createDatabase();
  }
  else {
  die("Have a nice day");
  }
}

array_shift($argv);
exec("vendor\\bin\\phinx" . " " . implode($argv, " "));

... functions below

then from the root folder you can say "php phinx migrate" and it works....EXCEPT there is no output shown. You gotta do something here!

[halfer]

@curtiscarlson: did you try adding your database creation in the init section? My experience was that it would not work in the up method since your Phinx connection config probably will specify the database that does not exist yet, and thus an error will occur, because it tries to get a connection before it reaches up.

My solution was like so:

    /**
     * Create the database
     *
     * We cannot create the database in an up() or change() method,
     * as Phinx wants to create a connection to a named database,
     * and so it would crash before it got to that code. Unfortunately
     * in the init phase, the adapter is not ready!
     *
     * So the workaround is that I am reading the YAML config manually
     * and hoping that Phinx has successfully done the %% Phinx token
     * replacements. If it has, then we create an connection manually
     * and create a database.
     */
    public function init()
    {
        // Get the settings for Phinx
        $root = realpath(__DIR__ . '/../..');
        $config = PhinxConfig::fromYaml($root . '/phinx.yml');
        $env = $config->getEnvironment($this->getEnvironment());

        // Get the vars we want, and make sure Phinx did the replacements
        $databaseName = $env['name'];
        $charset = $env['charset'];
        $this->verifyTokenReplacement($databaseName);
        $this->verifyTokenReplacement($charset);

        $conn = new DbConnection(
            $env['host'],
            'postgres', // This db is guaranteed to exist
            $env['user'],
            $env['pass']
        );
        $conn->init();

        // Push that big red button!
        $this->createDatabaseIfNotExists($conn->getPdo(), $databaseName, $charset);
    }

I've not listed all classes/methods here, the missing ones are:

• verifyTokenReplacement - double-checks that env-var replacements have been done by Phinx
• DbConnection - our wrapper for PDO
• createDatabaseIfNotExists - detects if the db needs to be created, and if so issues a custom CREATE DATABASE command

My guess is that since the maintainer does not want this feature, it will not get done unless someone steps up to do it. Personally, I am happy with my workaround.

[dereuromark]

Isnt it auto creating it all already nowadays? Can this ticket be closed?