Change the Primary Key Data Type with Sequelize

We recently adjusted how we handle primary keys. Previously they were UUIDs with a max length of 40 characters. With our Declarative Sync feature, we allow developers to set primary key values from their configuration files. Thus, we needed to lengthen the maximum number of characters allowed on primary keys in our database.

Seems simple, right?

I thought so, too. We're using Sequelize as our ORM tool, and I found a handy changeColumn method.

So that's what I did. It looked like this:

await migration.changeColumn(tableName, columnName, {
  type: DataTypes.STRING(191),
});

Note: In these examples, I'm accessing Sequelize methods through an object called migration. This is because we use Actionhero to run our database migrations. Your objects will look different, but the methods on them should be the same.

I first tested with SQLite and voila! It did exactly as I expected. All the primary keys were changed and working just lovely.

Changing PostgreSQL Primary Keys

Since we support both Postgres and SQLite as our application database, I moved on to test in Postgres, and that's when, instead of the database being properly migrated, I was presented with this lovely message:

column "id" is in a primary key

I thought: Yes, true. That is correct. And ... ?

It turns out Sequelize doesn't handle this action well with Postgres. After going down a rabbit hole in playing around with constraints, I ended up just writing the SQL statement directly. It looked something like this:

const query = `ALTER TABLE "${tableName}" ALTER COLUMN "${columnName}" SET DATA TYPE varchar(${maxIdLength}); `;
await migration.sequelize.query(query);

That worked!

Consistency is Awesome! (SQLite is Weird.)

It made sense to me to try to use the same approach with both databases. So I tried my Postgres solution with SQLite.

It didn't work. (Sensing a theme yet?)

That seemed odd. But, of course, we already know that SQLite is weird. And it turns out SQLite's ALTER TABLE methods are extremely (and intentionally) limited.

Which meant I was stuck with two solutions. And when that happens, we tend to look at the current dialect and execute the appropriate code. And that's why this is the weird function that alters the primary key column in both Postgres and SQLite:

const changeColumn = async (tableName, columnName) => {
  if (config.sequelize.dialect === "postgres") {
    const query = `ALTER TABLE "${tableName}" ALTER COLUMN "${columnName}" SET DATA TYPE varchar(${maxIdLength}); `;
    await migration.sequelize.query(query);
  } else {
    await migration.changeColumn(tableName, columnName, {
      type: DataTypes.STRING(191),
    });
  }
};

You can see the complete set of changes that came along with this code in this pull request.