Add data when running Symfony migrations

Question

I have a Symfony project that is using the DoctrineMigrations bundle, and I have a really simple question: When I run a migration (e.g., when I'm pushing an update to production), how can I insert data to the database?

For example: I have an Entity which is the type of an add. The entity is:

private $addType; // String
private $type1;   // Boolean
private $type2;   // Boolean
private $type3;   // Boolean

I add another field ($type4), and I want to add a new record to the database, with this values:

$addType = 'Type number 4';
$type1 = false;
$type2 = false;
$type3 = false;
$type4 = true;

How can this be done with DoctrineMigrations? Is it possible?

Oldwife

Hi. I am not familiar with DoctrineMigrations, but I am just trying to understand your question. Can I clarify that I understand what you mean: when you are running a migration, you have added a new property ($type4) to your entity, and you want to insert a default value (true) into the database for this property and its underlying db column. Is this accurate? – Heptagon

@Darragh: Not exactly. The first part is right: add a new property to the entity. But I don't want to define a default value, what I want is to add a new record to the database. I know that this example is so silly, but this is what I need... – Oldwife

Oh right, sorry I didn't understand you. I'll remove my answer. I guess you could still use $this->connection->executeQuery('INSERT INTO add (foo, bar) VALUES ('a', 'b') in your migrations. I have no idea if it is possible to use the ORM layer in migrations, assuming this is what you want to do. – Heptagon

Use the doctrinefixturebundle – Behoove

@Matteo: AFAIK, this isn't an option, because when you run the fixtures:load command (I don't remember if it's exactly this command), the database is purged... And I don't want to purge the production database each time I install an update... EDIT: There is a flag (--append) to not purge the database, but this way you charge ALL the fixtures, not only the new ones... – Oldwife

Yes you can append and load selectively by direcory selector check the option of the cli task. – Behoove

Answer 1

Using the Entity Manager as suggested in another answer is not a good idea, as it leads to troubles later.

In the first migration, I created a table with users and populated some users via $em->persist($user); which seemed fine at the beginning.

But after a month, I added a phone column to my User model. And Doctrine generates INSERT statements with this column within the first migration, which fails due to the non-existing column phone. Of course it doesn't exist yet in the first migration. So it is better to go with pure SQL INSERTs.

Garold

To avoid that, every change should be made by the migration, even the first schema:update... – Christ

Just to clarify once a migration version is migrated, it will never run again in the future for the same database. It's only once per server. – Razor

@Christ I think there is still a bigger issue which is that using the ORM depends on your current codebase (entity classes), not your database schema. So let's say in one of you migrations you drop a column. If you have to go back to an earlier migration which references the dropped column, an error will be thrown because the current entity class doesn't have that property. So you are right that it needs to have the schema:update but also the migrations should use raw sql instead of the current entity class. – Sizeable

Answer 2

I just asked a related related question.

It is possible to use the migrations bundle to add data to the database. If you add a new property and use the doctrine mapping then the

php app/console doctrine:migrations:diff

command will generate a new migration file. You can just put your insert statements inside this file using the syntax:

$this->addSql('INSERT INTO your_table (name) VALUES ("foo")');

Make sure you put it after the auto-generated schema changes though. If you want to separate your schema changes and your data changes then you can use

php app/console doctrine:migrations:generate

to create an empty migrations file to put your insert statements in.

Like I said in my related question, this is one way to do it, but it requires manually creating these if you want to change this data in the database.

Edit:

Since this answer seems to get a few views I think it's worth adding that to more clearly separate the data changes from the schema changes there is a postUp method that can be overridden and that will be called after the up method.

https://www.doctrine-project.org/projects/doctrine-migrations/en/3.0/reference/migration-classes.html#postup

Linked

Answer 3

It does, if you know how to format the array;

$this->connection->insert('user', ['id' => 1, 'gender' => 'Male']);

Fidelia

Answer 4

I've "found" the correct way to solve my problem (insert data after running migrations, using my entity classes).

Here is: https://stackoverflow.com/a/25960400

The idea is to declare the migration as ContainerAware, and then, from the postUp function, call the DI to get the EntityManager. It's really easy, and you can use all your entities and repositories.

// ...
use Symfony\Component\DependencyInjection\ContainerAwareInterface;
use Symfony\Component\DependencyInjection\ContainerInterface;

class Version20130326212938 extends AbstractMigration implements ContainerAwareInterface
{

    private $container;

    public function setContainer(ContainerInterface $container = null)
    {
        $this->container = $container;
    }

    public function up(Schema $schema)
    {
        // ... migration content
    }

    public function postUp(Schema $schema)
    {
        $em = $this->container->get('doctrine.orm.entity_manager');
        // ... update the entities
    }
}

Oldwife

This is bad practice and is not recommended because if you then change your entity then all migrations using it will fail (as mentioned here) – Yarborough

Answer 5

when you make the new field you need to enter this annotation "options={"default":1}" and it should work.

/**
 * @var boolean
 * @ORM\Column(name="type4", type="boolean", options={"default":1})
 */
private $type4 = true;

Took me some time to figure this out :)

Multifaceted

Answer 6

this is good solution for me. Just use bin/console make:migration and when migration is generated just edit if and add "DEFAULT TRUE": $this->addSql('ALTER TABLE event ADD active TINYINT(1) NOT NULL DEFAULT TRUE');

Bacolod

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Clothe

Answer 7

A Short Summary working with Symfony 7

<?php 

// symfony 7
// php 8.2

declare(strict_types=1);

namespace DoctrineMigrations;

use Doctrine\DBAL\Schema\Schema;
use Doctrine\Migrations\AbstractMigration;
use App\Entity\CustomEntity;

final class Version20230101000000 extends AbstractMigration 
{
  /** @var CustomEntity[] */
  private static function getData(): array
  {
    return [
      (new CustomEntity())->setProperty('value'),
      (new CustomEntity())->setProperty('nextValue'),
    ];
  }

  public function up(Schema $schema): void 
  {
    // table structure is create here
  }

  public function postUp(Schema $schema): void
  {
    foreach(self::getData() as $entity) {
      $this->connection->insert(
        table:'table_name', 
        data: ['property' => $entity->getProperty()]
      );
    }
  }

  public function down(Schema $schema):void
  {
    // no changes required
  }

}

Fixtures are Build for working with sample data in your development environment. So They are not an option to be use to prevent Data to a productive database.

For small sets of default entries, it is fine to use the migration files. If you need to prevent mass of data, think about to build a TaskRunner and work in chunks.

You can also use the postUp Method to update entries by using the $this->connection->update method if you updated the structure of a table. feel free to do more ;)

Bedtime

Answer 8

It doesn't sound a good idea to fill date in migration, not its responsibility, symfony has a way of doing that. https://symfony.com/doc/current/bundles/DoctrineFixturesBundle/index.html

Scandal

Fixtures are not intended for production data, so this answer is definitely incorrect. If you're adding something like a status or role to a table, fixtures are not an option as they don't keep track of what has been run, and by default clear the entire database. – Ancestress