DEV Community

loading...
Cover image for Change the Primary Key Data Type with Sequelize
Grouparoo

Change the Primary Key Data Type with Sequelize

Sean C Davis
Sean is a tinkerer and a teacher. He is driven to learn by doing (often failing) and loves passing those learnings onto others who may find them beneficial.
Originally published at grouparoo.com ・2 min read

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),
});
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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);
Enter fullscreen mode Exit fullscreen mode

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),
    });
  }
};
Enter fullscreen mode Exit fullscreen mode

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

Discussion (0)