在 Node 环境中导入 SQL 转储 [英] Import SQL dump within Node environment

问题描述

我想要一个 npm 脚本来创建/配置/等.最后导入一个 SQL 转储.整个创建、配置等都在工作，但是，我无法让导入工作.永远不会插入数据.这是我所拥有的(不要介意嵌套的回调，因为它们会变成承诺):

I'd like a npm script to create/configure/etc. and finally import a SQL dump. The entire creation, configuring, etc. is all working, however, I cannot get the import to work. The data never is inserted. Here's what I have (nevermind the nested callback as they'll be turned into promises):

connection.query(`DROP DATABASE IF EXISTS ${config.database};`, err => {
  connection.query(`CREATE DATABASE IF NOT EXISTS ${config.database};`, err => {
    connection.query('use DATABASENAME', err => {
      const sqlDumpPath = path.join(__dirname, 'sql-dump/sql-dump.sql');
      connection.query(`SOURCE ${sqlDumpPath}`, err => {
        connection.end(err => resolve());
      });
    })
  });
});

我还使用 Sequelize (ORM) 尝试了以下操作:

I also tried the following with Sequelize (ORM):

return new Promise(resolve => {
  const sqlDumpPath = path.join(__dirname, 'sql-dump/sql-dump.sql');
  fs.readFile('./sql/dump.sql', 'utf-8', (err, data) => {
    sequelize
      .query(data)
      .then(resolve)
      .catch(console.error);
  });
}); 

推荐答案

这是我如何设置我的初始 Sequelized 使用 migrations 框架导入.这里有很多事情要做，但总之我:

Here's how I set up my initial Sequelized import using the migrations framework. There is plenty of going on here but in short I:

1. 在迁移文件夹中找到最新的 sql-dump
2. 使用fs
读取文件
3. 将文本拆分为多个查询
4. 检查它是否是一个有效的查询，如果是，则应用我的数据所需的一些清理(查看相关帖子)
5. 推送一个充满查询的数组 - 我首先通过调用 this.down 来确保数据库是干净的
6. 使用 将所有内容作为承诺运行(如此处的建议)mapSeries(不是map)

1. find the latest sql-dump in the migrations folder
2. read the file using fs
3. split the text into queries
4. check if its a valid query and if so apply some cleaning that my data required (see related post)
5. push an array full of queries - I start with making sure that the database is clean by calling the this.down first
6. run everything as a promise (as suggested here) using the mapSeries (not the map)

使用 sequelize-cli 你可以在你的 shell 中创建一个迁移:

Using sequelize-cli you can in your shell create a migration by writing:

sequelize migration:create

您将自动拥有您在下面输入代码的文件.为了执行迁移，您只需编写:

And you will automatically have the file where you enter the code below. In order to execute the migration you simply write:

sequelize db:migrate

"use strict";
const promise = require("bluebird");
const fs = require("fs");
const path = require("path");
const assert = require("assert");
const db = require("../api/models"); // To be able to run raw queries
const debug = require("debug")("my_new_api");

// I needed this in order to get some encoding issues straight
const Aring = new RegExp(String.fromCharCode(65533) +
  "\\" + String.fromCharCode(46) + "{1,3}", "g");
const Auml = new RegExp(String.fromCharCode(65533) +
  String.fromCharCode(44) + "{1,3}", "g");
const Ouml = new RegExp(String.fromCharCode(65533) +
  String.fromCharCode(45) + "{1,3}", "g");

module.exports = {
  up: function (queryInterface, Sequelize) {
    // The following section allows me to have multiple sql-files and only use the last dump
    var last_sql;
    for (let fn of fs.readdirSync(__dirname)){
      if (fn.match(/\.sql$/)){
        fn = path.join(__dirname, fn);
        var stats = fs.statSync(fn);
        if (typeof last_sql === "undefined" ||
            last_sql.stats.mtime < stats.mtime){
          last_sql = {
            filename: fn,
            stats: stats
          };
        }
      }
    }
    assert(typeof last_sql !== "undefined", "Could not find any valid sql files in " + __dirname);

    // Split file into queries
    var queries = fs.readFileSync(last_sql.filename).toString().split(/;\n/);

    var actions = [{
      query: "Running the down section",
      exec: this.down
    }]; // Clean database by calling the down first

    for (let i in queries){
      // Skip empty queries and the character set information in the 40101 section
      //   as this would most likely require a multi-query set-up
      if (queries[i].trim().length == 0 ||
          queries[i].match(new RegExp("/\\*!40101 .+ \\*/"))){
        continue;
      }

      // The manual fixing of encoding
      let clean_query = queries[i]
        .replace(Aring, "Å")
        .replace(Ouml, "Ö")
        .replace(Auml, "Ä");

      actions.push({
        query: clean_query.substring(0, 200), // We save a short section of the query only for debugging purposes
        exec: () => db.sequelize.query(clean_query)
      });
    }

    // The Series is important as the order isn't retained with just map
    return promise.mapSeries(actions, function(item) {
      debug(item.query);

      return item.exec();
    }, { concurrency: 1 });
  },

  down: function (queryInterface, Sequelize) {
    var tables_2_drop = [
      "items",
      "users",
      "usertypes"
    ];
    var actions = [];
    for (let tbl of tables_2_drop){
      actions.push({
        // The created should be created_at
        exec: () => db.sequelize.query("DROP TABLE IF EXISTS `" + tbl +"`")
      });
    }

    return promise.map(actions, function(item) {
      return item.exec();
    }, { concurrency: 1 });/**/
  }
};

这篇关于在 Node 环境中导入 SQL 转储的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！