Exporting to CSV in Rails 6 multi DB setup

Expected behavior

To export table data from current db connection to CSV

Actual behavior

Table from the default db table is exported.


Forest Liana 6.3.1

We have a rails 6 app, with a mutli db (horizontal sharding) setup.

We have implemented a smart action so the end user can select which db they want to connect to.

Then an around_action callback is performed by overriding ForestLiana::BaseController.
The action has code analogous to:

ActiveRecord::Base.connected_to(role: :writing, shard: shard) do

This works well, and ensures the controller action is executed in the context of the correct db connection.

However, when exporting the table data to csv, the default db connection is always used.

Can someone provide insight into this behaviour?

Many thanks

Hello @veritas1,

It seems that your use case is very specific and out of our usual scope.
Are you willing to share your code in order for us to try to understand more and investigate?

Thank you

Yes. What would you like?

Hi @veritas1,

The smart action allowing this DB switch, alongside the ForestLiana::BaseController override you seems to have made might help us dig into this.

We can take a look to try and find something that might have gone wrong with your data export. But by overriding forest base controller and having a smart action dynamically changing your db connection, you’re changing forest’s original behavior and this kind of issues might be out of the scope of what we can help you with.

Best regards

The smart action just writes the user selected db connection to a file.

Here is the base controller:

ForestLiana::BaseController.class_eval do

  around_action :set_database_connection

  def set_database_connection
    yield and return if defined?(forest_user).nil?
    user_id = forest_user['id']
    yield and return if user_id.nil?

    file_path = File.join(Rails.root, DB_CONNECTIONS_FILE)

    data = File.read file_path
    hash = eval data

    user_db_connection = hash[user_id]
    yield and return if user_db_connection.nil?

    Rails.logger.info "Admin panel connecting to database: #{user_db_connection}"

    ActiveRecord::Base.connected_to(role: :writing, shard: user_db_connection.to_sym) do

Thank you for sharing this with us, if I understand well, this configures a custom connection for each user when they are querying on your admin panel. I have two questions then:

  • Does that work for the all app except the export (listing values, dashboards, smart actions, etc)?
  • When exporting, is the queried DB the one you defined by default through your env variables?

Thank you

Yes. Everything tested so far like listing values, dashboards, smart actions all work as expected e.g. the correct database connection is used.

No. It is not using the database specified in the DATABASE_URL env variable. It is using the first database defined in the config e.g shard1 as per the below.

production: &production
    <<: *default
    database: defaultdb
    url: <%= ENV["SHARD_1_DATABASE_URL"] %>
    prepared_statements: false
    advisory_locks: false
    <<: *default
    database: defaultdb
    url: <%= ENV["SHARD_2_DATABASE_URL"] %>
    prepared_statements: false
    advisory_locks: false
    <<: *default
    database: defaultdb
    url: <%= ENV["SHARD_3_DATABASE_URL"] %>
    prepared_statements: false
    advisory_locks: false

Hello @veritas1,

I’ve dig into our code. The good news: It should work.

To be honest, I don’t see any reason your code is not working on this specific case.

So, can you debug your code and look at the forest_user and user_id. In order to know if in the case of Exporting to CSV they are well defined.

Best regards,

Hi @morganperre

Sorry for the slow reply.

Yes, the forest_user object is set properly and looks normal.

Something I noticed though is on the csv request, the recorded active record time is 0.0ms

[Tracing] Starting <rails.request> transaction </forest/Organisation.csv>
Started GET "/forest/Organisation.csv?fields%5BOrganisation%5D=active%2Ccreated_at%2Cid%2Cname%2Cslug%2Cupdated_at&search=&filters=&searchExtended=0&timezone=Europe%2FLondon&filename=organisations&header=active%2Ccreated%20at%2Cid%2Cname%2Cslug%2Cupdated%20at" for at 2021-05-10 13:53:03 +0100
Processing by ForestLiana::UserSpace::OrganisationController#index as CSV
  Parameters: {"fields"=>{"Organisation"=>"active,created_at,id,name,slug,updated_at"}, "search"=>"", "filters"=>"", "searchExtended"=>"0", "timezone"=>"Europe/London", "filename"=>"organisations", "header"=>"active,created at,id,name,slug,updated at", "collection"=>"Organisation"}
Completed 200 OK in 10ms (ActiveRecord: 0.0ms | Allocations: 19542)

My thoughts are that either cached values are used, or the work is carried out on a background thread.

It could be that a new thread is created? and the db connection is not maintained.

1 Like

Hey @veritas1,

This still seems weird, since the code for the CSV export & the records fetch to display the table view are almost the same.

Looking at the connected_to documentation (here)

At the end of the block the connection will be returned to the original role / shard.

I guess that is the behavior you are experiencing, right?
Looking at the forest_liana code, I can’t figure out any reason this would not work. I would highly suggest to try debugging this with the forest_liana code, especially here and here since I’m pretty sure that if you are experiencing this issue only for CSV export, you’ll see it here.

Let me know if that helps :pray:


I see what’s happening. The difference with the CSV response, is that the CSV response is streamed.

This code:

ActiveRecord::Base.connected_to(role: :writing, shard: user_db_connection.to_sym) do

ensures the correct db connection for the scope of the controller action.

But on a CSV request, the controller action returns immediately (hence 0.0ms ActiveRecord time, as the db has not been touched yet).

The response body is made available by the Enumerator, but the controller action has already completed when the passed code block is executed, so the db connection has reset to the default one.