Should you store data in a file or a database?

This is a question that I regularly see coming up among junior developers, and one that I'll try to answer as clearly as possible today.

While the question may seem basic, it actually holds all the subtleties of how different database management systems work internally, and why they exist.

To begin with, let's see what we can do with a simple file stored on an operating system.

Storing in a file

When we get into the habit of setting up new projects quickly, we sometimes forget that storing data can simply come down to writing a few lines in a plain file.

Because after all, that's what files have been used for since their creation, to store data!

Whether manually or programmatically, using them remains straightforward: you open the file, add the data, and close it. Simple, effective, and generally very low in resource consumption.

And going further, several applications (let's say several processes) can even read the data simultaneously, and this data can even be formatted in XML, JSON, CSV,... in order to make it easier to represent this information!

If you want to learn more about the different file formats for storing data, I recommend reading my article on the subject.

But then, in the end, why use a database?

Using a database

How much information, and how often?

The two main reasons why you should turn to a database rather than a simple file are: the amount of data and the frequency of modification.

As seen previously, a file can be consulted concurrently, but not modified. As soon as a file is opened by a process, it is locked (a lock is placed on it) until that process has finished writing.

Databases (most of them) have concurrent writing systems and even transactions in order to maintain data consistency.

If the term transaction isn't familiar to you, I explain it in detail in this article.

If a file becomes too heavy, or is modified too often, your application's performance may suffer.

Normalization, logical links, and searching

While we mentioned that files can follow data formats, that doesn't necessarily make the data normalized and structured.

It's only by using a more complex storage method like a database that we're able to create logical links between pieces of information and, above all, perform complex searches on it (queries), which gives your data a much more interesting value!

The information above mainly applies to so-called "relational" databases, but it can also be applied in certain non-relational models.

If you want to learn how to choose between an "SQL" or "NoSQL" database, I have an article that should help you!

A concrete example

A few years ago, a very good example demonstrating poor use of a simple file for storing data appeared in the video game world.

The game "GTA Online" suffered from abnormally long loading times when the game was first launched. After some investigation, a "hacker" managed to reduce loading time by 70%, simply due to a JSON configuration file that weighed... 10MB and contained more than 60,000 objects.

Reading and "parsing" this file had become far too heavy, and no longer optimized enough, for the original application.

Next time, a simple SQLite database will do the job!

In summary

With a file:

• You store data
• You can read simultaneously
• You can format the data
• It's easy to use and consumes (normally) few resources

With a database:

• You store a larger quantity of data
• You can read AND write concurrently
• You can perform much more complex and optimized searches (queries)
• You can normalize and link data together