Add migrations change log table

[bancer]

It would be nice to log the executed migrations to a table with fields like 'migration_id', 'executed_at', 'executed_by', 'description'.

We also find it quite useful to have descriptive migration file names like 001234_add_index_to_users_table.sql that could be saved to 'description' column.

Ref.: https://martinfowler.com/articles/evodb.html#AllDatabaseChangesAreMigrations

[byjg]

Hello @bancer ,

We have two questions here. Let's start by the simpler one:

We also find it quite useful to have descriptive migration file names like 001234_add_index_to_users_table.sql that could be saved to 'description' column.

Specically about this item I discussed here: https://github.com/byjg/migration/issues/30#issuecomment-700163219 . If you want discuss more, please open another issue to keep the topics in each issue.

It would be nice to log the executed migrations to a table with fields like 'migration_id', 'executed_at', 'executed_by', 'description'.

That one is quite interesting. However let's understand what are you suggesting here. For example, if you want add to each in the database the fields: migration_id, executed_at, executed_by, description I have to say it is very difficult task to execute in a generic SQL migration. It happens, because you can virtually write what you want in the SQL you are applying the migration. To have the ability to add these information, I have to create a SQL parser for each SQL dialect I have and until this is 100% test I may introduce errors during the migration.

The other possibility we have is instead to add for each table I'll create a Journal Table with the migration already applied. This is easier and it is development currently for the next revision https://github.com/byjg/migration/pull/31

[bancer]

I think you misunderstood. There is no need to create SQL parser in order to record what migration file was executed, when and by whom. Please take a look at the article I linked above.

Regarding naming - I mentioned it in the same issue because it would make it simpler to record "description" by using part of the file name - that would not require to read the contents of the file but to read only the file name in order to determine what the migration was about. And it is also more continent for developers to take a look at file names when there is need to find a specific migration than to open each file.

We handle these demands by giving each migration a sequence number. This acts as a unique identifier and ensures we can maintain the order that they're applied to the database. When a developer creates a migration she puts the SQL into a text file inside a migrations folder within the project's version control repository. She looks up the highest currently used number in the migrations folder, and uses that number together with a description to name the file. Thus the earlier pair of migrations might be called 0007_add_insurance_value_to_equipment_type.sql and 0008_data_location_equipment_type. [1]

To track the application of the migrations to the database we use a changelog table. Database migration frameworks typically create this table and automatically update it whenever a migration is applied. This way the database can always report which migration it is synchronized with. If we don't use such a framework, after all they didn't exist when we starting doing this, we automate this with a script.