jmoneygram jmoneygram - 4 days ago 5
Node.js Question

Using Sequelize/Node.js, how can I insert my awkward TEXT datatype into a postgres database?

I can't seem to get Sequelize to allow me to post this awkward data into my postgres DB.

I originally thought the problem was due to the large size of the file, however I updated bodyParser to allow 100mb of data.

***(The example below is a mere snippet of the entire file, which is normally several hundred characters.)

This data needs to be posted to my database AS IS, so no regex and no reformatting.

When left as noted in the example below OR when attempting to wrap the data in quotes, I receive the error, 'SequelizeValidationError: string violation: data cannot be an array or an object'.

When attempting to use bodyParser.text() instead, I receive the error 'SequelizeValidationError: notNull Violation: data cannot be null'.

Here is an example of the data that I am receiving and trying to post to my DB:

{
"data": {"message":{"$":{"version":"CiVI1122","release":"5","xmlns":"http://www.messages.com/messaging"},"header":[{"recipients":[{"to":[{"_":"myname@cutting.cert.messaging.com","$":{"Qualifier":"MESSAGE","FriendlyName":"Joe"}}]}],"from":[{"_":"bob@bob.messag-cert.com","$":{"Qualifier":"MESSAGE","FriendlyName":"BOB"}}],"messageid":["68d3e5d7c97fb3"],"senttime":["2016-05-12T16:05:43.0000000Z"],"sendersoftware":[{"sendersoftwaredeveloper":["Bob'sSoftware"],"sendersoftwareproduct":["Bob's Integration"],"sendersoftwareversionrelease":["0.1"]}],"information":[{"given":["LOY"],"name":["SHMO"],"dob":["1949-04-01"],"gender":["M"],"zip":["77777"]}]}],"body":[{"message":[{"subject":["Message for: Shmo, Joe Sent: 2016-05-12T16:05:43.0000000Z"],"document":[{"plaintext":[""]}],"attachment":[{"documentname":["null"],"file":[{"documenttype":["application/pdf"],"documentdata":["JVBERi0xLjMNCjEgMCBvYmoNClsvUERGIC9UZXh0IC9JbWFnZUIgL0ltYWdlQyAvSW1hZ2VJXQ0KZW5kb2JqDQo3IDAgb2JqDQo8PCAvTGVuZ3RoIDIzNjkgL0ZpbHRlciAvRmxhdGVEZWNvZGUgPj4gc3RyZWFtDQpYCa1a23LbyBF93yr/Q1flIbuJPJo7BnrZkiXH2pS3fBFj11Y2DxAxIhGDAAOA0jJf49f8ZXoGAAlKBHgRy1WUKII9p0+fvsyMX/3wH6CgQ0JBaU1CHoI2AREhFBa+QvYKP+chERAIRTjnII0hXGtghjBtVk+9GcH53wQwRsIwhNE9UCJUgK+aGnw1QkExAY1LcKZh9N4bpRICSQnDL8Tw4+0/3vz97dXoly9vf4LRv2H0F3g7evXDpzUC1DQolJUVPRg=="]}]}]}]}]}}
}


Here is my 'Message' model:

'use strict';

var dateFormat = require('dateformat');

module.exports = function(sequelize, DataTypes) {
var Surescript = sequelize.define('Message', {
data: {
type: DataTypes.TEXT,
allowNull: false
}
}, {
classMethods: {
associate: function(models) {
// associations can be defined here
}
},
instanceMethods: {
insertDate: function() {
return dateFormat(this.createdAt, "dddd, mmmm dS, yyyy, h:MM TT")
}
}
});
return Message;
};


Here is my POST route:

router.post('/', utils.auth, function(req, res, next) {
var currentUser = auth(req);
UserModel.findOne({
where: { username: currentUser.name, password: currentUser.pass }
}).then(function(user) {
if (user.username == currentUser.name && user.password == currentUser.pass && user.admin == true) {
MessageModel.create({
data: req.body.data
})
res.send('POST');
}
})
.then(function() {
res.end();
})
.catch(function(err) {
res.sendStatus(401);
});
});


I have this code in my server.js file:

app.use(methodOverride('_method'));
app.use(logger('dev'));
app.use(bodyParser.json({limit: '100mb'}));
app.use(bodyParser.urlencoded({ limit: '100mb', extended: false }));
app.use(cookieParser());
app.use(express.static(path.join(__dirname, 'public')));


Thus far I have tried changing the data type in my Message model from TEXT to JSON to BLOB, however those changes resulted in the same issue.

Any help is greatly appreciated!!!

Answer

If that's properly valid JSON, you may want to check out the following Sequelize type:

http://docs.sequelizejs.com/en/v3/api/datatypes/json

Or

http://docs.sequelizejs.com/en/v3/api/datatypes/jsonb

Along with the proper type in postgresql:

https://www.postgresql.org/docs/9.4/static/datatype-json.html

Another awesome note about the JSON field is that it is kind of treated like a No-SQL database in and of itself, so you can do some cool querying into the field itself.

Edit: Also, I noticed you still had an issue with JSON too; it's important that you JSON.stringify that object before attempting to store it. That's probably the full root of the problem since it doesn't want to store an object, but rather a text representation of the object.

Comments