查询现有列时出现SequelizeDatabaseError

Question

我试图创建一个简单的API来获取现有的PostgreSQL表中的数据，但由于某种原因，当它确实存在于数据库和模型中时，它会在Postman响应中抛出'Request failed with status code 501'与消息'SequelizeDatabaseError: column "latitude" does not exist'。

下面是PostgreSQL的patient结构:

Column             |           Type           | Collation | Nullable | Default 
--------------------------------|--------------------------|-----------|----------|---------
patient_ref_no                 | character(32)            |           | not null |
gender                         | character varying(20)    |           |          |
age                            | integer                  |           |          |
longitude                      | numeric                  |           |          |
latitude                       | numeric                  |           |          |
segment                        | character varying(10)    |           |          |

这是../../models/patient.model中的模型:

export default function(sequelize, DataTypes) {
return sequelize.define('patient', {
patient_ref_no: {
type: DataTypes.STRING,
allowNull: false,
primaryKey: true
},
gender: {
type: DataTypes.STRING,
allowNull: false
},
age: {
type: DataTypes.INTEGER,
allowNull: true
},
longitude: {
type: DataTypes.FLOAT,
allowNull: true
},
latitude: {
type: DataTypes.FLOAT,
allowNull: true
},
segment: {
type: DataTypes.STRING,
allowNull: true
}
},
{
tableName: 'patient',
freezeTableName: true,
id: false,
createdAt: false,
updatedAt: false,
}
);
}

下面是我在../../models/index.js中的sequelize初始化:

import config from '../../config/environment';
import Sequelize from 'sequelize';
let db = {
Sequelize,
Op: Sequelize.Op,
sequelize: new Sequelize(config.sequelize.dbName, config.sequelize.userName, config.sequelize.password,
{
host: config.sequelize.host,
dialect: config.sequelize.dialect,
port: config.sequelize.port,
logging: (...msg)=>console.log(msg),
pool: {
max: 5,
min: 0,
idle: 20000,
acquire: 20000
},
dialectOptions: {
dateStrings: true,
typeCast: true,
useUTC: false
},
operatorsAliases: false,
timezone: '+08:00'
})
};
db.PatientModel = db.sequelize.import('../../models/patient.model');
module.exports = db

这是我的控制器:

import { errorJsonResponse } from '../../config/commonHelper';
import { PatientModel } from '../../models'
const {Op} = require('sequelize');
export async function getLonLat(req, res) {
try {
const { segmentIds } = req.body;
const patients = await PatientModel.findAll({
where: Object.assign(conditions, {
segment: {  [Op.in]: segmentIds },
latitude: { [Op.ne]: 0 },
longitude: { [Op.ne]: 0 }
}),
attributes: ['patient_ref_no', 'latitude', 'longitude', 'gender', 'age', 'segment'],
raw: true
});
return res.status(200).json({patients});
} catch(error) {
Log.writeLog(Log.eLogLevel.error, `[getLonLat] : ${JSON.stringify(error)}`);
return res.status(501).json(errorJsonResponse(error.toString(), error.toString()));
}
}

上面只是我怀疑有问题的代码，但实际的端点函数要复杂得多。我很感激你对我的帮助。

Answer 1

发生这种情况是因为我们的开发和生产数据库之间的差异，与这个问题中包含的代码无关。如果遇到同样的问题，请确保获取DB结构和配置，特别是模式,对吧。我们改变了DB prod来适应DB dev，它们现在工作得很好。