手动插入PostgreSQL数据触发Sequelize createdAt非空约束错误

执行手动SQL插入tenants表时，触发错误：

ERROR: null value in column "createdAt" of relation "tenants" violates not-null constraint

尝试过添加/移除timestamp选项、调整createdAt列属性，问题仍未解决。

相关代码文件 #

迁移文件 #

'use strict';

module.exports = {
    up: async (queryInterface, Sequelize) => {
        await queryInterface.createTable('tenants', {
            id: {
                allowNull: false,
                autoIncrement: true,
                primaryKey: true,
                type: Sequelize.INTEGER
            },
            company_name: {
                type: Sequelize.STRING,
                allowNull: false
            },
            first_name: {
                type: Sequelize.STRING,
                allowNull: false
            },
            last_name: {
                type: Sequelize.STRING,
                allowNull: false
            },
            address_1: {
                type: Sequelize.STRING,
                allowNull: false
            },
            address_2: {
                type: Sequelize.STRING,
                allowNull: true
            },
            city: {
                type: Sequelize.STRING,
                allowNull: false
            },
            state: {
                type: Sequelize.STRING,
                allowNull: true
            },
            postal_code: {
                type: Sequelize.STRING,
                allowNull: false
            },
            country: {
                type: Sequelize.STRING,
                allowNull: false
            },
            email: {
                type: Sequelize.STRING,
                allowNull: false
            },
            phone: {
                type: Sequelize.STRING,
                allowNull: false
            },
            security_code: {
                type: Sequelize.STRING,
                allowNull: false
            },
            account_status: {
                type: Sequelize.ENUM([
                    'active',
                    'inactive',
                    'deleted'
                ]),
                defaultValue: 'inactive'
            },
            forgotPasswordToken: Sequelize.STRING,
            forgotPasswordTokenSetTime: Sequelize.DATE,
            createdAt: {
                allowNull: false,
                type: Sequelize.DATE,
                defaultValue: Sequelize.literal('NOW()'),
            },
            updatedAt: {
                allowNull: false,
                type: Sequelize.DATE,
                defaultValue: Sequelize.literal('NOW()'),
            }
        });
    },

    down: async (queryInterface, Sequelize) => {
        await queryInterface.dropTable('tenants');
    }
};

模型文件 #

module.exports = (sequelize, DataTypes) => {
    const Tenants = sequelize.define('tenants', {
        company_name: {
            type: DataTypes.STRING,
            allowNull: false
        },
        first_name: {
            type: DataTypes.STRING,
            allowNull: false
        },
        last_name: {
            type: DataTypes.STRING,
            allowNull: false
        },
        address_1: {
            type: DataTypes.STRING,
            allowNull: false
        },
        address_2: {
            type: DataTypes.STRING,
            allowNull: true
        },
        city: {
            type: DataTypes.STRING,
            allowNull: false
        },
        state: {
            type: DataTypes.STRING,
            allowNull: true
        },
        postal_code: {
            type: DataTypes.STRING,
            allowNull: false
        },
        country: {
            type: DataTypes.STRING,
            allowNull: false
        },
        email: {
            type: DataTypes.STRING,
            allowNull: false
        },
        phone: {
            type: DataTypes.STRING,
            allowNull: false
        },
        security_code: {
            type: DataTypes.STRING,
            allowNull: false
        },
        status: {
            type: DataTypes.ENUM([
                'active',
                'inactive',
                'deleted'
            ]),
            defaultValue: 'inactive'
        },
        forgotPasswordToken: DataTypes.STRING,
        forgotPasswordTokenSetTime: DataTypes.DATE,

    },
        {
            timestamps: true // Enable timestamps
        }

    );
    return Tenants;
}

插入SQL语句 #

insert into tenants (company_name, first_name, last_name, address_1, city, postal_code, country, email, phone, security_code, status)
values('Matrix Tech', 'Joe', 'Smith', 'A1 Road', 'New York', '467250', 'USA', 'user1@gmail.com', '123456789', '1234', 'active')

问题根源与解决方法 #

核心问题 #

1. 模型与迁移字段不匹配：迁移文件中状态字段为account_status，但模型中写的是status，虽不是直接导致createdAt错误的原因，但会引发数据一致性问题，干扰排查。
2. 数据库中createdAt默认值未生效：迁移文件定义了defaultValue: Sequelize.literal('NOW()')，但如果迁移未正确执行，数据库表的createdAt字段不会有默认值，手动插入时触发非空约束。

解决步骤 #

1. 验证数据库表结构
   执行以下SQL查看tenants表字段定义：

   \d tenants;
   

   确认createdat（PostgreSQL默认小写）字段是否带有DEFAULT now()配置。如果没有，重新执行迁移：

   npx sequelize-cli db:migrate:undo
   npx sequelize-cli db:migrate
   

2. 修正模型与迁移的字段一致性
   将模型中的status字段改为account_status，与迁移文件保持一致：

   account_status: {
       type: DataTypes.ENUM([
           'active',
           'inactive',
           'deleted'
       ]),
       defaultValue: 'inactive'
   },
   

3. 手动插入的两种处理方式

   • 方式一：依赖数据库默认值（推荐）
     确保数据库createdAt和updatedAt有默认值后，直接使用现有插入SQL即可，数据库会自动填充时间值。
   • 方式二：显式指定时间值
     如果默认值仍不生效，插入时手动传入当前时间：

     insert into tenants (company_name, first_name, last_name, address_1, city, postal_code, country, email, phone, security_code, account_status, createdAt, updatedAt)
     values('Matrix Tech', 'Joe', 'Smith', 'A1 Road', 'New York', '467250', 'USA', 'user1@gmail.com', '123456789', '1234', 'active', NOW(), NOW())
     

4. 确认Sequelize配置
   模型中timestamps: true仅对Sequelize的ORM操作生效，手动SQL插入不受此影响，必须确保数据库层面的默认值配置正确。

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