My journey with PostgreSQL begins as I delve into creating a REST API using Sequelize for PostgreSQL, Express.js, and Node.js. One of the challenges I encountered was setting up a user system where each user can have multiple parcels associated with them. However, when trying to create a new parcel linked to a user through their userId, I kept running into a vague database error in Sequelize without any clear message, only indicating 'checkInsertTarget' routine being the issue.
Error Encountered
{
"name": "SequelizeDatabaseError",
"parent": {
"name": "error",
"length": 190,
"severity": "ERROR",
"code": "42703",
"position": "48",
"file": "d:\\pginstaller.auto\\postgres.windows-x64\\src\\backend\\parser\\parse_target.c",
"line": "1033",
"routine": "checkInsertTargets",
"sql": "INSERT INTO \"Parcels\" (\"id\",\"name\",\"delivered\",\"presentLoc\",\"destination\",\"description\",\"createdAt\",\"updatedAt\",\"userId\") VALUES (DEFAULT,'Shoe',false,'Onitsha','ESUT','black shoe, size 34','2018-11-25 15:48:13.100 +00:00','2018-11-25 15:48:13.100 +00:00','3') RETURNING *;"
},
"original": {
"name": "error",
"length": 190,
"severity": "ERROR",
"code": "42703",
"position": "48",
"file": "d:\\pginstaller.auto\\postgres.windows-x64\\src\\backend\\parser\\parse_target.c",
"line": "1033",
"routine": "checkInsertTargets",
"sql": "INSERT INTO \"Parcels\" (\"id\",\"name\",\"delivered\",\"presentLoc\",\"destination\",\"description\",\"createdAt\",\"updatedAt\",\"userId\") VALUES (DEFAULT,'Shoe',false,'Onitsha','ESUT','black shoe, size 34','2018-11-25 15:48:13.100 +00:00','2018-11-25 15:48:13.100 +00:00','3') RETURNING *;"
},
"sql": "INSERT INTO \"Parcels\" (\"id\",\"name\",\"delivered\",\"presentLoc\",\"destination\",\"description\",\"createdAt\",\"updatedAt\",\"userId\") VALUES (DEFAULT,'Shoe',false,'Onitsha','ESUT','black shoe, size 34','2018-11-25 15:48:13.100 +00:00','2018-11-25 15:48:13.100 +00:00','3') RETURNING *;"
}
Parcel Migration Setup
'use strict';
module.exports = {
up: (queryInterface, Sequelize) => {
return queryInterface.createTable('Parcels', {
id: {
allowNull: false,
autoIncrement: true,
primaryKey: true,
type: Sequelize.INTEGER
},
name: {
type: Sequelize.STRING
},
delivered: {
type: Sequelize.BOOLEAN
},
presentLoc: {
type: Sequelize.STRING
},
destination: {
type: Sequelize.STRING
},
description: {
type: Sequelize.STRING
},
createdAt: {
allowNull: false,
type: Sequelize.DATE
},
updatedAt: {
allowNull: false,
type: Sequelize.DATE
},
userId: {
type: Sequelize.INTEGER,
onDelete: 'CASCADE',
references: {
model: 'Users',
key: 'id',
as: 'userId',
}
}
});
},
down: (queryInterface, Sequelize) => {
return queryInterface.dropTable('Parcels');
}
};
Parcel Model Structure
'use strict';
module.exports = (sequelize, DataTypes) => {
const Parcel = sequelize.define('Parcel', {
name: {
type: DataTypes.STRING,
allowNull: false
},
delivered: {
type: DataTypes.BOOLEAN,
defaultValue: false
},
presentLoc: {
type: DataTypes.STRING,
allowNull: false
},
destination: {
type: DataTypes.STRING,
allowNull: false
},
description: DataTypes.STRING,
}, {});
Parcel.associate = (models) => {
Parcel.belongsTo(models.User, {
foreignKey: 'userId',
onDelete: 'CASCADE'
})
};
return Parcel;
};
Parcel Controller Logic
const Parcel = require('../models').Parcel;
const joiSchema = require('../joischema/parcelSchema');
const validator = require('../joischema/validator');
module.exports = {
create(req, res) {
const data = {
name: req.body.name,
description: req.body.description,
destination: req.body.destination,
presentLoc: req.body.presentLoc,
userId: req.params.userId,
};
const valid = validator.isValid(req, res, joiSchema, data);
if (valid != null){
res.status(500).send(valid);
}else{
return Parcel
.create(data)
.then(parcel => res.status(201).send(parcel))
.catch(error => res.status(400).send(error));
}
}