ERROR: null value in column "createdAt" of relation "column_name" violates not-null constraint sequelize postgres

Question

I am trying to manually add data to table with a query and i am getting the error ERROR: null value in column "createdAt" of relation "tenants" violates not-null constraint.

I tried adding and removing the timestamp option and playing around with the attributes of the createdAt column

This is my migrations file

'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');
    }
};

and this is my models file

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;
}

this is my insert query:

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')

Answer 1

Sequelize does not support DEFAULT values for PostgreSQL, see the addColumn() documentation. Therefore your table is not created with DEFAULT values and when inserting data without explicit values for those columns they default to NULL which is not allowed in your case.

The following query should work:

insert into tenants (company_name, first_name, last_name, address_1, city, postal_code, country, email, phone, security_code, status, "createdAt", "updatedAt")

values('Matrix Tech', 'Joe', 'Smith', 'A1 Road', 'New York', '467250', 'USA', 'user1@gmail.com', '123456789', '1234', 'active', NOW(), NOW())