Hello,
I have this code.
public function up(): void
{
Schema::table('packages', function (Blueprint $table) {
$table->foreignUlid('client_id')->after('site_id')->nullable()->constrained()->cascadeOnDelete();
});
$packages = Package::with('site')->get();
foreach ($packages as $package) {
$package->client_id = $package->site->client_id;
$package->save();
}
}
All works fine except saving the client_id for each package.
Why ?
If you have any idea, thanks to share ;).
V
Error text?..
Are you sure Eloquent in migrations is good? Migrations are about raw database tables/columns, Eloquent is about models which are "next level" compared to DB calls. Sometimes they align, sometimes not. You cannot guarantee your migrations chain is consistent and always replayed with this approach.
I believe you need something like this: https://github.com/TimoKoerber/laravel-one-time-operations
It's like migrations for data, not structure.
Oh it was so silly ... I had refreshed all the database and the database is seed after the migrations, so while he migrations are run, there is no data.
This is a bit off-topic, but I'd advise against using Eloquent in migrations. Migrations represent a change in the database schema at a specific point in time, while Eloquent models rely on the current schema. If and when the models go through changes, your earlier migrations may start failing during tests or deployments and you have to rewrite them.
It's best to use the DB query builder and reference the columns and relations that exist at that point.
Edit. Looks like @glukinho already pointed this out. I skipped the reply because I thought it was from Lary.
I'm ok with this.
But for an application in production, if I need to add some fields in a table in the database, there is sometimes datas to change inside the database.
Why in this case wouldn't it be a bad idea to add this code inside the migration ?
Where should I place it ?
You're right to update the data in the migration. The issue is the use of Eloquent model classes. You should instead use a query builder with hard-coded table and column names, e.g.:
$results = DB::table('packages')
->join('sites', 'packages.site_id', '=', 'sites.id')
->selectRaw('packages.id as package_id, sites.client_id as client_id')
->get();
(...)
DB::table('packages')->where('id', $result->package_id)->update([
'client_id' => $result->client_id
]);
I wrote that off the cuff, so it may contain errors.
You don't know what's going to happen with the schema years later. The model will always try to use columns and relations from the latest schema, which may not match the database structure at this migration step.
If something changes about the Package -> Site relation, for example, then this migration step will fail: the Package model will try to use columns or tables that don't exist (yet).
I had similar task years ago: need to add not null column to existing prod db and fill it with (computed) value from related tables. While I don't like this approach, passing years I still can't imagine better solution
All of this inside one migration file.
I was not proud of this solution, but - as I told - I still do not not have better one.
That sound completely normal to me. I've done it many times.
It's exactly my case.
So why do you tell me that it's not a good idea ?
I used DB facade instead of Eloquent
That's the main point. Package::get() can fail in migration file unpredictably: imagine your Package model uses different table since some moment; but your old migration doesn't know about it. So, migrations chain fails (during tests or further development) or you have inconsistent data, both are bad.
DB::table('packages')->get() in migrations is more or less reliable, you operate on the same DB level at least.
Still, there is a thought you shouldn't operate with data in migrations at all, only tables structure.
As was mentioned before, migrations are about changing database SCHEMA, not the DATA. https://laravel.com/docs/13.x/migrations
Migrations are like version control for your database, allowing your team to define and share the application's database schema definition.
That's why I don't like the data changing in migration. But looks like Laravel do not suggest anything to archive the goals like in OP, that's why we do that in migration
Schema changes are the main use case, but there's nothing wrong with migrating data too. It's the easiest way to keep the application state consistent. The feature is called database migration, not schema migration.
@silencebringer @jussimannisto What could be the best way to migrate data when migrating database ?
The way we described above - by using DB facade in migration - is the only way I know. So, we have no choice =)
I have some thoughts against that.
Manipulating data in migrations makes sense on production only.
Migrations are "replayable" - they are applied one after another many times during development and testing. You can launch php artisan migrate:fresh hundred times while developing on your local dev machine. If you have data manipulation inside one of your migrations which data is actually manipulated on dev machine? And why? Testing/dev data is seeded many times (and AFTER all migrations, not among them), it doesn't need to "evolve" along with migrations.
To have "data migration" properly tested I have to seed database with various sets of data instead of only latest version. I have to track different data sets to always match with different DB schemas/migrations. I don't think this is worthwhile.
Therefore, I wouldn't place data manipulation inside migration. Instead I would use Artisan command which is launched manually on production only and one time only, after a corresponding migration is applied.
This package offers something similar: https://github.com/TimoKoerber/laravel-one-time-operations
Quote from description:
This package is for you if... ... you often seed or process data in a migration file (which is a big no-no!)
I don't understand the objection. If you change something in the DB and need to convert data, it's better to run it chronologically in a migration rather than accumulate one-off Artisan commands. Relying on manual commands is bad if you have any kind of CI/CD pipeline or zero-downtime update process.
As for re-running migrations in dev and testing, what's the issue? You run schema changes and data changes together. Except with migrate:fresh there are usually no rows to modify.
Regarding seeding / data migrations, here's the opinion of one Laravel core developer: https://youtu.be/HNgDhZYg3VI?si=DVnuh4USmoL-pYEX&t=91
Thanks! I'll think about it. However for now I prefer to have code, data structure and data itself as separated as possible.
Manipulating data in migrations makes sense on production only.
I'm ok with this and it's exactly the case for me : no need to manipulate datas if the application is not in production.
But my question is about an application in production.
However my post was about understanding why it doesn't work and not about the relevance of manipulation datas inside a migration.
Looks like I missed general question, my apologies. Please provide more details, what exactly "doesn't work" means. Any errors? At which step?
It works already, see comments at the very top. However nothing wrong to chat a bit about some related things.
I think it depends on how often you end up doing this, and for the given case this is fine. Manipulating data in migrations is fine depending on the complexity and occurrence but I have seen this go wrong when you overdo it, now the new team member can't setup their local db bc the migrations are a mess.
The data you updated turned out to have a bug so now you either need to revert the migration on prod to run it again or create a new migration with no schema changes and just data updates.
Data updates are much more likely to fail midway due to data complexity of production and edge cases than predictable schema changes and now your production is down with half ran migrations.
A safer way would be a multi step migration/deployment (where possible assuming you have the time and resources)
First you change the schema to support both options, (like having the col nullable, adding a secondary column without removing the original, etc) and the business logic to fill new entries, while supporting old entries for now.
The second step would be to create a command to migrate the old data to the new structure.
And the third step, after you verify all data follows the new requirements, make the schema change the only option and fully deprecate the old logic, (like making the col non nullable, remove the command of step 2, deprecate old logic, etc)
Please or to participate in this conversation.