I am following the guide at https://github.com/nsebhastian/react-node-postgres. Against the comments in below that blog, I can confirm that the full tutorial is working, see this documented at TypeError: Cannot read property 'rows' of undefined.
The ADD and the DELETE command work, and the SELECT to show the table merchant.
The blog ends with:
I’ll leave you the implementation of the UPDATE query as an exercise.
And I thought, why not try that? This was hours ago...
I just took the exact files provided in the GitHub repository.
In the "/node-postgres" directory, I changed:
- "index.js" by adding at the bottom:
app.put('/merchants', (req, res) => {
merchant_model
.updateMerchant(req.body)
.then((response) => {
res.status(200).send(response);
})
.catch((error) => {
res.status(500).send(error);
});
});
- "merchant_model.js" by adding and changing at the bottom, almost copying the
createMerchantfunction of the guide.
The original createMerchant function:
const createMerchant = (body) => {
return new Promise(function (resolve, reject) {
const { name, email } = body;
pool.query(
'INSERT INTO merchants (name, email) VALUES ($1, $2) RETURNING *',
[name, email],
(error, results) => {
if (error) {
reject(error);
}
resolve(
`A new merchant has been added added: ${JSON.stringify(
results.rows[0]
)}`
);
}
);
});
};
Now the new updateMerchant function:
const updateMerchant = (body) => {
return new Promise(function (resolve, reject) {
const { id, name } = body;
pool.autoCommit = true;
pool.query(
'UPDATE merchants SET name = "$2" WHERE id = $1',
[id, name],
(error, results) => {
if (error) {
reject(error);
}
resolve(
`A merchant has been updated: ${JSON.stringify(results.rows[0])}`
);
}
);
});
};
module.exports = {
getMerchants,
createMerchant,
deleteMerchant,
updateMerchant,
};
In the /node-postgres/react-postgres directory, I changed (using PUT method for UPDATE because of Need help about @Get @Post @Put @Delete in Restful webservice or Explain and example about 'get', 'delete', 'post', 'put', 'options', 'patch', 'head' method?):
- "App.js" by adding and changing at the bottom:
function updateMerchant() {
let id = prompt('Enter merchant ID');
let name = prompt('Enter merchant name');
fetch('http://localhost:3001/merchants', {
method: 'PUT',
headers: {
'Content-Type': 'application/json',
},
body: JSON.stringify({ id, name }),
})
.then((response) => {
return response.text();
})
.then((data) => {
alert(data);
getMerchant();
});
}
return (
<div>
{merchants ? merchants : 'There is no merchant data available'}
<br />
<button onClick={createMerchant}>Add</button>
<br />
<button onClick={deleteMerchant}>Delete</button>
<br />
<button onClick={updateMerchant}>Update</button>
</div>
);
When I click on Update in the React app in the browser at localhost:3000, I get the right questions for id and name, running ... 'UPDATE merchants SET name = "$2" WHERE id = $1', [id, name] ...:
and:
which then throws the error:
<!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8"> <title>Error</title> </head> <body> <pre>Cannot PUT /merchants</pre> </body> </html>
which is nothing but this error message in html (I only need to format it as a markdown quote here to get this):
ErrorCannot PUT /merchants
The error stays the same when I run the UPDATE without parametrisation:
... 'UPDATE merchants SET name = "test" WHERE id = 2', [id, name] ...
I have tried switching from PUT method to POST, but then every UPDATE just adds a new merchant with a new id and a null value for the email, see the screenshot above. The record in SQL is not updated by the UPDATE command, you see the change in the table in the first lines in real-time.
What should I do to get the UPDATE to work?
UPDATE:
As answer + comment show, using "" makes a field. But that does not solve the issue, I changed the code to 'UPDATE merchants SET name = $2 WHERE id = $1' since quotation marks are not needed for parameters and I get the error in in "node.js" terminal, stopping the "node.js" service:
my_user@MY_PC:~/my_project/node-postgres$ node index.js
App running on port 3001.
/home/my_user/my_project/node-postgres/merchant_model.js:61
resolve(`A merchant has been updated: ${JSON.stringify(results.rows[0])}`)
^
TypeError: Cannot read property 'rows' of undefined
at pool.query (/home/my_user/my_project/node-postgres/merchant_model.js:61:70)
at Query.client.query [as callback] (/home/my_user/my_project/node-postgres/node_modules/pg-pool/index.js:387:18)
at Query.handleError (/home/my_user/my_project/node-postgres/node_modules/pg/lib/query.js:128:19)
at Client._handleErrorMessage (/home/my_user/my_project/node-postgres/node_modules/pg/lib/client.js:335:17)
at Connection.emit (events.js:198:13)
at parse (/home/my_user/my_project/node-postgres/node_modules/pg/lib/connection.js:114:12)
at Parser.parse (/home/my_user/my_project/node-postgres/node_modules/pg-protocol/dist/parser.js:40:17)
at Socket.stream.on (/home/my_user/my_project/node-postgres/node_modules/pg-protocol/dist/index.js:11:42)
at Socket.emit (events.js:198:13)
at addChunk (_stream_readable.js:288:12)
UPDATE2:
As I has been asked for in the comments, I switched on the log for this test database. Here are the last queries before it worked:
2021-09-20 23:33:09.071 CEST [2637] my_user@my_database LOG: statement: SELECT * FROM merchants ORDER BY id ASC
2021-09-20 23:33:44.346 CEST [2641] my_user@my_database ERROR: bind message supplies 2 parameters, but prepared statement "" requires 0
2021-09-20 23:33:44.346 CEST [2641] my_user@my_database STATEMENT: UPDATE merchants SET name = 'test' WHERE id = 2
2021-09-20 23:37:37.465 CEST [2674] my_user@my_database LOG: statement: SELECT * FROM merchants ORDER BY id ASC
2021-09-20 23:37:38.586 CEST [2674] my_user@my_database LOG: statement: SELECT * FROM merchants ORDER BY id ASC
2021-09-20 23:37:46.853 CEST [2674] my_user@my_database ERROR: bind message supplies 2 parameters, but prepared statement "" requires 1
2021-09-20 23:37:46.853 CEST [2674] my_user@my_database STATEMENT: UPDATE merchants SET name = '$2' WHERE id = $1
2021-09-20 23:41:16.399 CEST [2704] my_user@my_database LOG: statement: SELECT * FROM merchants ORDER BY id ASC
2021-09-20 23:41:31.879 CEST [2706] my_user@my_database ERROR: bind message supplies 2 parameters, but prepared statement "" requires 1
