12

Can I set rails to use mysql with MEMORY as the DB engine? I never restart mysql, and rebuild the db so often I'd rather have it be fast. Having the memory db for running tests would be nice too.

EDIT: I should have specified this is for dev/testing only, not production.

Daniel Huckstep
  • 5,368
  • 10
  • 40
  • 56

3 Answers3

7

I don't see why you couldn't; your choice of storage engine is a MySQL implementation detail. All you should need to do is set :options => "ENGINE=MEMORY" in your create_table declaration in your migrations.

Of course, I also don't see why you would -- especially in production. The MySQL documentation for the MEMORY engine is full of caveats, like fixed length field allocation, and the speed gain you'd realize has got to be trivial compared to the risk of losing everything. If your application is such that nothing needs to be persisted, ever, why not just skip ActiveRecord completely and layer your models on top of Memcached?

SFEley
  • 7,660
  • 5
  • 28
  • 31
  • I should have said originally, but this is NOT for production, just me developing. Putting the engine in the migration is not an option then (unless I conditionally do it based on environment) – Daniel Huckstep Oct 22 '09 at 17:25
  • So do it conditionally based on environment. Why is that not an option? `:options => (RAILS_ENV != 'production' ? "ENGINE=MEMORY" : nil)` (Just make sure your testing isn't thrown off by some weirdness that only exists in the memory engine. Personally, I still wouldn't bother with this; but if you really think database speed is your testing bottleneck, knock yourself out.) – SFEley Oct 22 '09 at 18:47
  • 1
    Because I don't want to commit the migrations to the scm with all that crap in there. I wanted to just do it locally, in my own little world, and if it made things a bit faster for me when I had to do db rebuilds or something, then win, but if not, who cares, I'd just use the regular setup. From the answers and some other reading, it's more work than it's (potentially not even) worth. – Daniel Huckstep Oct 23 '09 at 06:17
  • Upvote and accept since this does seem to be the way to do it though. – Daniel Huckstep Oct 23 '09 at 06:18
3

I use sqlite3 in memory database for testing. It's usually a little bit faster than file based, but not THAT much unless you have a ton of test data.

To set that up your database.yml will look like this:

test:
adapter: sqlite3
database: ":memory:"

You'll also have to load your schema into your in memory database in your test helper like so:

config = YAML::load(IO.read(File.dirname(__FILE__) + "/../config/database.yml"))
ActiveRecord::Base.logger = Logger.new(File.dirname(__FILE__) + "/../log/debug.log")
ActiveRecord::Base.establish_connection(config["test"])
load(File.dirname(__FILE__) + "/../db/schema.rb")
Jason stewart
  • 1,085
  • 8
  • 8
  • Why would you need to establish the connection in the test? I thought this is something Rails would do for you. – Ryan Bigg Oct 21 '09 at 20:47
  • I've done this too and it is sweet, but I stopped using SQLite locally due to the differences between it and MySQL/PostgreSQL. ActiveRecord hides most of the details but there's enough difference to make it annoying. – Luke Francl Oct 22 '09 at 14:23
  • The connection has to be established before the tests are run so that the schema can be loaded into the memory database before rails is loaded. The only place that I know of to load it is in the test helper, and at this point there is no connection made to the database by rails. Please correct me if I'm wrong, or if there's an easier way to do it. – Jason stewart Oct 22 '09 at 20:47
2

For testing purposes consider https://github.com/mvz/memory_test_fix plug-in. It's usage as easy as to update database.yml and to run 

rails plugin install git://github.com/mvz/memory_test_fix.git

You may try also install memory_test_fix gem, but it's from different git branch and does not support Rails 3.

The gem helped me to reduce test cases execution time from 25 seconds to 19. On the other hand, it introduces 2 second overhead on database schema initialisation (and i don't have that much of them). So on small sets of tests it's not worth bothering.

Solution from Jason Stewart's answer is basically the same. And I used it instead of plugin, because it was easier to combine it with Spork plug-in.

Alexey
  • 9,197
  • 5
  • 64
  • 76