When keeping all state in an SQL DB, how to handle one-off variables?

Question

I noticed this pattern in my programs: I'm building a system, like the one I'm building for my local hackerspace right now. It's using database as its "brain" - a scraper as a separate service fetches building rental advertisements and a chat bot announces them on a task-related group chat. Both synchronize using the database.

The problem is, that the API we use to communicate with the chat requires me to keep track of one or more variables that need to persist across reboots and service restarts for the bot to operate properly. They don't really have much in common with each other, but it's tempting to just create a "bot_inner_state" (key TEXT PRIMARY KEY, value TEXT) table and be done with this.

At the same time, it kind of feels like bad design - especially when there's just one variable to keep. Are there any other sensible options? I assumed that since I have one mode of storage for non-configuration data, it would make sense to use it for everything, but basically every option feels like a compromise of sorts.

Answer 1

There is nothing inherently wrong with persisting chatbot related data in a relational database. It helps to think of a database as not a "brain" of the app and more of a means to persist important data that your application needs. If your chatbot app needs this data persisted then of course you could use a database for this.

I think though that your issue you are seeing is probably that you are coupling your applications together far too much. This is probably what your intutition is picking up on.

You should try to decouple your application components so that they don't share things like database schema objects. Ideally your scraper component and chat bot should not be using the same database objects. So then that would leave you with the problem that your chatbot would require the data that the scraper collected, so how should the chatbot fetch this data if not through the scrapers tables? This question highlights that your design is probably incomplete. There is an additional component that you would need to help enable the proper relational persistence of scraper data and to give a well defined API/interface that the chatbot can access.

I would define my components and responsibilities below to decouple everything:

• Scraper - Collects data and stages it to database tables or files for processing.
• Data Processor - Fetches data from stage tables and performs logic and transformations if necessary. Stores the data in relational form for use.
• Data API - Provides CRUD operations against data in relational tables through an API or interface.
• Chatbot - Utilizes the Data API interface to request data and perform chat functions. Owns its own database table(s) for persistence of chat bot specific data. This is a "thin" layer that contains no Data specific business logic and is focused entirely on being a dumb chatbot.

This provides a much cleaner design that is decoupled.