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
CodePudding user response：
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).
CodePudding user response：
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, '');
}
},
})