Laravel Migrate Old Data: 4 Steps & 2 Optimizations

Today I'm showing how to create a new DB field and migrate the old data: in a straightforward way, or with performance optimization.

- - - - -
Support the channel by checking out our products:

[Samuel.Mwangi]

Great tip as always. Thanks Povilas.

One micro optimisation at 5:13; since you don't need the User model, instead of User::all() you could select distinct city from users table using the DB facade instead. The difference in this example would be some 2 or 3 seconds but could be significant in a larger dataset.

[tetleyk]

I would use a raw SQL statements to retrieve and update the data, no foreach loops required.

So, DB::->select("select distinct city from users") to get an array of cities. A to add the cities to the cities table and then another raw update SQL command to update the user_id field.

Something like DB::update('update users inner join cities on (cities.name = users.city) set users.city_id = cities.id') should do the trick...I think !

In other words, make the database do all the heavy lifting.

Probably not that much quicker than your examples for small amounts of data but the larger the data set becomes, the quicker this should run.

You'd need to test it to confirm that, however.

[EDIT] Even better way:
In the migrations make sure that users.city and cities.name are indexed.

Run the following SQL statement:
DB::statement('insert into cities select distinct city from users order by city');

Then this one:
DB::statement('update users inner join cities on (cities.name = users.city) set users.city_id = cities.id');

[TobiasOlssonHovby]

I would have done like this:

INSERT INTO cities (`name`)
SELECT DISTINCT `city` FROM `users`;

UPDATE `users` SET `city_id` = (
SELECT `id` FROM `cities` WHERE `name` = `users`.`city`
);

Two queries, and took around 500ms

[object_name]

As others have already mentioned, i would not (anymore) use the EloquentModelBuilder (like User::all()),
but not because of the performance other people pointed out, but because that might not be a persitent query, when installing the project later on.

Imagine you add the 'soft-delete' functionality later to the City Model.
Now when calling City::create will automaticly populate a 'deleted_at = null' column, which does not yet exist in the database,
because the migration for that only appears later on. Using the DB facade can prevent that, since it likely won't change with the rest of the code.

Also i have all 4 steps in one migration usually ?
Is there any reason to split that,
it feels like more readable way to know they belong together,
instead of wondering, why somone just deleted the 'city_name' collumn for example.

[nadjinmalade]

Thanks for your DailyVideos.
Since DBMS are faster than PHP loop, and for memory reason i'll use DB facade wich something like this:
DB::Select("select distinct city from users") then insert all $cityArray directly in the cities tables.
then use Raw Sql querry like -> " update users SET city_id = (SELECT id FROM cities WHERE name = users.city )"

[jprudence]

The code could gain more speed by using :

😉 ( line 20 )

Thanks for your videos 🙏

[fahnleindieselschweif]

interesting approach. I am am doing it by creating a console command like import:cities

[KastsEcho]

@2:27 Wouldn't it better to make a seeder instead a migration to move the data to the new cities table / city_id field?

[belaouraabdelwahab]

Great video but I used seeders to do that for more complex migrating tables with a lot of relationships like more than 20 relationships and it's working fine for me but I'm gonna try this thank a lot for this

[intipontt]

The chunking is not intended to make something run faster. It's intended to avoid running out of memory. User::all() in your example, is an Eloquent Collection that contains 60 thousand hydrated User models. All of this allocated in memory.

If you had, say 10 million users (I'm inventing a number here), maybe doing this with User::all() would give you the classic php memory limit error. In that case, using User::chunk() would avoid that.

A better way to do the first part is
1) using a SELECT DISTINCT query,
2) selecting only the fields you're interested in
3a) using cursor() to return a lazy collection. foreach as $user)
3b) alternatively, using pluck('city') to get a collection of the cities. foreach as $city)

If you want to make a single update query, you could make the query something like
UPDATE users SET city_id = CASE WHEN city = "city_1" THEN 1 WHEN city = "city_2" THEN 2

In my opinion, migrations should be reversible. Seeding should be separate but even in this case, you can go back to the previous state by de-normalizing the database in the down() method.

[phil_]

Great explanation about migrations, very useful

[blank]

Data migration through eloquent looks neat but I always use raw sql data migrations it is way to fast and hence ideal when dealing with large table and fairly complex migrations, more over database engine can optimize the raw queries and the total number of DB call will be the least hence takes even less time.

[GavinKimpson]

So simple and effective I like these tips

[abdonajjar]

I had i have trouble for changing a columns that has foreign key constrained for a project running in the production server i was doing it manually instead of using the migrations.
for example add nullable for the foreign or changing the on delete action for that foreign key cascade or null on cascade is it possible to do it from migrations file only?

[Flankymanga]

Very nice example. This is basically a way how to version database schema as DDLs is basically "hidden" inside Laravel migration files.

[alexanderanastasiadis]

Hello , i have a problem with Laravel 9, i need to find a way to auto storage:link in production mode.
Every time i upload an image into /storage/images cant't load them into blade.

[mc]

I would implement "down" methods too... once you've implemented "up", reverse would take few minutes, but this way you'll keep migrations consistent and you can revert any time...
if reverting is not necessary, I would create instead a command

[maximilianoalbino]

I always avoid using any of the app's classes (models included) so the migrations can be executed at any version of the app lifetime.
I had cases where features were removed from the app and some classes deleted, so some migrations stopped working if ran with ":fresh".

[daxitzadafiya]

Hello, how we can create the mind map in Laravel if you have any resources then please share them with me. Does anyone has any idea about it?

[jacquesmatike]

Also make sure that before removing city column on users table, update your code to avoid new user to write data on that table, but instead code the new logic of the cities’ table.