How can I store required 'base' or 'initial' data for a database (in particular Symfony)?

Question

I use the doctrine migrations bundle to track changes in my database structure. I would like to ensure that when I'm deploying / adding a new server for my application that:

• (A) the database schema is up to date (doctrine:migrations:migrate)
• (B) the database always contains a pre-defined set of data

For (B) a good example is roles. I want a certain set of roles to always be present. I realize it is possible with database migrations, but I don't like the idea of mixing schema changes with data changes. Also if I use MySql migrations I would have to create a equivalent Sqlite migration for my test database.

Another option I'm aware of is data fixtures. However from reading the documentation I get the feeling that fixtures are more for loading test data. Also if I changed a role name I don't know how that would be updated using fixtures (since they either delete all data in the database before loading or append to it). If I use append then unique keys would also be a problem.

I'm considering creating some sort of command that takes a set of configuration files and ensures that certain tables are always in a consistent state matching the config files - but if another option exists I'd like to use it of course.

What is the best way to handle loading and managing required data into a database?

Answer 1

If you're using Doctrine Migrations, you can generate initial migration with whole database schema, then you should generate migrations (doctrine:migrations:generate or doctrine:migrations:diff) for all changes that are made in database structure AND also add there queries that will migrate existing data.

Fixtures are designed to pre-populate data (with doctrine:fixtures:load) and, in my opinion, they should be kept up-to-date with latest database schema and executed after doctrine:migrations:migrate / doctrine:schema:create.

So finally:

• Create base migration with initial database schema (instead of executing doctrine:schema:create just generate migration file and migrate it)
• Create new migrations for each database schema change AND for migrating existing data (such as role name changing)
• Keep fixtures up-to-date with latest schema (you can use --append option and only update fixtures instead of deleting all database data first)

Then, when deploying new instance you can run doctrine:schema:create, then doctrine:migrations:version --add --all --no-interaction (mark all migrations as migrated, because you have already created latest schema) and doctrine:fixtures:load which will populate data to the database (also latest version, so data migrations from Doctrine migrations files are not required).

Note: Existing instances should NOT use doctrine:schema:update, but only doctrine:migrations:migrate. In our app we even block usage of this command, in app/console:

use Symfony\Component\Console\Output\ConsoleOutput;
use Symfony\Component\Console\Helper\FormatterHelper;

// Deny using doctrine:schema:update command
if(in_array(trim($input->getFirstArgument()), ['doctrine:schema:update'])) {
    $formatter = new FormatterHelper();
    $output = new ConsoleOutput(ConsoleOutput::VERBOSITY_NORMAL, true);

    $formattedBlock = $formatter->formatBlock(['[[ WARNING! ]]', 'You should not use this command! Use doctrine:migrations:migrate instead!'], 'error', true);

    $output->writeln($formattedBlock);
    die();
}

This is what I figured out from my experience. Hope you will find it useful :-)