Export directus Script

export-directus.js

//show tables;
//SHOW FIELDS FROM directus_users

var fs      = require('fs');
var mysql      = require('mysql');
var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'docker',
  password : 'docker',
  database : 'docker'
});
var filename = "./mysql_export.sql";
if(fs.existsSync(filename)) fs.unlinkSync(filename);


connection.connect();

//
// STEP 1 GET THE STRUCTURE
//

// STEP 1.1 GET THE SINGLE TABLE SCHEMA
connection.query('SELECT table_name AS c FROM information_schema.tables WHERE table_schema = DATABASE()', function (error1, results1, fields1) {
  if (error1) throw error1;
  
  // Counter because for some reason this foreach turns async after making the third level depth of MySQL Call See this Mark: #async?
  var crnt=0;
  results1.forEach(element => {
    
    //
    // STEP 1.2 GET THE SINGLE FIELD SCHEMA
    //
    connection.query('SHOW FIELDS FROM '+element.c, function (error2, results2, fields2) {
      crnt++;
      if (error1) throw error1;

      
      // Contains all Field Data + PK Data
      var create_table_data = [];
      var select_the_table_data = [];
      var primary_key="";

      results2.forEach((row_data)=>{
        //Check if Field is PK
        if(row_data.Key.indexOf("PRI")!=-1){
          primary_key = row_data.Field;
        }

        switch(row_data.Null) {
          case "YES":
            row_data.Null="NULL";
            break;
          case "NO":
            row_data.Null="NOT NULL";
            break;
          case undefined:
            row_data.Null=undefined;
            break;
        }

        // Push Field Data String into create_table_data Array
        create_table_data.push([row_data.Field, row_data.Type, row_data.Null, (row_data.Extra.indexOf("auto_increment")!=-1?'AUTO_INCREMENT':undefined)]
                  .filter(function (el) {
                      return !(el == null || el == undefined);
                    })
                  .join(' '));
        select_the_table_data.push([row_data.Field]
                  .filter(function (el) {
                      return !(el == null || el == undefined);
                    })
                  .join(' '));
      });

      // Add PK to the end of create_table_data
      if(primary_key!="") {
        create_table_data.push("PRIMARY KEY ("+primary_key+")");
      }

      fs.appendFileSync(filename, "CREATE TABLE IF NOT EXISTS "+element.c+"(\n       "+ create_table_data.join(',\n       ') +");\n");

      //
      // STEP 2 GET THE DATA
      //
      //fs.appendFileSync(filename,"SELECT `"+ select_the_table_data.join('`, `') +"` FROM "+element.c+";\n");
      
      connection.query("SELECT `"+ select_the_table_data.join('`, `') +"` FROM "+element.c, function (error3, results3, fields3) { // This somehow makes the foreach async #async?
        if (error3) throw error3;
        
        fs.appendFileSync(filename,"\n");
        //
        // STEP 2 GET THE DATA
        //
        // All Data of a single Row is Available in this Loop 
        // single_row: {id:Number,name:String...}
        results3.forEach((single_row)=>{
          normalize(single_row);
          var insert_query ="INSERT INTO "+element.c+" (`"+Object.keys(single_row).join('`, `')+"`) "+
          "VALUES (`"+Object.values(single_row).join('`, `')+"`);\n"
          fs.appendFileSync(filename,insert_query);
        });

      });
      
      //
      // STEP 3 - Close MySQL Conenction when done with all tables.
      //

      console.log(String(crnt).padStart(2,"0"), "/" , results1.length, element.c+"... done.")
      if(crnt==results1.length) {
        connection.end();
      }
    });
  });
});


function normalize(obj) {
  return JSON.parse(JSON.stringify(obj))
}