我开始构建我的应用程序的用户认证部分，并使用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 方法中的 async/await 函数。我正在解构来自req.body的值，然后将它们作为参数插入到 SQL 查询中，以代替$1, $2, $3...etc。对于非必需的值（如description、image和friends），我使用了像friends||null这样的语句作为预防措施（我不确定是否需要）。我还进行了一些基本的错误处理，但那只是我目前的一些东西。当重要性更高时，我将在将来更新它。
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"
}
{
"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加密，以及JWTs，但现在，我只是想让一个简单的INSERT运行起来！
谢谢！
BODY { username: 'Amy', password: '123456', date_of_birth: '02/24/1957', gender: 'female', role: 'member', description: '我是一个有趣的女孩', friends: [ 'Jack', 'Suzie' ] }- brff19pggem中不像预期那样工作，而我设置配置的方式是这样的：const { Client } = require("pg"); const client = new Client({ connectionString: process.env.DATABASE_URL || process.env.PG_CONNECTION_STRING }); client.connect();我将把列类型分配为varchar并以编程方式验证输入（例如if (validate(gender)) { // continue} else { throw new Error("error message"))）。 - brff19