CODEWITHSUNDEEP
javascriptmysqlnode.js
writingdeveloper
writingdeveloper

Reputation: 1076

Insert Data to MySQL DB table with shorter db query code

I have some questions about insert the data into MySQL DB. Actually, I already made it work. But I think the code is too long so that It looks awful and hard to modify it when if there are some problems in server or something.

Here's my code and the codes are really long so I want to know that If there are some solutions to make it shorter code.

result variable have JSON Data and it seems that I can use it in db query. I already think that I can do with array or object and I tried some of the codes but they are not working.

router.post("/user", function(req, res, next) {
  let id = req.body.id;
  let githubAPI = "https://api.github.com/users/";

  let options = {
    url: githubAPI + id,
    headers: {
      "User-Agent": "request"
    }
  };
  console.log(id);
  request(options, function(error, response, data) {
    if (error) {
      throw error;
    }
    // result have JSON Data
    let result = JSON.parse(data);

    let nick = result.login;
    let id = result.id;
    let node_id = result.node_id;
    let avatar_url = result.avatar_url;
    let gravatar_id = result.gravatar_id;
    let url = result.url;
    let html_url = result.html_url;
    let followers_url = result.followers_url;
    let following_url = result.following_url;
    let gists_url = result.gists_url;
    let starred_url = result.starred_url;
    let subscriptions_url = result.subscriptions_url;
    let organizations_url = result.organizations_url;
    let repos_url = result.repos_url;
    let events_url = result.events_url;
    let received_events_url = result.received_events_url;
    let type = result.type;
    let site_admin = result.site_admin;
    let name = result.name;
    let company = result.company;
    let blog = result.blog;
    let location = result.location;
    let email = result.email;
    let hireable = result.hireable;
    let bio = result.bio;
    let public_repos = result.public_repos;
    let public_gists = result.public_gists;
    let followers = result.followers;
    let following = result.following;
    let created_at = result.created_at;
    let updated_at = result.updated_at;

    if (bio == null) {
      bio = "Developer";
    }
    db.query(
      `INSERT INTO user (login, id, node_id, avatar_url, gravatar_id, url, html_url, followers_url, following_url, gists_url, starred_url, subscriptions_url, organizations_url, repos_url, events_url, received_events_url, type, site_admin, name, company, blog, location, email, hireable, bio, public_repos, public_gists, followers, following, created_at, updated_at) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)`,
      [
        nick,
        id,
        node_id,
        avatar_url,
        gravatar_id,
        url,
        html_url,
        followers_url,
        following_url,
        gists_url,
        starred_url,
        subscriptions_url,
        organizations_url,
        repos_url,
        events_url,
        received_events_url,
        type,
        site_admin,
        name,
        company,
        blog,
        location,
        email,
        hireable,
        bio,
        public_repos,
        public_gists,
        followers,
        following,
        created_at,
        updated_at
      ]
    );
  });

Upvotes: 0

Views: 61

Answers (1)

Neil Lunn
Neil Lunn

Reputation: 151092

Instead of hard coding SQL and translations from the object, just keep an expected order of fields as a constant and then map the values from parsed JSON into an array of values.

You can also generate the SQL and placeholders from the same constant list:

// Keep a list for the fields where order is important
const fieldOrder = [
  'login',
  'id',
  'node_id',
  'avatar_url',
  'gravatar_id',
  'url',
  'html_url',
  'followers_url',
  'following_url',
  'gists_url',
  'starred_url',
  'subscriptions_url',
  'organizations_url',
  'repos_url',
  'events_url',
  'received_events_url',
  'type',
  'site_admin',
  'name',
  'company',
  'blog',
  'location',
  'email',
  'hireable',
  'bio',
  'public_repos',
  'public_gists',
  'followers',
  'following',
  'created_at',
  'updated_at'
];

// Parse your content in the same place    
let result = JSON.parse(data);

// Extract the value by the same key names
let values = fieldOrder.map(k => result[k]);

// Generate the statement rather than hardcoding    
let sql = `INSERT into user (${fieldOrder.join(',')}) values(${fieldOrder.map(e => '?').join(',')})`

// pass these arguments to your function    
db.query(sql, values);

That's fairly straightforward and is in essence what many ORM libraries do for you under the look in their functional implementation.

Note that many parts of this are reasonably generic and reusable, which is another feature such libraries implement.

So the one "evil" you cannot really avoid is keeping that field list because the order might matter and it's a reasonable way to scrub out any unexpected data.

The "cheap and nasty" way could be:

let fieldOrder = Object.keys(result);

Or even:

let [fieldOrder, values] = Object.entries(result);

But that does not really give you control over valid things being sent in the data, and could also be potentially damaging.

At any rate, even with keeping a constant list somewhere in your code, this is a big reduction from the present listing by simply moving all the places where you listed the same field names into a single list.

Upvotes: 1

Related Questions