Working with migrations in ORM-CLI

In my ORM-CLI, the database migration management process is organized through a series of specialized classes that interact with each other to ensure efficiency and accuracy of changes. The main class in this system is MigrationManager, which coordinates the work of all other managers. It serves as the central element responsible for initiating, applying, and rolling back migrations.

IndexManager handles the creation and deletion of indexes in the database. This class compares the current state of indexes with the previous one and generates SQL queries, adapting them to the specifics of each database, whether PostgreSQL or MySQL. Concurrently, TableManager manages all aspects of tables in the database, including creating new tables, deleting old ones, and renaming existing ones.

TriggerManager specializes in triggers, ensuring their creation and deletion according to changes in the database schema. It also adapts queries for different types of databases, ensuring correct execution.

MigrationInvoker is a key class for managing changes in table columns. This class handles migrations related to changes in columns, ensuring their proper execution within the context of the overall migration.

ForeignKeysManager is responsible for managing foreign keys. It generates the necessary SQL queries for creating and deleting foreign keys, comparing them with the previous state of the database.

When a migration is run, MigrationManager activates all relevant managers, each performing its role. IndexManager and TableManager handle changes to indexes and tables, respectively, while TriggerManager and ForeignKeysManager deal with triggers and foreign keys. MigrationInvoker ensures correct changes to table columns.

If a migration needs to be rolled back, MigrationManager initiates the rollback by activating the relevant methods in all managers to undo the changes. The interaction between managers is facilitated through the exchange of data about the current and previous state of the database, allowing for precise migration queries.

This organization allows my migration system to efficiently and accurately manage the database schema, automating the processes of applying and rolling back changes while reducing the risk of errors. Each class performs its specific role, but all work together to ensure the integrity and consistency of the database.

MigrationManager Class

The MigrationManager class is part of my ORM-CLI and is responsible for managing database migrations. Here is a breakdown of its functions and objectives:

Class Overview The MigrationManager class handles database migrations, including applying (forward) and rolling back (backward) migrations. It also helps create new migration files based on changes in the database schema.

Main Methods and Their Purposes

• Constructor: Initializes MigrationManager with necessary dependencies, such as database context, connection data, and database manager.

• migrationDown(migrationName: string): Rolls back the migration identified by migrationName. Calls the internal method _migrateDownOrUp with the direction set to 'down'.

• migrationUp(migrationName: string): Applies the migration identified by migrationName. Also calls _migrateDownOrUp, but with the direction set to 'up'.

• migrate(direction: MigrationsType): Applies or rolls back all migrations in the migrations directory based on the provided direction ('up' or 'down').

• createMigration(migrationName: string | boolean, isMigrationEmpty?: boolean): Creates a new migration file with the specified migrationName. Can generate an initial migration if isMigrationEmpty is true, or create a migration file based on differences between the current and last state of the database.

• _prepareMigration(): Ensures that the database connection is established and the database context is connected.

• _extractMigrationClassName(migrationName: string): Converts the migration file name into a class name suitable for import.

• _updateMigrationStatus(migrationName: string, isUp: boolean): Updates the migration status in the database, indicating whether it has been applied or rolled back.

• _handleMigrationFolderCreation(migrationPath: string): Ensures that the migrations directory exists. If not, prompts the user to create it.

• _generateMigrationName(migrationName: string | boolean, isMigrationsExist: boolean): Generates a unique migration name, either using a default value or prompting the user if the name is not provided.

• _promptMigrationName(): Prompts the user to enter a migration name if one is not provided.

• _handleMigrationCreation(migrationPath: string, migrationName: string, isMigrationsExist: boolean, isMigrationEmpty?: boolean): Creates a migration file based on the current and last state of the database or as an empty migration if specified.

• _createMigrationQuery(currentDatabaseIngot: DatabaseIngotInterface<DT>, lastDatabaseIngot: DatabaseIngotInterface<DT>, isMigrationsExist: boolean): Generates SQL queries for applying and rolling back migrations based on differences between the current and last state of the database.

• _createMigrationFile(migrationPath: string, migrationName: string, migrationQuery: string, undoMigrationQuery: string): Creates a TypeScript migration file with up and down methods containing the generated SQL queries.

Additional Methods

• Handling Triggers and Indexes: The _createMigrationQuery method includes logic for managing triggers and indexes, showing that the class is designed to handle various schema changes.

• Error Handling and User Prompts: The class includes error handling for various migration operations and user prompts for necessary input, helping to manage migration creation interactively.

Overall, MigrationManager serves as the central hub for managing schema changes, ensuring proper application and rollback of migrations, and facilitating the creation of new migration files based on schema changes.

A crucial method in this class is _createMigrationQuery, which is used to generate SQL queries needed for applying and rolling back database migrations.

• Initialization of Variables: Initially, the method creates empty strings to hold SQL queries for applying and rolling back migrations.

• Handling the Case of No Migrations: If no migrations existed previously:

  • Generates SQL queries for creating new tables.

  • Adds queries for handling triggers and indexes.

  • Creates queries for dropping tables in the case of rollback, considering the database specifics (Postgres or MySQL).

• Handling Existing Migrations: If migrations already exist:

  • The method compares the current database state with the last known state.

  • Determines which changes need to be made and creates appropriate SQL queries for each type of change (adding or removing columns, changing default values, data types, etc.).

  • Generates SQL queries for working with tables, foreign keys, triggers, and indexes.

• Error Handling: If no migration queries could be generated or if migrations already exist without changes, the method outputs an error message and throws an exception.

• Returning the Result: Finally, the method returns an object containing two SQL query strings: one for applying migrations, the other for rolling back changes.

This method ensures automatic generation of SQL queries for managing schema changes, simplifying the migration process.

https://github.com/MyroslavShymon/ORM-CLI/blob/main/src/managers/migration-manager/migration-manager.ts

The ForeignKeysManager class is designed for managing changes to foreign keys and relationships between tables in relational databases during migrations. It includes methods for generating SQL queries for adding and removing foreign keys, as well as handling "many-to-many," "one-to-many," and "one-to-one" relationships. The main method manage compares the current and previous states of the database, generating corresponding queries for migrations and their rollback.

The internal methods of the class check which foreign keys have been added or removed, as well as verifying and adjusting relationships between tables. The _handleForeignKeysInTable method creates SQL queries for adding or removing foreign keys based on changes in table structure. The _handleManyToManyRelationsOfTable method manages changes in "many-to-many" relationships, including creating and deleting junction tables.

The _checkOneToManyRelations method checks the correctness of "one-to-many" relationships, ensuring the presence of corresponding foreign keys and proper references to primary columns. The _handleOneToManyRelationsOfTable method generates SQL queries for adding or removing foreign keys in "one-to-many" relationships.

Similarly, the _checkOneToOneRelations method verifies the correctness of "one-to-one" relationships, and the _handleOneToOneRelationsOfTable method creates queries for these relationships. The class ensures automatic management of the database schema, facilitating the maintenance of its integrity during schema evolution.

https://github.com/MyroslavShymon/ORM-CLI/blob/main/src/managers/migration-manager/foreign-keys-manager/foreign-keys-manager.ts

And also a list of other managers:

• IndexManager: Responsible for creating and deleting database indexes. It generates migration queries for creating new indexes and deleting existing ones, comparing the current and previous states of the database. The class can handle indexes for different databases (PostgreSQL or MySQL) and generates corresponding SQL queries depending on the database type.

• TableManager: Manages schema changes for tables, including creating, deleting, and renaming tables. It compares the current and previous states of the database to determine which tables need to be added or removed. Also manages table renaming and adapts to different types of databases using specific options for dropping tables (e.g., cascading delete in PostgreSQL).

• TriggerManager: Manages the creation and deletion of database triggers. It generates migration queries for adding or removing triggers based on changes between the current and previous states of the database. The class adapts to different databases, generating appropriate SQL for creating and deleting triggers, depending on whether it is PostgreSQL or MySQL.

These managers together ensure the correct application and rollback of changes to the database schema during migrations, taking into account different database systems and maintaining synchronization between the internal state of the ORM and the actual database schema.

ORM-CLI employs an integrated approach to managing the database structure through specialized classes. The MigrationManager, IndexManager, TableManager, TriggerManager, ForeignKeysManager, and MigrationInvoker classes work closely together to ensure an efficient and secure migration process. Each of these classes performs its unique role, from handling indexes and tables to managing triggers and foreign keys, enabling precise changes and preventing errors.

This comprehensive approach allows my migration system to automate and streamline the processes of applying and rolling back changes to the database structure, enhancing data reliability and consistency. The interaction between classes enables accurate tracking and implementation of changes, reducing the risk of errors and ensuring correct execution of migrations across different types of databases.

Such an approach not only provides automation but also flexibility in managing the database schema, making your ORM-CLI a powerful tool for developing and maintaining modern information systems.