skip to content

In Rails 8.1, how should code borrow a database connection to run select_all, and why is ActiveRecord::Base.connection discouraged?

level: middleimportance: nice to knowfreq 22%

answer

  1. borrow for a block, give back
  2. with_connection yields the connection
  3. lease_connection holds it until release
  4. connection is soft-deprecated
  5. permanent_connection_checkout: :deprecated, :disallowed

basics

~20 s

Use Model.with_connection { |conn| conn.select_all(sql) }, which checks a connection out for the block and returns it to the pool. ActiveRecord::Base.connection is soft-deprecated in Rails 8.1 because it leases the connection for the rest of the request or job.

solid answer

~40 s

In Rails 8.1, `Member.with_connection { |conn| ... }` yields a connection from the model's pool and returns the block's value. If the thread already holds a lease, the same connection is reused; otherwise it is checked back in when the block ends. `lease_connection` takes one for the rest of the request or job, until `release_connection`. The older `ActiveRecord::Base.connection` is soft-deprecated: it does the same permanent lease, which ties up a pool slot when an app runs more threads or fibers than it has connections. Setting `config.active_record.permanent_connection_checkout` to `:deprecated` warns on those calls and `:disallowed` raises; the default `true` does neither. Calling it on the model, not on `ActiveRecord::Base`, also picks the right pool in a multi-database app.

go deeper

for a junior

Remember the form Member.with_connection { |conn| conn.select_all(sql) } and that the block's value is returned.

for a middle

Explain checkout and check-in around the block, reuse of an existing lease, and how lease_connection and connection differ in how long they hold a slot.

for a senior

Tie it to pool pressure: permanent leases park slots when threads or fibers outnumber connections; use permanent_connection_checkout to find old calls, and pick the model's pool in multi-database apps.

for a principal

Plan the migration off connection across a large code base: warn first with :deprecated, burn down the call sites, then lock it with :disallowed.

## Where raw SQL gets its connection `find_by_sql` and `count_by_sql` fetch a connection for you. The connection-level methods (`select_all`, `select_rows`, `select_value`, `execute`) need you to **hold a connection object** first. Rails 8.1 has three ways to get one, and they differ in how long the connection stays tied to the current thread or fiber. | Call | Lease lasts | Typical use | |---|---|---| | `Member.with_connection { \|conn\| ... }` | the block, unless already leased | raw SQL in app code | | `Member.lease_connection` | until `release_connection` or the end of the request or job | code that must keep one connection, such as a session-level setting | | `ActiveRecord::Base.connection` | the rest of the request or job | legacy code; soft-deprecated | ## with_connection `with_connection(prevent_permanent_checkout: false)` is defined on every model class and delegates to that class's connection pool: 1. If the current thread or fiber already leases a connection, the block gets **that same connection**, so it sees the same open transaction. 2. Otherwise the pool **checks out** a connection, yields it, and **checks it back in** when the block ends, unless something inside the block took a permanent lease. 3. The method returns **the block's value**, so `Member.with_connection { |c| c.select_value(sql) }` evaluates to the scalar. ```ruby top = Member.with_connection do |conn| conn.select_all(Member.sanitize_sql_array([ "SELECT name, rating FROM members WHERE club_id = ? ORDER BY rating DESC LIMIT 10", club.id ])) end ``` Active Record's own query methods use `with_connection` internally, which is why ordinary model code does not hold a connection between queries. ## Why connection is discouraged `ActiveRecord::Base.connection` predates this design. Unless the code already holds an explicit lease or runs inside `with_connection(prevent_permanent_checkout: true)`, it **leases the connection permanently**, so it stays tied to the thread or fiber until the executor releases it at the end of the request or job. The configuring guide gives the reason it is now discouraged: that behaviour is undesirable when an app uses many more threads or fibers than there are connections, because each one parks a pool slot it may not be using. Rails 8.1's source marks the method **soft-deprecated** and points to `with_connection` or `lease_connection`. ## Finding the old calls `config.active_record.permanent_connection_checkout` controls what happens when code calls `ActiveRecord::Base.connection` in a way that would take that permanent lease: - `true`, the default, which `load_defaults` does not change: neither a warning nor an error. - `:deprecated`: emit a deprecation warning, useful for logging every call site in a test run. - `:disallowed`: raise `ActiveRecord::ActiveRecordError`, useful once the code base is clean. Any other value raises `ArgumentError` at boot. ## The symptom of parked connections A pool hands out a fixed number of connections. When every one is checked out, the next thread that needs one waits up to `checkout_timeout` (5 seconds by default) and then raises `ActiveRecord::ConnectionTimeoutError`. Permanent leases make that more likely: a thread that touched `ActiveRecord::Base.connection` early in a long request keeps its slot even while it is rendering, calling an API or sleeping. Short `with_connection` blocks return the slot as soon as the SQL is done, which is the point of the newer API. ## Picking the right pool Call `with_connection` on the **model whose table you query**, not on `ActiveRecord::Base`. In an app with several databases, a model under an abstract class that `connects_to` another database has its own pool, and `ActiveRecord::Base.with_connection` would hand you a connection to the primary database instead. ## Practical rules - Raw reads in app code: `Model.with_connection { |c| ... }` around the smallest block that needs it. - Do not keep the yielded connection after the block ends; it may already belong to another thread. - Use `lease_connection` deliberately, when a series of statements must share one session, and release it if you are not inside a request or job. - In console sessions and scripts, convenience calls matter less, but the same method names work.

  • Inside Member.transaction, does Member.with_connection give you a different connection?
    No. The transaction already leases a connection for the thread, and `with_connection` yields the leased one when it exists, so raw SQL inside the block runs in the same transaction and sees its uncommitted rows.
  • What does prevent_permanent_checkout: true change in with_connection?
    If code inside the block calls `ActiveRecord::Base.connection`, it normally turns the lease permanent and the connection is not returned at the end of the block. With `prevent_permanent_checkout: true`, that call does not make the lease permanent, so the connection is still checked back in.

A connection pool is a shared set of meeting rooms: with_connection books a room for one meeting and frees it when the meeting ends, while ActiveRecord::Base.connection keeps the key for the rest of the day, even while the room sits empty.

  • ActiveRecord::Base.connection is the recommended way to run raw SQL in Rails 8.1.
  • with_connection always opens a new connection, even inside a transaction.
  • with_connection returns the connection, not the block's value.
  • The default permanent_connection_checkout already raises on ActiveRecord::Base.connection.
  • ActiveRecord::Base.with_connection reaches every database in a multi-database app.

Assumes Rails 8.1, whose source marks ActiveRecord::Base.connection soft-deprecated in favour of with_connection or lease_connection.