I have been trying to create a tree table with self join (mssql, sequelize) .

\+----+--------------+--------+
| id |     name     | parent |
\+----+--------------+--------+
|  1 | ABC          | null   |
|  2 | DEF          | null   |
|  3 | GHI          | 2      |
|  4 | JKL          | 3      |
|  5 | MNO          | 4      |
\+----+--------------+--------+

I will need the below result. Note that the depth level is infinite.

[
    {
        "id": 1,
        "name": "ABC",
        "children": null
    },
    {
        "id": 2,
        "name": "DEF",
        "children": [
                      {
                        "id": 3,
                        "name": "GHI",
                        "children": [
                                  {
                                  "id": 4,
                                  "name": "JKL",
                                  "children": [ {
                                     "id": 5,
                                     "name": "MNO",
                                     "children": null
                                },]
                      },
]
                      },
                     
                    ]
    }
]

Below is my Model

@Table()
export class AccHead extends Model {
  @Column({
    type: DataType.BIGINT,
    primaryKey: true,
    autoIncrement: true,
  })
  id: number;
  name: string;

@ForeignKey(() => AccHead)
  @Column({
    type: DataType.BIGINT,
    onDelete: 'NO ACTION',
  })
  parentId: number;

@BelongsTo(() => AccHead, { foreignKey: 'parentId' })
  parent: AccHead;
  @HasMany(() => AccHead, { foreignKey: 'parentId' })
  children: AccHead[];
}

I have tried the below Query but it only limits to the level i include the model, I want to Include children recursively: (More than 150k+ records in original Table so, query needs to be optimized)

const hierarchicalData = await AccHead.findAll({
        where: {
          parentId: null, // Retrieve top-level categories
        },

        include: [
          {
            model: AccHead,
            as: 'children',
            where: {
              parentId: { [Op.ne]: null }, 
            },
            include: [
              {
                model: AccHead,
                as: 'children', 
                include: [
                  {
                    model: AccHead,
                    as: 'children', 
                   
                  },
                ],
              },
            ],
          },
        ],
      });

One of the best solution i found for this is the
sequelize-hierarchy Documentation which is exactly what i need. It has its own functions to handle tree operations.

const Folder = sequelize.define('folder', { name: Sequelize.STRING });
Folder.isHierarchy();

Or

const Folder = sequelize.define('folder', {
  name: Sequelize.STRING
}, {
  hierarchy: true
});

But i am unable to implement it in Nest Js as this documentation is for sequelize in Node. Can someone please help me to do the same in nest js, or any other better approach?