How can I include big support databases in my RSpec tests?

Question

I have 3 large read-only databases that support my Rails 3.2 application in that values in them are examined and some of them stored along with ancillary information in my main database. I have these set up as schemas in my development and production Postgres databases.

Is this the wrong approach? Since they are only read-only, is there a better way to just have them live in their own Postgres database and just reference them from there? I don't think the PG gem works that way.

If they do need to be in every environment, how can I assure they are a part of my test environment?

I can't just copy these environments to test, they will get whacked every time I run:

rake db:test:prepare

Answer 1

If I understood right:

• You're having 3 large databases with supporting data which is essential for running your application
• Since they are large, you don't want to store them as XML/JSON/serialized files, and you prefer to have them in database for easy querying
• You want to be able to access the same 3 databases in all your environments and servers

So what you have here is basically something that is on par with source code or configuration, and which should ideally be part of your codebase. i.e. its a fixture, albeit a database fixture and not a YAML or JSON fixture.

The ideal solution is, you should convert that database dump into a fixture. Here is how:

• Take a PostgreSQL backup of the data, and save it as test/fixtures/your_model_name.dmp
• Create a new ERB fixture test/fixtures/your_model_name.erb. ERB fixtures can have Ruby code

• In the ERB fixture add the following code:

  <%
    cfg = YourModel.connection_config
    FileUtils.sh "psql " +
                 "-u #{cfg['username']} " +
                 "-p #{cfg['password']} " + 
                 "-h #{cfg['host'] || 'localhost'} " +
                 " < #{Rails.root + '/test/fixtures/yourmodel.dmp'}"
  %>
  

Since it is ERB file, it will automatically run the code inside it and will import data into PostgreSQL. The only catch is that you need psql in your path. If your app server and database are in separate locations, then you need to ssh in to the server and then run the import.

One alternative option is to directly save these databases as SQLite files, and then for these 3 models alone use a separate connection. This works more widely, unless you don't have any direct foreign key references and RESTRICT/CASCADE definitions between your main application tables and these 3 tables. But I guess you're already not having direct relationships between your main database and these 3 databases, since cross-database references are a pain to handle in most cases.