7 Comments // Reading Time: 13 min.
This article is part of the series. The first article discussed the creation of an initial Symfony 2 project, the second is about Symfony2 forms, and the third article describes a nice and easy way to implement a REST API with the Symfony framework.
Introduction
Let's say that you have made some changes in the model of your Symfony project, you have committed the code, and it will be applied on the production. But, you also need to update the production database. You might run the command: php app/console doctrine:schema:update, but then you might lose your data (it depends on the model changes you made). Right and data safe way is to use Doctrine migrations. Doctrine can automatically generate migration for you, but that migration will contain the same SQL code like doctrine:schema:update command, and it doesn't care about the existing data. To migrate the data, you will have to modify migration file, and we will talk about that later in the text. Let's get started.
1. Install Doctrine migrations bundle
If Doctrine migrations bundle is not already included in your Symfony project, add this line in "require" section of your composer.json file:
"doctrine/migrations": "1.0.*@dev"
Adjust the version if there is a newer one and run composer update (or run composer install if you don't wish to update your other dependencies).
Also, don't forget to add the bundle in your AppKernel file:
public function registerBundles() {
$bundles = array(
...
new Doctrine\Bundle\MigrationsBundle\DoctrineMigrationsBundle(),
);
...
}2. Generating migration file
After you've made changes on the model (you added/deleted some fields of some entities, or you added/deleted whole entities), you can create the migration file by running the command:
php app/console doctrine:migrations:diff
You will notice that a new file is added to your project in directory:
app/DoctrineMigrations
The file will be named something like this:
Version20151118233337.php
It contains timestamp of creation moment: VersionYYYYMMDDhhmmss.php. In that way, migration names are unique and properly ordered, and order of migrations execution is important. But, doctrine takes care of all of that for you.
Before executing the migration, you might wish to peek in the file just to check if the generated SQL is OK. If everything is fine, and there is no need to migrate the data (we will come to it later), you can run the migration.
Before going on, I would like to comment on migration class methods. By default, auto generated migration class (which is contained in the migration file, and has same name as the file) contains two methods: up and down. Up method is executed if we are migrating to a newer version, and down migrates back to the older version of project. You can see it in the example below. I have added the title field to the person table. If migrating up, the field is added, and if migrating down, it is dropped.
<?php
namespace SGalinski\LicenceManagement\Migrations;
use Doctrine\DBAL\Migrations\AbstractMigration;
use Doctrine\DBAL\Schema\Schema;
/**
* Auto-generated Migration: Please modify to your needs!
*/
class Version20151118233337 extends AbstractMigration
{
/**
* @param Schema $schema
*/
public function up(Schema $schema)
{
// this up() migration is auto-generated, please modify it to your needs
$this->abortIf($this->connection->getDatabasePlatform()->getName() != 'mysql', 'Migration can only be executed safely on \'mysql\'.');
$this->addSql('ALTER TABLE person ADD title VARCHAR(255) DEFAULT NULL');
}
/**
* @param Schema $schema
*/
public function down(Schema $schema)
{
// this down() migration is auto-generated, please modify it to your needs
$this->abortIf($this->connection->getDatabasePlatform()->getName() != 'mysql', 'Migration can only be executed safely on \'mysql\'.');
$this->addSql('ALTER TABLE person DROP title');
}
}3. Execute the migration
php app/console doctrine:migrations:migrate
This is the main command you will use to execute the migrations. It runs all the new (unexecuted) migrations. Before running it, you can check what migrations are new, and see a lot of more info by executing this command:
php app/console doctrine:migrations:status
There are more migration commands:
php app/console doctrine:migrations:generate
php app/console doctrine:migrations:execute
php app/console doctrine:migrations:version
php app/console doctrine:migrations:latest
but they are not so much interesting for us now. To see more info about any command, and how to use it, run the command with --help argument.
public function up(Schema $schema)
{
$this->abortIf($this->connection->getDatabasePlatform()->getName() != 'mysql', 'Migration can only be executed safely on \'mysql\'.');
$this->addSql('CREATE TABLE address (id INT AUTO_INCREMENT NOT NULL, street VARCHAR(255) DEFAULT NULL, street_number VARCHAR(255) DEFAULT NULL, postal_code VARCHAR(255) DEFAULT NULL, city VARCHAR(255) DEFAULT NULL, country VARCHAR(255) DEFAULT NULL, PRIMARY KEY(id)) DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ENGINE = InnoDB');
$this->addSql('ALTER TABLE person ADD address_id INT DEFAULT NULL, DROP address_street, DROP address_street_number, DROP address_postal_code, DROP address_city, DROP address_country');
$this->addSql('ALTER TABLE person ADD CONSTRAINT FK_34DCD176F5B7AF75 FOREIGN KEY (address_id) REFERENCES address (id)');
$this->addSql('CREATE INDEX IDX_34DCD176F5B7AF75 ON person (address_id)');
}The example shows a migration which moves the address fields from the person table to a separate table - address and references it in the person table. The migration, as you can see it above, cares only for database structure, and doesn't care about the data. But if the application is already running on the production and it contains a data, we need to migrate the data too. In this example we will need to copy address data (street, street_number, city, ...). So, we will do it in these three steps:
- Create the address table and create the foreign key address_id in the person table.
- Copy all the address data from the person table to the address table.
- Drop the address fields from person table.
Steps 1 and 3 already exist, and we just need to separate and order them properly. Step 2 is the tricky part, and we need to implement it.
What exactly do we need to do in the step 2? We need to take the address data of each person and insert it in the address table. Then we need to connect each person with it's address by inserting right address_id in the person table. It would be easy if there is a unique address field (we can't use address_id because it didn't exist in the person table - we need to insert it now), or if there is unique combination of the address fields, then we might copy the data by executing these SQL queries:
INSERT INTO address (street, street_number, postal_code, city, country)
SELECT address_street, address_street_number, address_postal_code, address_city, address_country
FROM person;
UPDATE person, address
SET person.address_id = address.id
WHERE address.street = person.address_street
AND address.street_number = person.address_street_number
AND address.postal_code = person.address_postal_code
AND address.city = person.address_city
AND address.country = person.address_country;But the problem is that there can be 2 or more persons with the same address. And each of them should have his copy of the address in the address table (they would have only different id-s). So, the SQL above can't be used in this example.
We can copy the data by iterating the person table, and in each iteration we read/select the address data from the person, insert the data in the address table, fetch address.id and update it in the person table to the same row from which we have read the data. How to implement that?
We can make iterations in SQL, but we need to make a stored procedure to do the job, and we can also make the iterations in PHP. The problem with PHP approach is that we can't work with our data from within the up function because all the SQL code added by the $this->addSql method is executed at the end of the up function. Solution is simple:
We are going to use postUp function which is executed after up function, in that way securing that all the tables are already created when we handle the data migration. So, even SQL-procedure-approach should be implemented within the postUp function. Here is how it should look like:
public function postUp(Schema $schema) {
// Copy addresses from person to address table
$this->connection->exec('
CREATE PROCEDURE copy_address()
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE var_street, var_street_number, var_postal_code, var_city, var_country VARCHAR(255);
DECLARE var_person_id INT;
DECLARE var_address_id INT;
DECLARE address_cursor CURSOR FOR SELECT id, address_street, address_street_number, address_postal_code, address_city, address_country FROM person;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN address_cursor;
read_loop: LOOP
FETCH address_cursor INTO var_person_id, var_street, var_street_number, var_postal_code, var_city, var_country;
IF done THEN
LEAVE read_loop;
END IF;
INSERT INTO address (street, street_number, postal_code, city, country) VALUES (var_street, var_street_number, var_postal_code, var_city, var_country);
SET var_address_id = LAST_INSERT_ID();
UPDATE person SET address_id = var_address_id WHERE id = var_person_id;
END LOOP;
CLOSE address_cursor;
END;
CALL copy_address();
DROP PROCEDURE IF EXISTS copy_address;'
);
// Dropping - step 3
$this->connection->exec('ALTER TABLE person DROP address_street, DROP address_street_number, DROP address_postal_code, DROP address_city, DROP address_country, DROP active_from, DROP active_to');
}We did these 3 sub steps:
- Create the stored procedure.
- Execute (call) it
- Delete (drop) it
As you can see, in the procedure, we are using a CURSOR for selecting address fields only and then we are iterating the selection (in a loop) while there is the data available. within each iteration, we are inserting the data in the address table, then fetching the address.id by LAST_INSERT_ID() function and updating it to the person table.
Read here more on mySQL cursors.
4.2 Use of PHP for handling the data in the postUp method
In the postUp method we can even use entitymanager, but first we need to fetch it from the context. So we need to have the context aware migration:
class Version20151111205453Copy extends AbstractMigration implements ContainerAwareInterface
{
/**
* @var ContainerInterface
*/
private $container;
public function setContainer(ContainerInterface $container = null)
{
$this->container = $container;
}
// ...
public function postUp(Schema $schema) {
/** @var EntityManager $em */
$em = $this->container->get('doctrine.orm.entity_manager');
// Copy work from customer to company table
// Use PHP code in migrations:
$queryBuilder = $this->connection->createQueryBuilder();
$queryBuilder
->select('customer.work')
->from('customer', 'customer')
->groupBy('customer.work')
->orderBy('customer.work');
$customersGroupedByWork = $queryBuilder->execute();
foreach ($customersGroupedByWork as $customerGroupedByWork) {
// Persisting by Doctrine\ORM\EntityManager
$company = new Company();
$company->setName($customerGroupedByWork['work']);
$em->persist($company);
/** @var CustomerRepository $customerRepository */
$customerRepository = $em->getRepository('CompanyManagementBundle:Customer');
$allCustomers = $customerRepository->findAll();
/** @var Customer $customer */
foreach ($allCustomers as $customer) {
if ($customer->getWork() === $company->getName()) {
$company->addEmployee($customer);
}
}
$em->flush();
}
}This is a different example than before. We are making the company table out of the string field work which is defined in the customer table. And those customers are actually employees in the company (this doesn't make much sense now because you don't see complete model in these examples, but just accept it as it is).
I must say right away that this example has an error! The work field doesn't exist anymore in the Customer class, and we are trying to get it. So we can't rely on entities - objects, because their code can be inconsistent with current database state. Another example of inconsistency between the code and the migration is when the migration is executed in the future. Let's say that a user is creating a new database, so when he runs the migrate command, all the migrations are executed, and the code - entities are of the newest version and some of the entities/fields which exist in the migration, might be deleted in the code. So there might be some migrations which won't work.
Solution to this problem is simple: don't use entities within migrations, you must not relay on the entity (PHP) code, but on the database only!
So, don't use entitymanager, nor DQL, just access the database via Doctrine\DBAL\Connection, like in the next part of the code:
// Copy work from customer to company table
// Use PHP code in migrations:
$queryBuilder = $this->connection->createQueryBuilder();
$queryBuilder
->select('customer.work')
->from('customer', 'customer')
->groupBy('customer.work')
->orderBy('customer.work');
$customersGroupedByWork = $queryBuilder->execute();
foreach ($customersGroupedByWork as $customerGroupedByWork) {
// Insert statement by Doctrine\DBAL\Connection
$this->connection->insert('company', ['name' => $customerGroupedByWork['work']]);
// ...
// ...The code above creates companies (inserts company names in the company table) from the customer.work string field.
But the best solution is to use simple SQL queries if it is possible. Example of creating companies based on the customer.work field and connecting the customers with their company:
public function postUp(Schema $schema) {
// ...
// Copy work from customer to company table
$this->connection->exec(
'INSERT INTO company (name)
SELECT customer.work
FROM customer
GROUP BY customer.work
ORDER BY customer.work;'
);
$this->connection->exec(
'INSERT INTO companies_employees (company_id, customer_id)
SELECT company.id, customer.id
FROM company, customer
WHERE company.name = customer.work;'
);
// ...
}5. All in all, on the whole
Let's sum the important stuff:
- Use the console command to generate the migration (php app/console doctrine:migrations:diff)
- Modify the migration to preserve/migrate the data.
- Use postUp method for migrating the data
- Do all relevant droppings on the end of the postUp method
- If it is possible execute simple SQL queries to migrate the data
- If simple SQL can't do the job, use SQL stored procedure with loops
- Or use PHP within postUp method but be sure to rely on database only
- don't use entities, use Doctrine\DBAL\Connection ($this->connection)
- Execute the migrations (php app/console doctrine:migrations:migrate)
Enjoy the migrations. Questions, discussion and comments are welcome. :)
Contact us!
We are a digital agency, which is specialized in the development of digital products. Our core topics are websites and portals with TYPO3, eCommerce with Shopware and Android and iOS-Apps. In addition, we deal with many other topics in the field of web development. Feel free to contact us with your concerns!
Comments
Stefan Galinski
at 23.11.2015Very helpful, Damjan! Very helpful, Damjan!
Martins
at 20.12.2015Thanks! Thanks!
Olivier
at 28.04.2016Hi Damjan !
Very nice and helpful introduction to Doctrine Migrations.
I am currently working on a SaaS web app. It means all our customers share the same backend Symfony application but each [...] Hi Damjan !
Very nice and helpful introduction to Doctrine Migrations.
I am currently working on a SaaS web app. It means all our customers share the same backend Symfony application but each one as its own database. Is it helpful to use Migrations in this case and why ?
Thank you
Saint-Cyr
at 28.05.2016Nice post !
Olivier at your place I would do separate the whole project like one Symfony application for only one Data Base because normally designing a Data base is a standalone project (or [...] Nice post !
Olivier at your place I would do separate the whole project like one Symfony application for only one Data Base because normally designing a Data base is a standalone project (or sub-project) and designing an application to communicate to is another one and most of the time the application structure (data structure), logic (algorithms), ... all rely on the DB but the reverse is not. But according to your case if more than one customer have the same business logic ( they can share one DB) then you can share the DB among them in this case but steel there is a probability that one day one of them ask for a feature that require change of DB schema and this might not be the desire of the other....
tomasz
at 27.03.2017Any clues on debugging except var_dumping? Any clues on debugging except var_dumping?
Christian Elowsky
at 23.10.2017One quick note. For this section where you execute the query, the results should actually be retrieved via a call to fetchAll() like so:
$queryBuilder = [...] One quick note. For this section where you execute the query, the results should actually be retrieved via a call to fetchAll() like so:
$queryBuilder = $this->connection->createQueryBuilder();
$queryBuilder
->select('customer.work')
->from('customer', 'customer')
->groupBy('customer.work')
->orderBy('customer.work');
$stmt = $queryBuilder->execute();
$customersGroupedByWork = $stmt->fetchAll();
foreach ($customersGroupedByWork as $customerGroupedByWork) {
// Insert statement by DoctrineDBALConnection
$this->connection->insert('company', ['name' => $customerGroupedByWork['work']]);
Liam
at 26.11.2019Nice guide! Nice guide!