migration fails in Rails 6 project with multiple database types

[mhw]

It's always been possible to have connections to multiple databases of different types specified in database.yml and used through establish_connection. In Rails 6 this was expanded to support primary and replica databases, with a new 3-layer configuration style in database.yml and enhancements to the rake tasks to manage multiple databases.

It's also possible to use this 3-layer configuration style with databases of different types - for example, mixing Postgres and MySQL in a legacy recovery project. The pg-mysql branch of this sample project shows how this can be done. However this set up breaks the rake db:migrate task as the schema dumper attempts to treat the MySQL database as though it were Postgres. An excerpt from the error that results:

ActiveRecord::StatementInvalid: Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'current_schemas(false))
            ORDER BY c.oid' at line 11
.../vendor/bundle/ruby/2.6.0/gems/mysql2-0.5.3/lib/mysql2/client.rb:131:in `_query'
.../vendor/bundle/ruby/2.6.0/gems/mysql2-0.5.3/lib/mysql2/client.rb:131:in `block in query'
.../vendor/bundle/ruby/2.6.0/gems/mysql2-0.5.3/lib/mysql2/client.rb:130:in `handle_interrupt'
.../vendor/bundle/ruby/2.6.0/gems/mysql2-0.5.3/lib/mysql2/client.rb:130:in `query'
.../vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.2.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:202:in `block (2 levels) in execute'
.../vendor/bundle/ruby/2.6.0/gems/activesupport-6.0.2.1/lib/active_support/dependencies/interlock.rb:48:in `block in permit_concurrent_loads'
.../vendor/bundle/ruby/2.6.0/gems/activesupport-6.0.2.1/lib/active_support/concurrency/share_lock.rb:187:in `yield_shares'
.../vendor/bundle/ruby/2.6.0/gems/activesupport-6.0.2.1/lib/active_support/dependencies/interlock.rb:47:in `permit_concurrent_loads'
.../vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.2.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:201:in `block in execute'
.../vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:718:in `block (2 levels) in log'
.../vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:717:in `block in log'
.../vendor/bundle/ruby/2.6.0/gems/activesupport-6.0.2.1/lib/active_support/notifications/instrumenter.rb:24:in `instrument'
.../vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:708:in `log'
.../vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.2.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:200:in `execute'
.../vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.2.1/lib/active_record/connection_adapters/mysql/database_statements.rb:41:in `execute'
.../vendor/bundle/ruby/2.6.0/gems/scenic-1.5.1/lib/scenic/adapters/postgres/views.rb:26:in `views_from_postgres'
.../vendor/bundle/ruby/2.6.0/gems/scenic-1.5.1/lib/scenic/adapters/postgres/views.rb:18:in `all'
.../vendor/bundle/ruby/2.6.0/gems/scenic-1.5.1/lib/scenic/adapters/postgres.rb:49:in `views'
.../vendor/bundle/ruby/2.6.0/gems/scenic-1.5.1/lib/scenic/schema_dumper.rb:25:in `dumpable_views_in_database'
.../vendor/bundle/ruby/2.6.0/gems/scenic-1.5.1/lib/scenic/schema_dumper.rb:12:in `views'
.../vendor/bundle/ruby/2.6.0/gems/scenic-1.5.1/lib/scenic/schema_dumper.rb:8:in `tables'
.../vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.2.1/lib/active_record/schema_dumper.rb:44:in `dump'
.../vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.2.1/lib/active_record/schema_dumper.rb:28:in `dump'
.../vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.2.1/lib/active_record/tasks/database_tasks.rb:377:in `block in dump_schema'
.../vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.2.1/lib/active_record/tasks/database_tasks.rb:376:in `open'
.../vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.2.1/lib/active_record/tasks/database_tasks.rb:376:in `dump_schema'
.../vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.2.1/lib/active_record/railties/databases.rake:392:in `block (4 levels) in <main>'
.../vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.2.1/lib/active_record/railties/databases.rake:390:in `each'
.../vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.2.1/lib/active_record/railties/databases.rake:390:in `block (3 levels) in <main>'
.../vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.2.1/lib/active_record/railties/databases.rake:97:in `block (2 levels) in <main>'
.../vendor/bundle/ruby/2.6.0/gems/activerecord-6.0.2.1/lib/active_record/railties/databases.rake:88:in `block (2 levels) in <main>'
.../vendor/bundle/ruby/2.6.0/gems/railties-6.0.2.1/lib/rails/commands/rake/rake_command.rb:23:in `block in perform'
.../vendor/bundle/ruby/2.6.0/gems/railties-6.0.2.1/lib/rails/commands/rake/rake_command.rb:20:in `perform'
.../vendor/bundle/ruby/2.6.0/gems/railties-6.0.2.1/lib/rails/command.rb:48:in `invoke'
.../vendor/bundle/ruby/2.6.0/gems/railties-6.0.2.1/lib/rails/commands.rb:18:in `<main>'
.../vendor/bundle/ruby/2.6.0/gems/bootsnap-1.4.5/lib/bootsnap/load_path_cache/core_ext/kernel_require.rb:22:in `require'
.../vendor/bundle/ruby/2.6.0/gems/bootsnap-1.4.5/lib/bootsnap/load_path_cache/core_ext/kernel_require.rb:22:in `block in require_with_bootsnap_lfi'
.../vendor/bundle/ruby/2.6.0/gems/bootsnap-1.4.5/lib/bootsnap/load_path_cache/loaded_features_index.rb:92:in `register'
.../vendor/bundle/ruby/2.6.0/gems/bootsnap-1.4.5/lib/bootsnap/load_path_cache/core_ext/kernel_require.rb:21:in `require_with_bootsnap_lfi'
.../vendor/bundle/ruby/2.6.0/gems/bootsnap-1.4.5/lib/bootsnap/load_path_cache/core_ext/kernel_require.rb:30:in `require'
.../vendor/bundle/ruby/2.6.0/gems/activesupport-6.0.2.1/lib/active_support/dependencies.rb:325:in `block in require'
.../vendor/bundle/ruby/2.6.0/gems/activesupport-6.0.2.1/lib/active_support/dependencies.rb:291:in `load_dependency'
.../vendor/bundle/ruby/2.6.0/gems/activesupport-6.0.2.1/lib/active_support/dependencies.rb:325:in `require'
bin/rails:4:in `<main>'

The issue is that Scenic.database is a singleton, and the schema dumper additions assume it will always be possible to call views on it. The Rails database tasks now change the ActiveRecord::Base.connection to run migrations and the schema dumper against each database listed in database.yml. In the test app above this results in Scenic's Postgres adapter being used with a MySQL database connection, hence the error.

As Scenic.database is also really part of the Scenic public API (here for example), the best option I've come up with is to extend the Configuration class along the lines of the initializer on the master branch. I've also added a NullAdapter which gives the flexibility to work with database connections where Scenic is not used, as on the pg-mysql-fix branch.

I wanted to check the overall direction here before submitting a pull request along these lines. Does this look like a reasonable addition?

[derekprior]

Hmmm, yeah. I like the direction you took with NewConfiguration in your sample repository, but that's a breaking change.

I wonder if we can think of a way to enable this in a non-breaking way? Or perhaps this just has to be 2.0?

[mhw]

Hi! Thanks for looking it over.

I'd tried to extend Configuration in a way that would not break existing code: providing a database= writer and if that is used the database method reverts to its previous behaviour. What usage are you thinking of that this would break?

(It might not be clear, but I'd intended the body of NewConfiguration to replace the Configuration class, as in this commit. For what it's worth, with that change in place the test suite passes unchanged.)

[hopsoft]

Any updates on Rails 6 multi-database support? I'll be testing with the branch from @mhw but would certainly prefer to use the official gem.

[mhw]

@hopsoft: In the project where I need this working I've been using scenic 1.5.4 along with the initializer without any issues. I'd suggest going that route as I've not kept my fork up to date.

[rquant]

@mhw Hi! I am liking this gem very much, however I have one issue related to this thread. I have a Rails 6 app that has a second database configured. I am able to simply move the generated migration file for a given view to the correct folder (e.g. db/migrate/secondary_db) and migration will create view for that db. However, the second part of the migration fails because it does not support similar pattern for the sql view files in db/views directory.

Is there a workaround for this? Thanks!

[mhw]

@rquant Not that I'm aware of, I'm afraid. My project only required migrations run against the primary PostgreSQL database so I didn't go as far as that. I'm sure it would be feasible but it would take some digging through the Rails source to figure out how to implement it.