• Jobs
  • About Us
  • professionals
    • Home
    • Jobs
    • Courses and challenges
    • Questions
    • Teachers
  • business
    • Home
    • Post vacancy
    • Our process
    • Pricing
    • Assessments
    • Payroll
    • Blog
    • Sales
    • Salary Calculator

0

223
Views
Is it better to have separate query functions or one dynamic one?

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();
    }
}
almost 3 years ago · Juan Pablo Isaza
1 answers
Answer question

0

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);
almost 3 years ago · Juan Pablo Isaza Report
Answer question
Find remote jobs

Discover the new way to find a job!

Top jobs
Top job categories
Business
Post vacancy Pricing Our process Sales
Legal
Terms and conditions Privacy policy
© 2025 PeakU Inc. All Rights Reserved.

Andres GPT

Recommend me some offers
I have an error