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);