Custom Sql Patterns

[jackcb123]

Hi! We use a lot of sql patterns where we have comments at the beginning of custom queries in blocks or in-line, use ctes like 'WITH' , etc. Is it possible to customize the sql checker that decides whether to use the primary or replica? If not, do you mind me asking where that occurs within your repo?

This is what we currently use with makara if that's helpful! Hoping to make the switch to yours (as of course makara is no longer maintained)

CUSTOM_SQL_REPLICA_MATCHERS = [
# Match comment blocks + inline comments and whitespace, finding queries with the 'with' or 'select' keywords
/\A\s*((--.\n\s)|(/*.?*/\s))*\b(select|with)\b/i
].freeze

[mateuscruz]

Welcome back @jackcb123.

You can find the logic here: https://github.com/Nasdaq/active_record_proxy_adapters/blob/main/lib/active_record_proxy_adapters/primary_replica_proxy.rb#L196C1-L202C8

We don't have a quick way to customize the regexes at the moment but here are a few options you could try:

• Create a custom adapter and a custom proxy (there's instructions in README for that)
• Monkey patch the constant SQL_REPLICA_MATCHERS to include any other regex patterns you need
• Monkey patch method need_primary?(sql_string) directly. Something like this:

    def need_primary?(sql_string)
      return true  if cte_for_write?(sql_string)
      return true  if SQL_PRIMARY_MATCHERS.any?(&match_sql?(sql_string))
      return false if SQL_REPLICA_MATCHERS.any?(&match_sql?(sql_string))
      return false if CUSTOM_SQL_REPLICA_MATCHERS.any?(&match_sql?(sql_string))
  
      true
    end