Where to sync Database and Search-Database in MVC pattern

Question

I have a website-sideproject (Application Server: Django, Database: sqlite) with "search" functionality and I've recently stumbled over this problem.

When you have a full-text-search-functionality, typically you have some kind of data-duplication going on. Typically this is by storing your original data in some kind of NoSQL database (or similar, sqlite's fts5 functionality in my case) in a specific format for faster/better searching.

This creates the fundamental problem that you need to keep the data in your search database up to date with the data in your "real" database. Since, if you're doing web development, you're using the MVC pattern, there's 3 places where you could reasonably do this:

1. Do it in your original-database directly. This would be via triggers, one for each update/create/delete operation on each table in your original-database you want to search. Looks like the best option for ensuring data-integrity.

2. Do it in your model. The "How" for that is dependent on the framework, in my case with Django I'd likely have to write some custom "Manager" classes and tell my models to use those. Looks like the best option for maintainability.

3. Do it in your controller aka your application server. This would by through some kind of mixin, inheritance or similar that grants your controllers for creating/updating/deleting entries on your original-database the side-effect of also creating/updating/deleting an entry on your search-database. Looks also like a decent option but prone to causing bugs.

I'm not sure which is the best fit for what I intend to do. Here's the pros and cons that I've got so far:

• Option 1) expresses my intent the best by firmly linking my "original" database and my "search" database, making sure the two are always in sync regardless of who connects through whatever means they want and changes anything.

• Option 1) Might be the fastest option at runtime since you're executing things directly on the database. Given that you typically aren't receiving all that many update/delete/create requests though, how much that matters is questionable.

• Option 1) is very hard to troubleshoot and do complex things in as SQL really isn't meant for combining data in any complicated sort of thing.

• Option 2) expresses my intent well, but only as long as no other applications access the database (which is the case for me, but in general might be a point of consideration)

• Options 2) + 3) are the easiest to code, as whatever backend language you have will be better suited if you have some more complicated logic that needs to be applied to get your data in the desired format for your search-database.

• Option 3) runs the risk of bugs down the line, as you need to keep in mind to keep including the functionality that also updates the search-database down the line (be it through inheritance, mixin, decorator or whatever else).

Is the correct choice to put the logic in the model (Option 2.) ? Are there points to consider that I am missing?

Answer 1

In your situation I would primarily consider how to keep this search service as decoupled as possible from the existing implementation. Given you're using django will strongly influence your choices. Asynchronous event or signal-based solutions can be very robust for your particular problem.

Triggering on the database itself could work (though I don't know that mechanism for sqlite specifically). However these implementations of notifications and hooks tend to be flavor specific. If you find yourself wanting to move to something other than sqlite you'll find yourself at best re-implementing or worse, possibly having a substandard or no option.

The model itself is not where you want to do the work for a search service, but in django where it is responsible for persistent data manipulation, makes it a good candidate to start a trigger. Producing some kind of event on change that something else can respond to asynchronously keeps things decoupled and you can move the heavy lifting how how to respond to that signal elsewhere.

This has been done for you already as django offers an existing post_save signal from models https://docs.djangoproject.com/en/3.2/ref/signals/ you can listen for. This already separates your data access and gives you a nice area to put your business logic (such as filtering types of events you care about responding to). This should also minimize the amount of new code you would need to use rather than inheritance and mixins as you proposed. If you're eager to learn, studying this type of architecture is useful.

From there I would consider asynchronously signaling the search service depending your implementation. Something already in python, message queues, and other IPC are all on the table depending on how your search service is implemented. The contents of that signal can be a simple "reindex" or even the incremental change depending on your requirements and needs.

Answer 2

Since, if you're doing web development, you're using the MVC pattern, there's 3 places where you could reasonably do this

MVC is not the be all end all solution to everything under the sun. MVC focuses on serving web requests. The data replication between two data stores is not a web request, and MVC is not designed to handle that.

It sounds like you've not really separated your logic into different layers, instead having pushed everything into MVC. Your model really shouldn't be responsible for the kind of things you're looking at right now.

It would be better if you separated your DAL and BLL logic into layers of their own, because this allows you to start configuring more intricate behaviors on those layers without leading to further complication on other layers.

For example, your DAL provides access to both the regular and search data stores, and your BLL services are able to orchestrate a replication. The only thing MVC does for you here is respond to the incoming web request by talking to the BLL service, and reporting the result back via the HTTP response.