Hello campers, I am stuck at this project here message board
I made this app already with MongoDB. now I am trying to make it with Postgres
but, almost stuck at everything from structuring the database to implementing raw queries
I get an error when inserting any doc.
Here are the table commands:
CREATE TABLE threads (
_id SERIAL NOT NULL PRIMARY KEY,
board_id INT NOT NULL REFERENCES board(_id),
text TEXT NOT NULL,
delete_password TEXT NOT NULL,
reported BOOLEAN NOT NULL,
created_on TIMESTAMP NOT NULL,
bumped_on TIMESTAMP
);
CREATE TABLE board (
_id SERIAL NOT NULL PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE replies (
_id SERIAL PRIMARY KEY ,
thread_id INT NOT NULL REFERENCES threads(_id),
text TEXT NOT NULL,
delete_password TEXT NOT NULL,
reported BOOLEAN NOT NULL
);
routes
const pool = require('../pg')
module.exports = function (app) {
// _id, text, created_on(date&time), bumped_on(date&time, starts same as created_on), reported(boolean), delete_password,
// table for every board
function exist(exist) {
const {_id } = exist.rows[0]
return _id
}
async function donotexist(name) {
const board = await pool.query('INSERT INTO board (name) values ($1) returning (_id)', [name])
return board.rows[0]._id
}
app.route('/api/threads/:board')
.get(async (req, res)=> {
const name = req.params.board
const thread_id = req.query
// let board = await pool.query
// ('SELECT * FROM board WHERE name = $1', [name]);
let thread = await pool.query('SELECT * FROM threads where _id = $1', [thread_id]);
let {board_id} = thread.rows[0]
let joined =
await pool.query('select * from board left join threads on board._id = threads.board_id where id = $1 ',[board_id])
})
.post(async (req, res)=> {
try{
const name = req.params.board;
console.log(name)
const { text, delete_password } = req.body;
console.log(req.body)
const exist = await pool.query('select * from board where name = $1', [name]);
let board_id;
if(exist.rows.length > 1) {
board_id = exist(exist)
}
board_id = donotexist(name)
let created_on = Date.now
let bumped_on = Date.now
let reported = false
const thread =
await pool.query(
'INSERT INTO threads (board_id, text, delete_password, reported ,created_on, bumped_on) values ($1, $2, $3, $4, $5, $6) RETURNING (_id)',
[board_id, text, delete_password, reported ,created_on, bumped_on])
console.log(thread)
const thread_id = thread.rows[0]._id
res.redirect(`b/${name}/${thread_id}/`)
}catch(e) {
console.error(e)
}
})
.put((req, res)=> {
const name = req.params.board
})
.delete((req, res)=> {
const name = req.params.board
})
app.route('/api/replies/:name')
.get((req, res)=> {
const name = req.params.board
})
.post((req, res)=> {
const name = req.params.board
})
.put((req, res)=> {
const name = req.params.board
})
.delete((req, res)=> {
const name = req.params.board
})
}
The error I receive when making a post request to /api/threads/:board
{ error: invalid input syntax for type integer: "{}"
at Parser.parseErrorMessage (D:\boilerplate-project-messageboard\node_modules\pg-protocol\dist\parser.js:278:15)
at Parser.handlePacket (D:\boilerplate-project-messageboard\node_modules\pg-protocol\dist\parser.js:126:29)
at Parser.parse (D:\boilerplate-project-messageboard\node_modules\pg-protocol\dist\parser.js:39:38)
at Socket.stream.on (D:\boilerplate-project-messageboard\node_modules\pg-protocol\dist\index.js:8:42)
at Socket.emit (events.js:198:13)
at addChunk (_stream_readable.js:288:12)
at readableAddChunk (_stream_readable.js:269:11)
at Socket.Readable.push (_stream_readable.js:224:10)
at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
length: 169,
name: 'error',
severity: 'ERROR',
code: '22P02',
detail: undefined,
hint: undefined,
position: undefined,
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file:
'd:\\pginstaller_12.auto\\postgres.windows-x64\\src\\backend\\utils\\adt\\numutils.c',
line: '259',
routine: 'pg_strtoint32' }
popps
{ board: 'popps', text: 'we', delete_password: 'wewe' }
(node:8472) UnhandledPromiseRejectionWarning: error: duplicate key value violates unique constraint "board_name_key"
at Parser.parseErrorMessage (D:\boilerplate-project-messageboard\node_modules\pg-protocol\dist\parser.js:278:15)
at Parser.handlePacket (D:\boilerplate-project-messageboard\node_modules\pg-protocol\dist\parser.js:126:29)
at Parser.parse (D:\boilerplate-project-messageboard\node_modules\pg-protocol\dist\parser.js:39:38)
at Socket.stream.on (D:\boilerplate-project-messageboard\node_modules\pg-protocol\dist\index.js:8:42)
at Socket.emit (events.js:198:13)
at addChunk (_stream_readable.js:288:12)
at readableAddChunk (_stream_readable.js:269:11)
at Socket.Readable.push (_stream_readable.js:224:10)
at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
(node:8472) UnhandledPromiseRejectionWarning: Unhandled promise rejection. This error originated either by throwing inside of an async function without a catch block, or by rejecting a promise which was not handled with .catch(). (rejection id: 2)
{ error: invalid input syntax for type integer: "{}"
at Parser.parseErrorMessage (D:\boilerplate-project-messageboard\node_modules\pg-protocol\dist\parser.js:278:15)
at Parser.handlePacket (D:\boilerplate-project-messageboard\node_modules\pg-protocol\dist\parser.js:126:29)
at Parser.parse (D:\boilerplate-project-messageboard\node_modules\pg-protocol\dist\parser.js:39:38)
at Socket.stream.on (D:\boilerplate-project-messageboard\node_modules\pg-protocol\dist\index.js:8:42)
at Socket.emit (events.js:198:13)
at addChunk (_stream_readable.js:288:12)
at readableAddChunk (_stream_readable.js:269:11)
at Socket.Readable.push (_stream_readable.js:224:10)
at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
length: 169,
name: 'error',
severity: 'ERROR',
code: '22P02',
detail: undefined,
hint: undefined,
position: undefined,
internalPosition: undefined,
internalQuery: undefined,
where: undefined,
schema: undefined,
table: undefined,
column: undefined,
dataType: undefined,
constraint: undefined,
file:
'd:\\pginstaller_12.auto\\postgres.windows-x64\\src\\backend\\utils\\adt\\numutils.c',
line: '259',
routine: 'pg_strtoint32' }
Thank you guys,
I would love IF someone offers to help me with this project