Storing config data - RDBMS vs File?

[Seumas]

I'm looking for some opinions on storing configuration data in a database versus in a file.

I notice that the majority of programs I've used seem to have a lot of their preferences and configuration settings stored in a file. Especially when dealing with PHP. But it would seem to me that if you're using a relational database and you're already making a connection to the database to serve a page or anything else, then why not suck your configuration information out of a config table?

Currently, I place a line near the top of my program that does a selectrow_hashref() to grab configuration data. The only configuration settings outside of the database are placed in the script itself, and that's just the connect() line.

I've been considering using Config::IniFiles, instead. That might be overkill, though. Should I really use all of that extra code instead of the simple one selectall_hashref?

Or, are there other suggestions?

[tilly]

My strongly held opinion is that configuration information belongs in a file, under revision control.

If that configuration information has a good reason to go into a database, it should still be maintained in a file, under revision control, and then the database table should occasionally be reloaded from said file.

There are many reasons for this design choice. The key one is that sane organizations do not develop against their production database. Instead they have a separate development environment that they can work against, and then they have a release process into production. (Often this release process involves more databases, for instance you may have one devoted to QA, and another for load testing.) If you store the configuration in the database, this release process quickly becomes more complicated and risky. Furthermore the development process also runs into issues when people need to do things like change a configuration temporarily just to test something. (What if you don't change it back correctly?)

By contrast when the information is in a configuration file, under revision control, then necessary configuration changes get released in sync with the code that it is connected to. Furthermore a slew of development problems are now handled by your revision control system. You can ask, and answer, important questions such as, "Here's when we changed Y, that was after this problem started being reported, so it cannot be the cause."

This opinion is strong enough that I would rather not work with companies which make the design mistake of keeping configuration information in a database. Just as I would prefer not to work at an organization without a development environment, or which does not use revision control. (The latter two are more important than this design mistake though.)

UPDATE: s/avoid working/rather not work/ and added clarifying comment.

[meredith]

If you store your config in a RDBMS, how do you want to get the configuration info for the DBI connection? I stick to human-readability, myself. I've used the basic INI-style stuff, and abused Data::Dumper once before, but my favorite is YAML. I can often make a human-readable config file, that is parsed into the exact data structures I want to work with.

Update: Also, if you're using a DB for key-value pairs, a table with tons of fields is horrible to edit. Use a two-field table, and make a class that uses AUTOLOAD to grab individual keys. If nothing else, you will strike fear in the hearts of your co-workers!

[kanwisch]

I'm with hattmoward. You'll have to have configuration information for the database then, and will you have really gained anything? The only reason I can think to store information in a database is if you want to make it more difficult for cracker (and any future maintainers of your code) to piece together the information, or if you're going to have lots of different configurations available to a single application (think multi-threading oriented at users; aka portals).

[dragonchild]

hattmoward made the very point I was going to make - how are you going to connect to your database?

There's an additional point, too. How can you trust that the configuration data in your database is unchanged? Most (nearly all) schemas don't audit themselves. This means that data can be changed and, short of a DBA going back through the redo logs, you would have no chance of detecting that. (And, frankly, very little chance at that, given the size and inscrutability of most redo logs.)

However, you can detect changes to a configuration file comparison with your source control. Thus, you can do something like cvs diff -r last_version_to_be_released config.ini and be sure nothing has changed. (Well, someone could mess with your CVS revisions, but that would require hacking root. After that, why would they stop at your CVS?)

If you're looking for a good config-file module, I'd take a look at Config::ApacheFormat. I'm using it right now in a production environment and it works great.

[Seumas]

The only reason I've used the database for my configuration data all this time is that it seemed better to make one query to a databse that I was already connected to each time a user accessed a page than to load up the extra code of a config file parser, access the file, parse the file and then move on every time.

This wouldn't be an issue with mod_perl, obviously, but I'm not using mod_perl for my project, yet. I don't have the experience or confidance to do so, though I'm working on it.

[dragonchild]

my $config = Config::ApacheFormat->new;
$config->read( '/path/to/config.ini' );

my $block = $config->block( Database => $database_name );

my $dbh = DBI->connect(
    (map { $block->get( $_ ) || '' } qw( sid user password )),
);

What's so hard about that? And, before you worry about loading the code of a config fileparser ... compare that with the cost of performing an extra database query. I think you'll be (un)pleasantly surprised.

Or, think about it this way - when so many people are telling you something, isn't it worth a day to test it out?

[talexb]

Consider this a vote for YAML .. works very well, platform independent, easy to set up, especially compared to going through a database.

[mpeppler]

I'm with tilly on this - and I hold this opinion just as strongly.

Pure configuration data should probably live outside of the database, although in some cases you might need it in the db (for example, to control the behavior of stored procedures). This information should in all cases be kept under revision control, just as your database schema should be managed under revision control.

Michael

[jZed]

Personally, the only reason[1] I can see for storing config information in a file rather than an RDBMS (assuming the script uses an RDBMS anyway) is the issue of human readability. And for that you can use DBD::CSV or DBD::AnyData (which supports XML, Ini, and other human readable formats) which will allow the data to be human readable *and* accessible with selectall_hashref(). If you don't need it to be human readable/editable, just stick with your current RDBMS. My $0.02.

update [1] : well, obviously my imagination is somewhat limited this morning. What tilly said.

[water]

YAML is a very good thing indeed. Not just for config files, but config files are a great application.

print "YAML++ " x 100;

Kudos to the YAML developers.