DB-DIFF

This Node.js script is designed to help generate the differences between two database.

The purpose of this script is to synchronize two databases, db1 and db2, by identifying any missing tables, columns, and stored procedures in either database and generating the necessary SQL queries to make both databases consistent.

The script generates JSON files that contain a list of the missing objects in each database. it also generates SQL queries that need to be executed on both databases to bring them to the same state. These SQL queries are saved in the following directory structure:

├── migrations
│   ├── {timestamp}
│   │   ├── db1
│   │   │   ├── create.sql
│   │   │   ├── alter.sql
│   │   │   ├── store-proceedure-files.sql
│   │   │   ├── store-proceedure-files.sql
│   │   │   └── ....
│   │   └── db2
│   │   │   ├── create.sql
│   │   │   ├── alter.sql
│   │   │   ├── store-proceedure-files.sql
│   │   │   ├── store-proceedure-files.sql
│   │   │   └── ....

Prerequisites

Please note that before using the script, you need to set the following environment variables:

• DB1_HOST: The hostname of db1.

• DB1_USER: The username used to authenticate with db1.

• DB1_PASSWORD: The password used to authenticate with db1.
  

• DB2_HOST: The hostname of db2.

• DB2_USER: The username used to authenticate with db2.

• DB2_PASSWORD: The password used to authenticate with db2.
  

• DATABASE_NAME: The name of the database to compare

Here are instructions on how to start and use the script

Installation

1. Clone the repository or download the script file.
2. Install Node.js and npm if you haven't already.
3. Open a terminal and navigate to the directory containing the script.
4. Install the required dependencies by running npm install.

Usage

To run the script, enter the following command in your terminal:

npm run start

Sample Output

The script generate two json files tables.json and stored-p.json

• tables.json contains information about missing tables and colomns for each db which is in this format

{
  "stats": {
    ... but in db1
    "totalMissingTableInDb2": "number",
    ... but in db2
    "totalMissingTableInDb1": "number",
    ... existing tables but missing colomns in both
    "totalDifferenceBetweenDBs": "number"
  },
  missing table in db2 but in db1
  "missingTableInDb2": [
    {
      "schema_name": "string",
      "table_name": "string",
      "columns": [
        {
          "column_name": "string",
          "data_type": "string",
          "max_length": "number",
          "default_value": "string|number|any",
          "is_nullable": "boolean"
        }
      ]
    }
  ],

  missing table in db1 but in db2
  "missingTableInDb1": [
    {
      "schema_name": "string",
      "table_name": "string",
      "columns": [
        {
          "column_name": "string",
          "data_type": "string",
          "max_length": "number",
          "default_value": "string|number|any",
          "is_nullable": "boolean"
        }
      ]
    }
  ],

  tables that exist in both dbs but has missing columns based on the other db
  "existingTablesDifferences": [
    {
      "table_name": "string",
      "missingColumnsInDB2": [
        {
          "column_name": "string",
          "data_type": "string",
          "max_length": "number",
          "default_value": "string|number|any",
          "is_nullable": "boolean"
        }
      ],
      "missingColumnsInDB1": [
        {
          "column_name": "string",
          "data_type": "string",
          "max_length": "number",
          "default_value": "string|number|any",
          "is_nullable": "boolean"
        }
      ]
    }
  ]
}

• stored-p.json contains information about missing stored procudures in both db based on the other.

format:

{
  "stats": {
    "totalMissingInDB2": "number",
    "totalMissingInDB1": "number",
    "totalExistInBothButUpdatedFunction": "number"
  },
  missing stored proceedure in db2 but available in db1
  "missingInDB2": [
    {
      "name": "string",
      "raw": "string"
    }
  ],

  missing stored proceedure in db1 but available in db2
  "missingInDB1": [
    {
      "name": "string",
      "raw": "string"
    }
  ],

  exising stored proceedure in both db but with differnt function
  "existInBothButUpdatedFunction": [
    {
      "name": "string",
      "db1Raw": "string",
      "db2Raw": "string"
    }
  ]
}

Disclaimer

Before applying any SQL queries generated by this module, be sure to thoroughly review them to ensure they are appropriate for your specific use case.