is it better to store platform configuration in database or a file?

Question

currently we are developing an app in which we use database table to store platform settings, like maximum file size, maximum users number, support email, etc.

it means that each time we add a platform setting we have to add a column to this table.

in my previous projects i am used to store such information in file.

what is better/faster approach?

ps, i am almost sure someone already had such question, but i can't seem to find it

Answer 1

Like others have said, it depends. The questions that need to be asked are: How often does the setting need to be changed? If it needs to be changed does it imply there are larger changes being made, or is the functionality to accept that different value already in place? And most importantly...What is your deployment process? If you have an automated deployment process and the predicted frequency of value changing is weekly or longer I would typically use a settings file.

Why? versioning and environment promotion. The exact thing that makes it convenient to toggle this here and that there at a whim can make it almost impossible to say "My dev environment is stable and working as expected lets promote the changes to the next env." Why, because unknown to you the enableSuperDuperFeatureEveryoneExpects was toggled to false by unknown acceptance tester last Friday. Now there are ways around this and in small applications with 5 configs and 1 developer its a non-issue, but if you are working on a enterprise application with 8 teams with 100s of config tables over 40 schemas you have a nightmare that never ends. This can be solved more or less with a config schema...but I personally would try and keep as few configs as possible in a db unless your application is small...and to define "small" I would say as soon as there are multiple teams on an application your application is no longer small.

Another concern with db config is protection against invalid entries being made on the fly. If your application has a configuration, lets say defaultLandingPageAfterAuth and the only accepted values are home and dashboard and someone accidently puts homw in prod on a Friday afternoon...what happens? Now you can use db enums...which I recommend...but do you?

Answer 2

I think like everyone said, depends on your application environment and requirements. But if I had to pick a general rule, I'd say BOTH. First, you create a database table to store all your configurations. This is good for two reasons: 1) Versioning - allows you to easily keep track of previous configurations. 2) Management - You can create an interface that your users can access to change settings.

Secondly, you create a file that after you saved and published the site to production, it will also save all those settings into a file that will be easily accessed. In fact, if you are programming in PHP for the web, that file should be a php file with array data (key-value pairs) that need no further manipulation. By this I mean, no need to convert your yaml, or json into array if that is the final output you need to have.

Answer 3

To be fair, the answer is not so cut and clear.

The answers above do not seem to take into account applications that need to be deployed in different environnements ie: dev , qa, staging, prod.

They also do not take into account the importance of versioning configuration, ie knowing who changed what, where and when.

All modern frameworks provide a way of fetching proper configuration for specific environments, usually via environment variable.

Each environnment has its own config, consider the way symfony lays out its configuration files:

your-project/
├─ app/
│  ├─ ...
│  └─ config/
│     ├─ config.yml
│     ├─ config_dev.yml
│     ├─ config_prod.yml
│     ├─ config_test.yml
│     ├─ parameters.yml
│     ├─ parameters.yml.dist
│     ├─ routing.yml
│     ├─ routing_dev.yml
│     └─ security.yml
├─ ...

For these reasons, I most certainly prefer configuration in file.

My 2 cents.

Answer 4

It really depends on your application.

Storing the settings in the database has several advantages:

1. Security - users can easily alter settings in the file or overwrite the contents.
2. For Distribution - the same settings can be updated and loaded onto any machines on the network.

Disadvantages:

1. Relies on database connection
2. Overhead when reading from database

Storing in file advantages:

1. Fast and easy to read and modify.

Disadvantages:

1. Security issue as mentioned above.
2. May need encryption on sensitive data.
3. Versioning is difficult, since you have to create separate files for different versions.

it means that each time we add a platform setting we have to add a column to this table - depending on what database you are using, but you can store the whole settings as XML (SQL server allows this) in the table, so you do not need to alter the table schema everytime adding a settings; all you need to do is to modify the XML, adding elements to it or removing from it.

but in the end, you have to decide yourself, there's no better or worse for everyone.

Answer 5

I can tell you as I manage a particularly large application at numerous sites that keeping configs in local files is a complete pain. Often times the configs are read and cached and not able to be changed during run time others have scale-out systems where configs need to be repeatedly changed and bounced.

My life would be 10% easier during system landscape implementation if the designers would just keep system properties at the DB.

Answer 6

We store config settings in a key/value type table, something like:

CREATE TABLE Configuration.GlobalSettings
(
    SectionName VARCHAR(50),
    SettingName VARCHAR(50),
    SettingValue VARCHAR(1000),
    SettingType TINYINT
);

The SectionName & SettingName are the primary key, we just split them up to make it easier to query what is in a section, and to allow the loading of individual sections into handlers rather than loading the whole lot at once. The SettingValue is a string, and then the SettingType is a discriminator that tells us how the setting value should be interpreted (e.g. 1 = string, 2 = bool, 3 = decimal, etc.).

This means you don't have to change the table structure for new settings, just add a new one in the deployment script or wherever it is you set these things up.

We find it a better way do do config than a file because it means you can easily programmatically change config values through an admin interface when needed, which can enforce logic around what can go into each setting. You can't do that so easily with a file (though, of course, it is possible).

Answer 7

Why a new column every time? Why not just 2 columns: NAME and VALUE.

What we do is set defaults in a file, then override those defaults in the database when needed, per deployment.

Also, in terms of speed, we cache the configuration (with the ability to trigger a reload). Makes no sense to re-read the configuration every time you need a property from it. So in terms of speed, it doesn't really matter. You do it once.