Choosing The Best Database For Your Project

There are SO many database options available it’s kind of overwhelming to know which option will be the best for your next project.

This isn’t a complete list, but it summarises the big players:

Relational databases

• SQLite
• Postgres
• MySQL

Document databases

• MongoDB
• Firestore

Graph databases

• Fauna
• Hasura
• Neo4j

K/V stores

• Redis
• Worker KV
• Memcached

At their core, all these databases do pretty much the same thing. They store data created by users for access later. However, they all work very differently and aren't suited to solving the same problems.

Without spending time investigating each solution and having an intimate understanding of the philosophy behind the technology, it’s easy to pick the wrong database for your project. This can come back to haunt you down the road.

Rather than suggesting you go off and spend a month playing with every database available on the market, we’re going to summarise the big differences and how you can pick the right database for whatever you’re building.

Quick definition of a database

For the sake of the next section of this article, we’ll lay down a quick definition of a database (otherwise you can argue that Google Sheets and Airtable are databases).

A database is something external that stores data, persists that data and is accessible via API and standard client.

The fact that you’re reading an article about databases means you don’t need a “Harvard Dictionary” definition of a database, so let’s move on.

The next decision: Hosting

Knowing what kind of database you want to run is arguably easier than deciding on where and how it will be hosted. Database hosting options have different skill requirements that start with “I don’t know how databases work, but I know I need one” to “I practice DevOps on the weekend”.

There’s a specific set of skills required to spin up a database on a server like DigitalOcean, AWS, EC2, or Heroku. The idea here is that you spin up a virtual container, essentially like a Docker image, where the box is running an instance of your database software. You need to know which provider you want to use, how to deploy the database, how to manage the database and what kind of limits you’ll run into.

This is a hill that even some developers decide isn’t worth dying on. For the aspiring entrepreneurs or non-technical founders looking to take charge of their technical direction, this is usually a poor place to start.

The next option is managed services. There are provided or autoscaled hosts like Planetscale and Railway. These serverless database services are super quick to get started with, but they follow standard database conventions. The gif below shows how long it took me to spin up a Postgres database on Railway (15 seconds, although it looks quicker because I trimmed down the gif).

Finally, at the end of the technical food chain are Superbase and Firestore (Firebase). These options move further away from the “standard” SQL conventions. Even though Superbase is built on top of SQL, it does things in its own way. This means less configuration, but as a tradeoff, once your app has committed to Superbase or Firestore, there’s no easy way to move onto a more scaleable platform.

Comparing the types of databases

For the sake of suggesting which type of database to use, we’re going to ignore graph databases and talk about their particular use case later in the article. That leaves us with three types of Db’s:

• Relational
• Document
• Key value store

Key-value stores are the more specific and least comprehensive data storage solution. They’re used in very specific situations where relationships between data aren’t necessary to capture, and returning data as quickly as possible is the goal. For example, a cache like Redis.

Document databases can do everything a KV store can do and more. Are they as quick as a key-value store for performing the same role? No, but they can achieve the same functionality. However, they can do things KV stores can’t, like data normalisation and lookup by subtables. This is perfect for cases where your application or website simply stores logged data that doesn’t need to have relationships. Document DBs can even be a good fit for simple content management systems.

Relational databases can do everything a Document DB can do, but also define relationships between data. It might seem a bit over the top to configure a Postgres database simply storing objects with no relations, but being able to add the relations in the future offers flexibility in the future as your application grows.

How to pick the right DB?

The best approach for figuring out what type of database your project needs is working from the top down. Start by assuming you’re going to need a SQL / relational database to handle complex data relationships, and then see if it’s feasible to trim down the scope.

The only time you would commit to using a document DB like Mongo, Firestore or Couchbase is if you’re absolutely certain that your application won’t evolve to need relationships.

Applications pretty much always have data problems with relations. Here’s an example of what happens when an application architecture has committed to a Document DB and needs to accommodate these relationships.

Scenario: An application has users who can write comments.

The application already has a user table that records the user’s ID and name.

To record comments, since it’s a document, we can add a suitable.

But comments can also have reactions.

Reactions have a User ID. To get this data, we’ll need to query the User table and get all the same data again just to access that ID. It’s a really sloppy and unmanageable process for handling data relationships.

On the other hand, in a relational model, the same problem looks like this.

In this model, a User has Comments which can be selected via foreign key, and the Comments have a user ID to identify which user left the comment. Comments also have Reactions which can be called by the Reaction ID.

The point of this demonstration is that having the ability to define data relationships is going to save a ton of headaches down the line. Defining a set of relationships is a more agile approach to building a data model than adopting a Document DB where you really need to define every possible data relationship upfront. Otherwise, you’ll be in for some fun data migrations when things change.

Should I use Firestore?

Firestore is Google Firebase’s data solution. It’s a subset of the broader tools that make up Firebase. It’s known in the development community as an innovative data solution that asked questions that, at the time of its invention, didn’t have answers like ‘what if the client updated automatically when the database’s content changed?’ and ‘do we need a server?’

However, since 2017 other solutions answering the same questions have popped up that do a much better job, particularly Superbase and Cloudflare DurableObject. Firebase was a Google acquisition rather than an invention, and the innovation velocity dried up when they were purchased.

The biggest issue with Firestore is that when a client requests data, there’s no server that speaks to the database, finds the right field and returns the data. This becomes a problem when user-generated content enters the equation. If your application shows the most recent 20 comments on your homepage, but there are 10,000 comments in Firestore, then the full dataset will be called just to show the homepage.

Firebase, like many other Google products (looking at you AWS), has its own opinions and standards that don’t align with the rest of the market. Developers experienced with SQL will still need to learn how Firebase wants them to handle authentication.

There are also hidden risks in using Firebase that aren’t well publicized:

• It can get crazy expensive as your app scales.
• It’s a proprietary platform and migrating off Firebase is painful. Once you’re committed, that’s it.
• Non-standard security practices are easy to misunderstand and leave your application vulnerable
• Not modular, so you can’t swap parts out if they don’t suit your use case

So coming back to the question, “should I use Firestore?”, the answer is no unless you’re already using it. If you really don’t want to handle the backend part for your small iPhone app or your company’s internal PWA, then Superbase would be a better alternative. It’s built on top of open-source technology, namely Postgres, so if you need to migrate in the future, it’s not such a headache. It’s also miles cheaper when your app starts to scale.

Derisk your database

There’s a time and a place to get crazy with your tech stack. For example, our team are huge fans of building applications with Next.js, tRPC, and Tailwind CSS. These are all evolving technologies that do some cool stuff older technology can’t do for the user.

Database, on the other hand, are not the place to go wild. They need to be stable and operational. They’re essentially invisible to the user, so there’s no great benefit to stepping outside of the norm. A good database has the responsibility to be scaleable, cost-effective and allow offboarding in situations where you need to use a different client or host.

This is why applications using Firebase or FaunaDB can end up in sticky situations if there’s a strong reason to move to a new data solution. Since the database server handling your application’s data is proprietary and non-standard (compared with SQL), these companies provide no easy offboarding method. There’s no tool that lets you dump your data into a SQL format to pick up and use with another solution.

Wrapping up

Picking the right database solution isn’t as daunting a task as we made it out to be. Most applications need a relational database and will do well with Postgres or MySQL. The tricky part is hosting, or it was before solutions like Firebase and Railway entered the scene.

Firebase is almost always a bad idea because it’s built on top of proprietary technologies, and you can’t escape, plus it gets super expensive. Superbase is a better alternative if you have to head in that direction.

We recommend always having a server between the client and your data. It will future-proof your application and avoid the tricky situations serverless databases create.

Let us know your thoughts below. We’ll get back to you in a day or two.