As a developer learning to build full-stack applications, one of our most important early decisions is selecting the right database management system (DBMS). This under-the-hood software handles the complex task of storing, organizing and managing access to data for our app.
The two most common approaches nowadays are relational databases like SQL and non-relational systems like MongoDB. In this blog, I’ll unpack the nitty-gritty details - pros and cons - of each option to help us choose the right tools for our next web app.
Relational Databases
Relational databases have been around for a while, powering everything from mainframe accounting systems in the Fed down to the website running this blog. The name “relational” refers to how they structure data into tidy, interlinked tables. Relational databases organize data into tables that are connected through common id's or other values. For example, you may have a users table and a posts table, connected by associating each post to the ID of the user who wrote it.
Notice how the posts table links to specific user records via the user_id field. In relational databases, you define these table structures (called schemas) upfront to optimize performance and consistency. You interact with the data using Structured Query Language (SQL), with preset commands like INSERT, SELECT, UPDATE, JOIN etc. Popular relational databases include MySQL, PostgreSQL, and SQLite. By defining these relations in advance, we can query and combine data efficiently while keeping consistency:
SELECT
users.first_name,
users.last_name,
posts.title,
posts.content
FROM users
JOIN posts
ON users.id = posts.user_id
This structured query language (SQL) allows us to request and join related data across tables in one step. SQL also provides commands for inserting, updating and deleting records while protecting data integrity with transactions and constraints.
Under the hood, relational DBMS like MySQL and PostgreSQL store data in fixed rows and columns optimized for disk storage and performance. They also include features like indexes to quickly locate records by a given field. Most full-stack web frameworks have built-in tools like Flask-SQLAlchemy in Python and ActiveRecord in Ruby on Rails to simplify interfacing with relational databases.
On the backend, languages like Python and frameworks like Flask allow you to execute SQL queries to get and modify data in your database:
import sqlite3
conn = sqlite3.connect('mydatabase.db')
cursor = conn.cursor()
cursor.execute("SELECT * FROM users")
results = cursor.fetchall()
Pros:
Structured for consistency
Advanced query functionality
High performance at scale
Atomic transactions
Cons:
More rigid schemas require planning
Joins can get complex for nested data
When To Use: Relational databases shine for apps with clear entity relationships you can model upfront. The predefined structure pays off when scaling to support millions of records and users.
Non-Relational Databases
Non-relational systems (often called NoSQL) take a more flexible approach, storing semi-structured and unstructured data in ways beyond simple tables and rows. Non-relational databases like MongoDB store structured data too, but without defining formal relationships between "tables" upfront. The data formats can vary from item to item and change over time. Instead of SQL, you typically interact with the database using an API in a programming language like JavaScript.
JSON (JavaScript Object Notation) has emerged as a popular document model:
{
name: 'Jane Doe',
email: 'jane@example.com',
posts: [
{ title: 'Post One', content: '...' },
{ title: 'Post Two', content: '...' }
]
}
Here a single record nested under a user document contains all the info we might have spread across multiple tables before. NoSQL databases like MongoDB were designed to handle modern applications with messy, complex data that's harder to confine into neat tables.
By focusing less on rigid schemas and relations, NoSQL databases emphasize simplicity, flexibility, and scalability:
// Fetch Jane's document
db.users.find({name: "Jane Doe"})
// Add new post
db.users.update({name: "Jane Doe"}, {
$push: {
posts: {
title: "Post Three",
content: "..."
}
}
})
As you can see, the data for a user and their posts are stored in one JSON-like document instead of separate tables. This flexible "schema-less" storage can be useful for certain data models and faster prototyping. MongoDB and other non-relational databases are categorized as "NoSQL" solutions.
For web and mobile apps, JSON-centric storage can make for faster prototyping and iterating compared to configuring complex SQL table layouts. Cloud services like MongoDB Atlas provide easy scaling with built-in replication and sharding to divide data across servers.
Most Node.js and other JavaScript frameworks include NoSQL drivers and object-document mappers like MongooseJS to simplify interactions. Since data lives in ordinary JavaScript objects, that mapping is more straightforward compared to the impedance mismatch between SQL tables and application code.
Pros:
Flexible schemas
Horizontal scalability
Integrates well into JS apps
Rapid iteration
Cons:
Less structured
More burden on app code
Complex aggregations
When To Use: Non-relational databases allow quick iterations and modifications as projects grow. They easily scale out to handle large volumes of traffic across distributed clusters.
Summary
So what is a dev to use? It depends, SQL/relational databases provide more structure up front, while NoSQL/non-relational databases offer more flexibility. As a beginner, trying both types can help you better understand which approach works better for the app you want to build. The good news is that popular web frameworks make it fairly painless to connect and interact with both database types!
As with most technology decisions, there are compelling use cases for both approaches. SQL reigns supreme for established datasets and workflows, while NoSQL brings developer velocity and adaptable schemas. In some cases, a hybrid strategy combining both relational and non-relational technologies may even prove ideal.
Luckily most web frameworks make swapping databases fairly simple. As we progress in building real-world apps, do not be afraid to experiment until you find the data storage technologies best suited for each project! It's a big beautiful world out there, go and explore... as always - do your homework and read the docs!! Happy coding :)