I am learning postgres/SQL and am wondering what the best approach is here.
I have an invoice db design that has recipients, drafts, items.
Drafts are the invoices, items are the lines on the invoices, and recipients are who it's going to.
When creating an invoice I have to insert into each table accordingly. Which approach is considered best practice?
There is this one where it's one giant complicated query.
db.query('
WITH new_recipient AS (
INSERT INTO
recipients(...)
VALUES (...)
RETURNING id AS recipient_id, user_id
), new_draft AS (
INSERT INTO drafts(user_id, recipient_id)
SELECT user_id, recipient_id FROM new_recipient
RETURNING id AS draft_id
)
SELECT new_recipient.*, new_draft.* FROM new_draft, new_recipient'
,[...])
OR:
const data = await pool.query(
`INSERT INTO recipients (...) VALUES (...) RETURNING *`,
[....]
);
const draft = await pool.query(
`INSERT INTO drafts (recipient_id, ...) VALUES ($1, ...) RETURNING *`,
[data.rows[0].recipient_id, ...data]
);
I am inclined to use the second approach for readability and simplicity. Is there any reason one should use the first one instead? Perhaps performance is slower if you break it up into multiple queries?
