Development & Production Sites with Database Prefixes

Posted March 17th, 2008 by castlin

I'm playing around on a couple of test sites with an idea I had during a BoF at Drupalcon. Running two sites off the same database and prefixing certain tables should allow for a simple dev/prod environment.

The idea is to isolate the configurations for the two different sites in two differently prefixed sets of tables, and allow all the content, users, taxonomy, etc. to mingle. To test this I've set up two subdomains, dev1.castlin.net and dev2.castlin.net. Here is the entry in settings.php for dev2 (dev1 is strikingly similar):

$db_prefix = array (
  'default' => '',
  'access' => 'dev2_',
  'authmap' => 'dev2_',
  'blocks' => 'dev2_',
  'blocks_roles' => 'dev2_',
  'boxes' => 'dev2_',
  'cache' => 'dev2_',
  'filters' => 'dev2_',
  'filter_formats' => 'dev2_',
  'flood' => 'dev2_',
  'menu' => 'dev2_',
  'node_access' => 'dev2_',
  'node_type' => 'dev2_',
  'permission' => 'dev2_',
  'role' => 'dev2_',
  'system' => 'dev2_',
  'users_roles' => 'dev2_',
  'variable' => 'dev2_',
);

Let's pretend dev1 is my production site and dev2 is my development site. I can use .htaccess to limit access to dev2 to just my IP address, then tweak it freely without worrying about disturbing the functionality of dev1. Also, I have access to all the actual content, user records, and taxonomy of my production site in real time, so I can see exactly how the changes I'm making are affecting the site.

When I get the dev site into a state I'm happy with, rolling it out to production should be easy:

  1. Put production offline for a minute.
  2. Change the prefix in production's settings.php to be the one I'm currently using for dev
  3. Clear the cache in production.
  4. Turn production back on.

After this, production and development are effectively the same site. However, if I make another copy of the unshared tables and give them a different prefix, and set dev's prefix to that new one, I've split them again. You could either switch prefixes back and forth, or use a datestamp as the prefix to get a kind of site versioning.

This approach is probably only useful for smaller sites with a single or very small pool of administrators and developers. Also, you couldn't make any configuration changes in production without having them "overwritten" when dev rolled over it. However, I think it might be very nimble, and I really like the idea of having all the live content available in development in real time.