I am beginning to build out the user authentication part of my application and I am using PostgreSQL as my database. I have a table set up in PG with the following code. This is the code I used to set-up my USERS table. When I did INSERTS in the psql console everything works fine. However, when I do it in my NodeJS application which uses the pg npm package and is querying the database via a client instance, does NOT work properly.
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();
Command Line (successful) Attempts:
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 Code (unsuccessful) Attempt
Below is my NodeJS code that I currently have for this query. I have an async/await function wrapped inside of a POST method. I am destructuring the values from req.body and then inserting them into the SQL query as parameters in place of the $1, $2, $3...etc, and for the NON-required values (like description, image, and friends), I have a statement like friends||null as a fallback to be precautionary (I'm not 100% sure it's needed). I also have some primitive error handling going on, but that is just there for me to have something atm. I will update that when it the time comes and it is more important for the application.
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 });
}
});
The result of the above NodeJS code:
{
"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"
}
When I plug in the following object on PostMan (with raw data button selected, application/JSON set as the text type).
{
"username": "Amy",
"password": "123456",
"date_of_birth": "02/24/1957",
"gender": "female",
"role": "member",
"description": "I am a fun girl",
"friends": ["Jack", "Suzie"]
}
I am hoping to figure out what I am doing wrong with this enumerator type. I am thinking it's something to do with the way I'm inputting the data with PostMan because the psql console works just fine. Please forgive me if this post is long-winded. I am fairly new to backend programming, so I am taking in a lot of information here, and someone's help will be extremely appreciated. Just for the record, user's passwords will be encrypted with bCryptJS along with JWTs, but for now, I'm just trying to get a simple INSERT to work!!
Thank you!
BODY { username: 'Amy', password: '123456', date_of_birth: '02/24/1957', gender: 'female', role: 'member', description: 'I am a fun girl', friends: [ 'Jack', 'Suzie' ] }– Belita