How do I change my schema without data loss in MySQL ?
Hey everyone, I'm pretty new to SQL so I'm having some disturbing questions in my mind.
How should I proceed when I need to add a new and notNull column to a schema without losing data?
Do I need to set a default ? But then I can't remove the default because it causes data-loss, so I might aswell not use a notNull field, but what if I have to ?
I've been reading about migrations, but I don't really see a big difference between a migration and a db:push (I'm using drizzle + planetscale)
What is the correct way to approach this problem?
Thank you!
1 Reply
If you CAN'T set a default, then you'll have to create the data through relations.
From what i know, you have 3 options for changing the schema:
1 - Set a default value.
2 - Set a default value in which you'll force the event to set the value.
silly example: you'll add a role to all users. set the default value to a number out of the range of choices for the user. Then, every time you would need the data, check if it's not the default one, and ask for the input.
3 - Create a new Table in which one of the columns are the tableId that you wanted to extend.
As far as i know, these are the most common ones