I made a mistake writing a schema for a PostgreSQL database. How do I fix it?

sql-server
nodejs

#1

Hello,

I’m using Sequelize in a node.js app to talk to a PostgreSQL database.

I made an error in the schema for one of my tables - I defined a key as a string but I’d like it to be an integer. There are a few rows of data in the table now.

I know I need to use a migration to fix this, but I’m unclear on the best way to generate and run it to do what I want, and ideally avoid losing any data.

I’ve had a look at Sequelize’s docs but they don’t mention anything particular

What’s the best way to do this?


#2

I don’t have any experience using sequelize with node, but generally speaking, I have an idea. You could 1) rename the current column you want to deprecate via migration, 2) add the integer column you want, 3) migrate the existing data in the string column to your integer column, and 4) drop the string column.


#3

Adding on to @ni_lo’s excellent advice, make sure to do all of this in a transaction! You should be able to verify that everything looks the way you expect before committing the changes, and rollback if not without losing any data.