Adonis 5 data recovery script from previous data.

1_instruction.md

1. First run this command into adonisJS => node ace data:migrate > output.txt
2. You got output.txt with all commands that has to run. test this script on local system using backups of old and new, and finally you will get the exact order of execution regarding internal foreign key refernce.
3. Now let's do this on production db. generate a temp db as source_db and run this command with ofcource change the actual db names(source_db and dest_db).

P.S.:: I know one branch which is accidentally deleted was const branchId = 345 on const mainTable = 'branches' write your init logic according to that.

2_DataMigrate.ts

import { BaseCommand } from '@adonisjs/core/build/standalone'
import Knex from 'knex'

export default class DataMigrate extends BaseCommand {
  public static commandName = 'data:migrate'

  public static description = ''

  public static settings = {
    loadApp: true,
    stayAlive: false,
  }

  public selectRecordQuery(srcDb, table, branchId) {
    return `SELECT * FROM ${srcDb}.${table} where branch_id = ${branchId}`
  }

  public copyRecordQuery(srcDb, destDb, table, branchId) {
    return `INSERT INTO ${destDb}.${table} SELECT t1.* FROM ${srcDb}.${table} t1 LEFT JOIN ${destDb}.${table} t2 ON t1.id = t2.id WHERE t2.id IS NULL AND t1.branch_id = ${branchId};`
  }

  public mainTableRecordCopyQuery(srcDb, destDb, table, branchId) {
    return `INSERT INTO ${destDb}.${table} SELECT t1.* FROM ${srcDb}.${table} t1 LEFT JOIN ${destDb}.${table} t2 ON t1.id = t2.id WHERE t2.id IS NULL AND t1.id = ${branchId};`
  }

  public async run() {
    // const { default: env } = await import('@ioc:Adonis/Core/Env')
    const { default: config } = await import('@ioc:Adonis/Core/Config')
    const source = config.get('database.connections')['mysql2']
    // const destination = config.get('database.connections')['mysql']

    const queries: string[] = []

    try {
      const knex1 = Knex(source)
      // const knex2 = Knex(destination)

      const sourceDb = 'source_db'
      const destDb = 'dest_db'
      const mainTable = 'branches'
      const branchId = 345

      // const [branch] = await knex1('branches').where('id', branchId)
      // await knex2('branches').insert(branch)
      // this.logger.info(this.mainTableRecordCopyQuery(sourceDb, destDb, mainTable, branchId))
      queries.push(this.mainTableRecordCopyQuery(sourceDb, destDb, mainTable, branchId))

      const refTables = await knex1.raw(`SELECT
                            KCU.TABLE_NAME AS referencing_table,
                            KCU.COLUMN_NAME AS referencing_column,
                            KCU.CONSTRAINT_NAME,
                            KCU.REFERENCED_TABLE_NAME,
                            KCU.REFERENCED_COLUMN_NAME,
                            RC.DELETE_RULE,
                            RC.UPDATE_RULE
                        FROM
                            information_schema.KEY_COLUMN_USAGE KCU
                            JOIN
                            information_schema.REFERENTIAL_CONSTRAINTS RC
                            ON KCU.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
                        WHERE
                            KCU.TABLE_SCHEMA = '${sourceDb}'
                            AND KCU.REFERENCED_TABLE_NAME = '${mainTable}'
                            -- AND RC.DELETE_RULE = 'SET NULL'
                            -- AND RC.DELETE_RULE = 'NO ACTION'
                            `)

      const records = refTables[0]

      for (let record of records) {
        const refData = await knex1.raw(`SELECT * FROM ${record.referencing_table} WHERE ${record.referencing_column} = ${branchId}`)
        const data = refData[0]
        if (data[0]) {
          // this.logger.info('#########')
          // this.logger.info(JSON.stringify(data[0], null, 2))
          // this.logger.info(`-----------${record.referencing_table}`)
          // this.logger.info(this.selectRecordQuery(sourceDb, record.referencing_table, branchId))

          // this.logger.info(this.copyRecordQuery(sourceDb, destDb, record.referencing_table, branchId))
          queries.push(this.copyRecordQuery(sourceDb, destDb, record.referencing_table, branchId))
        }
      }

      const finalScript = queries.join('\r\n')
      this.logger.info(finalScript)
      await knex1.destroy()
      // await knex2.destroy()
    } catch (e) {
      this.logger.error(e)
    }
  }
}

3_output.txt

[ info ]  INSERT INTO dest_db.branches SELECT t1.* FROM source_db.branches t1 LEFT JOIN dest_db.branches t2 ON t1.id = t2.id WHERE t2.id IS NULL AND t1.id = 345;
INSERT INTO dest_db.borrowers SELECT t1.* FROM source_db.borrowers t1 LEFT JOIN dest_db.borrowers t2 ON t1.id = t2.id WHERE t2.id IS NULL AND t1.branch_id = 345;
INSERT INTO dest_db.board_sub_tasks SELECT t1.* FROM source_db.board_sub_tasks t1 LEFT JOIN dest_db.board_sub_tasks t2 ON t1.id = t2.id WHERE t2.id IS NULL AND t1.branch_id = 345;
INSERT INTO dest_db.board_task_fields SELECT t1.* FROM source_db.board_task_fields t1 LEFT JOIN dest_db.board_task_fields t2 ON t1.id = t2.id WHERE t2.id IS NULL AND t1.branch_id = 345;
INSERT INTO dest_db.export_links SELECT t1.* FROM source_db.export_links t1 LEFT JOIN dest_db.export_links t2 ON t1.id = t2.id WHERE t2.id IS NULL AND t1.branch_id = 345;
INSERT INTO dest_db.board_recurring_tasks SELECT t1.* FROM source_db.board_recurring_tasks t1 LEFT JOIN dest_db.board_recurring_tasks t2 ON t1.id = t2.id WHERE t2.id IS NULL AND t1.branch_id = 345;
INSERT INTO dest_db.client_invoices SELECT t1.* FROM source_db.client_invoices t1 LEFT JOIN dest_db.client_invoices t2 ON t1.id = t2.id WHERE t2.id IS NULL AND t1.branch_id = 345;
INSERT INTO dest_db.export_links SELECT t1.* FROM source_db.export_links t1 LEFT JOIN dest_db.export_links t2 ON t1.id = t2.id WHERE t2.id IS NULL AND t1.branch_id = 345;
INSERT INTO dest_db.board_tasks SELECT t1.* FROM source_db.board_tasks t1 LEFT JOIN dest_db.board_tasks t2 ON t1.id = t2.id WHERE t2.id IS NULL AND t1.branch_id = 345;
INSERT INTO dest_db.board_recurring_tasks SELECT t1.* FROM source_db.board_recurring_tasks t1 LEFT JOIN dest_db.board_recurring_tasks t2 ON t1.id = t2.id WHERE t2.id IS NULL AND t1.branch_id = 345;
INSERT INTO dest_db.board_sub_tasks SELECT t1.* FROM source_db.board_sub_tasks t1 LEFT JOIN dest_db.board_sub_tasks t2 ON t1.id = t2.id WHERE t2.id IS NULL AND t1.branch_id = 345;
INSERT INTO dest_db.board_task_fields SELECT t1.* FROM source_db.board_task_fields t1 LEFT JOIN dest_db.board_task_fields t2 ON t1.id = t2.id WHERE t2.id IS NULL AND t1.branch_id = 345;
INSERT INTO dest_db.board_tasks SELECT t1.* FROM source_db.board_tasks t1 LEFT JOIN dest_db.board_tasks t2 ON t1.id = t2.id WHERE t2.id IS NULL AND t1.branch_id = 345;
INSERT INTO dest_db.borrowers SELECT t1.* FROM source_db.borrowers t1 LEFT JOIN dest_db.borrowers t2 ON t1.id = t2.id WHERE t2.id IS NULL AND t1.branch_id = 345;
INSERT INTO dest_db.client_invoices SELECT t1.* FROM source_db.client_invoices t1 LEFT JOIN dest_db.client_invoices t2 ON t1.id = t2.id WHERE t2.id IS NULL AND t1.branch_id = 345;
INSERT INTO dest_db.export_links SELECT t1.* FROM source_db.export_links t1 LEFT JOIN dest_db.export_links t2 ON t1.id = t2.id WHERE t2.id IS NULL AND t1.branch_id = 345;

4_final_order_of_execution.sql

INSERT INTO dest_db.branches SELECT t1.* FROM temp_source_db.branches t1 LEFT JOIN dest_db.branches t2 ON t1.id = t2.id WHERE t2.id IS NULL AND t1.id = 345;
INSERT INTO dest_db.borrowers SELECT t1.* FROM temp_source_db.borrowers t1 LEFT JOIN dest_db.borrowers t2 ON t1.id = t2.id WHERE t2.id IS NULL AND t1.branch_id = 345;
INSERT INTO dest_db.client_invoices SELECT t1.* FROM temp_source_db.client_invoices t1 LEFT JOIN dest_db.client_invoices t2 ON t1.id = t2.id WHERE t2.id IS NULL AND t1.branch_id = 345;
INSERT INTO dest_db.board_tasks SELECT t1.* FROM temp_source_db.board_tasks t1 LEFT JOIN dest_db.board_tasks t2 ON t1.id = t2.id WHERE t2.id IS NULL AND t1.branch_id = 345;
INSERT INTO dest_db.board_sub_tasks SELECT t1.* FROM temp_source_db.board_sub_tasks t1 LEFT JOIN dest_db.board_sub_tasks t2 ON t1.id = t2.id WHERE t2.id IS NULL AND t1.branch_id = 345;
INSERT INTO dest_db.board_task_fields SELECT t1.* FROM temp_source_db.board_task_fields t1 LEFT JOIN dest_db.board_task_fields t2 ON t1.id = t2.id WHERE t2.id IS NULL AND t1.branch_id = 345;
INSERT INTO dest_db.export_links SELECT t1.* FROM temp_source_db.export_links t1 LEFT JOIN dest_db.export_links t2 ON t1.id = t2.id WHERE t2.id IS NULL AND t1.branch_id = 345;
INSERT INTO dest_db.board_recurring_tasks SELECT t1.* FROM temp_source_db.board_recurring_tasks t1 LEFT JOIN dest_db.board_recurring_tasks t2 ON t1.id = t2.id WHERE t2.id IS NULL AND t1.branch_id = 345;
INSERT INTO dest_db.client_invoices SELECT t1.* FROM temp_source_db.client_invoices t1 LEFT JOIN dest_db.client_invoices t2 ON t1.id = t2.id WHERE t2.id IS NULL AND t1.branch_id = 345;
INSERT INTO dest_db.export_links SELECT t1.* FROM temp_source_db.export_links t1 LEFT JOIN dest_db.export_links t2 ON t1.id = t2.id WHERE t2.id IS NULL AND t1.branch_id = 345;
INSERT INTO dest_db.board_recurring_tasks SELECT t1.* FROM temp_source_db.board_recurring_tasks t1 LEFT JOIN dest_db.board_recurring_tasks t2 ON t1.id = t2.id WHERE t2.id IS NULL AND t1.branch_id = 345;
INSERT INTO dest_db.board_sub_tasks SELECT t1.* FROM temp_source_db.board_sub_tasks t1 LEFT JOIN dest_db.board_sub_tasks t2 ON t1.id = t2.id WHERE t2.id IS NULL AND t1.branch_id = 345;
INSERT INTO dest_db.board_task_fields SELECT t1.* FROM temp_source_db.board_task_fields t1 LEFT JOIN dest_db.board_task_fields t2 ON t1.id = t2.id WHERE t2.id IS NULL AND t1.branch_id = 345;
INSERT INTO dest_db.board_tasks SELECT t1.* FROM temp_source_db.board_tasks t1 LEFT JOIN dest_db.board_tasks t2 ON t1.id = t2.id WHERE t2.id IS NULL AND t1.branch_id = 345;
INSERT INTO dest_db.borrowers SELECT t1.* FROM temp_source_db.borrowers t1 LEFT JOIN dest_db.borrowers t2 ON t1.id = t2.id WHERE t2.id IS NULL AND t1.branch_id = 345;
INSERT INTO dest_db.export_links SELECT t1.* FROM temp_source_db.export_links t1 LEFT JOIN dest_db.export_links t2 ON t1.id = t2.id WHERE t2.id IS NULL AND t1.branch_id = 345;