sequelize#Sequelize TypeScript Examples

connectDatabase = async (options: ConnectOptions): Promise<Sequelize> => {
  const { host, username, password, database, logging } = options

  // Use port 5432 by default
  const port = options.port || 5432

  // Connect to the database
  const sequelize = new Sequelize({
    dialect: 'postgres',
    host,
    port,
    username,
    password,
    database,
    pool: {
      max: 10,
      min: 0,
    },
    logging,
  })

  // Test the connection
  await sequelize.authenticate()

  // All good, return the connection
  return sequelize
}

export async function initModels() {
    let dialectOptions = {}
    if (process.env.OPENPIM_DATABASE_OPTIONS) {
        dialectOptions = JSON.parse(process.env.OPENPIM_DATABASE_OPTIONS)
    }
    sequelize = new Sequelize(
        <string>process.env.DATABASE_NAME,
        <string>process.env.DATABASE_USER,
        <string>process.env.DATABASE_PASSWORD, {
        host: process.env.DATABASE_URL,
        port: process.env.DATABASE_PORT ? parseInt(process.env.DATABASE_PORT) : 5432,
        dialect: 'postgres',
        dialectOptions: dialectOptions,
        logging: logger.debug.bind(logger)
    })

    users.init(sequelize)
    types.init(sequelize)
    attributes.init(sequelize)
    relations.init(sequelize)
    lang.init(sequelize)
    lovs.init(sequelize)
    search.init(sequelize)
    items.init(sequelize)
    itemRelations.init(sequelize)
    actions.init(sequelize)
    dashboards.init(sequelize)
    channels.init(sequelize)

    items.Item.hasMany(itemRelations.ItemRelation, {sourceKey: 'id', as: 'sourceRelation', foreignKey: 'itemId'})
    itemRelations.ItemRelation.belongsTo(items.Item,{targetKey: 'id', as: 'sourceItem', foreignKey: 'itemId'}) 

    items.Item.hasMany(itemRelations.ItemRelation, {sourceKey: 'id', as: 'targetRelation', foreignKey: 'targetId'})
    itemRelations.ItemRelation.belongsTo(items.Item,{targetKey: 'id', as: 'targetItem', foreignKey: 'targetId'})

    await sequelize.sync();
}

export function init(sequelize: Sequelize):void {
  Dashboard.init({
      identifier: {
        type: new DataTypes.STRING(250),
        allowNull: false,
        unique: 'uniqueIdentifier'
      },
      name: {
        type: DataTypes.JSONB,
        allowNull: false
      },
      users: {
        type: DataTypes.JSONB,
        allowNull: false
      },
      components: {
        type: DataTypes.JSONB,
        allowNull: false
      },
      ...BaseColumns,
      tenantId: { // override base for uniqueIdentifier
        type: new DataTypes.STRING(50),
        allowNull: false,
        unique: 'uniqueIdentifier'
      }
    }, {
      tableName: 'dashboards',
      paranoid: true,
      timestamps: true,
      sequelize: sequelize
  });    
}

export function init(sequelize: Sequelize):void {
  Action.init({
      identifier: {
        type: new DataTypes.STRING(250),
        allowNull: false,
        unique: 'uniqueIdentifier'
      },
      name: {
        type: DataTypes.JSONB,
        allowNull: false
      },
      code: {
        type: new DataTypes.STRING(65535),
        allowNull: false
      },
      order: {
        type: new DataTypes.INTEGER,
        allowNull: false,
      },
      triggers: {
        type: DataTypes.JSONB,
        allowNull: false
      },
      ...BaseColumns,
      tenantId: { // override base for uniqueIdentifier
        type: new DataTypes.STRING(50),
        allowNull: false,
        unique: 'uniqueIdentifier'
      }
    }, {
      tableName: 'actions',
      paranoid: true,
      timestamps: true,
      sequelize: sequelize
  });    
}

export function init(sequelize: Sequelize):void {
  Language.init({
      identifier: {
        type: new DataTypes.STRING(250),
        allowNull: false,
        unique: 'uniqueIdentifier'
      },
      name: {
        type: DataTypes.JSONB,
        allowNull: false,
      },
      ...BaseColumns,
      tenantId: { // override base for uniqueIdentifier
        type: new DataTypes.STRING(50),
        allowNull: false,
        unique: 'uniqueIdentifier'
      }
    }, {
      tableName: 'languages',
      paranoid: true,
      timestamps: true,
      sequelize: sequelize
  });    
}

export function init(sequelize: Sequelize):void {
  LOV.init({
      identifier: {
        type: new DataTypes.STRING(250),
        allowNull: false,
        unique: 'uniqueIdentifier'
      },
      name: {
        type: DataTypes.JSONB,
        allowNull: false,
      },
      values: {
        type: DataTypes.JSONB,
        allowNull: false,
      },
      ...BaseColumns,
      tenantId: { // override base for uniqueIdentifier
        type: new DataTypes.STRING(50),
        allowNull: false,
        unique: 'uniqueIdentifier'
      }
    }, {
      tableName: 'lovs',
      paranoid: true,
      timestamps: true,
      sequelize: sequelize,
      scopes: {
        tenant(value) {
          return {
            where: {
              tenantId: value
            }
          }
        }
      }        
    });    
}

sequelize = new Sequelize({
    dialect: 'sqlite',
    // todo change this to locDb for using db inside src/data.db
    storage: univDb,
    // ** db event logging true in dev and false in production
    logging: (process.env.NODE_ENV !== 'production') ? true : false,
    define: {
        timestamps: false,
        underscored: true,
    },
})

function configureORM() {
  const sequelize = new Sequelize('sqlite::memory:')

  class User extends Model {}
  class Project extends Model {}

  User.init({
    name: { type: DataTypes.STRING },
    email: { type: DataTypes.STRING },
  }, { sequelize, modelName: 'user' })

  Project.init({
    name: { type: DataTypes.STRING },
    active: { type: DataTypes.BOOLEAN }
  }, { sequelize, modelName: 'project' })

  Project.belongsTo(User)
  User.hasMany(Project)

  return { User, Project }
}

sequelize = new Sequelize(DATABASE_URI, {
  // disable string operators (https://github.com/sequelize/sequelize/issues/8417)
  // operatorsAliases: false,
  logging:
    process.env.NODE_ENV === 'test'
      ? false
      : (msg) => {
          log.trace(msg);
        },
  dialectOptions:
    process.env.NODE_ENV !== 'production'
      ? {
          requestTimeout: 40000,
        }
      : {
          requestTimeout: 40000,
          ssl: { rejectUnauthorized: false },
        },
  pool: {
    max: 10,
    min: 0,
    acquire: 40000,
    idle: 40000,
  },
})

export async function checkDatabaseClient(config: IntegrationConfig): Promise<HTTPChecker> {
  const { dbName, dbUser, dbPwd, dbDialect, dbPort, host } = config;

  return new Promise(async (resolve, _) => {
    // init sequelize
    const sequelize = new Sequelize(dbName || "postgres", dbUser || "", dbPwd, {
      dialect: dbDialect || "mysql",
      port: dbPort,
      host,
      logging: false,
    });
    // check authenticate to database
    try {
      await sequelize.authenticate();
      await sequelize.close();
      resolve({
        status: true,
      });
    } catch (error) {
      resolve({
        status: false,
        error,
      });
    }
  });
}

export async function initialize() {
    const sequelize = new Sequelize({
        dialect: "sqlite",
        storage: path.resolve(__dirname, "..", "..", "storage.db"),
        logQueryParameters: true,
        logging: sql => {
            // currently way too noisy because of the fading messages
            if(!sql.includes(FadingMessage.tableName)) {
                log.verbose(sql);
            }
        }
    });

    log.info("Initializing Database Schemas...");

    FadingMessage.initialize(sequelize);
    AdditionalMessageData.initialize(sequelize);
    GuildRagequit.initialize(sequelize);
    Stempel.initialize(sequelize);
    Birthday.initialize(sequelize);
    Ban.initialize(sequelize);
    Penis.initialize(sequelize);
    Boob.initialize(sequelize);
    Nickname.initialize(sequelize);
    Reminder.initialize(sequelize);

    await sequelize.sync();
}

/**
 * Given a list of groups, it will fetch the member count of each,
 * and inserts a "memberCount" field in every group object.
 */
async function extendGroups(groups: Group[]): Promise<ExtendedGroup[]> {
  /**
   * Will return a members count for every group, with the format:
   * [ {groupId: 35, count: "4"}, {groupId: 41, count: "31"} ]
   */
  const memberCount = await Membership.findAll({
    where: { groupId: groups.map(g => g.id) },
    attributes: ['groupId', [Sequelize.fn('COUNT', Sequelize.col('groupId')), 'count']],
    group: ['groupId'],
    raw: true
  });

  return groups.map(g => {
    const match: any = memberCount.find(m => m.groupId === g.id);
    return { ...(g.toJSON() as any), memberCount: parseInt(match ? match.count : 0) };
  });
}

static getAveragePenisSizes = async(): Promise<Record<string, number>> => {
        // Everything hacky, but I just want to implement it.
        const averageObj: Record<string, number> = {};
        // @ts-ignore
        const result: ({ id: string, avgSize: number })[] = (await Penis.findAll({
            attributes: [
                "id",
                [Sequelize.fn("AVG", Sequelize.col("size")), "avgSize"]
            ],
            group: [ "id" ]
        }));

        for(const res of result) {
            averageObj[res.id] = res.avgSize;
        }

        return averageObj;
    };

static initialize(sequelize: Sequelize) {
        this.init({
            id: {
                type: DataTypes.STRING(36),
                defaultValue: () => uuidv4(),
                primaryKey: true
            },
            userId: {
                type: DataTypes.STRING(32),
                allowNull: false
            },
            measuredAt: {
                type: DataTypes.DATE,
                allowNull: true
            },
            size: {
                type: DataTypes.INTEGER,
                allowNull: false
            },
            diameter: {
                type: DataTypes.INTEGER,
                allowNull: false
            }
        }, {
            sequelize,
            indexes: [
                {
                    using: "BTREE",
                    fields: [
                        {
                            name: "measuredAt",
                            order: "ASC"
                        }
                    ]
                }
            ]
        });
    }

static initialize(sequelize: Sequelize) {
        this.init({
            id: {
                type: DataTypes.STRING(36),
                defaultValue: () => uuidv4(),
                primaryKey: true
            },
            userId: {
                type: DataTypes.STRING(32),
                allowNull: false,
                unique: false
            },
            nickName: {
                type: DataTypes.STRING(32),
                allowNull: false,
                unique: true
            }
        },
        {
            sequelize,
            modelName: "Nickname"
        });
    }

static getAverageBoobSizes = async(): Promise<Record<string, number>> => {
        // Everything hacky, but I just want to implement it.
        const averageObj: Record<string, number> = {};
        // @ts-ignore
        const result: ({ id: string, avgSize: number })[] = (await Boob.findAll({
            attributes: [
                "id",
                [Sequelize.fn("AVG", Sequelize.col("size")), "avgSize"]
            ],
            group: [ "id" ]
        }));

        for(const res of result) {
            averageObj[res.id] = res.avgSize;
        }

        return averageObj;
    };

static initialize(sequelize: Sequelize) {
        this.init({
            id: {
                type: DataTypes.STRING(36),
                defaultValue: () => uuidv4(),
                primaryKey: true
            },
            invitator: {
                type: DataTypes.STRING(32),
                allowNull: false
            },
            invitedMember: {
                type: DataTypes.STRING(32),
                allowNull: false,
                unique: true
            }
        },
        {
            sequelize,
            modelName: "Stempel"
        });
    }

static initialize(sequelize: Sequelize) {
        this.init({
            id: {
                type: DataTypes.STRING(36),
                defaultValue: () => uuidv4(),
                primaryKey: true
            },
            userId: {
                type: DataTypes.STRING(32),
                allowNull: false
            },
            measuredAt: {
                type: DataTypes.DATE,
                allowNull: true
            },
            size: {
                type: DataTypes.INTEGER,
                allowNull: false
            }
        }, {
            sequelize,
            indexes: [
                {
                    using: "BTREE",
                    fields: [
                        {
                            name: "measuredAt",
                            order: "ASC"
                        }
                    ]
                }
            ]
        });
    }

/**
 * Given a list of competitions, it will fetch the participant count of each,
 * and inserts a "participantCount" field in every competition object.
 */
async function extendCompetitions(competitions: Competition[]): Promise<ExtendedCompetition[]> {
  /**
   * Will return a participant count for every competition, with the format:
   * [ {competitionId: 35, count: "4"}, {competitionId: 41, count: "31"} ]
   */
  const participantCount = await Participation.findAll({
    where: { competitionId: competitions.map(countMap => countMap.id) },
    attributes: ['competitionId', [Sequelize.fn('COUNT', Sequelize.col('competitionId')), 'count']],
    group: ['competitionId'],
    raw: true
  });

  return competitions.map(c => {
    const match: any = participantCount.find(m => m.competitionId === c.id);
    const duration = durationBetween(c.startsAt, c.endsAt);
    return { ...(c.toJSON() as any), duration, participantCount: parseInt(match ? match.count : 0) };
  });
}

export function init(sequelize: Sequelize):void {
    Type.init({
        path: {
          type: 'LTREE',
          allowNull: false,
          unique: true
        },
        identifier: {
          type: new DataTypes.STRING(250),
          allowNull: false,
          unique: 'uniqueIdentifier'
        },
        link: {
          type: DataTypes.INTEGER.UNSIGNED,
          allowNull: false,
        },
        name: {
          type: DataTypes.JSONB,
          allowNull: false,
        },
        icon: {
          type: new DataTypes.STRING(50),
          allowNull: true,
        },
        iconColor: {
          type: new DataTypes.STRING(50),
          allowNull: true,
        },
        file: {
          type: 'BOOLEAN',
          allowNull: false
        },
        mainImage: {
          type: DataTypes.INTEGER.UNSIGNED,
          allowNull: false,
        },
        images: {
          type: DataTypes.JSONB,
          allowNull: false,
        },        
        options: {
          type: DataTypes.JSONB,
          allowNull: false,
        },
        ...BaseColumns,
        tenantId: { // override base for uniqueIdentifier
          type: new DataTypes.STRING(50),
          allowNull: false,
          unique: 'uniqueIdentifier'
        }
      }, {
        tableName: 'types',
        paranoid: true,
        timestamps: true,
        sequelize: sequelize, // this bit is important
        scopes: {
          tenant(value) {
            return {
              where: {
                tenantId: value
              }
            }
          }
        }        
    });    
}

constructor (path: string) {
    super()
    this.sequelize = new Sequelize({
      dialect: 'sqlite',
      storage: path,
      logging: false
    })
  }

sequelize:Sequelize

export function init(sequelize: Sequelize):void {
  ItemRelation.init({
        identifier: {
          type: new DataTypes.STRING(250),
          allowNull: false,
          unique: 'uniqueIdentifier'
        },
        itemId: {
          type: DataTypes.INTEGER.UNSIGNED,
          allowNull: false,
        },
        itemIdentifier: {
          type: new DataTypes.STRING(250),
          allowNull: false
        },
        relationId: {
          type: DataTypes.INTEGER.UNSIGNED,
          allowNull: false,
        },
        relationIdentifier: {
          type: new DataTypes.STRING(250),
          allowNull: false
        },
        targetId: {
          type: DataTypes.INTEGER.UNSIGNED,
          allowNull: false,
        },
        targetIdentifier: {
          type: new DataTypes.STRING(250),
          allowNull: false
        },
        values: {
          type: DataTypes.JSONB,
          allowNull: true,
        },
          ...BaseColumns,
        tenantId: { // override base for uniqueIdentifier
          type: new DataTypes.STRING(50),
          allowNull: false,
          unique: 'uniqueIdentifier'
        }
      }, {
        tableName: 'itemRelations',
        paranoid: true,
        timestamps: true,
        sequelize: sequelize,
        scopes: {
          tenant(value) {
            return {
              where: {
                tenantId: value
              }
            }
          }
        }        
    });    
}

async function getLeaderboard(filter: LeaderboardFilter, pagination: Pagination) {
  const { playerBuild, country } = filter;
  const countryCode = country ? findCountry(country)?.code : null;

  if (filter.metric && ![...VIRTUAL_METRICS, 'ehp+ehb'].includes(filter.metric)) {
    throw new BadRequestError('Invalid metric. Must be one of [ehp, ehb, ehp+ehb]');
  }

  if (playerBuild && !PLAYER_BUILDS.includes(playerBuild as PlayerBuild)) {
    throw new BadRequestError(`Invalid player build: ${playerBuild}.`);
  }

  if (country && !countryCode) {
    throw new BadRequestError(
      `Invalid country. You must either supply a valid code or name, according to the ISO 3166-1 standard. \
      Please see: https://en.wikipedia.org/wiki/ISO_3166-1_alpha-2`
    );
  }

  const metric = filter.metric || Metrics.EHP;
  const playerType = filter.playerType || PlayerType.REGULAR;

  const isCombined = metric === 'ehp+ehb';

  const query = buildQuery({ type: playerType, build: playerBuild, country: countryCode });

  // When filtering by player type, the ironman filter should include UIM and HCIM
  if (query.type && query.type === PlayerType.IRONMAN) {
    query.type = { [Op.or]: [PlayerType.IRONMAN, PlayerType.HARDCORE, PlayerType.ULTIMATE] };
  }

  const results = await Player.findAll({
    attributes: isCombined && { include: [['(ehp + ehb)', 'ehp+ehb']] },
    where: query,
    order: isCombined ? [[Sequelize.literal(metric), 'DESC']] : [[metric, 'DESC']],
    limit: pagination.limit,
    offset: pagination.offset
  });

  if (metric === Metrics.EHP && pagination.offset < 50 && playerType === PlayerType.REGULAR) {
    // This is a bit of an hack, to make sure the max ehp accounts always
    // retain their maxing order, manually set their registration dates to
    // ascend and use that to order them.
    return results.sort((a, b) => {
      return b.ehp - a.ehp || a.registeredAt.getTime() - b.registeredAt.getTime();
    });
  }

  return results;
}

export function init(sequelize: Sequelize):void {
    Item.init({
        identifier: {
          type: new DataTypes.STRING(250),
          allowNull: false,
          unique: 'uniqueIdentifier'
        },
        path: {
          type: 'LTREE',
          allowNull: false,
          unique: true
        },
        name: {
          type: DataTypes.JSONB,
          allowNull: false,
        },
        typeId: {
          type: DataTypes.INTEGER.UNSIGNED,
          allowNull: false,
        },
        typeIdentifier: {
          type: new DataTypes.STRING(250),
          allowNull: false
        },
        parentIdentifier: {
          type: new DataTypes.STRING(250),
          allowNull: false
        },
        values: {
          type: DataTypes.JSONB,
          allowNull: true,
        },
        channels: {
          type: DataTypes.JSONB,
          allowNull: true,
        },
        fileOrigName: {
          type: new DataTypes.STRING(250),
          allowNull: false
        },
        storagePath: {
          type: new DataTypes.STRING(500),
          allowNull: false
        },
        mimeType: {
          type: new DataTypes.STRING(250),
          allowNull: false
        },
        ...BaseColumns,
        tenantId: { // override base for uniqueIdentifier
          type: new DataTypes.STRING(50),
          allowNull: false,
          unique: 'uniqueIdentifier'
        }
      }, {
        tableName: 'items',
        paranoid: true,
        timestamps: true,
        sequelize: sequelize, // this bit is important
        scopes: {
          tenant(value) {
            return {
              where: {
                tenantId: value
              }
            }
          }
        }        
    });    
}

export function init(sequelize: Sequelize):void {
  Relation.init({
      identifier: {
        type: new DataTypes.STRING(250),
        allowNull: false,
        unique: 'uniqueIdentifier'
      },
      name: {
        type: DataTypes.JSONB,
        allowNull: false,
      },
      sources: {
        type: DataTypes.JSONB,
        allowNull: true,
      },
      targets: {
        type: DataTypes.JSONB,
        allowNull: true,
      },
      child: {
        type: 'BOOLEAN',
        allowNull: false,
      },
      multi: {
        type: 'BOOLEAN',
        allowNull: false,
      },
      order: {
        type: new DataTypes.INTEGER,
        allowNull: false,
      },
      options: {
        type: DataTypes.JSONB,
        allowNull: false,
      },
      ...BaseColumns,
      tenantId: { // override base for uniqueIdentifier
        type: new DataTypes.STRING(50),
        allowNull: false,
        unique: 'uniqueIdentifier'
      }
    }, {
      tableName: 'relations',
      paranoid: true,
      timestamps: true,
      sequelize: sequelize,
      scopes: {
        tenant(value) {
          return {
            where: {
              tenantId: value
            }
          }
        }
      }        
});    
}

sequelize = new Sequelize({
  dialect: "sqlite",
  storage: sqliteDatabasePath,
  logging: false,
  define: {
    freezeTableName: true
  }
})

db = {
  sequelize: sequelize,
  Sequelize: Sequelize,
}

constructor(sequelize: Sequelize) {
    sequelize.sync();
  }