Generate all changes into one migration file

[SunriseCoder]

Hello! Is there any way to generate all changes into a single migration file?

[bennett-treptow]

I'm not sure of the purpose of that? It is best practice to have each table as a separate migration so that each table operation is atomic.

If you're looking for all of your data to just be available as a migration from the start, I would suggest the Laravel php artisan schema:dump command to get the SQL dump of the current database.

[SunriseCoder]

The purpose is to keep about 100 changes as a legacy in a single file and then start own changes from the 2-nd file and keep one change per file.

Thank You very much that You explained that this is best practice to use each operation in a separate file to achieve atomicity.
I was wondered, how to achieve it, because if I have 2-3 DDL-changes in a single file, let's say, 2-nd or 3-rd operation fails, transaction rollbacks, but the 1-st DDL operation is not reverted usually...

So good luck, but maybe it worth to make such opportunity to generate all code in a single file if there is nothing more urgent to do :)
Or just add this idea to backlog :)

Thank You very much.

[SunriseCoder]

Hello @bennett-treptow!
I appreaciate Your advice about one change per file for atomicity very much, but I got another case, could You please be kind and give me advice from the best-practices and Your own experience and vision?

We have a database in production with 100+ tables, which we created without migrations.
Now we want to adopt using migrations.

The questions are:

1. Is it worth to create 100+ migration files for each database tables or better to create one big SQL-file instead?
   We will need the ability to create database from scratch for integration tests, possibly deploy new production instances, maybe some other reasons which I don't foresee now...
2. If we do 100+ migration files, should we add "if (table('table_name')->exists())" checks in each file or there is another approach?
3. If we do 1 big SQL-file, we also will need to add "if (table('table_name')->exists())" check before executing this file?
4. What if we later can possibly move to another database, which SQL syntax could be incompatible?

What do You think?
Thank You very much in advance.

[bennett-treptow]

If the goal is to use say SQLite in your tests instead of another RDMS like MySQL, then I think it would make sense to just have these migrations generated in the default tests/database/migrations directory, and then you can register those migrations to be run by your test runner instead of the default migrations path. See https://github.com/laravel/framework/blob/10.x/src/Illuminate/Foundation/Testing/Traits/CanConfigureMigrationCommands.php#L12 -- you can override this method on your base test case to include --path parameter from the Migration command.

If you're just looking to get your current database state into the code base so that you can have it for anyone picking up your repository, the Laravel squashed migrations is just what you're looking for. This would work such that deploying to a new production instance would just get the base structure, without any data.

Question 1: 100+ migration files for testing? Yes. This is fine, and especially if you use any of the nice Laravel testing features like transactions and lazy refreshing of the database, these migrations would only ever be run once per test cycle anyways. In regards to deploying new production instances, you could just use the squashed migrations for this and wouldn't need the 100+ migration files, since you would hopefully be deploying to the same stack as what another instance is using.

Question 2: it depends. If you are using these migrations on a production environment and not for testing, you would either need to insert those migrations into the database manually (see also a related issue that I think is the same thing you're asking about: #74) so that they don't get run again on the environment that you're creating these from, and then you wouldn't need to worry about ensuring the table doesn't already exist.

Question 3: I think one big file is not a great idea. Keeping them separate will be easier to follow and will ensure that migrations are roll-backable.

Question 4: I would surely hope that this is not an actual concern. Swapping databases is not something you should particularly be worried about, unless you are working on a code base where the tests are run on SQLite and the deployed instances don't. I would still recommend keeping migration files one-file per table.

[SunriseCoder]

Hello @bennett-treptow !

Thank You very much for the detailed explanation :)