How to insert multiple rows from a data array in a Doctrine migration?

Question

I have created an Entity and generated the migration file. Now, I want to insert some data into the table in the MySQL database when doing the migration.

My up function is as follows.

public function up(Schema $schema) : void
{
  $data = array(
    'My Data 1',
    'My Data 2',
    'My Data 3',
  );

  $this->abortIf($this->connection->getDatabasePlatform()->getName() !== 'mysql', 'Migration can only be executed safely on \'mysql\'.');

  $this->addSql('CREATE TABLE my_table (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(100) NOT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB');

  $this->addSql('INSERT INTO my_table (name) VALUES (?)', $data, \PDO::PARAM_STR);
}

I want to add all data in the $data array to my_data table. All elements in the $data array are strings.

When I run php bin/console doctrine:migrations:migrate, it gives the following error.

Argument 3 passed to Doctrine\Migrations\AbstractMigration::addSql() must be of the type array, int given...

How to fix this error and achieve what I want?

Check out the [signature of `addSql`](https://github.com/doctrine/migrations/blob/3.0.x/lib/Doctrine/Migrations/AbstractMigration.php#L143). — El_Vanja, Nov 30 '20 at 10:05
You might also want to take a look at an [example](https://www.doctrine-project.org/projects/doctrine-migrations/en/3.0/reference/migration-classes.html#addsql) of usage. — El_Vanja, Nov 30 '20 at 10:06
@El_Vanja Thank you for your comments. I managed to achieve what I wanted. I acknowledged you in my answer. :) — Sennen Randika, Nov 30 '20 at 10:46

score 1 · Accepted Answer · answered Nov 30 '20 at 10:45

I managed to achieve what I wanted as follows... This answer is inspired by the resources commented on my question by @El_Vanja.

public function up(Schema $schema) : void
{
  $data = array(
    'My Data 1',
    'My Data 2',
    'My Data 3',
  );

  $this->abortIf($this->connection->getDatabasePlatform()->getName() !== 'mysql', 'Migration can only be executed safely on \'mysql\'.');

  $this->addSql('CREATE TABLE my_table (id INT AUTO_INCREMENT NOT NULL, name VARCHAR(100) NOT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8mb4 COLLATE `utf8mb4_unicode_ci` ENGINE = InnoDB');

  foreach($data as $d) {
    $this->addSql('INSERT INTO my_table (name) VALUES (?)', array($d));
  }
}

score 0 · Answer 2 · answered Jun 24 '22 at 14:35

Very late to the party, but perhaps this will help someone in the future. Using an associative array as input for the query described in Sennen Randika' answer will not work and throws the following error: Positional parameter at index 0 does not have a bound value.

So do not use an array structure as below:

  $data = array(
    'key1' => 'My Data 1',
    'key2' => 'My Data 2',
    'key3' => 'My Data 3',
  );

How to insert multiple rows from a data array in a Doctrine migration?

2 Answers2