Sequelize How do you format date to YYYY-MM-DD HH:mm:ss and keep the columns as snake case

Question

Hi I am using Sequelize with a Postgres Database

So I am trying to format date on the sequelize createdAt and updatedAt columns as YYYY-MM-DD HH:mm:ss As well as keeping the columns as snake_case not camelcase so they would be created_at and updated_atHow can I achieve this? I have tried the following:

    createdAt: {
      type: DataTypes.DATE,
      allowNull: false,
      defaultValue: moment.utc().format('YYYY-MM-DD HH:mm:ss'),
      field: 'created_at'
    },

or

    createdAt: {
      type: DataTypes.DATE,
      defaultValue: sequelize.NOW,
      set(value) {
        return value.toISOString().replace(/\..+/g, '')
// coming in the db as 2021-10-31 01:34:48.81+00 so wanted to remove evrything after the dot
      },
      name: 'createdAt',
      field: 'created_at',
    },

Is not working and I am getting this error

          throw new Error(`Value for "${key}" option must be a string or a boolean, got ${typeof this.options[key]}`);
          ^

Error: Value for "createdAt" option must be a string or a boolean, got object

Here is the whole table defined above is what I have pinpointed which I need help with

  const Supplier = sequelize.define('Supplier', {
    id: {
      type: DataTypes.UUID,
      defaultValue: DataTypes.UUIDV4,
      allowNull: false,
      primaryKey: true
    },
    name: {
      type: DataTypes.STRING,
      allowNull: false,
      unique: true,
      validate: {
        len: [1, 50]
      }
    },
    description: {
      type: DataTypes.STRING,
      allowNull: true,
    },
  }, {
    tableName: 'suppliers',
    timestamps: true,
    createdAt: {
      type: DataTypes.DATE,
      defaultValue: sequelize.NOW,
      set(value) {
        return value.toISOString().replace(/\..+/g, '')
      },
      name: 'createdAt',
      field: 'created_at',
    },
    updatedAt: {
      type: DataTypes.DATE,
      defaultValue: sequelize.NOW,
      set(value) {
        return value.toISOString().replace(/\..+/g, '')
      },
      field: 'updated_at',
    },
    // freezeTableName: true
    // paranoid: true
  });

Thanks

Today Sequelize docs is not working here is a webarchive's for it

https://web.archive.org/web/20200731154157/http://sequelize.org/master/index.html

Answer 1

As I understand your question contains some subquetions:

• Sequelize table column underscored names;
• Sequelize date format;
• Usage without moment;

Here's some working example (with requirements mentioned above) I've just got:

Have a sequelize migration like this:

'use strict';

module.exports = {
    up: (queryInterface, Sequelize) => {
        return queryInterface.createTable('items', {
            // ...
            created_at: {
                allowNull: false,
                type: Sequelize.DATE
            },
            // ...
        });
    },
    down: (queryInterface, Sequelize) => {
        return queryInterface.dropTable('items');
    }
};

Have a sequelize model like this:

'use strict';

// WITH moment
// const moment = require('moment');

module.exports = (sequelize, DataTypes) => {
    // const now = new Date();
    return sequelize.define('Item', {
        // ...
        created_at: {
            allowNull: false,
            // defaultValue: now,
            type: DataTypes.DATE,
            get() {
                // 1. WITHOUT moment
                const date = new Date(`${this.dataValues.created_at}`);
                return `${date.toISOString().split('T')[0]} ${date.toLocaleTimeString([], {month: '2-digit', timeStyle: 'medium', hour12: false})}`;
                
                // 2. WITHOUT moment (another solution)
                // const parts = date.toISOString().split('T');
                // return `${parts[0]} ${parts[1].substring(0, 8)}`;
                
                // 3. WITH moment
                // return moment(this.dataValues.created_at).format('D MM YYYY HH:mm:ss'); // 'D MMM YYYY, LT'
            }
        },
        // ...
    }, {
        tableName: 'items',
        freezeTableName: true,
        // underscored: true,
        timestamps: false,
        charset: 'utf8',
        collate: 'utf8_general_ci'
    });
};

Don't forget to rename your table name for your needs, here is "items".

Seems there is an issue posted on github with sequelize "underscored" property. Anyway it worked for me, cuz there is a small trick with other properties, so just do like that and I believe it should work (Sequelize version for me was "^5.22.3").

Sources I've used:

• date formatting with/without Moment.js
• JavaScript Date toLocaleString()
• How to format a JavaScript date
• Javascript add leading zeroes to date
• How do I use .toLocaleTimeString() without displaying seconds?

Feel free to edit and optimize if you need (I did some small edits in the get() method for getting the exact format as you want, anyway I prefer to use moment as a column accessor).

Answer 2

Solution

As for above the get() method will only return the value as formatted to the client but it wont save it to the database. I have found three options to make it save to the database thus the solution for this issue for anyone experiencing something similar.

1. First Option

Inside the node_modules/sequelize/lib/data-types.js

We have to modify the following code

DATE.prototype._stringify = function _stringify(date, options) {
  date = this._applyTimezone(date, options);

  // Z here means current timezone, _not_ UTC
  // return date.format('YYYY-MM-DD HH:mm:ss.SSS Z');// from this to the below code
  return date.format('YYYY-MM-DD HH:mm:ss.SSS'); // to this
};

2. Second Option

If you don't to touch your node_modules folder and do not like option 1, than a somewhat better solution would be to do what you did at option 1 but in your own db.js file:

const { Sequelize } = require('sequelize');
const { DB } = require('../config');

// Override timezone formatting by requiring the Sequelize and doing it here instead
Sequelize.DATE.prototype._stringify = function _stringify(date, options) {
  date = this._applyTimezone(date, options);

  // Z here means current timezone, _not_ UTC
  // return date.format('YYYY-MM-DD HH:mm:ss.SSS Z');
  return date.format('YYYY-MM-DD HH:mm:ss Z');
};

const db = new Sequelize(`${DB.DIALECT}://${DB.USER}:${DB.PASS}@${DB.HOST}:${DB.PORT}/${DB.DB}`, {
  logging: false
})

module.exports = db;

So this two option is more like global way off doing it so the format will be for all your models.

3. Third Option

Final option is to do it for each single models by using hooks

Example:

const Supplier = sequelize.define('Supplier', {
// col attributes 
}, {
    tableName: 'suppliers',
    timestamps: true,
    createdAt: 'created_at',
    updatedAt: 'updated_at',
    hooks : {
      beforeCreate : (record, options) => {
          record.dataValues.created_at = new Date().toISOString().replace(/T/, ' ').replace(/\..+/g, '');
          record.dataValues.updated_at = new Date().toISOString().replace(/T/, ' ').replace(/\..+/g, '');
      },
      beforeUpdate : (record, options) => {
          record.dataValues.updated_at = new Date().toISOString().replace(/T/, ' ').replace(/\..+/g, '');
      }
    },
})