I have a React app that uses node js on backend. I use PostgreSQL as my db. I have written several APIs that do various inserts, deletes, or updates. Some of these inserts have proven to be somewhat more complicated in that I may be inserting a large variable amount of records to a table at once. Therefore, I have created separate functions designed to construct these statements. These methods live on the client side and usually loop through some list and create a string that represents the SQL statement to be executed. Then, one of the APIs is called, and it passes this string to it to be executed.
However, I am wondering if this is actually best practice, or if there are reasons to why this is frowned upon, unnecessary, or there's just a plain better way of doing this.
These are some options I have thought of:
Doing the same procedure described, but moving the code to construct the statements to the server side (Though I think this is the worst option of the 3, since it involves more server side code).
Using prepared statements, though I couldn't find a clean way of doing this since my SQL statements have a variable amount of records to be inserted and prepared statements typically expect an explicit number of parameters (correct me if this is wrong).
Stick with what I am doing now (build a String with the SQL statement in it on the client side and call the api passing the string and then executing). I really feel like this is not best practice though for some reason.
Please let me know if any of these options make sense and what I am missing.
Also: I thought I would create an example scenario of what my application does currently to make things clearer.
User: creates a list of "objects". They may add as many "objects" to the list as they want.
User: will hit "submit"
Client code: Will loop through, appending "objects" to a String called "sql_query" which looks something like the following -> "INSERT INTO lists(num, char) VALUES (1, "A"), (2, "B"), (3, "C").
Client code: Calls api: axios.post(api/createlist, {sql_query});
Api: verifies cookies, executes sql command, error handling.
