如何使用Sequelize创建并通过钩子初始化动态字段？

问题描述 #

我需要在应用中创建并初始化动态字段的相关帮助。具体来说，我已从MySQL中获取并整理了字段数据，但不确定如何使用钩子来初始化这些字段。能否提供有效完成该任务的建议？

当前代码 #

Sequelize数据模型

const { Model } = require('sequelize');

module.exports = (sequelize, DataTypes) => {
    const getColumns = async (tableName) => {
        console.log(tableName);
        const tableColumns = `SELECT COLUMN_NAME AS columnName
          FROM INFORMATION_SCHEMA.COLUMNS
          WHERE TABLE_NAME = 'results';`;
        const data = await sequelize.query(tableColumns);
        return data[0].reduce(
            (acc, cur) => {
                if (cur.columnName !== 'id') {
                    acc[cur.columnName] = {
                        type: DataTypes.STRING,
                        allowNull: true,
                    };
                    return acc;
                }
                return acc;
            },
            {
                roll: {
                    type: DataTypes.INTEGER,
                    allowNull: false,
                },
            },
        );
    };

    sequelize.addHook('beforeCreate', async (attributes, options) => {
        if (options.tableName === 'results') {
            const columns = await getColumns(options.tableName);
            Object.assign(attributes, columns);
        }
    });

    class Result extends Model {}

    Result.init(
        {},
        {
            sequelize,
            modelName: 'results',
            tableName: 'results',
            underscored: true,
        },
    );

    return Result;
};

Sequelize配置文件

const fs = require('fs');
const path = require('path');
const Sequelize = require('sequelize');

const basename = path.basename(__filename);
const env = process.env.NODE_ENV || 'development';
// eslint-disable-next-line import/no-dynamic-require
const config = require(`${__dirname}/../config/database.js`)[env];
const db = {};

let sequelize;
if (config.use_env_variable) {
    sequelize = new Sequelize(process.env[config.use_env_variable], config);
} else {
    sequelize = new Sequelize(config.database, config.username, config.password, config);
}

fs.readdirSync(__dirname)
    .filter((file) => {
        return file.indexOf('.') !== 0 && file !== basename && file.slice(-3) === '.js';
    })
    .forEach((file) => {
        // eslint-disable-next-line global-require,import/no-dynamic-require
        const model = require(path.join(__dirname, file))(sequelize, Sequelize.DataTypes);
        db[model.name] = model;
    });

Object.keys(db).forEach((modelName) => {
    if (db[modelName].associate) {
        db[modelName].associate(db);
    }
});

db.sequelize = sequelize;
db.Sequelize = Sequelize;

module.exports = db;

问题分析 #

你当前的思路存在核心错误：beforeCreate钩子是在创建模型实例前触发，作用是修改实例的属性值，而非定义模型的字段结构。Sequelize的模型字段是在Model.init()阶段确定的，钩子无法动态添加模型字段定义，因此当前代码无法实现动态初始化字段的需求。

解决方案 #

要实现动态字段初始化，需在Result.init()执行前获取字段定义并传入该方法。由于获取字段的操作是异步的，需调整模型加载和初始化流程：

1. 修改模型文件为异步导出

将模型导出改为异步函数，确保初始化模型前拿到动态字段：

const { Model } = require('sequelize');

module.exports = async (sequelize, DataTypes) => {
    const getColumns = async (tableName) => {
        const tableColumns = `SELECT COLUMN_NAME AS columnName
          FROM INFORMATION_SCHEMA.COLUMNS
          WHERE TABLE_NAME = 'results';`;
        const data = await sequelize.query(tableColumns);
        return data[0].reduce(
            (acc, cur) => {
                if (cur.columnName !== 'id') {
                    acc[cur.columnName] = {
                        type: DataTypes.STRING,
                        allowNull: true,
                    };
                }
                return acc;
            },
            {
                roll: {
                    type: DataTypes.INTEGER,
                    allowNull: false,
                },
            },
        );
    };

    // 初始化前先获取动态字段
    const dynamicColumns = await getColumns('results');

    class Result extends Model {}

    Result.init(dynamicColumns, {
        sequelize,
        modelName: 'results',
        tableName: 'results',
        underscored: true,
    });

    return Result;
};

2. 修改配置文件为异步加载模型

由于模型改为异步导出，配置文件需调整为异步加载，确保所有模型初始化完成后再导出db对象：

const fs = require('fs');
const path = require('path');
const Sequelize = require('sequelize');

const basename = path.basename(__filename);
const env = process.env.NODE_ENV || 'development';
const config = require(`${__dirname}/../config/database.js`)[env];
const db = {};

let sequelize;
if (config.use_env_variable) {
    sequelize = new Sequelize(process.env[config.use_env_variable], config);
} else {
    sequelize = new Sequelize(config.database, config.username, config.password, config);
}

// 异步加载所有模型
const loadModels = async () => {
    const files = fs.readdirSync(__dirname).filter(file => {
        return file.indexOf('.') !== 0 && file !== basename && file.slice(-3) === '.js';
    });

    for (const file of files) {
        const modelFactory = require(path.join(__dirname, file));
        // 调用异步模型工厂函数
        const model = await modelFactory(sequelize, Sequelize.DataTypes);
        db[model.name] = model;
    }

    // 执行模型关联
    Object.keys(db).forEach(modelName => {
        if (db[modelName].associate) {
            db[modelName].associate(db);
        }
    });

    db.sequelize = sequelize;
    db.Sequelize = Sequelize;
};

// 导出Promise，在应用启动时等待模型加载完成
module.exports = loadModels().then(() => db);

3. 应用启动时等待模型加载

在应用入口文件中，需等待模型加载完成后再启动服务：

const db = require('./models');

// 等待模型加载完成
db.then(() => {
    console.log('模型初始化完成');
    // 启动服务器或执行其他操作
    // 示例：app.listen(3000, () => console.log('Server running on port 3000'));
}).catch(err => {
    console.error('模型加载失败:', err);
});

额外建议 #

• 如果results表结构不会频繁变化，可将字段定义缓存起来，避免每次启动都查询INFORMATION_SCHEMA，提升启动速度。
• 注意字段类型映射：当前代码将所有动态字段设为DataTypes.STRING，若表中有其他类型（如INT、DATE等），需在getColumns中查询DATA_TYPE字段并映射对应的Sequelize类型。

内容的提问来源于stack exchange，提问作者Anik Ahmed fathe