In a Laravel migration, how would you add a required currency column to a marketplace's large orders table, and what does change() do to modifiers you omit?
answer
- existing rows need a value
- default() or nullable, backfill, change()
- change() drops unrestated modifiers
- MySQL DDL is not transactional
- --pretend shows the ALTER first
basics
~20 sExisting rows need a value: add it with a default, or add it nullable, backfill, then make it required with change(). change() redefines the whole column, so any modifier not restated, such as default or comment, is dropped.
solid answer
~40 s`$table->string('currency', 3)` alone is `NOT NULL` with no default, which existing rows cannot satisfy: depending on the driver the `ALTER` fails or fills an implicit value you did not choose. If one value is right for old rows, add it in one step with `->default('EUR')`. Otherwise use three migrations: add it `->nullable()`, backfill in batches, then make it required with `$table->string('currency', 3)->nullable(false)->change()`. `change()` rebuilds the **whole** column definition, so every modifier you omit is dropped: repeat `->default('EUR')` or `->comment()` in the `change()` call if you want to keep them. Run `php artisan migrate --pretend` to read the exact `ALTER TABLE` first. Laravel wraps migrations in a transaction only on PostgreSQL and SQL Server, so on MySQL a migration that fails halfway leaves its earlier statements applied and is not recorded as run.
code
php · 23 lines<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
// Third migration: after the backfill left no NULLs
return new class extends Migration
{
public function up(): void
{
Schema::table('orders', function (Blueprint $table) {
$table->string('currency', 3)->nullable(false)->default('EUR')->change();
});
}
public function down(): void
{
Schema::table('orders', function (Blueprint $table) {
$table->string('currency', 3)->nullable()->change();
});
}
};go deeper
Recall that new columns are NOT NULL unless nullable() is called, and that default() gives existing rows a value.
Explain the default-versus-nullable-and-backfill choice and that change() rewrites the whole column definition.
Plan the column change as separate migrations, read --pretend output, and account for non-transactional DDL on MySQL.
Set the team's policy for schema changes on large tables: what ships in one migration, what needs a backfill step, and who reviews the SQL.
## The problem A marketplace's `orders` table already holds millions of rows. The business now needs a required `currency` column. The naive migration is: ```php Schema::table('orders', function (Blueprint $table) { $table->string('currency', 3); }); ``` Laravel columns are `NOT NULL` unless you call `nullable()`, and this one has no default. Every existing order needs **some** value in the new column, so the database must either reject the `ALTER TABLE` or invent one. Which happens depends on the driver, and neither outcome is a decision you made on purpose. ## Option 1: one step with a default If one value is correct for every existing order, say all historical orders were in euros, give the column a default: ```php $table->string('currency', 3)->default('EUR')->after('total_cents'); ``` Existing rows receive `EUR`, new inserts that omit the column get it too, and the column is required from the start. `after()` only affects column order on MySQL and MariaDB. ## Option 2: three migrations When old rows need **different** values, derived from the seller's country for instance, split the work: 1. **Add it nullable.** `$table->string('currency', 3)->nullable();` The app keeps working, and new code starts writing the column. 2. **Backfill.** A separate migration or command fills existing rows in batches. How to run a large backfill safely is a data-migration topic of its own. 3. **Make it required.** Once no `NULL` remains: ```php Schema::table('orders', function (Blueprint $table) { $table->string('currency', 3)->nullable(false)->change(); }); ``` ## What `change()` really does `change()` does not "patch" one attribute. It takes the definition you wrote on that line and **replaces** the column's definition with it. The docs are explicit: you must include every modifier you want to keep, and any missing attribute is dropped. | Existing column | `change()` call | Result | |---|---|---| | `string(3)`, default `EUR`, comment | `string('currency', 3)->nullable(false)->change()` | NOT NULL, **no default, no comment** | | same | `string('currency', 3)->default('EUR')->comment('ISO 4217')->change()` | NOT NULL, default and comment kept | | unique index on column | any `change()` | index unchanged; indexes are separate | Indexes are the exception: `change()` leaves them alone, and you add or drop them explicitly, for example `->unique(false)->change()`. ## Checking before running - **`php artisan migrate --pretend`** prints the SQL each pending migration would run without executing it. Reading the `ALTER TABLE` before a deploy is the cheapest review you can do. - **Transactions.** Laravel runs each migration inside a transaction only when the schema grammar supports transactional DDL, which is PostgreSQL and SQL Server. On MySQL and MariaDB each DDL statement commits on its own, so a migration that fails on its third statement leaves the first two applied and is **not** recorded in `migrations`. Re-running it then fails on the column that already exists. Keep one DDL change per migration on those drivers. - **Indexes on big tables.** If the new column needs an index, `->online()` adds `CONCURRENTLY` on PostgreSQL and `WITH (online = on)` on SQL Server so the build does not block writes. ## Keeping the running app happy While the migration runs, the previous release is still serving requests. A nullable column or a column with a default is invisible to old code, which is why both options above are safe to deploy before the code that reads the column. The general expand-and-contract discipline behind that ordering is a schema-evolution topic; the Laravel part is choosing between `default()`, `nullable()` and `change()` and knowing exactly what each emits.
- A colleague runs $table->string('currency', 3)->change() to widen nothing, just to tidy up. What can go wrong?`change()` replaces the column definition with exactly what was written, so the column loses any default, comment or nullability it had, and becomes `NOT NULL` with no default. Inserts that relied on the default start failing. Every `change()` call must restate each modifier the column should keep.
- Why can a failed migration on MySQL not simply be re-run after fixing the bug?MySQL commits each DDL statement immediately, and Laravel only wraps migrations in a transaction on PostgreSQL and SQL Server. Statements that succeeded before the failure stay applied, but the migration is not recorded as run. Re-running repeats them and fails. Undo the partial changes by hand, or guard them, before retrying.
saying these in an interview costs you the question
- Believes change() only alters the modifiers you list
- Adds a NOT NULL column without default to a populated table
- Assumes every failed migration is rolled back automatically
- Thinks nullable() is the default for new Laravel columns
- Expects change() to drop the column's existing indexes