NodeJS

MySQL connect with NodeJS

Connect to MySQL database and execute sql queries with NodeJS scripts.

NodeJS dependencies

Add dependencies to package.json

yarn add mysql

db.js

Create a connection var and a config var with the MySQL access

const options = {
  host: {{host}},
  user: {{username}},
  password: {{password}},
  database: {{database}},
};
let connection;

Connect to the database and execute sql query

connection = mysql.createConnection(options);
connection.query(q, (err, row) => {
  if (err) {
    console.log(err);
  } else {
    console.log(row);
  }
});

Full Exemple

Full exemple of a script that makes the conection and implements js Promises.

#! /usr/bin/env node
const fs = require('fs');
const path = require('path');
const colors = require('colors');
const mysql = require('mysql');
const config = JSON.parse(fs.readFileSync(path.resolve(__dirname, '../.sshconfig'), 'utf8'));
const options = {
  host: config.database.host,
  user: config.database.username,
  password: config.database.password,
  database: config.database.database,
};
let connection;
// -----------------------------------
const queryError = (err, query) => {
  console.log(`Error while performing Query:`.red);
  console.log(`${query}`.yellow);
  console.log(`${err}`.red);
  connection.end();
};
const queryHelper = (query, index, data) => {
  query[index] = query[index].replace('%LEAD_ID%', data.id);
  return query;
};
// -----------------------------------
const queriesExecute = (q, index = 0) => {
  connection = mysql.createConnection(options);
  let queries = q;
  new Promise((resolve, reject) => {
    console.log(`${queries[index]}`.yellow);
    connection.query(queries[index], (err, row) => {
      if (err) {
        reject(err);
      } else {
        console.log(`done!`.green);
        resolve(row);
      }
    });
  })
    .then((data) => {
      if (index < queries.length - 1) {
        index++;
        queryHelper(queries, index, {id: data.insertId});
        queriesExecute(queries, index);
      } else {
        connection.end();
      }
    })
    .catch((err) => {
      queryError(err, queries[index]);
    });
};
const queryExecute = (q) => {
  connection = mysql.createConnection(options);
  return new Promise((resolve, reject) => {
    new Promise((resolve, reject) => {
      console.log(`${q}`.yellow);
      connection.query(q, (err, row) => {
        if (err) {
          reject(err);
        } else {
          console.log(`done!`.green);
          // console.log(row);
          resolve(row);
        }
      });
    })
      .then((data) => {
        connection.end();
        resolve(data);
      })
      .catch((err) => {
        queryError(err, q);
      });
  });
};
// -----------------------------------
exports.query = queryExecute;
exports.queries = queriesExecute;

File that makes the sql calls

#! /usr/bin/env node
const db = require('./db');
/**
* Example1
*/
db.queries([
  `INSERT INTO i18n (es) VALUES ('Etapa <span>5</span>')`,
  `INSERT INTO i18n (es) VALUES ('Etapa <span>%LEAD_ID%</span>')`
]);
/**
 * Example2
 */
let createPildora = (num, total) => {
  db.query(`INSERT INTO i18n (es) VALUES ('Etapa <span>${num}</span>')`).then((data) => {
    let i18nId = data.insertId;
    db.query(`INSERT INTO i18n (es) VALUES ('Texto info pildora ${num}')`).then((data) => {
      let pildoraId = data.insertId;
      db.query(`INSERT INTO pildoras (id, titulo_id, texto_id, activa) VALUES (${num}, ${i18nId}, ${pildoraId}, 1)`).then((data) => {
        if (num < total) {
          num++;
          createPildora(num, total);
        }
      });
    });
  });
};
createPildora(1, 10);

Bulk queries with promises

Implementing a Promise function we can recurvise execute a list of queries to a data base. This function wait for the response of each query for execute the next of the list. [ read ]