There are two tables that are related with a one to many relationship:
envelopes:
CREATE TABLE envelopes (
id integer DEFAULT nextval('envelope_id_seq'::regclass) PRIMARY KEY,
title text NOT NULL,
budget integer NOT NULL
);
transaction:
CREATE TABLE transactions (
id integer DEFAULT nextval('transaction_id_seq'::regclass) PRIMARY KEY,
envelope_id integer REFERENCES envelopes(id),
date date NOT NULL,
title text NOT NULL,
amount integer NOT NULL
);
Each transaction is linked to an envelope and also affects the budget of the envelope.
I'm trying to find the best way to execute this query in Express.js but facing some difficulties.
This is what I have for POST request on /envelopes/:id/transactions:
...
const sql = "INSERT INTO transactions(title, amount, date, envelope_id)VALUES($1, $2, $3, $4) RETURNING *";
try {
const newTransaction = await db.query(sql, [title, amount, date, id]);
res.status(201).send(newTransaction.rows[0]);
} catch (err) {
return res.status(500).send({
error: err.message
});
}
...
I'm unsure if this is the correct approach. Any suggestions?