Using mysql module

data.sql

-- MySQL dump 10.13  Distrib 5.7.16, for osx10.11 (x86_64)
--
-- Host: localhost    Database: consulta_veiculo
-- ------------------------------------------------------
-- Server version	5.7.16

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Current Database: `consulta_veiculo`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `consulta_veiculo` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `consulta_veiculo`;

--
-- Table structure for table `veiculo`
--

DROP TABLE IF EXISTS `veiculo`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `veiculo` (
  `resultado` json NOT NULL,
  `placa` varchar(7) GENERATED ALWAYS AS (json_unquote(json_extract(`resultado`,'$.placa'))) STORED,
  `chassi` varchar(17) GENERATED ALWAYS AS (json_unquote(json_extract(`resultado`,'$.chassi'))) STORED,
  KEY `p` (`placa`),
  KEY `c` (`chassi`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `veiculo`
--

LOCK TABLES `veiculo` WRITE;
/*!40000 ALTER TABLE `veiculo` DISABLE KEYS */;
INSERT INTO `veiculo` (`resultado`) VALUES ('{\"placa\": \"FLU4386\", \"chassi\": \"9BGKS48R0FG314856\", \"veiculo\": \"onix A\"}'),('{\"placa\": \"\", \"chassi\": \"8BGKS48R0FG314856\", \"veiculo\": \"onix X\"}');
/*!40000 ALTER TABLE `veiculo` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Dumping routines for database 'consulta_veiculo'
--
/*!50003 DROP PROCEDURE IF EXISTS `consulta_chassi` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'IGNORE_SPACE,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `consulta_chassi`(IN _chassi VARCHAR(17))
BEGIN
    SELECT resultado FROM veiculo WHERE chassi = _chassi LIMIT 1;
  END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `consulta_placa` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'IGNORE_SPACE,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `consulta_placa`(IN _placa VARCHAR(7))
BEGIN
    SELECT resultado FROM veiculo WHERE placa = _placa LIMIT 1;
  END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `consulta_save` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = utf8 */ ;
/*!50003 SET character_set_results = utf8 */ ;
/*!50003 SET collation_connection  = utf8_general_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'IGNORE_SPACE,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' */ ;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `consulta_save`(IN _resultado JSON)
BEGIN
    DECLARE total INT;
    DECLARE _chassi varchar(17);
    DECLARE _placa varchar(7);
    SET _chassi = json_unquote(json_extract(_resultado,'$.chassi'));
    SET _placa = json_unquote(json_extract(_resultado,'$.placa'));
    IF LENGTH(_chassi) = 17 THEN
      SELECT COUNT(chassi) INTO total FROM veiculo WHERE chassi = _chassi;
      IF total = 0 THEN
        SELECT COUNT(placa) INTO total FROM veiculo WHERE placa = _placa;
        IF total = 0 THEN
          INSERT INTO veiculo (resultado) VALUES (_resultado);
        END IF;
      ELSEIF total = 1 THEN
        SELECT COUNT(placa) INTO total FROM veiculo WHERE placa = _placa OR placa = '' OR placa IS NULL;
        IF total = 1 THEN
          UPDATE veiculo SET resultado = _resultado WHERE chassi = _chassi LIMIT 1;
        END IF;
      END IF;
    END IF;
  END ;;
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2017-11-28  1:48:31

db.js

#!/usr/bin/env node

'use strict'

const {promisify} = require('util')
const {join} = require('path')
const fs = require('fs')
const query = require('../app/lib/mysql/query')

const readFile = promisify(fs.readFile)

readFile(join(__dirname, '..', 'app', 'resource', 'data.sql'), {mode: 0o644})
	.then(sql => {
		const _sql = sql.toString().replace(/DELIMITER ;?;/gm, '').replace(/;;/gm, ';')
		console.log(_sql)
		return query(_sql)
	})
	.then(({results}) => {
		console.log(results)
		process.exit()
	})
	.catch(err => {
		console.error(err)
		process.exit(1)
	})

pool.js

'use strict'

const mysql = require('mysql')

/* istanbul ignore next */
const {
	MYHOST: host = 'localhost',
	MYPORT: port = 3306,
	MYUSER: user = 'root',
	MYPASS: password = ''
} = process.env

const pool = mysql.createPool(
	Object.assign({
		host,
		port,
		user,
		password
	}, {
		connectionLimit: 10,
		multipleStatements: true
	})
)

module.exports = pool

query.js

'use strict'

const pool = require('./pool')

function _conn() {
	return new Promise((resolve, reject) => {
		pool.getConnection((err, connection) => {
			if (err) {
				reject(err)
			} else {
				resolve(connection)
			}
		})
	})
}

function query(q, data = []) {
	return _conn()
		.then(connection => {
			return new Promise((resolve, reject) => {
				connection.query(q, data, (err, results, fields) => {
					connection.release()
					if (err) {
						reject(err)
					} else {
						resolve({results, fields})
					}
				})
			})
		})
}

module.exports = query