Node & SQL: complex insert vs sql function

Question

I am designing node + postgres web service and I need to do 3 inserts to 3 tables. Should I do 3 separate insert queries in node backend code, or implement postresql function.

Example:

    insert into users(id, name, address) values (1, 'username', 'address') returning id as userid;
    insert into company(id, contactPersonId, name) values (11, 1, 'Companyname) returning id as companyid;
    insert into report(id, userid, companyid) values (111,1,11);

Or with a function:

create or replace function add_report_details(userid, username, address, companyid, companyname, reportid)
    ....
    insert into users(id, name, address) values (1, 'username', 'address') returning id as userid;
    insert into company(id, contactPersonId, name) values (11, 1, 'Companyname') returning id as companyid;
    insert into report(id, userid, companyid) values (111,1,11);
    ....

Implementing this with function seems like better option, because that way I don't have to change the insert query to multiple places in code if table structure changes or I want to implement some additional features.

Is there any other benefits to use functions or is there some alternative way to handle this?

Edit: My takeaway from this is that it's better to keep insert queries in js function for readability and maintainability sake. However more complex solutions might have more relation with database, so in that case function/procedure might be preferred method.

Here are also some points to consider if you decide to use functions: How to insert data into table using stored procedures in postgresql

If you have any other suggestions or opinions about using sql functions over queries in code please comment below. I feel like this is quite common problem or that I'm not aware of limitations/best practices.

Thank you for your answers and feedback!

If you had a code function that did the same thing as the PG function you wouldn't have to change it in multiple places either. Which is "best" is an opinion, and does it actually matter right now? — Dave Newton, Feb 10 '21 at 14:31
Keeping it in nodejs as a javascript function with all three queries and use that function everywhere is a much better option for code readability and maintenance perspective. You can use a transaction to achieve ACID. — Shaharyar, Feb 10 '21 at 14:34

score 0 · Accepted Answer · answered Feb 10 '21 at 14:27

You could a single query, using a CTE:

WITH s1 AS (
    insert into users(id, name, address) values (1, 'username', 'address') 
    returning id as userid
), s2 AS (
    insert into company(id, contactPersonId, name) 
    SELECT 11
        , userid
        , 'Companyname') 
    FROM s1
    returning id as companyid, userid
)
insert into report(id, userid, companyid) 
SELECT 111
    , userid
    , companyid
FROM s2;

(not tested)

Node & SQL: complex insert vs sql function

1 Answers1