Subject: Prisma Migration: Adding Non-Nullable Field to Existing Table (vs. TypeORM)
I'm new to Prisma (coming from TypeORM) and have a question about migrations.
I added a new, non-nullable field to my
User model, which already had data. When I ran prisma migrate dev, it errored out, as expected, because existing rows wouldn't have a value for the new required field.
Prisma suggested using --create-only and manually editing the SQL. This is different from my TypeORM experience, where adding a required field and generating/applying a migration was more direct.
I understand why Prisma flags this – it's logical that a new, required field needs a value for existing rows. However, the process of manually editing SQL feels like an extra step, and I want to ensure I'm grasping the intended workflow correctly.
My questions are:
1. Is using --create-only and manually editing the SQL migration the standard/recommended approach in Prisma when adding a non-nullable column to an existing table with data?
2. If so, what does a typical manual SQL edit involve for this scenario? (e.g., ALTER TABLE ... ADD COLUMN ... NOT NULL DEFAULT 'some_value', or perhaps making it nullable first, updating data, then altering to NOT NULL within the same SQL migration file?)
3. How does TypeORM generally handle adding a new, non-nullable field to an existing table with data without requiring this explicit manual SQL intervention by the developer during migration generation?
4. What are the best practices or common migration workflows you'd recommend when working with Prisma for these kinds of schema changes?
5. Am I perhaps misunderstanding a part of the Prisma workflow here? The idea of manually intervening in the SQL for what seems like a common schema change makes me wonder if there's a more "Prisma-idiomatic" way I'm missing.
I'm trying to understand the best practices here. Any insights would be greatly appreciated!
Thanks!1 Reply
You opted for speed from this bot. Let's conjure up an answer right now!
Great questions! Here’s how Prisma handles this scenario and how it compares to TypeORM:
1. Is --create-only and manual SQL editing standard in Prisma for this?
Yes, this is the recommended approach. When you add a non-nullable field to a table with existing data, Prisma cannot apply the migration automatically because existing rows would violate the NOT NULL constraint. Prisma suggests using
Common patterns include: - Adding the column as nullable, updating existing rows with a value, then altering the column to be NOT NULL—all within the same migration file. - Or, adding the column with a default value so existing rows are populated, then (optionally) removing the default constraint later [How to migrate with a new required column]. 3. How does TypeORM handle this?
TypeORM migrations are more programmatic and can include both schema and data changes in the same migration file (using TypeScript). This allows you to update existing rows as part of the migration logic, which can feel more seamless [Schema migration workflows]. 4. Best practices for Prisma migrations in this scenario:
- Use the "expand-contract" pattern:
1. Add the new column as nullable. 2. Backfill data for existing rows. 3. Alter the column to be NOT NULL. - Alternatively, add with a default value, then remove the default after updating your app logic [Preventing failing migrations in CICD]. 5. Are you missing a more "Prisma-idiomatic" way?
No, manual intervention is intentional in Prisma to ensure data integrity and explicitness. It’s a trade-off for safety and transparency, especially in production environments [Customizing migrations]. Let me know if you need a concrete example of the SQL! ✅ Great, thanks for letting us know that this solved your question!
Yes, this is the recommended approach. When you add a non-nullable field to a table with existing data, Prisma cannot apply the migration automatically because existing rows would violate the NOT NULL constraint. Prisma suggests using
prisma migrate dev --create-only to generate a draft migration, which you then edit to handle the data issue before applying it. This ensures you explicitly decide how to handle existing data, avoiding accidental data loss or invalid states [Customizing migrations].
2. What does a typical manual SQL edit involve?Common patterns include: - Adding the column as nullable, updating existing rows with a value, then altering the column to be NOT NULL—all within the same migration file. - Or, adding the column with a default value so existing rows are populated, then (optionally) removing the default constraint later [How to migrate with a new required column]. 3. How does TypeORM handle this?
TypeORM migrations are more programmatic and can include both schema and data changes in the same migration file (using TypeScript). This allows you to update existing rows as part of the migration logic, which can feel more seamless [Schema migration workflows]. 4. Best practices for Prisma migrations in this scenario:
- Use the "expand-contract" pattern:
1. Add the new column as nullable. 2. Backfill data for existing rows. 3. Alter the column to be NOT NULL. - Alternatively, add with a default value, then remove the default after updating your app logic [Preventing failing migrations in CICD]. 5. Are you missing a more "Prisma-idiomatic" way?
No, manual intervention is intentional in Prisma to ensure data integrity and explicitness. It’s a trade-off for safety and transparency, especially in production environments [Customizing migrations]. Let me know if you need a concrete example of the SQL! ✅ Great, thanks for letting us know that this solved your question!