Loading schema in production?

[cjamison]

I hope there is a better way to do this that I am not seeing.

According to the read me:

Then run db:prepare in production to ensure the database is created and the schema is loaded.

I am testing this out in staging first. So I ran the following on the server:

RAILS_ENV=staging bin/rails db:prepare

This did nothing to the queue database. Maybe because the queue database already existed? Our deployment process sets up the databases ahead of time. So running this command resulted in zero tables in the queue DB. Plus, db:prepare is not listed as a command for multiple databases. However, db:setup is. So, I ran:

RAILS_ENV=staging bin/rails db:setup:queue

This might have worked. However, it tried connecting to the postgres database. Presumably to drop and recreate the database. However, connecting as postgres is not allowed on our staging and production systems.

So, I tried db:schema:load:queue (I actually tried this first):

RAILS_ENV=staging bin/rails db:schema:load:queue

This resulted in this error:

bin/rails aborted!
TypeError: Invalid type for configuration. Expected Symbol, String, or Hash. Got nil (TypeError)
        raise TypeError, "Invalid type for configuration. Expected Symbol, String, or Hash. Got #{config.inspect}"
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Tasks: TOP => db:schema:load:queue => db:test:purge:queue

It looks like it is doing something with regard to a test DB that does not exist? There is actually an open PR about this: https://github.com/rails/rails/issues/50672.

Then I started a console in the Staging environment on the server and ran these commands to load the schema in the queue database:

ActiveRecord::Base.establish_connection(:queue)
load Rails.root.join('db','queue_schema.rb')

This successfully loaded the queue database.

Is there a better way to do this? I don't remember having to load a schema in Staging or Production before. It seems like an anti-pattern? I started using Solid Queue with version 0.3 and the migrations sure seemed easier. Would it be easier to place the contents of the queue_schema.rb in a single migration file?

[rosa]

Hey @cjamison, thanks for writing this up! This is all very strange.

This did nothing to the queue database. Maybe because the queue database already existed?

No... it should work just fine.

4.3 Preparing the Database The bin/rails db:prepare command is similar to bin/rails db:setup, but it operates idempotently, so it can safely be called several times, but it will only perform the necessary tasks once.

• If the database has not been created yet, the command will run as the bin/rails db:setup does.
• If the database exists but the tables have not been created, the command will load the schema, run any pending > migrations, dump the updated schema, and finally load the seed data. See the Seeding Data documentation for more details.
• If both the database and tables exist but the seed data has not been loaded, the command will only load the seed data.
• If the database, tables, and seed data are all in place, the command will do nothing.

So, if this did nothing to the queue database, it makes me think you were missing some configuration for staging, either the DB configuration in database.yml, or config.solid_queue.connects_to = { database: { writing: :queue } } in your staging environment.

[stefanoc]

Hey @rosa , I'm having the exact same problem. I've just added solid_queue to an existing application, and I've followed the setup instructions:

• config/database.yml has both primary and queue databases
• config/environment/development.rb has config.solid_queue.connects_to = { database: { writing: :queue } }

Running db:prepare creates the queue database but it does not load the schema (only the ar_internal_metadata and schema_migrations tables are there). Running db:schema:load:queue produces the same error reported by @cjamison

FYI this is the full output of the rake task:

17:08:07 %% bin/rails db:schema:load:queue --trace
save_history = 0                   # BOOT: maximum save history lines (default: 10000)
** Invoke db:schema:load:queue (first_time)
** Invoke db:test:purge:queue (first_time)
** Invoke db:load_config (first_time)
** Invoke environment (first_time)
** Execute environment
** Execute db:load_config
** Invoke db:check_protected_environments (first_time)
** Invoke db:load_config 
** Execute db:check_protected_environments
** Execute db:test:purge:queue
bin/rails aborted!
TypeError: Invalid type for configuration. Expected Symbol, String, or Hash. Got nil (TypeError)

        raise TypeError, "Invalid type for configuration. Expected Symbol, String, or Hash. Got #{config.inspect}"
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
/lib/ruby/gems/3.3.0/gems/activerecord-8.0.0.rc1/lib/active_record/database_configurations.rb:183:in `resolve'
/lib/ruby/gems/3.3.0/gems/activerecord-8.0.0.rc1/lib/active_record/connection_adapters/abstract/connection_handler.rb:275:in `resolve_pool_config'
/lib/ruby/gems/3.3.0/gems/activerecord-8.0.0.rc1/lib/active_record/connection_adapters/abstract/connection_handler.rb:115:in `establish_connection'
/lib/ruby/gems/3.3.0/gems/activerecord-8.0.0.rc1/lib/active_record/tasks/database_tasks.rb:525:in `with_temporary_pool'
/lib/ruby/gems/3.3.0/gems/activerecord-8.0.0.rc1/lib/active_record/tasks/database_tasks.rb:496:in `with_temporary_pool_for_each'
/lib/ruby/gems/3.3.0/gems/activerecord-8.0.0.rc1/lib/active_record/railties/databases.rake:585:in `block (5 levels) in <main>'
/lib/ruby/gems/3.3.0/gems/rake-13.2.1/lib/rake/task.rb:281:in `block in execute'
/lib/ruby/gems/3.3.0/gems/rake-13.2.1/lib/rake/task.rb:281:in `each'
/lib/ruby/gems/3.3.0/gems/rake-13.2.1/lib/rake/task.rb:281:in `execute'
/lib/ruby/gems/3.3.0/gems/rake-13.2.1/lib/rake/task.rb:219:in `block in invoke_with_call_chain'
/lib/ruby/gems/3.3.0/gems/rake-13.2.1/lib/rake/task.rb:199:in `synchronize'
/lib/ruby/gems/3.3.0/gems/rake-13.2.1/lib/rake/task.rb:199:in `invoke_with_call_chain'
/lib/ruby/gems/3.3.0/gems/rake-13.2.1/lib/rake/task.rb:243:in `block in invoke_prerequisites'
/lib/ruby/gems/3.3.0/gems/rake-13.2.1/lib/rake/task.rb:241:in `each'
/lib/ruby/gems/3.3.0/gems/rake-13.2.1/lib/rake/task.rb:241:in `invoke_prerequisites'
/lib/ruby/gems/3.3.0/gems/rake-13.2.1/lib/rake/task.rb:218:in `block in invoke_with_call_chain'
/lib/ruby/gems/3.3.0/gems/rake-13.2.1/lib/rake/task.rb:199:in `synchronize'
/lib/ruby/gems/3.3.0/gems/rake-13.2.1/lib/rake/task.rb:199:in `invoke_with_call_chain'
/lib/ruby/gems/3.3.0/gems/rake-13.2.1/lib/rake/task.rb:188:in `invoke'
/lib/ruby/gems/3.3.0/gems/rake-13.2.1/lib/rake/application.rb:188:in `invoke_task'
/lib/ruby/gems/3.3.0/gems/rake-13.2.1/lib/rake/application.rb:138:in `block (2 levels) in top_level'
/lib/ruby/gems/3.3.0/gems/rake-13.2.1/lib/rake/application.rb:138:in `each'
/lib/ruby/gems/3.3.0/gems/rake-13.2.1/lib/rake/application.rb:138:in `block in top_level'
/lib/ruby/gems/3.3.0/gems/rake-13.2.1/lib/rake/application.rb:147:in `run_with_threads'
/lib/ruby/gems/3.3.0/gems/rake-13.2.1/lib/rake/application.rb:132:in `top_level'
/lib/ruby/gems/3.3.0/gems/railties-8.0.0.rc1/lib/rails/commands/rake/rake_command.rb:27:in `block (2 levels) in perform'
/lib/ruby/gems/3.3.0/gems/rake-13.2.1/lib/rake/application.rb:214:in `standard_exception_handling'
/lib/ruby/gems/3.3.0/gems/railties-8.0.0.rc1/lib/rails/commands/rake/rake_command.rb:27:in `block in perform'
/lib/ruby/gems/3.3.0/gems/railties-8.0.0.rc1/lib/rails/commands/rake/rake_command.rb:44:in `block in with_rake'
/lib/ruby/gems/3.3.0/gems/rake-13.2.1/lib/rake/rake_module.rb:59:in `with_application'
/lib/ruby/gems/3.3.0/gems/railties-8.0.0.rc1/lib/rails/commands/rake/rake_command.rb:41:in `with_rake'
/lib/ruby/gems/3.3.0/gems/railties-8.0.0.rc1/lib/rails/commands/rake/rake_command.rb:20:in `perform'
/lib/ruby/gems/3.3.0/gems/railties-8.0.0.rc1/lib/rails/command.rb:150:in `invoke_rake'
/lib/ruby/gems/3.3.0/gems/railties-8.0.0.rc1/lib/rails/command.rb:67:in `block in invoke'
/lib/ruby/gems/3.3.0/gems/railties-8.0.0.rc1/lib/rails/command.rb:143:in `with_argv'
/lib/ruby/gems/3.3.0/gems/railties-8.0.0.rc1/lib/rails/command.rb:63:in `invoke'
/lib/ruby/gems/3.3.0/gems/railties-8.0.0.rc1/lib/rails/commands.rb:18:in `<main>'
/lib/ruby/gems/3.3.0/gems/bootsnap-1.18.4/lib/bootsnap/load_path_cache/core_ext/kernel_require.rb:30:in `require'
/lib/ruby/gems/3.3.0/gems/bootsnap-1.18.4/lib/bootsnap/load_path_cache/core_ext/kernel_require.rb:30:in `require'
bin/rails:4:in `<main>'
Tasks: TOP => db:schema:load:queue => db:test:purge:queue

[rosa]

Hey @stefanoc, could you copy your database.yml configuration? Is this in development or production? Have you defined config.solid_queue.connects_to for the environment you are running this in?

[stefanoc]

@rosa here it is:

default: &default
  adapter: postgresql
  encoding: unicode
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>

development:
  primary: &primary_development
    <<: *default
    database: app_development
  queue:
    <<: *primary_development
    database: "app_development_queue"
    migrations_paths: "db/queue_migrate"

Yes, connect_to is defined in the development environment. Also note that SQ itself is working fine (I've manually loaded the schema), the issue is just in the initial setup.

[erichstark]

I have the same issue. I have just installed solid_queue, updated db config and I am not able to start solid server

development:
  primary: &primary_development
    <<: *default
#    host: <%#= ENV["DB_HOST"] %>
    host: localhost
    port: 5432
    database: name
    username: user
    password: password
  cache:
    <<: *primary_development
    database: autoform_cache
    migrations_paths: db/cache_migrate
  queue:
    <<: *primary_development
    database: autoform_queue
    migrations_paths: db/queue_migrate
  cable:
    <<: *primary_development
    database: autoform_cable
    migrations_paths: db/cable_migrate

[dbreunig]

I am having an identical problem.

When I install solid queue, it creates the schema file. I then modify my database.yml like so:

default: &default
  adapter: postgresql
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  timeout: 5000

development:
  primary: &primary_development
    <<: *default
    url: "hidden"
  queue:
    <<: *primary_development
    database: steplist_development_queue
    migrations_paths: db/queue_migrate

test:
  <<: *default
  url: "hidden"

production:
  primary: &primary_production
    <<: *default
    url: <%= ENV['DATABASE_URL'] %>
    connect_timeout: 2
    checkout_timeout: 5
    variables:
      statement_timeout: 5000 # ms
  queue:
    <<: *primary_production
    database: steplist_production_queue
    migrations_paths: db/queue_migrate

If I run rails db:prepare nothing happens. The db is not created and the schema does not load. In both my dev and prod config files, I have the lines:

  # Job backend
  config.active_job.queue_adapter = :solid_queue
  config.solid_queue.connects_to = { database: { writing: :queue } }

Running bin/rails db:schema:load:queue --trace gets exactly the same output as @stefanoc.

[dbreunig]

Some further info...

When my development databases were already running and seeded, bin/rails db:prepare quietly completed -- but no new databases were created.

However, when I db:drop'ed them and then run db:prepare, it fails:

Created database 'steplist-dev'
Created database 'steplist-test'
2024-10-28T19:41:06.190Z pid=70490 tid=1hmi INFO: Sidekiq 7.3.2 connecting to Redis with options {:size=>10, :pool_name=>"internal", :url=>nil}
Creating static users
No default list found for Drew Breunig
bin/rails aborted!
SolidQueue::Job::EnqueueError: ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  relation "solid_queue_jobs" does not exist (SolidQueue::Job::EnqueueError)
LINE 10:  WHERE a.attrelid = '"solid_queue_jobs"'::regclass
                             ^
/Users/dbreunig/Development/StepList/db/seeds/00_static_user.rb:4:in `<top (required)>'
/Users/dbreunig/Development/StepList/db/seeds.rb:81:in `block in <top (required)>'
/Users/dbreunig/Development/StepList/db/seeds.rb:80:in `each'
/Users/dbreunig/Development/StepList/db/seeds.rb:80:in `<top (required)>'

Caused by:
ActiveRecord::StatementInvalid: PG::UndefinedTable: ERROR:  relation "solid_queue_jobs" does not exist (ActiveRecord::StatementInvalid)
LINE 10:  WHERE a.attrelid = '"solid_queue_jobs"'::regclass
                             ^
/Users/dbreunig/Development/StepList/db/seeds/00_static_user.rb:4:in `<top (required)>'
/Users/dbreunig/Development/StepList/db/seeds.rb:81:in `block in <top (required)>'
/Users/dbreunig/Development/StepList/db/seeds.rb:80:in `each'
/Users/dbreunig/Development/StepList/db/seeds.rb:80:in `<top (required)>'

Caused by:
PG::UndefinedTable: ERROR:  relation "solid_queue_jobs" does not exist (PG::UndefinedTable)
LINE 10:  WHERE a.attrelid = '"solid_queue_jobs"'::regclass
                             ^
/Users/dbreunig/Development/StepList/db/seeds/00_static_user.rb:4:in `<top (required)>'
/Users/dbreunig/Development/StepList/db/seeds.rb:81:in `block in <top (required)>'
/Users/dbreunig/Development/StepList/db/seeds.rb:80:in `each'
/Users/dbreunig/Development/StepList/db/seeds.rb:80:in `<top (required)>'
Tasks: TOP => db:prepare
(See full trace by running task with --trace)

Running db:create succeeds, but it only doesn't create the solid queue databases:

Created database 'steplist-dev'
Database 'steplist-dev' already exists
Created database 'steplist-test'

But now I'm curious why steplist-dev is trying to be created twice... I think the url parameter is overiding the database name, so let's change it to:

development:
  primary: &primary_development
    <<: *default
    url: "postgres://dbreunig:dbreunig@localhost:5434/steplist-dev"
  queue:
    <<: *primary_development
    url: "postgres://dbreunig:dbreunig@localhost:5434/steplist-development-queue" # <- this is new
    database: steplist_development_queue
    migrations_paths: db/queue_migrate

Suddenly it all works. I would suggest adding something in the docs about setting up the separate DB so this is more clear for those migrating.

[rosa]

Huh, I just tested a brand new app using Rails 8.0.0.beta1 and everything worked perfectly from the start, just following the instructions and configuring Solid Queue in development, using SQLite 🤔 I get the same error with bin/rails db:schema:load:queue --trace, but this looks like a bug in Rails to me. The issue with the url and DB name for PostgreSQL seems also something to document in Rails's multiple databases configuration, and perhaps link from here, but it doesn't seem Solid Queue related.

I realise that before Solid Queue started using multiple DBs, not many people were using multiple DBs with Rails, and as such, we're running into a few rough edges around this Rails feature... (eg. https://github.com/rails/rails/issues/52829) 🤔

[dbreunig]

I think the issue here isn't new apps, but those looking to migrate. Including instructions for running it in dev, essential for migrations, would be helpful. I heard of a few migrations before the separate database methodology was introduced, but none since.

[rosa]

@dbreunig, not sure I follow 🤔 Do you mean apps that were using Solid Queue before version 0.8? In that case, you have instructions about how to proceed here, as you won't be able to use the new schema file, you'd need to run the incremental migrations until your Solid Queue DB looks like the one in the schema file now.

If you mean existing apps that weren't using Solid Queue before, then there's no difference between that and a new app.

[theoo]

As a temporarily solution, we loaded the schema in a rails console with something like this:

ActiveRecord::Base.establish_connection(:queue)
load 'db/queue_schema.rb'

You might need to adjust the database and file name to your setup.

[rosa]

I'm going to close this one as it doesn't seem to be a problem with Solid Queue.

[igor-alexandrov]

@dbreunig the problem happens if your application stores schema in the SQL format config.active_record.schema_format = :sql. I tried to reproduce the issue on a fresh Rails 8.0.2 app and it still exists.

[alimi]

We worked around this issue by copying db/queue_schema.rb into a migration (db/queue_migrate/1_initial_schema.rb). We've been using Solid Queue for a while (and it's been great!), and we're moving it to its own database. We tried the workaround as described in https://github.com/rails/solid_queue/issues/365#issuecomment-2548639056, but it just ran the queue schema against our primary database (luckily we were testing on staging 😆).

Hopefully there's a fix soon for loading a database specific schema (e.g., bin/rails db:schema:load:queue) because that's the underlying issue here. https://github.com/rails/rails/pull/54853 looks promising. 🤞🏾

[gczh]

As a temporarily solution, we loaded the schema in a rails console with something like this:

ActiveRecord::Base.establish_connection(:queue) load 'db/queue_schema.rb' You might need to adjust the database and file name to your setup.

Thanks for this! Works like a charm.

A bit painful to have to dig through issues just to find this fix.

[haroldus-]

What about putting this in a migration?

class CreateSolidQueueTables < ActiveRecord::Migration[8.0]
  def change
    load Rails.root.join("db", "queue_schema.rb")
  end
end