Database-stored configuration management

Question

Developing database-backed software, often one finds it convenient to store stuff in the database which one might traditionally express in code, but which is useful to store in the database (so that it can be changed without requiring a reload of the application).

For instance, one might have processes which send automated email using a template; it makes sense to store this template in the database, so that the text can be tweaked by the users without having to change the code/redeploy/etc.

However, these templates are "important"- they are required for the correct operation of the system, and for instance, if they become malformed or missing, the underlying functionality would probably stop working. As they live now in the database, they don't outside version control- you might add audit tables to have change history about this information, but revisions/etc. are separate and independent from your main code's revisions.

How do you handle this kind of stuff? Not putting this kind of stuff in the database and make changes go through development/change control/deployment cycles? Or something else?

Answer 1

If I got you right, you have data which may be edited by your users (at least, to some degree), but this data is "required for the correct operation of the system" and "if it becomes malformed or missing, the underlying functionality would probably stop working".

There are some things you can do here:

• forbid your end users to manipulate that data. You can store those data in the database, but your program does not offer any functionality to change it. The only one who is allowed to change that data is, for example, an update installer provided by you when a new release of your software gets deployed. Or, you allow your end user copying of that data and let them manipulate the copy, but never the original thing
• allow only some special users (admins) to change that data in the database (but beware, admins can make errors, too)
• add a functionality in your application (or only for admins) to restore that data to its original state provided it becomes malformed or missing
• provide a functionality to check if the data is malformed or missing; run this check after someone changes the data, and (more important): run it whenever your program tries to use that data
• separate the parts of the data which can be safely changed by the end user clearly from the parts which cannot (and don't allow users to change the latter)
• try to make your updates "intelligent": when you deliver a new release, including an update installer for your database, the installer should expect that data like email templates was manipulated since the initial installation, and act accordingly

It should be obvious that each of these suggestions can be combined with each other.

Answer 2

It is better to keep templates in DB for the reason you listed in your question and also because you might have a huge number of templates which needn't be loaded at once.

An easy way to tie templates with app versions is to tag them. The app should have a static field indicating which version of the template is up-to-date. That field could be a number, string, date, whatever (number is the best, it is easier to search with in DB). The Template table in the DB should also have a column for the tag of the same type. So when requesting for a template the app could add another condition specifying the tag.

There could also be a default template (the newest one, for example), if the new version of the app doesn't need an updated template. Or the new version could have the same tag as the previous one.

Answer 3

We've actually brought important configuration tables under source control. We're using Red Gate Data Compare which works great. But if it's too expensive, it's easy to write a custom tool that exports configuration tables to a text files. Those text files can then be versioned with source control.

An hourly job checks if there are changes in the database that are not in source control, so we are aware if anything changes outside the regular process.

Answer 4

Create one table ::: Templet Table ( Templet ,ModifiedDate , Version ,UserName , Published (Y/N) ) .

Published : Read the templet into app only when published=Y ,Version: To maintain the version of the document .

And create on admin UI to manager the templets .Maintain your admin code in such a way that only one published templet should be there for one templet .

Answer 5

Many systems can be broken just as easily with bad configuration settings as with bad source/logic: Config is source (or at least just as important), so configuration settings should be stored under version control just like source/logic.

Common automation mechanisms can be used to synchronise production systems with configuration and source changes.

For example:

Keep code & config that is currently in production in a particular area of the repository. (Call it "prod", and place restrictions on who can commit to that location).

To change configuration, make the changes and commit the modified files to prod. Your development process will probably dictate some level of testing before the changes are actually applied to prod.

Once this happens, let your continuous integration system pull the files back out of prod, and (after some automated checks have been performed) push the changed configuration out to the actual production system. A database may well be the mechanism that is used to change configuration without restarts.

The important thing is that automation is used to ensure that the "prod" branch in the repository holds the code and the configuration that is really in production at any one time. (That way, if anything goes wrong, you will not be led astray by out-of-date or inconsistent config and/or source files).

For your particular example, I would be tempted to carefully define the extent to which customers can customize or otherwise modify the functionality of your system. People are fiendishly clever at finding novel ways to stretch a technology beyond the intentions of its creator.