How to create dynamic insert statement on the basis of POST body

The number of properties in body object is different which is based on a value user_type.

If the user_type is job_seeker then the body object is having these properties --> username, user_type, name, email, password, resume, resume_date_time, salary_expectation. I've created the hard coded insert statement like this -->

insert into users (username, user_type, name, email, password, resume, resume_date_time, salary_expectation) values (?, ?, ?, ?, ?, ?, ?, ?);

If the user_type is employer then the body object is having these properties --> username, user_type, name, email, password, company_name, company_profile. Insert statement for this case is this -->

insert into users (username, user_type, name, email, password, company_name, company_profile) values (?, ?, ?, ?, ?, ?, ?);

So, how to create a dynamic insert query on the basis of varying no. of properties in the body of POST request?

Solution

This is handled for you by the mysqljs module so you don't need to overthink it or over engineer it. Provide your insert payload as an object where the keys share the same naming as your table field names. Whatever you provide will be used in the insert. If you do not provide a field it will defer to the table defaults in your database schema (id will auto populate and increment if you've set it up that way, as will created_at, timestamp or other similar date fields, if a field is set to use a default value when no value is provided, that default will be used).

See the documentation here for further details: https://github.com/mysqljs/mysql#escaping-query-values

Below is an example using the two scenarios you provided.

var mysql = require('mysql');
var connection = mysql.createConnection({
  host: 'localhost',
  user: 'me',
  password: 'secret',
  database: 'my_db'
});

connection.connect();

let job_seeker = {
  username: 'j.smith',
  user_type: 'job_seeker',
  name: 'john smith',
  email: 'j.smith@example.com',
  password: 'keyboard_cat',
  resume: true,
  resume_date_time: '2109-01-01',
  salary_expectation: 100000
};

let employer = {
  username: 'b.burke',
  user_type: 'employer',
  name: 'betty burke',
  email: 'b.burke@example.com',
  password: 'admin_cat',
  company_name: 'Acme Inc.',
  company_profile: 'http://acme.example.com/about_us/'
};


connection.query('INSERT INTO users SET ?', job_seeker, function(error, results, fields) {
  if (error) throw error;
  console.log(results.insertId);
});

connection.query('INSERT INTO users SET ?', employer, function(error, results, fields) {
  if (error) throw error;
  console.log(results.insertId);
});

// if a scenario exists where you might encounter unique key conflicts, consider using a query model that will update on duplicates:

connection.query('INSERT INTO users SET ? ON DUPLICATE KEY UPDATE', employer, function(error, results, fields) {
  if (error) throw error;
  console.log(results.insertId);
});

// this would update every field provided in the employer object. If you didn't want to update every fields, you'd have to list out only the fields you'd like to update:

connection.query('INSERT INTO users SET ? ON DUPLICATE KEY UPDATE name = VALUES(name)', employer, function(error, results, fields) {
  if (error) throw error;
  console.log(results.insertId);
});

// in the above example, only the name field will be updated if a duplicate is found, even though the full employer object is provided.

If you structure your POST body properly prior to delivering it to this step in your code, you can save yourself some time and just let it flow through to the query function with no additional work or object constructing or manipulating.

Edit based on comment

If your plan is to include some query parameters you likely want to use an UPDATE rather than an INSERT. Either way we can use this as an example of how to illustrate the dynamic features provided by the mysqljs module.

Every time you use a ?, it is a placeholder that maps to the array index which follows in the function.

In the documentation section I linked, you can see an example where 4 ? map to 4 values provided in an array that follows. You just need to make sure that the order of the values in your array align with the correct order corresponding to their question marks.

If we want to tackle this all in one query with one object, we can just add some complexity to our POST object such that it contains all the info we will need for our query. Using your example, consider the following:

// you could define the following object as we have for this example, or it could be coming right from req.body

let update_payload = {
  table_name = 'users',
  query_field = 'email',
  query_value = 'j.smith.old@example.com',
  job_seeker = {
    username: 'j.smith',
    user_type: 'job_seeker',
    name: 'john smith',
    email: 'j.smith.new@example.com',
    password: 'keyboard_cat_new',
    resume: true,
    resume_date_time: '2109-01-01',
    salary_expectation: 100000
  }
};


connection.query('UPDATE ?? SET ? WHERE ?? = ? ', [update_payload.table_name, update_payload.job_seeker, update_payload.query_field, update_payload.query_value], function(error, results, fields) {
  if (error) throw error;
  console.log(results);
});

// expected console output…

OkPacket {
  fieldCount: 0,
  affectedRows: 1,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '(Rows matched: 1  Changed: 1  Warnings: 0',
  protocol41: true,
  changedRows: 1
}

Note: You will see sometimes I use ?? while other times I use ?. This is a feature of the module which provides escaping to prevent SQL injection. Depending on the specifics of your database configuration, architecture, field types, and your personal security policies, the necessity of where and when you use a single vs. a double will vary and will require testing with your own code base.