Sequelize And The Disappearing ID Column

23 Dec, 2023

I recently fixed a bug in some code that uses Sequelize. Let me set the scene and let's see if you can figure out what happened.

Here's some example models that are sufficient to illustrate the situation I faced. I'll use a typical example domain. There is a table for books, a table for authors, and a join table to associate them since books can have multiple authors at once.

// Book.model.ts

class Book extends Model {
  static BookAuthors: HasMany<Book, Author>;
}

Book.init({
  name: DataTypes.TEXT,
}, {
  sequelize,
  modelName: 'Book',
});

Book.BookAuthors = Book.hasMany(BookAuthor);

// Author.model.ts

class Author extends Model {}

Author.init({
  name: DataTypes.TEXT,
}, {
  sequelize,
  modelName: 'Author',
});

// BookAuthor.model.ts

class BookAuthor extends Model {
  static Book: BelongsTo<BookAuthor, Book>;
  static Author: BelongsTo<BookAuthor, Author>;
}

BookAuthor.init({}, {
  sequelize,
  modelName: 'BookAuthor',
});

BookAuthor.Book = BookAuthor.belongsTo(Book);
BookAuthor.Author = BookAuthor.belongsTo(Author);

You may have already noticed something a little non-idiomatic. The Book model is using hasMany instead of belongsToMany. That's how this was set up in the actual code I was working in.

Let's do a simple query to see what the data structure looks like.

console.log(
  JSON.stringify(
    (
      await Book.findOne({
        where: { id: 1 },
        include: [{ model: BookAuthor, include: [{ model: Author }] }],
      })
    )?.toJSON(),
    null,
    2
  )
);

{
  "id": 1,
  "name": "The Hobbit",
  "BookAuthors": [
    {
      "id": 1,
      "BookId": 1,
      "AuthorId": 1,
      "Author": {
        "id": 1,
        "name": "J. R. R. Tolkien"
      }
    }
  ]
}

Fantastic. No surprises there.

Fast forward a couple of months and a bug was reported. An error was occurring because of a missing ID field in the results of that same query.

Let's run the query again and see what we get.

--- before.json 2023-12-23 20:50:56.638119488 +0000
+++ after.json  2023-12-23 20:50:59.674119628 +0000
@@ -1,15 +1,14 @@
 {
   "id": 1,
   "name": "The Hobbit",
   "BookAuthors": [
     {
-      "id": 1,
       "BookId": 1,
       "AuthorId": 1,
       "Author": {
         "id": 1,
         "name": "J. R. R. Tolkien"
       }
     }
   ]
 }

Sure enough, the id of the join table is missing. The id column of the table still existed. This was an active project, so many files had been changed between the previous release, which I could confirm still returned the id of the join table, and the current version of the code. But the BookAuthor.model.ts file had not changed.

I could work around the issue by adding attributes: { include: ["id"] } like so:

console.log(
  JSON.stringify(
    (
      await Book.findOne({
        where: { id: 1 },
        include: [
          {
            model: BookAuthor,
            attributes: { include: ["id"] },
            include: [{ model: Author }],
          },
        ],
      })
    )?.toJSON(),
    null,
    2
  )
);

But that didn't explain why the id was suddenly missing. It took a git bisect script for me to figure it out. Git's bisect command is a very powerful tool and I recommend you add it to your tool belt if you haven't already.

If you want to try to figure it out for yourself, stop here.

After running git bisect, the culprit was a seemingly innocent change to the Author.model.ts file. A developer had added belongsToMany to the Author model to make it easier to traverse the relationship between Author and Book.

// Author.model.ts

class Author extends Model {
  static Books: BelongsToMany<Author, Book>;
}

Author.init(
  {
    name: DataTypes.TEXT,
  },
  {
    sequelize,
    modelName: "Author",
  }
);

Author.Books = Author.belongsToMany(Book, { through: BookAuthor });

Using the belongsToMany function to mark the BookAuthor model definitively as a join table in the eyes of Sequelize causes Sequelize to hide the id column. Sequelize uses the foreign keys of join tables as a complex unique primary key.

When you look at it from the point of view of Sequelize's happy path, this makes complete sense. This is how Sequelize thinks join tables should be defined. When you look at it through a historical lens, it's surprising behavior that a change to some other model should cause a column to disappear from the results of a query.

#sequelize