PostgreSQL - 带有连接表的 SequelizeDatabaseError(代码:42P01) [英] PostgreSQL - SequelizeDatabaseError with join table (Code: 42P01)

问题描述

我正在尝试为多对多关系创建连接表.

I am trying to create a join table for many to many relationships.

我收到以下错误:

"name": "SequelizeDatabaseError",
"parent": {
    "name": "error",
    "length": 110,
    "severity": "ERROR",
    "code": "42P01",
    "position": "13",
    "file": "parse_relation.c",
    "line": "1160",
    "routine": "parserOpenTable",
    "sql": "INSERT INTO \"user_routes\" (\"id\",\"userId\",\"routeId\",\"createdAt\",\"updatedAt\") VALUES (DEFAULT,'1','1','2017-11-15 03:57:21.791 +00:00','2017-11-15 03:57:21.791 +00:00') RETURNING *;"
},

User 和 Route 之间的关系:

module.exports = (sequelize, DataTypes) => {
  const Route = sequelize.define("Route", {
    open: {
      type: DataTypes.BOOLEAN,
      allowNull: true
    }
  });

  Route.associate = models => {
    Route.belongsToMany(models.User, {
      through: "userRoutes",
      as: "users"
    });
  };
  return Route;
};


module.exports = (sequelize, DataTypes) => {
  var User = sequelize.define("User", {
    email: DataTypes.TEXT,
    password: DataTypes.TEXT
  });

  User.associate = models => {
    User.belongsToMany(models.Route, {
      through: "userRoutes",
      as: "routes"
    });
  };

  return User;
};

用户和路由的迁移文件没有太多，只有基础.对于连接表:

Migration files for user and route does not have much but just the basics. For join table:

"use strict";
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable("user_route", {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      userId: {
        type: Sequelize.INTEGER,
        onDelete: "CASCADE",
        references: {
          model: "Users",
          key: "id"
        }
      },
      routeId: {
        type: Sequelize.INTEGER,
        onDelete: "CASCADE",
        references: {
          model: "Routes",
          key: "id"
        }
      }
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable("user_route");
  }
};

我的控制器和路由如下:

My controller and route is as following:

  create(req, res) {
    return UserRoutes.create({
      userId: req.body.userId,
      routeId: req.body.routeId
    })
      .then(userRoute => res.status(201).send(userRoute))
      .catch(err => res.status(400).send(err));
  },



app.post("/api/userRoutes", userRoutesController.create);

因此，当我尝试向该路线发帖时，我在帖子顶部收到了错误消息.

So when I try to post to that route, I get the error message on the top of the post.

推荐答案

我遇到了同样的错误，并且同样被一个无关的问题抛出(我的 SQL 子句有单引号，这些单引号都被转义了，并且，因为查询在我删除了它们，我专注于删除转义的方法).我的问题是我的迁移是使用违反 PostgreSQL 命名约定的表名生成的；当我更正这个(我的表名中有一个大写的第一个字母，所以我将迁移编辑为小写的第一个字母并重新运行)，错误消失了.也许这可能会解决您的问题?

I had this same error, and was similarly thrown by an unrelated issue (my SQL clause had single quotes that were all escaped, and, since the query ran when I removed them, I was focused on a way to remove the escaping). My problem was that my migration was generated with a table name that violated the PostgreSQL naming convention; when I corrected this (I had a capitalized first letter in my table name, so I edited the migration for a lowercase first letter and reran it), the error disappeared. Maybe that might fix yours?

这篇关于PostgreSQL - 带有连接表的 SequelizeDatabaseError(代码:42P01)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！