Define association in Sequelize

Say we have two model portfolio and stock. Each item from the portfolio belongs to one stock. We need to define this relationship in sequelize. The general rule is to have a column stockId in the portfolio that will map with the id from stock table.

But we'll map the string column here just to see how it is done. Each Portfolio item has symbol(stock symbol) and so does the Stock item too. We'll map these two tables based on the Symbol column.

The user can have multiple post. : hasmany relationship
A post belongs to one User only : belongsTo relationship

Similarly,
A stock can have multiple portfolio. (a stock can be seen on multiple portfolio record)
A portfolio belongs to one stock. (One portfolio record can have only one stock)

Let's define this relationship.

@Table
export class Portfolio extends Model<Portfolio> {
//other columns

@ForeignKey(()=>Stock)
@Column({
    type:DataType.STRING,
    allowNull:false
  })
 Symbol:string;

 @BelongsTo(()=>Stock,{
        targetKey:"Symbol"
    })
  Stock : Stock;

}

Here the @ForeignKey(()=>Stock) makes the "Symbol" in the table Portfolio a foreign key
to the table Stock
The foreign key has to have some column to reference on the stock table. This can be done as

 @BelongsTo(()=>Stock,{
        targetKey:"Symbol"
    })
  Stock : Stock;

Here we are saying to associate our "Symbol" column from portfolio to the "Symbol" column of stock using the key "targetKey".
The last line Stock is the key in the Portfolio which will hold the associated stock table data. This will not create a new column in the portfolio table.

This will generate the following SQL query

ALTER TABLE "Portfolios" ADD FOREIGN KEY ("Symbol")
 REFERENCES "Stocks" ("Symbol") ON DELETE NO ACTION ON UPDATE CASCADE;

Query the table with foreign Key

Now that we have the relationship, we'll query the table using sequelize.

  async findAll() {
    return await Portfolio.findAll({
      include: [Stock]
    });
  }

This will generate the query

SEECT * FROM "Portfolios" AS "Portfolio" 
LEFT OUTER JOIN "Stocks" AS "Stock" 
ON "Portfolio"."Symbol" = "Stock"."Symbol";

The result

But we may not be interested in all the records from the Stock table.
We can specify which column we want to pull from the stock using the attributes key as :

  async findAll() {
    return await this.portfolioRepository.findAll({
      include: {
        model: Stock,
        attributes: ["LTP","updatedAt"]
      }
    });
  }

result

Define association in Sequelize - Nest js

Query the table with foreign Key