Ruby on Rails 5中如何将SQLite3数据库导出为CSV并实现点击下载

Hey there! Let me walk you through how to add a one-click CSV export feature for your SQLite3 database in Rails 5. I'll cover both exporting a single model's data and the full database (as a ZIP of multiple CSVs) so you can pick what fits your needs.

Let's start with the simplest case—exporting all records from a single model (like User).

1.1 Add the Export Action to Your Controller #

Open your model's controller (e.g., app/controllers/users_controller.rb) and add this action:

def export_csv
  @users = User.all
  respond_to do |format|
    format.csv do
      # Set headers to trigger download
      headers['Content-Disposition'] = "attachment; filename=\"users-#{Date.today}.csv\""
      headers['Content-Type'] = 'text/csv'
      
      # Generate CSV content directly (no need for a separate view template)
      self.response_body = CSV.generate(headers: true) do |csv|
        # Add column headers as the first row
        csv << User.column_names
        # Add each record's data
        @users.each do |user|
          csv << User.column_names.map { |col| user.send(col) }
        end
      end
    end
  end
end

1.2 Update Your Routes #

Add a collection route for the export action in config/routes.rb:

resources :users do
  get :export_csv, on: :collection
end

1.3 Add a Download Button to Your View #

Place this link/button wherever you want users to trigger the download (e.g., in app/views/users/index.html.erb):

<%= link_to 'Download Users CSV', export_csv_users_path, class: 'btn btn-primary' %>

If you need to export all tables in your SQLite3 database, we can package each table's CSV into a ZIP file for easy download.

2.1 Add the Required Gem #

First, add the rubyzip gem to your Gemfile to handle ZIP creation:

gem 'rubyzip'

Run bundle install to install it.

2.2 Create an Exports Controller #

Make a new controller dedicated to exports:

class ExportsController < ApplicationController
  # Add authentication here if needed (e.g., before_action :authenticate_user!)

  def full_database
    # List all models you want to export (or auto-discover them if you prefer)
    models = [User, Post, Comment, Product]

    # Create a temporary ZIP file
    zip_file = Tempfile.new(["database-export-#{Date.today}", '.zip'])

    Zip::File.open(zip_file.path, Zip::File::CREATE) do |zip|
      models.each do |model|
        # Generate CSV for each model
        csv_content = CSV.generate(headers: true) do |csv|
          csv << model.column_names
          model.all.each do |record|
            csv << model.column_names.map { |col| record.send(col) }
          end
        end
        # Add the CSV to the ZIP file
        zip.get_output_stream("#{model.name.underscore.pluralize}.csv") do |os|
          os.write(csv_content)
        end
      end
    end

    # Send the ZIP file to the user
    send_file zip_file.path, 
              filename: "database-export-#{Date.today}.zip", 
              type: 'application/zip'
    zip_file.close
  end
end

2.3 Add the Route for Full Export #

Add this to config/routes.rb:

get 'exports/full_database'

2.4 Add the Full Export Button #

Add this link to your view (e.g., a dashboard page):

<%= link_to 'Download Full Database', exports_full_database_path, class: 'btn btn-danger' %>

• Handling Special Characters: Using CSV.generate automatically escapes commas, quotes, and newlines in your data—so you don't have to worry about broken CSV files.
• Authentication: Don't forget to add authentication checks (like before_action :authenticate_user!) to your export actions if only authorized users should access the database data.
• Performance: If your database has large datasets, consider adding pagination or background jobs (like Sidekiq) to avoid timeouts during export.

内容的提问来源于stack exchange，提问作者T.Aloufi