Import sql file using express js

Question

I am trying to import the sql file in express js. i try the following codes but it is not work while using mssql and fs module

fs.readFile(__dirname +'/database.sql', function (err, sqlFile) {
        if(err) { throw err; }
        connection.query(sqlFile, function(err, results) {
            if (err) throw err;
            console.log(results); 
          }); 
        console.log(sqlFile);
});

It is not working it says ER_EMPTY_QUERY: Query was empty

While i try execsql module.

var execsql = require('execsql'),
dbConfig = {
    host: 'localhost',
    user: 'root',
    password: 'root'
},
sql = 'use db_cam;',
sqlFile = __dirname + '/db.sql';

execsql.config(dbConfig) .exec(sql) .execFile(sqlFile, function(err, results){ console.log(results); }).end();

It returns ER_ACCESS_DENIED_ERROR

Answer 1

Try below code using ES6 :

    import mysql from 'mysql';
    import * as path from 'path';
    import fs from 'fs';

    class DBManager {
      constructor() {
        this.conn = mysql.createConnection({
          host: process.env.DB_HOST,
          user: process.env.DB_USER,
          password: process.env.DB_PASSWORD,
          port: process.env.DB_PORT
        });
        let connection=this.conn;
        this.setCurrentDB(function (data) {
          if (data == 'refused') {
            let queries = fs.readFileSync(path.join(__dirname, 'db_file.sql'), { encoding: "UTF-8" }).split(";\n");
            for (let query of queries) {
              query = query.trim();
              if (query.length !== 0 && !query.match(/\/\*/)) {
                connection.query(query, function (err, sets, fields) {
                  if (err) {
                    console.log(`Importing failed for Mysql Database  - Query:${query}`);
                  } else {
                    console.log(`Importing Mysql Database  - Query:${query}`);
                  }
                });
              }
            }
          } else if (data == 'connected') {
            console.log(`Connected to Mysql Database  `);
          }
        });
        this.conn.connect(function (err) {
          if (err) {
            console.log(`Mysql Database connection error`);
          } else {
            console.log(`Connected to Mysql Database`);
          }
        });
      }

  setCurrentDB(callback) {
    this.conn.query(`USE dbname`, function (err, rows) {
      if (err) {
        if (err.errno == 1049) {
          console.log(`${err.sqlMessage} : Failed to connect MySql database`);
          return callback('refused');
        } else {
          console.log(`Mysql Database connection error`);
          return callback('refused');
        }
      } else {
        return callback('connected');
      }
    });
  }

    }
    export default new DBManager();

Answer 2

Here are 2 methods.

1st, use npm package: mysql-import.

const host = 'localhost';
const user = 'root';
const password = 'password';
const database = 'mydb';

const Importer = require('mysql-import');
const importer = new Importer({host, user, password, database});

// New onProgress method, added in version 5.0!
importer.onProgress(progress=>{
  var percent = Math.floor(progress.bytes_processed / progress.total_bytes * 10000) / 100;
  console.log(`${percent}% Completed`);
});

importer.import('path/to/dump.sql').then(()=>{
  var files_imported = importer.getImported();
  console.log(`${files_imported.length} SQL file(s) imported.`);
}).catch(err=>{
  console.error(err);
});

2st, use fs's stream to create one read stream.

const readSQL = (path) => new Promise((resolve, reject) => {
    const fRead = fs.createReadStream(path);

    const objReadLine = readline.createInterface({
        input: fRead,
    });

    const sqlStr = new Array();
    const sqlArr = new Array();

    objReadLine.on('line', line => {
        if(line.endsWith(';')) {
            if(sqlStr.length !== 0) sqlStr.push(line);

            sqlArr.push(sqlStr.join(''));
            sqlStr.length = 0;
        } else if(!line.endsWith(';') && !line.startsWith('--')) {
            sqlStr.push(line);
        }
    });

    objReadLine.on('close', () => {
        resolve(sqlArr);
    })
});

above methods return promise, so you need to use then() to get sql files data. Like this:

const test = readSQL(files).then(data => console.log('this is one array:', data))