How to execute an individual SQL query from a MySQL .sql file containing multiple queries - nodejs

Question

I am currently working with .sql files in node js for the first time and I have a .sql file that is shown below. What code would I need to execute just one query from those shown in the file e.g. the first CREATE TABLE IF NOT EXISTS command? Any help will be greatly appreciated!

I have tried the following code but it does not return all lines of the code correctly. It still parses the first line of the sql file. Code and output shown below.

code:

function parseSqlFile() {
  try {
    const fileName = './youtube(35).sql'
    var sqlData = fs.readFileSync(fileName).toString()
      .replace(/(\r\n|\n|\r)(--[^.].*)/gm," ") // remove newlines
      .replace(/\s+/g, ' ') // excess white space
      .split(';') // split into all statements
      .map(Function.prototype.call, String.prototype.trim)
      .filter(function(el) {return el.length != 0});
    console.log(sqlData)
    return sqlData
  } catch (err) {
    console.log(err)
  }
}

output:

[
  '-- MySQL Workbench Forward Engineering SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0',
  'SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0',
  "SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES'",
  'CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8',
  'USE `mydb`',
  'CREATE TABLE IF NOT EXISTS `mydb`.`videos` (`id` INT NOT NULL, `title` VARCHAR(100) NULL, `date` DATETIME NULL, PRIMARY KEY (`id`)) ENGINE = InnoDB',
  'CREATE TABLE IF NOT EXISTS `mydb`.`channels` (`id` INT NOT NULL, `channel_name` VARCHAR(45) NULL, PRIMARY KEY (`id`)) ENGINE = InnoDB',
  'SET SQL_MODE=@OLD_SQL_MODE',
  'SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS',
  'SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS'
]

-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`foo`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`foo` (
  `id` INT NOT NULL,
  `title` VARCHAR(100) NULL,
  `date` DATETIME NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`foo`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `mydb`.`foo` (
  `id` INT NOT NULL,
  `foo` VARCHAR(45) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Answer 1

Solution

The comment on the first line appears in the result because it's not accounted for in the regex labeled "// remove newlines", which requires that a newline precedes a comment:

/(\r\n|\n|\r)(--[^.].*)/gm

Note that this regex is mislabeled, as it matches comment lines, rather than newline characters.

There a a few ways of updating the regex to match comments on the first line of the file, but since the next regex replace (/\s+/) should also take care of newlines, the simplest is to make it match comment lines only:

/^\s*--[^.].*/gm

Matching (and removing) all comments, not just comments that exist by themselves on a line, by dropping the beginning-of-line anchor ^ would allow parseSqlFile to handle statements that contain embedded comments, but would cause problems with strings containing double dashes (if there are any) in the SQL statements. Depending on what might appear in the SQL file(s), the unanchored regex might be preferable.

Additional

The approach used in parseSqlFile should be sufficient for the sample SQL and, in general, any SQL that doesn't contain data. It will result in errors or other failures if the SQL contains semicolons or double-dashes in quotes (including in backticks, though that would be surprising). If the SQL contains multiple sequential space characters in quotes, the approach won't likely fail outright but will alter the data. This is bound to be an issue with any regex based solution, as JS regexes (as with most flavors) don't support recursion.

A full SQL parser would make the task easy, but is itself much more complex problem to solve. Instead, a tokenizer could be applied, and the token stream processed to produce the desired result.

Another simple solution is to scan the string, skipping quoted regions & escaped characters in quoted regions and taking action when a relevant sequence (';' or '--') is encountered outside of quotes.