[Feature Request] Write SQL Executed to Migration Table

[jvanderen1]

Feature Request

For our team, it has become a slight pain to figure out what SQL was executed during a migration. There are a few cases where we need to know this:

1. A migration may have partially succeeded, but ultimately failed. This could lead to problems trying to debug what exactly has changed. While we can look at the last error thrown, it does not make it immediately clear which SQL statements need to be reversed or triaged.

2. The code which executed on a shared PostgreSQL instance may not be available anymore. Maybe a dev forgot to trigger down before removing the code? While it would be ideal to have automatic down migration occur scripts before they are modified / deleted, this is painful to backtrace sometimes. Also, there is the risk that down may not work properly. (Maybe we should also report all SQL migration queries executed, even for 1 migration?)

3. Sometimes, depending on the environment, SQL queries may execute differently. This could be due to how the schema has evolved over time or which additional frameworks are interacting with PostgreSQL or Models, but it would be helpful to debug any inconsistencies in executed SQL.

4. Certain SQL migrations may have executed through a variable. This is a pattern to probably avoid, but it is still possible to execute migrations through variables. Having a way to trace exactly what executed would be helpful.

Thanks for making an amazing framework!

[jeremyevans]

Thank you for submitting a feature request. A few notes:

1. If you are using PostgreSQL (like the majority of Sequel users), Sequel uses transactional migrations by default, so either all of the migration succeeds or the entire migration is rolled back. So you should not need to worry about partially applied migrations.

2. The schema_info/schema_migrations table has sufficient information on which migrations have been applied. Combined with transactional migrations, it should be simple to see what changes the migrations have made to the database.

3. While some Database methods can execute different SQL depending on current database state (e.g. create_table?), generally those should not be used in migrations. Migrations should assume the database is in the state left by the previous migration.

4. If you really need to store migration SQL in the database, add a logger to the Database object you are running the migration on, take the output of the logger, and insert it into another table after the migration. If I was going to ship an extension with Sequel for this, the logger approach is what I would use.

I'm not sure if you've used a logger when migrating before, but if not, I would recommend doing so.

I'd appreciate feedback about this from other Sequel users. Currently, the use case seems sufficiently niche to me that I wouldn't ship an extension for it with Sequel, but I'm open to being convinced otherwise.

[jvanderen1]

I appreciate the feedback and swift response!

We currently use our cloud providers logs for PostgreSQL, but it is quite large and is difficult to pinpoint which queries were executed since our database has many queries executed at a time.

To expand, here are more concrete examples of our pain-points:

• Sometimes, rubocop suggests we run our migrations with a no_transaction clause (such as creating an index concurrently). While these queries tend to work in our local environments, they may timeout or lock the database in a larger environment.
• We have larger testing databases that are shared amongst developers. These instances are checked out to test various changes before hitting production. While a developer may have good intent in testing a migration, if they were to run a migration script without sharing the migration code that ran, it would be difficult for us to know what changed.

With all this said, more robust logging techniques could be something we could still look into. In addition, we may have to add some development process for certain things as well. I am sure this community has a ton of other priorities and objectives, but I figured I'd make the suggestion anyway.

Thanks again for everything you have done @jeremyevans. You really are inspirational.

[tycooon]

@jvanderen1 we have a similar setup with staging environments and we use some custom rake tasks to track deployed migrations and handle automatic rollbacks. Just wanted to share some code in case it might help in your case.

1. This task stores all migrations code in schema_migrations_sources table.
   https://github.com/umbrellio/umbrellio-sequel-plugins/blob/master/lib/tasks/sequel/archive_migrations.rake

2. This one rollbacks migrations that are missing in current filesystem. The code for those migrations is retrieved from the schema_migrations_sources table.
   https://github.com/umbrellio/umbrellio-sequel-plugins/blob/master/lib/tasks/sequel/rollback_archived_migrations.rake

[jvanderen1]

Thank you very much! That's incredibly helpful. I will take note.