Sequelize is a powerful ORM for node.js that makes working with relational database management systems (RDMS) such as Postgres, MySQL, MariaDB and others seamless. Most schema definitions in a RDMS have a relationship between tables such as User to Post relationship or Order to Customer. Sequelize makes JOIN statements easy between associations with some simple configurations in your model definitions. If you followed our last post on Seeding Postgres Database we walked through a relationship example forr many posts records for each user. Expanding on that example, if you have a users table that is associated (has a relationship with) many posts entries, your table schema would look similar to:

CREATE TABLE IF NOT EXISTS "users" (
  "id" SERIAL,
  "username" VARCHAR(255),
  "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL,
  "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL,
  PRIMARY KEY ("id")
);

CREATE TABLE IF NOT EXISTS "posts" (
  "id" SERIAL,
  "title" VARCHAR(255),
  "body" TEXT,
  "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL,
  "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL,
  "userId" INTEGER REFERENCES "users" ("id") ON DELETE
  SET
    NULL ON UPDATE CASCADE,
    PRIMARY KEY ("id")
);

To create an association in sequelize you have to use the hasMany() association and/or belongsTo() when defining your sequelize models such as:

class Post extends Model {}
Post.init({ title: Sequelize.STRING }, { sequelize, modelName: 'post' });
class User extends Model {}
User.init({ username: Sequelize.STRING }, { sequelize, modelName: 'user' });

User.hasMany(Post); // Will add userId to Post model
Post.belongsTo(User); // Will also add userId to Post model

Now let’s say we have the userId (3) and want to get all the posts associated with a particular user. We can call:

const user = await User.findOne({ 
  where: { id: 3 },
  include: [{
    model: Post
  }]
});

This will perform a join statement such as:

SELECT "users".*, "posts".* FROM (SELECT "users"."id", "users"."username", "users"."createdAt", "users"."updatedAt" FROM "users" AS "users" WHERE "users"."id" = '3') AS "users" LEFT OUTER JOIN "post" AS "posts" ON "user"."id" = "posts"."userId";

And you should get a user object back such that it looks like:

console.log(user)
{
  id: 3,
  username: 'someone',
  createdAt: '2015-12-31 15:00:00+01',
  updatedAt: '2015-12-31 15:00:00+01'
  posts: [
    {
      id: 123,
      userId: 3,
      title: 'Some title',
      body: 'Loreum ipsum dolor sed',
      createdAt: '2015-12-31 15:00:00+01',
      updatedAt: '2015-12-31 15:00:00+01'
    }
    ...
  ]
}

This is great, and super convenient. But what if you wanted to change the name of the posts field in your returned user object? Let’s say you wanted to call that field articles. On first glance you might think about using the attributes property in the findOne definition such as:

const user = await User.findOne({
  attributes: ['id', 'username', 'createdAt', 'updatedAt', ['posts', 'articles']],
  where: { id: 3 },
  include: [{
    model: Post
  }]
});

However, you are going to get an error: "User.posts is not a field on User table". Hmm, so if that doesn’t work what do we have to do to change the field name? Well, we have to go back to our relationship/associate definitions and use the as property to define how we want the field name returned:

User.hasMany(Post, { as: 'articles' foreignKey'userId' }); // Will add userId to Post model
Post.belongsTo(User); // Will also add userId to Post model

And finally in our findOne() invocation we need to use the as property in our include object such as:

const user = await User.findOne({
  attributes: ['id', 'username', 'createdAt', 'updatedAt', ['posts', 'articles']],
  where: { id: 3 },
  include: [{
    model: Post,
    as: 'articles'
  }]
});

Now with any luck our SQL statement should look like:

SELECT "users".*, "articles".* FROM (SELECT "users"."id", "users"."username", "users"."createdAt", "users"."updatedAt" FROM "users" AS "users" WHERE "users"."id" = '3') AS "users" LEFT OUTER JOIN "post" AS "articles" ON "user"."id" = "articles"."userId";

And your returned object should be:

console.log(user)
{
  id: 3,
  username: 'someone',
  createdAt: '2015-12-31 15:00:00+01',
  updatedAt: '2015-12-31 15:00:00+01'
  articles: [
    {
      id: 123,
      userId: 3,
      title: 'Some title',
      body: 'Loreum ipsum dolor sed',
      createdAt: '2015-12-31 15:00:00+01',
      updatedAt: '2015-12-31 15:00:00+01'
    }
    ...
  ]
}

This wasn’t as intuitive as just changing a column/attribute name using a nested array in the attributes property and took a little digging to find how to do that. You can read up more on association aliases in the sequelize docs. Until next time, stay curious, stay creative!