Home

Change the Primary Key Type with Sequelize

It seems like it's going to be super easy. And it is! And then it's not.

Originally posted on www.grouparoo.com.

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.

Let's Connect

Keep Reading

7 Things that Make SQLite Unique and Awesome

SQLite is not like the others. But what makes it different also makes it a pretty cool database engine.

Apr 30, 2021

Understanding Types with SQLite and Node.js

SQLite is simple but very cool and powerful. Yet, it's a little quirky when it comes to handling types. Let's explore that goofiness together, and see how we can protect against it when using Node.

Apr 22, 2021

Generate Dynamic JSON Pages with Next.js

Two methods for generating JSON pages with Next.js. One that updates on every request, the other on every build.

Jun 10, 2021