Tag: <span>Sequelize</span>

I recently wanted to set up Did I Hike That? to allow for a demo user that could play with the app but not affect any existing data. This required that I know who created each hike record, which I didn’t include in the initial design. I realize now that was a bit shortsighted. But no worries, that’s what database migrations are for!

I use Sequelize as the ORM for this app and have already done one migration on the database, so this would just be a new one. I originally used the Umzug library to do things programmatically since I didn’t want to mess with calling the CLI. The new migration script needed to add a new column to the hike table and then update that value for every hike. Sounded easy, but there was a bit of nuance.

I wanted everything to run inside a transaction so if something went sideways, the database would be left untouched. But I also needed the new column to be present inside the transaction so I could update it later in the script, which was a bit of a catch-22. This project is using Sequelize 6, which doesn’t allow nested transactions (it seems v7 will support them). In v6 there are a couple ways to execute a transaction:

• Managed, where you call the built-in transaction method and pass it a callback. If an exception is thrown inside that function, the changes are rolled back, otherwise they are committed.
• Un-managed, where you call the transaction method and use the object it returns to manually commit or roll back changes by calling an appropriate method.

The previous migration script used the un-managed approach because I wanted maximum flexibility on when things happened. But when I ran the new migration script there were a couple of problems. The entire up function is in a try/catch, but the column change would persist even if an exception happened somewhere (the raw query of the hike table had some minor issues as I was writing it that would produce an error). Once I got through that it got down to the point of updating the new column, but kept throwing a column not found error.

I don’t remember if I knew this before, but after some web searching and re-reading the docs I realized you can include that transaction object in any call of the query function on the main sequelize object. The docs say having that object will cause Sequelize to create a save point for whatever query you are executing. It won’t commit it until you call commit but it will remember the change for later queries which include the same transaction object. Which is exactly what I needed. The end result looked something like this:

const transaction = await queryInterface.sequelize.transaction();

try {
    const hikeTableDefinition = await queryInterface.describeTable('hikes');

    if (!hikeTableDefinition.userId) {
        // Including the transaction here will make Sequelize aware of the new column so we can update it later
        await queryInterface.addColumn('hikes', 'userId', { type: DataTypes.STRING }, { transaction });
    }

    ...

    await queryInterface.sequelize.query("Update hikes Set userId = 'some_user_id' Where id = 'some_hike_id'", { transaction });

    await transaction.commit();
catch (error) {
    await transaction.rollback();
    throw error;
}

The two queries that modify the database include the top-level transaction so they are both aware of any changes being made. If the hike table update blows up for some reason it will roll back everything, which was perfect since I was looping through all the hike records and wanted it to be all or nothing. I added the check for the existence of the new column just to make doubly-sure it doesn’t throw unnecessarily, since trying to add a column that’s already there will definitely throw.