I'm working on the backend for a web app using node-postgres and I'm curious as to what others think. I need basic insert queries for a users table, products table, and other tables to come. I don't want redundant code but I also don't want to slack on readability. Would it be better to have several similar query functions:
NOTE: createValueString() just creates a parameter string ($1, $2, $3)
async function createUser(user) {
const client = await pool.connect();
const userKeys = Object.keys(user).join(",");
const userValues = Object.values(user);
try {
const { rows } = await client.query(
`
INSERT INTO users(${userKeys})
VALUES (${createValueString(user)})
RETURNING *;
`,
userValues
);
console.log(`USER CREATED`, rows);
return rows;
} catch (error) {
throw error;
} finally {
client.release();
}
}
async function createProduct(product) {
const client = await pool.connect();
const productKeys = Object.keys(product).join(",");
const productValues = Object.values(product);
try {
const { rows } = await client.query(
`
INSERT INTO products(${productKeys})
VALUES (${createValueString(product)})
RETURNING *;
`,
productValues
);
console.log(`PRODUCT CREATED`, rows);
return rows;
} catch (error) {
throw error;
} finally {
client.release();
}
}
OR would it be better to create one dynamic function:
async function insertQuery(tableName, item){
const client = await pool.connect();
const itemKeys = Object.keys(item).join(",");
const itemValues = Object.values(item);
try {
const { rows } = await client.query(
`
INSERT INTO ${tableName}(${itemKeys})
VALUES (${createValueString(itemValues)})
RETURNING *;
`,
itemValues
);
console.log(`ITEM CREATED`, rows);
return rows;
} catch (error) {
throw error;
} finally {
client.release();
}
}
You could potentially have both. Generally, you want code to be fairly clear on what code does what so insertQuery(); doesn't exactly shout this creates a user. Where as, createUser(); obviously does.
What you can have is one function such as executeQuery(sql, params); like your second idea with dynamic functions. As a result, don't have redundant/repeating code.
Simply pass in the SQL string and parameters. This works and makes more sense with stored procedures which is what I recommend you do.
const executeQuery = (sql, params) => {
// sql = "Call stored_procedure_name(parameter list)";
// params = [name, age, etc]
// Perform query code ..
// pool being a configured database connection object.
pool.query(sql, params);
}
Then, have the distinct functions you require re-use this.
const createUsers = () => {
const sql = "Call createUserProcedure(params)";
const params = [name, age, etc];
const res = executeQuery(sql, params);
}
Mysql version:
const sql = "CALL CreateUser(?,?);";
const params = [name,age,etc];
const res = await executeQuery(sql, params);