3

I'm building an application which uses SQLite, and now want to add settings that are stored between sessions

I'm considering if I'm going to store the settings in a config file or if I'm going to use a table in the database for this

(Please note that I'm using SQLite which stores data locally and doesn't have a user accounts)

The reason I'm thinking of using the database is that I'd like to avoid adding more complexity and since I have already set up my SQLite database I'm thinking that I might as well use it for settings as well

What do I need to consider when choosing between these two options?

I'm leaning towards using a table in the database but am a bit worried because this answer says that storing settings inside the database is a bad separation of concerns.

However I'm not sure what practical drawbacks there would be. In terms of the code being self-documenting and in terms of readability it's clear where the settings are if the db table is just called "settings"

(One drawback I can think of might be if I expect the settings to change much more quickly than the db schema, since that would mean changing the database which is more complicated than changing the settings)

Grateful for help with this!

Similar questions:

  • 2
    I agree with Bill the Lizard below; storing configurations in a database is not a violation of SoC. I've left a comment with the original author of that answer asking him to explain his reasoning further. – Robert Harvey Aug 25 '17 at 15:00
  • 1
  • 1
    Where are you going to store the connection info for the db? host/port/user/pass? – Daenyth Aug 25 '17 at 15:27
  • 1
    In all seriousness, though, I once worked on a system where all the error messages were stored in a table; including the error message for when the app could not connect to the DB. – JimmyJames Aug 25 '17 at 15:39
  • 1
    @Daenyth Thank you for the question, this helped me realize that my case is special since I use SQLite which doesn't have login credentials and stores the data locally. I will update the question to make this clear – sunyata Aug 25 '17 at 15:56
4

The largish legacy application I maintain has gone back and forth on this over the years, but has evolved towards keeping most settings in the DB. Yes, the DB connection string is read from the config file. This is a desktop application that heavily uses a DB and which has quite a few settings. If you have very few this will be less applicable, but these are the first reasons that come to mind:

  • Config files are typically read only on startup, changes mean restarting the application. (Not a hard rule, but intended use.)

  • Settings from the DB can be edited via a GUI that is part of the application and directly updated using the already existing DB routines without needing to access some external file.

  • Changes made by the GUI can easily be used to raise events and the new values can be used immediately. You really don't want to be monitoring changes to an external file - see first bullet.

  • The DB is not accessible to regular users and changes there can be restricted and validated. A config file is exposed and can be changed with any editor, with no validation of values.

  • We have sets of settings - "Profiles" - that are customized on a per-installation basis. This would be somewhat cumbersome to maintain with a config file.

  • Great points, thanks! I did not think about the aspect that config files are generally read on startup – sunyata Sep 1 '17 at 11:26
  • I find DB is not accessible to regular users and changes there can be restricted and validated important: it provides us one consistent access protection. – Wolf Apr 26 '18 at 10:27
9

I don't agree that storing settings in a database table is a bad separation of concerns. Your "concerns" will be separated by not defining a relationship between the settings table and other tables in your database. This is fine.

The main thing to consider is how you expect the administrator of your system to make changes to the settings. It's much easier for most people to just open a configuration file and make changes. If you store your configuration in a database, you'll have to give access to users/admins to make changes. If they're not comfortable making changes directly in the database, you may have to provide a screen in your application for making those changes. I've done this in both web and mobile applications and it works fine. (Since the changes are made through the application itself, it even bypasses the need to restart the application like you would if changes were made externally by editing a file.)

  • 3
    Another benefit of storing the settings in the database is that it makes it easier to share the settings across servers. (Such as load-balanced web server). Now all of your settings are stored in one shared location, and you don't have to worry about keeping configurations in sync on EVERY server. (Apart from the db connection config) – Caleb Aug 25 '17 at 15:44
  • 3
    One other benefit to in-database settings is that if any of them influence the queries you do against other tables, you can do joins and incorporate them into WHERE clauses without having to read the setting into your application and use it in building a query. – Blrfl Aug 25 '17 at 16:47
0

It all depends on how big your settings are. If it was a couple of values, then it would be simpler and less overhead to store it in a file. Especially that this file could be an XML that is easy to handle.

If you need to store your settings somewhere safe (e.g. saving credentials), then a database would be a slightly better option. Of course you should encrypt those credentials when you either save them on a file or in a table.

From an architectural point of view, you should save at least one piece of information in a file, i.e. your database connection string, so that you would not hard code it (bad practice).

  • 1
    You save your database connection string in a file instead of the database because you would have to access the database to get the connection string, and you can't do that without the connection string. Whether it's better to put that string in a file is debatable; hard-coding it into the program makes it (slightly) more secure. – Robert Harvey Aug 25 '17 at 15:02
  • I have one case in an application where the connection string is hard-coded in an assembly dll! Developers who wrote this application are long gone, and the dll has no code checked in our source control system. There is no way I could spin-off test environment for this system, or even change its IP address!!!! – A.Rashad Aug 25 '17 at 15:11
  • Sounds like you have bigger problems than a connection string. But yeah, obviously if you don't have access to the source code... Is this a .NET DLL? There are ways to decompile it. :) – Robert Harvey Aug 25 '17 at 15:12
  • Yes, it's a 3.5 .net dll, is it possible to decompile it and recompile a new one with different settings? – A.Rashad Aug 25 '17 at 15:47
  • Decompilation means that you would have access to the original source code, including the settings. See stackoverflow.com/q/179741 – Robert Harvey Aug 25 '17 at 15:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.