Using flat files vs database/API as a transport between a frontend and backend

Question

I've got an application which has generated a rather heated discussion between a couple of the developers.

Basically, it's split into a web layer and a backend layer. The web layer collects information by a simple web form, stashes this data as a JSON document (literally a .json file) into a watch folder used by the back end. The back end polls this folder every few seconds, picks the file up, and carries out its functions.

The files themselves are very simple (i.e. all string data, no nesting), and around 1-2k at their largest, with the system spending most of its time idle (but bursting up to 100 messages at any given time). The backend processing step takes about 10 minutes per message.

The argument comes in when one developer suggests that using the filesystem as a messaging layer is a bad solution, when something such as a relational database (MySQL), noSQL database (Redis), or even a plain REST API call should be used instead.

It should be noted that Redis is used elsewhere in the organization for queued message handling.

The arguments I've heard break down as follows

---

In favor of flat files:

• Flat files are more reliable than any other solution, since the file only gets moved from a "watch" folder, to a "processing" folder after it's picked up, and finally to a "done" folder when finished. There's zero risk of messages disappearing barring very low level bugs which would break other things anyways.

• Flat files require less technical sophistication to understand - just cat it. No queries to write, no risk of accidentally popping a message off the queue and having it be gone forever.

• File management code is simpler than database APIs from a programming standpoint, since it's part of every language's standard library. This reduces the overall complexity of the code base and the amount of third party code that must be brought in.

• The YAGNI principle states that flat files work just fine right now, there's no demonstrated need for changing to a more complicated solution, so leave it.

In Favor of a database:

• It's easier to scale a database than a directory full of files

• Flat files have a risk of someone copying a "done" file back to the "watch" directory. Due to the nature of this application (virtual machine management), this could result in catastrophic data loss.

• Requiring more technical sophistication to T/S the app means that uneducated staff are less likely to screw something up by just poking at things.

• DB connection code, especially for something like Redis, is at least as robust as the standard library file management functions.

• DB connection code is visibly (if not functionally) simpler from a developer standpoint, since its higher level than file manipulation.

---

From what I can see, both developers have a lot of valid points.

So of these two people, the pro-files dev, or the pro-databases dev, which one is more in line with software engineering best practice, and why?

Answer 1

Switching to a solution involving databases or the queuing systems mentioned by Ewan would

• create dependency on a new, complex system in both backend and frontend
• introduce unnecessary complexity and a sh*tload of new points of failure
• increase cost (including cost of ownership)

Moving/renaming files within a single volume is guaranteed to be atomic on all current OSes, whatever their difficulties might be with regard to things like file/record locking. OS-level rights management should be sufficient for locking out the unwashed and to prevent thoughtless/accidental mis-manipulation by authorised operators (admins/devs). Hence databases have nothing to offer at all, as long as the performance of the current solution is up to snuff.

At our company we have used similar file-based interfaces for decades with great success. Lots of other things have come and gone, but these interfaces have remained because of their utter simplicity, reliability and minimal coupling/dependencies.

Answer 2

I don't think either solution is inheritly a bad practice, so answering which is the best practice may be difficult.

I don't believe the YAGNI principal applies here if you're dealing with scale. "Working" is relative, if you have a strong potential for catastrophic data loss, and little ability to scale, I wouldn't really consider that working. I'm not exactly sure the scale you're dealing with, but if you have a massive amount of these entries, it only gets harder with each one to switch to a new system. So if this is the case, I'd say a database is best practice.

MongoDB or redis (I have no experience with redis, read only good things) should do fine as your data should already fit nicely into it (json documents are often trivially changed to BSON documents for MongoDB). It has an added advantage too of keeping a lot of data in memory instead of potential frequent read/writes to the disk all the time. It also makes sure concurrent reads/writes don't lead to corruption or blocking.

If the YAGNI principal does apply here and the files aren't a bottleneck, they scale within the scope, and don't have catastrophic issues, I would say sticking with the files is "best practice". There's no reason to change anything if there aren't issues, maybe write some tests, stress it and see where your limits and bottlenecks are.

I'm not sure if a database is the solution in this context anyways. If you're communicating with things on the same server, some sort of IPC could be done, no?

Answer 3

While the good 'ol save a file and copy it to a done dir is a staple of many communication layers esp. with older main frame systems and the like. The 'anti' guys do have a point; in that it has many problems and edge cases. Which are hard to deal with if you need 100% reliablitiy, and occur more often as you scale up the frequency and volume of files.

If you control both sides of the transaction I would suggest you look at some of the many simple queuing systems available. ZeroMQ, RabbitMQ, MSMQ etc rather than a database. But as you imply, if it aint broke...

Answer 4

The database solution is the right one. It solves a lot of dependency on a particular host or boundary conditions.

Both are similar solutions except that the database is not hosted in a particular host. This gets rid of firewall/access issues with unix system. We have had cases of "accidental" delete on filesystems and no one to blame.

With database , you can also have the same issue but you can put audit on or have insert only logic to get rid of deletes.

Also in file system if you need to put application in the file name e.g OASIS , then you will need to create files OASIS.john_doe.system1.20160202. This become tedious and can be represented more easily in database. You can even have null fields in database and logic based on that

It is also easy to update databases rather a whole file directory in case of any patches or fixes you might want to do on tables to . Of course you can do it on file system but the database update is more intuitonal.

e.g You want a rerun but with a different system than OASIS say DESERT and john_doe to doe_smith and date from 20160101 to 20151231

Easy to generate rows for DESERT/doe_smith/20151231 from original set rather than create those file with shell script.

So from readability , extension point of view database solution is better.