TypeScript中Sequelize freezeTableName不生效问题求助

环境版本 #

• node: 18.12.1
• sequelize: 6.29.0
• Typescript: 4.9.5

Sequelize实例配置 #

export const sequelize = new Sequelize(config.db.DB_NAME, config.db.DB_USER, config.db.DB_PASS, {
    host: 'localhost',
    dialect: 'postgres',
    port: 5432,
    define: {
        freezeTableName: true,
        underscored: true,
    }
});

用户模型代码 #

// TYPE DECLARATION and COLUMN NAME DECLARATION...
class User extends Model<InferAttributes<User>, InferCreationAttributes<User>> {
    declare id: CreationOptional<string>;
    declare first_name: string;
    declare last_name: string;
    declare user_name: string;
    declare email: string;
    declare password: string;
    declare last_login: CreationOptional<Date>;
    declare is_active: CreationOptional<boolean>;
    declare is_superadmin: CreationOptional<boolean>;
    declare created_at: CreationOptional<Date>;
    declare updated_at: CreationOptional<Date>;

    fullName(): string {
        return [this.first_name, this.last_name].join(' ');
    }
}

User.init({
    id: {
        type: DataTypes.UUID,
        allowNull: false,
        defaultValue: DataTypes.UUIDV4,
        primaryKey: true,
    },

    first_name: {
        type: DataTypes.STRING,
        allowNull: false
    },
    last_name: {
        type: DataTypes.STRING,
        allowNull: false
    },
    user_name: {
        type: DataTypes.STRING,
        allowNull: false
    },
    email: {
        type: DataTypes.STRING,
        allowNull: false,
        unique: true,
        validate: {
            isEmail: { msg: "invalid email, please provide valid email address." }
        },
    },
    password: {
        type: DataTypes.STRING,
        allowNull: false,
        set(value: string) {
            const pass = hashSync(value, 10);
            this.setDataValue("password", pass)
        }
    },
    last_login: {
        type: DataTypes.DATE,
        allowNull: false,
        defaultValue: DataTypes.NOW,
    },
    is_active: {
        type: DataTypes.BOOLEAN,
        allowNull: false,
        defaultValue: false,
    },
    is_superadmin: {
        type: DataTypes.BOOLEAN,
        allowNull: false,
        defaultValue: false,
    },

    created_at: DataTypes.DATE,
    updated_at: DataTypes.DATE,

}, {
    sequelize,
    schema: "auth",
    // freezeTableName: true,
    // modelName: 'User',
    // tableName: 'user',
    createdAt: "created_at",
    updatedAt: "updated_at"
});

export default User;

报错信息 #

error: relation "auth.User" does not exist

生成的SQL查询 #

Executing (default): SELECT "id", "first_name", "last_name", "user_name", "email", "password", "last_login", 
"is_active", "is_superadmin", "created_at", "updated_at" FROM "auth"."User" AS "User" WHERE "User"."first_name" = 'nawaraj' LIMIT 1;

问题分析 #

你误解了freezeTableName的作用：它只是阻止Sequelize将模型名复数化作为表名，并不会自动将表名转为小写。PostgreSQL对标识符大小写敏感：如果创建表时未加双引号，表名会被自动转为小写（比如你实际数据库里的表是auth.user），但Sequelize生成的查询用了带双引号的"User"，这会严格匹配大写表名，导致找不到对应关系。

解决方案 #

在模型的init配置里显式指定tableName: 'user'，覆盖默认的模型名作为表名的行为：

修改模型的init配置部分：

}, {
    sequelize,
    schema: "auth",
    freezeTableName: true, // 可以保留，确保表名不被复数化
    tableName: 'user', // 显式指定小写表名
    createdAt: "created_at",
    updatedAt: "updated_at"
});

这样Sequelize生成的SQL会查询auth.user，和数据库里的实际表名匹配，就能解决这个错误。

内容的提问来源于stack exchange，提问作者Nawaraj Jaishi