Rails database.yml, resolved

Nothing in config/database.yml is the configuration your application connects with. The file is YAML with ERB in it, Rails merges a shared: block into each environment, Active Record merges any DATABASE_URL over the result, and the PostgreSQL adapter throws away every key libpq does not recognise before it dials. Four transformations, and the two that surprise people are silent.

Everything below was run on a scratch Rails app generated with rails _8.1.3.1_ new against PostgreSQL 17.7 on port 15432, with activerecord 8.1.3.1, pg 1.6.3 and Ruby 4.0.5 on an Apple M2 Max. Twelve Minitest examples assert the claims and all of them pass; the outputs are pasted from bin/rails runner.

The one command that shows what the file became

bin/rails runner 'pp ActiveRecord::Base.connection_db_config.configuration_hash' prints the resolved hash for the current environment, and it is the only honest answer to "what is my app connecting to". Reading the YAML is guessing, because the YAML is an input to four steps.

The scratch app's config/database.yml, with a password in it on purpose so the merges further down are visible:

default: &default
  adapter: postgresql
  encoding: unicode
  max_connections: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  host: localhost
  port: <%= ENV.fetch("DATABASE_PORT", 5432) %>
  username: dbyml_app
  password: from_the_file

development:
  <<: *default
  database: dbyml_development

And the resolved configuration, with DATABASE_PORT=15432 exported:

{adapter: "postgresql",
 encoding: "unicode",
 max_connections: 5,
 host: "localhost",
 port: 15432,
 database: "dbyml_development",
 username: "dbyml_app",
 password: "from_the_file"}

The object holding that hash is an ActiveRecord::DatabaseConfigurations::HashConfig. It is worth knowing by name, because half the accessors you want are methods on it rather than keys in the hash: max_connections, checkout_timeout, idle_timeout, reaping_frequency, migrations_paths. The port above is an Integer because YAML retyped the ERB output, which is the same mechanism covered in environment variables in Rails.

bin/rails dbconsole reads the same resolved configuration and execs psql with it, so it is the second way to check, and it proves the credentials rather than the file:

 current_database  | current_user | inet_server_port
-------------------+--------------+------------------
 dbyml_development | dbyml_app    |            15432
(1 row)

default: &default is a YAML feature, shared: is a Rails one

The default: &default anchor that ships in every generated config/database.yml is plain YAML aliasing, and Rails knows nothing about it. shared: is the Rails equivalent, handled in Rails::Application::Configuration#database_configuration at railties-8.1.3.1/lib/rails/application/configuration.rb:459, and it does one thing the anchor cannot: in a three-tier environment it reverse-merges into each named entry by itself, so primary and cache both inherit without either of them writing <<: *default.

shared:
  adapter: postgresql
  encoding: unicode
  host: localhost
  port: <%= ENV.fetch("DATABASE_PORT", 5432) %>
  max_connections: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>

development:
  database: dbyml_development

production:
  primary:
    database: dbyml_production
  cache:
    database: dbyml_production_cache
    migrations_paths: db/cache_migrate

primary
{adapter: "postgresql",
 encoding: "unicode",
 host: "localhost",
 port: 15432,
 max_connections: 5,
 database: "dbyml_production"}
cache
{adapter: "postgresql",
 encoding: "unicode",
 host: "localhost",
 port: 15432,
 max_connections: 5,
 database: "dbyml_production_cache",
 migrations_paths: "db/cache_migrate"}

The anchor has a cost the shared: block does not. default: is a top-level key, so Active Record builds a configuration out of it, and that configuration exists for the rest of the process. Listing every configuration in both files, same app, same command:

--- anchor file: what environments exist ---
default/primary
development/primary
test/primary
--- shared file: what environments exist ---
development/primary
production/primary
production/cache

Nothing in the scratch app tripped on that phantom default/primary: bin/rails db:prepare created dbyml_test and never mentioned it, because the tasks filter on the current environment. Where it shows up is any code of your own that walks ActiveRecord::Base.configurations.configurations without filtering, which is how multi-database housekeeping usually gets written. Rails deletes shared before Active Record ever sees it (loaded_yaml.delete("shared"), same file, line 459); nothing deletes default.

The cost of shared: is a reader one. Somebody opening the file to find out what development connects to greps for development:, finds two lines, and has to know the key above exists. The anchor is uglier and more obvious. Either is fine; mixing them in one file is not, because then <<: *default and shared: both apply and the precedence is one more thing to work out.

DATABASE_URL merges over the file, it does not replace it

A DATABASE_URL does not stand in for config/database.yml, and the behaviour that costs people an afternoon is one line of Active Record: @configuration_hash = @configuration_hash.merge(build_url_hash) in activerecord-8.1.3.1/lib/active_record/database_configurations/url_config.rb:44. The file is the base, the URL is merged on top, and ConnectionUrlResolver#to_hash runs compact_blank before it merges, so a component the URL does not carry is simply absent from the merge and the file's value survives.

Same config/database.yml as above, with DATABASE_URL=postgres://db.internal/dbyml_from_url:

ActiveRecord::DatabaseConfigurations::UrlConfig
{adapter: "postgresql",
 encoding: "unicode",
 max_connections: 5,
 host: "db.internal",
 port: 15432,
 database: "dbyml_from_url",
 username: "dbyml_app",
 password: "from_the_file"}

Host and database came from the URL. Username and password came from the file. Port came from the file too, because the URL named no port, which means an application whose DATABASE_URL points at a managed Postgres on 5432 will keep connecting to whatever DATABASE_PORT your development file defaults to if the URL omits the port.

The sharp version of that is credentials. A URL that names a user and no password does not clear the file's password, and neither does an explicit empty one:

DATABASE_URL=postgres://url_user@db.internal/dbyml_from_url
{username: "url_user", password: "from_the_file"}

So rotating a password by changing DATABASE_URL while a stale password: sits in the committed file gives you url_user authenticating with the old secret, and the error you get back says the credentials are wrong without saying which half came from where. Delete the key from the file.

Query parameters become configuration keys, as strings. HashConfig#max_connections casts with &.to_i on read, so the pool is right even though the hash holds "11":

DATABASE_URL="postgres://u:p@db.internal:6543/d?max_connections=11&connect_timeout=2"
{adapter: "postgresql",
 encoding: "unicode",
 max_connections: "11",
 host: "db.internal",
 port: 6543,
 database: "d",
 username: "u",
 password: "p",
 connect_timeout: "2"}
c.max_connections = 11

With no config/database.yml on disk at all, DATABASE_URL alone boots and connects. That path is in configuration.rb:477 and it is worth knowing exists, mostly so that a missing file is not the first thing you suspect.

DATABASE_URL reaches primary and no other entry

In a three-tier environment, DATABASE_URL is merged into the entry named primary and nowhere else. ActiveRecord::DatabaseConfigurations#environment_value_for (database_configurations.rb:304) looks up "#{name.upcase}_DATABASE_URL" first and falls back to DATABASE_URL only when the entry is called primary. The shared: file above, production environment, with DATABASE_URL=postgres://pg.example/app_production:

primary: UrlConfig pg.example/app_production
cache: HashConfig localhost/dbyml_production_cache

Add CACHE_DATABASE_URL=postgres://pg.example/app_cache and the second line moves:

primary: UrlConfig pg.example/app_production
cache: UrlConfig pg.example/app_cache

A single-database platform that provides one DATABASE_URL therefore needs an explicit url: <%= ENV["DATABASE_URL"] %> on the cache, queue and cable entries, or those three keep the host from the file. That is exactly the failure behind the Solid trio not existing after a deploy, written up in the Rails 7 to 8 upgrade.

pool: is spelled max_connections: since Rails 8.1

Rails 8.1 renamed the pool size key. The activerecord CHANGELOG entry that did it introduces keepalive, max_age and min_connections and renames pool to max_connections to match, credited to Matthew Draper, Chris AtLee and Rachael Wright-Munn, and notes that default behaviour is unchanged. The generated config/database.yml for a new 8.1 app now carries:

default: &default
  adapter: postgresql
  encoding: unicode
  # For details on connection pooling, see Rails configuration guide
  # https://guides.rubyonrails.org/configuring.html#database-pooling
  max_connections: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>

pool: still works. HashConfig#max_connections reads :max_connections and falls back to :pool before defaulting to 5 (hash_config.rb:73), and HashConfig#pool is now an alias declared deprecated on line 85. What does not work is having both at different values, which raises in the constructor, before the adapter is loaded and before anything connects:

{pool: 12} -> max_connections=12
{pool: 5, max_connections: 20} -> RuntimeError: Ambiguous configuration: 'pool' (5) and 'max_connections' (20) are set to different values. Prefer just 'max_connections'.
{pool: 5, min_connections: 2} -> RuntimeError: Ambiguous configuration: when setting 'min_connections', use 'max_connections' instead of 'pool'.

The second of those is the one an upgraded application meets: the file still says pool: from Rails 7, somebody adds min_connections: because the 8.1 release notes mentioned it, and the application will not boot. Rename the key rather than keeping both, and do it in the same commit as the load_defaults bump so the diff explains itself.

Defaults on 8.1.3.1, printed off a live pool rather than recited: max_connections 5, min_connections 0, checkout_timeout 5.0, idle_timeout 300.0, keepalive 600.0, max_age Infinity, reaping_frequency 20.0. The last one is not a constant: it is [20, idle_timeout, max_age, keepalive].compact.min, so lowering idle_timeout below 20 speeds the reaper up as a side effect.

The dead end: min_connections: 3 opened nothing

min_connections: 3 with reaping_frequency: 1 in a bin/rails runner script left the pool at one connection for the full six seconds it was watched. Nothing was misconfigured; pool.min_connections read back as 3 and the AR Pool Reaper thread was in Thread.list. The floor simply does not apply yet.

ConnectionPool#prepopulate returns early unless @activated is true (connection_pool.rb:784), with the comment "We don't want to start prepopulating until we know the pool is wanted, so we can avoid maintaining full pools in one-off scripts etc." @activated is set in try_to_checkout_new_connection at line 1230, and only when the pool already holds a connection or the checkout arrives from a different execution context than the one that created the pool. A script that checks out once, from the thread that built the pool, satisfies neither.

Forcing a genuinely concurrent checkout flips it, and the reaper fills the pool on its next pass:

one checkout in a script: activated=false pool=1
after a concurrent one:   activated=true pool=2
2.5s later:               activated=true pool=3
backends in pg_stat_activity: 2

The last line is measured and not explained here. pool.connections.size reached 3 and stayed there, connected? was true for 2 of the 3 for the next 10 seconds, and pg_stat_activity agreed with connected? rather than with the pool size. Whatever keeps the third slot from dialling was not chased down, so treat min_connections as a floor on connection objects and verify the backend count on your own server before sizing anything around it.

The practical consequence is narrower than it looks, and the second half of this paragraph is read off the source rather than measured. A web server checks out from a different execution context than the one that built the pool, which is the other half of the condition on line 1230, so a Puma worker under real traffic should activate early and min_connections should behave as advertised. What it demonstrably does not do is warm a pool in a rake task, a runner script or a one-shot container, which is precisely where somebody would reach for it.

Which keys in the file reach PostgreSQL

Three different consumers read config/database.yml, and a key that belongs to none of them is discarded without a word. PostgreSQLAdapter#initialize renames username to user and database to dbname, then runs conn_params.slice!(*PG::Connection.conndefaults_hash.keys + [:requiressl]) at postgresql_adapter.rb:345. On pg 1.6.3 against PostgreSQL 17.7 that list has 50 entries:

service user password passfile channel_binding connect_timeout dbname host
hostaddr port client_encoding options application_name fallback_application_name keepalives keepalives_idle
keepalives_interval keepalives_count tcp_user_timeout sslmode sslnegotiation sslcompression sslcert sslkey
sslcertmode sslpassword sslrootcert sslcrl sslcrldir sslsni requirepeer require_auth
min_protocol_version max_protocol_version ssl_min_protocol_version ssl_max_protocol_version gssencmode krbsrvname gsslib gssdelegation
replication target_session_attrs load_balance_hosts scram_client_key scram_server_key oauth_issuer oauth_client_id oauth_client_secret
oauth_scope sslkeylogfile

A handful of keys belong to the adapter rather than to libpq and are applied after the socket is open: encoding: becomes set_client_encoding (line 981), and variables: becomes a SET per pair (line 998). Everything else is pool configuration, read by HashConfig. Anything in a fourth category is kept in the hash and never used. Adding application_name, a variables: block and a nonsense key to the development entry:

development:
  <<: *default
  database: dbyml_development
  application_name: yield-article
  variables:
    statement_timeout: 250ms
    lock_timeout: 100ms
  banana: yes

statement_timeout = 250ms
lock_timeout      = 100ms
application_name  = yield-article
client_encoding   = UTF8
banana in config  = true
banana reached PG = false
{user: "dbyml_app",
 password: "from_the_file",
 dbname: "dbyml_development",
 host: "localhost",
 port: 15432,
 application_name: "yield-article"}

variables: is the useful half of that. A per-application statement_timeout set in config/database.yml applies to every connection the pool opens, with no initializer and no SET scattered through the code. Three connections checked out of the same pool, then one slow query on the first of them:

connection 1: statement_timeout = 250ms
connection 2: statement_timeout = 250ms
connection 3: statement_timeout = 250ms
ActiveRecord::QueryCanceled: PG::QueryCanceled: ERROR:  canceling statement due to statement timeout

The cost is that migrations run on a pool connection like everything else. A migration whose up is nothing but execute "select pg_sleep(1)" does not survive the same setting, and neither will a CREATE INDEX on a table of any size:

ActiveRecord::QueryCanceled: PG::QueryCanceled: ERROR:  canceling statement due to statement timeout (ActiveRecord::QueryCanceled)

So a statement_timeout in this file wants a matching execute "SET statement_timeout = 0" at the top of the migrations that need it, or the first slow index build on production is a failed deploy.

The misspelling that reports as a password problem

Misspelling database: in config/database.yml produces an error about credentials. The typo survives into configuration_hash as databse:, slice! drops it before PG.connect, libpq defaults the database name to the user name, and PostgreSQL answers FATAL: database "dbyml_app" does not exist, which is the same thing psql -U dbyml_app with no database argument gets. Then PostgreSQLAdapter.new_client classifies the failure by substring (postgresql_adapter.rb:59 onwards): conn_params[:dbname] is nil, so the two dbname branches are skipped, the message contains conn_params[:user], and the branch that fires is the username one:

ActiveRecord::DatabaseConnectionError: There is an issue connecting to your database with your username/password, username: dbyml_app.

Please check your database configuration to ensure the username/password are valid.

The password was correct. Any message from that method naming your username is worth reading as "the error text mentioned your username", not as "the password is wrong", and the first thing to check is that the key next to it is spelled the way Active Record spells it.

What this page does not cover

Connection failure timing is a separate subject and is measured in Rails health checks: connect_timeout, the connection_retries default of 1 that makes a dead host cost twice what you configured, and what a blocked pool does to an unrelated request. Nothing here is about SQLite or MySQL, and the 50-key list is libpq's alone. Replicas, connects_to, connected_to and horizontal sharding are configuration this file carries and this page does not explain. Neither are schema_search_path, prepared_statements, advisory_locks or schema_dump, all of which are real keys with real effects. And putting the password somewhere other than the file is encrypted credentials, not config/database.yml.

The scratch app and its twelve Minitest examples are a few hundred lines and reproduce against any PostgreSQL you point DATABASE_PORT at.