Database migrations with phinx and schema.sql generation

[samuelgfeller]

For context, the conversation started in this commit.

When you run the test suite, your current schema.sql file will be "dumped" into the test database. This means you can develop and run tests with the schema changes even without writing any (phinx) migrations

I have to update that schema.sql if it doesn't match the current state of my dev db for the tests to not fail. I can do this with composer schema:generate.
Is there a way to execute this command automatically, ideal would be once before any test and then not again while tests are running? (especially when running tests from IDE)

And regarding migrations workflow, is that correct?:

• After every database change, I have to update the schema.sql (composer schema:generate) to be able to run tests with actual database.
• When I'm satisfied with my changes and want to commit, I generate new migration files composer migration:generate and push them to the version control.
• If I'm pulling from the remote and other devs made database changes, I migrate those changes to my local dev db composer migrate and then (as it's a database change) run composer schema:generate to update the schema.sql
• On deployment, after uploading all files, I execute composer migrate to update the remote database.
  • Additional question, how can I run composer migrate on the remote server so that its database gets updated when deploying with GitHub actions?

[odan]

Is there a way to execute this command automatically,

No, but you can do it in your code base if it make sense to your workflow.

After every database change, I have to update the schema.sql (composer schema:generate) to be able to run tests with actual database.

Yes

When I'm satisfied with my changes and want to commit, I generate new migration files composer migration:generate and push them to the version control.

Yes

If I'm pulling from the remote and other devs made database changes, I migrate those changes to my local dev db composer migrate and then (as it's a database change) run composer schema:generate to update the schema.sql

Yes, and you should also generate and commit the (phinx) migration(s) in that branch. After you have merged that branch into master/main, you also generate/commit a fresh schema.sql file in master/main that reflects the current database schema.

On deployment, after uploading all files, I execute composer migrate to update the remote database.

Yes

Additional question, how can I run composer migrate on the remote server so that its database gets updated when deploying with GitHub actions?

When you deploy with Github you may execute these commands via SSH.

[samuelgfeller]

No, but you can do it in your code base if it make sense to your workflow.

What do you mean by code base?

Yes, and you should also generate and commit the (phinx) migration(s) in that branch. After you have merged that branch into master/main, you also generate/commit a fresh schema.sql file in master/main that reflects the current database schema.

Okay, let's say I pull from develop where another developer made changes to the database, executed composer migration:generate and pushed the migration files.
I should then execute the migrations to update my current dev database and generate a new schema.sql.
Then, if I don't make other changes, I can merge my code into master making sure that it contains the most recent schema.sql and migration files before pushing it.

[odan]

What do you mean by code base?

The source code of your project/s.

Okay, let's say I pull from develop...

Yes, when you fetch the latest changes from master, then you also need to update your local dev database to the latest database version by executing the new migrations.

[dunkoh]

@samuelgfeller maybe you could have it in a post update git hook ?
https://githooks.com

an example of a hook I used to use that checked for composer.lock updates and ran composer install if needed

COMPOSER="/Users/me/php /usr/local/bin/composer"

CHANGED=`git diff $1 $2 --stat -- $GIT_DIR/../composer.lock | wc -l`
if [ $CHANGED -gt 0 ];
then
       $COMPOSER install
fi
exit 0

Alternatively you could have a run configuration in your IDE that makes it easy to click to run it

[samuelgfeller]

@dunkoh thank you for the input!