PostgreSQL - SequelizeDatabaseError with join table (Code: 42P01)

Question

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

I am getting the following error:

"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 *;"
},

The relationship is between User and 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.

I think i kind of figured out the issue. The problem is with the naming convention in sequelize and postgres. Something is not being transfered. It has nothing to do with the many to many relationships. I will update once I resolve the issue. — leogoesger
– leogoesger, Commented Nov 15, 2017 at 18:22

rainydaymatt · Accepted Answer · 2019-02-15 18:36:22Z

5

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?

answered Feb 15, 2019 at 18:36

rainydaymatt

6403 gold badges13 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jerry Chong · Accepted Answer · 2019-05-13 15:58:05Z

2

After you created db, models and migrations, you need to run

sequelize db:migrate

before running your application.

If not, it will throw SequelizeDatabaseError as mentioned above.

answered May 13, 2019 at 15:58

Jerry Chong

9,4504 gold badges54 silver badges44 bronze badges

Comments

Suffix · Accepted Answer · 2022-06-10 10:12:03Z

0

If you look at the line:

"routine": "parserOpenTable"

We will see an error when opening the table. Most likely, the error is due to the naming of the tables. Pay attention to capital letters.

Example, "./models/user.js": tableName "users", but in DBMS we have table "Users".

Just change tableName.

answered Jun 10, 2022 at 10:12

Suffix

407 bronze badges

Collectives™ on Stack Overflow

PostgreSQL - SequelizeDatabaseError with join table (Code: 42P01)

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related