Software Engineering
database configuration sqlite settings preferences
Updated Wed, 27 Jul 2022 01:17:10 GMT

Storing settings in a table in my SQLite database or separately?


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:




Solution

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.





Comments (2)

  • +0 – Great points, thanks! I did not think about the aspect that config files are generally read on startup — Sep 01, 2017 at 11:26  
  • +0 – 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. — Apr 26, 2018 at 10:27