Automate Migrations using 'ALTER TABLE' statements?

I've got a git branch in my project that involves adding and modifying dozens of tables to the project (using MySQL 8.0). They are all built around & interface with tables from a commercial vendor.

The branch is intended to accomplish several things:

Implement SoftDeletes and other traits, including wildside/userstamps.
Apply a complex ownership hierarchy that adds 5 columns to most tables.
Apply a standardized column order.
Integrate with venturedrake/laravel-crm.
Add a shedload of indexes and foreign keys to existing tables.
Enforce index and foreign key naming conventions.

I've got the 30 or so new models in place. However, I would like to avoid the tedium of manually writing migrations for even more pre-existing tables.

My schema design workflow leverages the Synchronize Model wizard in MySQL Workbench. The branch's changes are being done in a separate database from my primary workspace (still on my local dev box).

The Workbench wizard can sync either way between ER diagram and (any) database. If updating the database, it presents a collection of ALTER TABLE statements that can be exported.

So, if I sync the pending table changes with my primary workspace DB, I can get a complete set of SQL queries to affect the changes (which I would not execute from the sync wizard).

Is there a package or some other solution that can take this SQL as input and generate the necessary migrations?

flap152

2 years ago

Level 11

I assume you have no migrations yet. I would try a package that creates migrations from existing databases, run it once to get a baseline, then run it after your SQL ALTER TABLE statements have run, then do a diff of both migration sets. The difference is used to create the change migrations you need.

Create table and change table migrations are different but the column definitions are the same, likely saving you time.

Packages I found - never used them:

https://github.com/kitloong/laravel-migrations-generator

https://github.com/bennett-treptow/laravel-migration-generator?ref=laravelnews

Caraes_Naur

2 years ago

Level 1

@flap152 On the contrary, the project already has over 500 migrations in the main branch, including those that create and add FKs to the tables I now need to heavily modify. The migrations native to the project were made using kitloong/laravel-migrations-generator (one of the first packages to get installed, many months ago).

I briefly looked through the other package's code, but didn't see what I'm looking for.

In my research, I couldn't find anything like what I need. Laravel migration tooling seems biased toward creating tables rather than making substantial "automagic" changes to them. Google results are a deluge of content documenting how to make singular changes.

Please or to participate in this conversation.