Should I manipulate data on a Laravel database migration? Or should I do it somewhere else?

Question

I'm planning a major change to my database that will require a set of SQL instructions (and probably also a non-SQL script) to accommodate the old data to the new schema.

Now, is it a good idea to manipulate data on a migration script? Or is it just meant for schema (DB structure) changes?

Migrations can be used, but... I would have to recommend against migrations with large changes. What happens if the PHP code times out? What if you run out of memory. Instead, keep your original database. Copy / create a completely new environment you can change to. You might be able to forward people to the new site or change DNS records. You need to be able to revert back if something goes wrong. Have a backup plan! — jjwdesign, Feb 26 '16 at 20:43
@jjwdesign Good point. However, this is a CLI script that I set up for not timing out and this migration is to be run on my current dataset which is pretty small at the time. The DB is growing though (and this set of changes are due to growth). — Mauro, Feb 26 '16 at 21:13

score 0 · Answer 1 · answered Feb 26 '16 at 20:43

Data Manipulation is not a thing for migration. Because of the nature migration works. I will advice to use the migration to define only the database schema.

Migrations were made to be executed by Laravel one by one, in the exact order they were created, so it can keep track of execution and order of execution ref. Antonio Carlos Ribeiro.

This prevent one from taking charge of the class or files he/she want to run. for example you may choose to seed users table before departments table in another sense you can choose to do it vice versa. Migration does not come with this freedom.

With Data manipulation i will advice you use seeders to handle that because you can choose which class to run at any point in time by using

php artisan db:seed --class=ClassName

As the name goes seed. Meaning to populate the database.

score 0 · Answer 2 · answered Nov 17 '17 at 00:38

Two concerns are important when thinking about migrations.

speed: php usually has a timeout. What happens when your migration is 1/2 done?
running in the future: your app code might be different if you run this migration in a couple months.

Raw queries helps with both these issues.

$elements = DB::Select(DB::Raw('SELECT element_id FROM record GROUP BY element_id'));

foreach ($elements as $element)
{
    DB::Statement('
        INSERT INTO account (created_at, updated_at, name, element_id)
        VALUES (NOW(), NOW(), "Migrated account", '. $element->element_id .')
    ');
}

Should I manipulate data on a Laravel database migration? Or should I do it somewhere else?

2 Answers2