node.js - "Error": "invalid input value for enum" only when using pg package in application to do INSERT SQL operation

Question

我开始构建我的应用程序的用户身份验证部分，并且我使用 PostgreSQL 作为我的数据库。我在 PG 中设置了一个表，代码如下。这是我用来设置我的 USERS 表的代码。当我在 psql 控制台中执行 INSERTS 时，一切正常。但是，当我在使用 pg npm 包并通过客户端实例查询数据库的 NodeJS 应用程序中执行此操作时，无法正常工作。

  DROP EXTENSION IF EXISTS pgcrypto;
  DROP TYPE IF EXISTS genderEnum;
  DROP TYPE IF EXISTS roleEnum;
  -----------------------------------------------

  CREATE OR REPLACE FUNCTION trigger_set_timestamp()
  RETURNS TRIGGER AS $$
  BEGIN
      NEW.updated_at = NOW();
      RETURN NEW;
      END;
  $$ LANGUAGE plpgsql;

  -----------------------------------------------
  DROP EXTENSION IF EXISTS pgcrypto;
  CREATE EXTENSION pgcrypto;

  -----------------------------------------------

  CREATE TYPE genderEnum AS ENUM ('male', 'female', 'other');
  CREATE TYPE roleEnum AS ENUM ('banned', 'suspended', 'member', 'admin', 'developer');

  -----------------------------------------------
  CREATE TABLE users
  (
  id            serial       NOT NULL PRIMARY KEY,
  username      varchar(33)  NOT NULL UNIQUE,
  password      varchar(255) NOT NULL,
  date_of_birth date         NOT NULL,
  gender        genderEnum   NOT NULL,
  created_at    timestamptz  NOT NULL DEFAULT NOW(),
  updated_at    timestamptz  NOT NULL DEFAULT NOW(),
  role          roleEnum     NOT NULL DEFAULT 'member',
  description   text,
  image         jsonb,
  friends       jsonb
  );

  -----------------------------------------------
  CREATE TRIGGER set_timestamp
  BEFORE UPDATE ON users
  FOR EACH ROW
  EXECUTE PROCEDURE trigger_set_timestamp();

---

命令行(成功)尝试:

INSERT INTO USERS ( username, password, date_of_birth, gender, role, description, friends ) VALUES ( 'Amy', '123456', '02/24/1975', 'female', 'member', 'I am a fun girl.', '["Jack", "Suzie"]' );


INSERT 0 1


SELECT * FROM USERS WHERE username = 'Amy';


 id | username | password | date_of_birth | gender |          created_at           |          updated_at           |  role  |   description    | image |      friends
----+----------+----------+---------------+--------+-------------------------------+-------------------------------+--------+------------------+-------+-------------------
  9 | Amy      | 123456   | 1975-02-24    | female | 2019-08-17 03:19:34.518501-04 | 2019-08-17 03:19:34.518501-04 | member | I am a fun girl. |       | ["Jack", "Suzie"]
(1 row)

---

NodeJS 代码(不成功)尝试

下面是我目前用于此查询的 NodeJS 代码。我有一个包含在 POST 方法中的异步/等待函数。我正在解构 req.body 中的值，然后将它们作为参数插入到 SQL 查询中以代替 $1、$2、$3...etc，并且对于NON - 必需的值(如 description、image 和 friends)，我有一个声明，如 friends||null 作为预防措施的后备(我不是 100% 确定需要它)。我也有一些原始的错误处理正在进行，但那只是为了让我有一些 atm。我会在时机成熟时更新它，它对应用程序更重要。

router.post("/register", async (req, res) => {

  const date = new Date();
  const loggableDate = date.toLocaleDateString();
  const loggableTime = date.toLocaleTimeString();

  const { username, password, date_of_birth, gender, role, description, image, friends } = req.body;

  console.log("\nBODY", req.body, "\n");

  try {
    const user = await database.query(`INSERT into 
    USERS (
      username, 
      password, 
      date_of_birth, 
      gender, 
      role, 
      description, 
      image, 
      friends
    ) 
    VALUES ($1, $2, $3, $4, $5, $6, $7, $8)`, [
        [username],
        [password],
        [date_of_birth],
        [gender],
        [role],
        [description || null],
        [image || null],
        [friends || null]
      ]
    );

    if (!user) {
      return res.status(404).json({ message: errors.clientSideError404, date: loggableDate, time: loggableTime });
    }

    return res.status(200).json(newUser.rows[0]);

  } catch (error) {

    return res.status(500).json({ error: error.stack, date: loggableDate, time: loggableTime });

  }
});

上述 NodeJS 代码的结果:

{
    "error": "error: invalid input value for enum genderenum: \"{\"female\"}\"\n    at Connection.parseE (/Users/JON-DOE/Desktop/virtual-desktop/Work/app/node_modules/pg/lib/connection.js:604:11)\n    at Connection.parseMessage (/Users/JON-DOE/Desktop/virtual-desktop/Work/app/node_modules/pg/lib/connection.js:401:19)\n    at Socket.<anonymous> (/Users/JON-DOE/Desktop/virtual-desktop/Work/app/node_modules/pg/lib/connection.js:121:22)\n    at Socket.emit (events.js:203:13)\n    at addChunk (_stream_readable.js:294:12)\n    at readableAddChunk (_stream_readable.js:275:11)\n    at Socket.Readable.push (_stream_readable.js:210:10)\n    at TCP.onStreamRead (internal/stream_base_commons.js:166:17)",
    "date": "8/17/2019",
    "time": "3:44:51 AM"
}

当我在 PostMan 上插入以下对象时(选择原始数据按钮，应用程序/JSON 设置为文本类型)。

{
    "username": "Amy",
    "password": "123456",
    "date_of_birth": "02/24/1957",
    "gender": "female",
    "role": "member",
    "description": "I am a fun girl",
    "friends": ["Jack", "Suzie"]
}

我希望弄清楚我在使用此枚举器类型时做错了什么。我认为这与我使用 PostMan 输入数据的方式有关，因为 psql 控制台工作正常。如果这篇文章冗长，请原谅我。我对后端编程还很陌生，所以我在这里获取了很多信息，非常感谢有人的帮助。仅作记录，用户的密码将使用 bCryptJS 和 JWT 进行加密，但现在，我只是想让一个简单的 INSERT 工作!!

谢谢!

Answer 1

取消引用后的错误:

error: invalid input value for enum genderenum: "{"female"}"

看起来您尝试插入的值不是female，而是{"female"}。观察:

=> create temporary table test (a genderEnum);
=> insert into test values ('female');
INSERT 0 1
=> insert into test values ('{"female"}')
ERROR:  invalid input value for enum genderenum: "{"female"}"

所以看起来您的数据不是您认为的那样。