When developing full-stack applications, one of the key responsibilities of the backend is to manage and interact with databases. Databases allow you to store, retrieve, and manipulate data in a structured way. In this section, we’ll explore the two main types of databases—SQL and NoSQL—and demonstrate how to connect a backend built with Node.js and Express to a database.



SQL vs NoSQL


Before choosing a database, it's essential to understand the difference between SQL and NoSQL databases. Each type is designed for specific types of applications, and your choice will depend on the structure of your data and your project’s needs.

SQL Databases

SQL (Structured Query Language) databases are relational databases. This means they store data in tables with rows and columns, similar to a spreadsheet, and relationships between the data are clearly defined. Each table has a fixed structure, making SQL databases a good choice for structured data where you need to maintain complex relationships between entities.

Examples of popular SQL databases:
- MySQL
- PostgreSQL
- SQLite
- Microsoft SQL Server

Key features of SQL databases:
- Data is stored in tables.
- Schema-based: Requires defining a schema for your data (e.g., columns, data types).
- ACID compliance (Atomicity, Consistency, Isolation, Durability) ensures reliability in transactions.
- Ideal for applications where the data structure is clear and consistent, such as financial systems, e-commerce platforms, and content management systems (CMS).

Example SQL query:

sql
SELECT * FROM users WHERE age > 30;


NoSQL Databases

NoSQL databases are non-relational databases. They are designed to store unstructured or semi-structured data, and they don't require a fixed schema. NoSQL databases are highly scalable and flexible, making them ideal for handling large datasets, real-time data, and applications where the structure of the data may change over time.

Examples of popular NoSQL databases:
- MongoDB (Document-based)
- Cassandra (Column-based)
- Redis (Key-Value store)
- CouchDB (Document-based)

Key features of NoSQL databases:
- Data is stored in collections or documents (e.g., JSON-like objects in MongoDB).
- Schema-less: No strict schema, so each record can have a different structure.
- Highly scalable, designed for horizontal scaling (adding more servers as needed).
- Ideal for applications like social networks, real-time analytics, and IoT (Internet of Things).

Example NoSQL query (MongoDB):

javascript
db.users.find({ age: { $gt: 30 } });







Connecting to a Database


Once you’ve chosen the appropriate database for your project, the next step is connecting your backend to it. In this section, we’ll explore how to connect a Node.js and Express application to both SQL and NoSQL databases.

1. Connecting to a SQL Database (MySQL)

To connect to an SQL database like MySQL in Node.js, you can use the mysql2 or pg package for PostgreSQL. In this example, we’ll use MySQL.

Step 1: Install MySQL package

First, install the mysql2 package via npm:

bash
npm install mysql2


Step 2: Create a MySQL Connection

Here’s how to connect your Node.js app to a MySQL database:

javascript
const mysql = require('mysql2');

// Create a connection to the database
const connection = mysql.createConnection({
    host: 'localhost',
    user: 'root',
    password: 'password',
    database: 'mydatabase'
});

// Connect to the database
connection.connect((err) => {
    if (err) {
        return console.error('error connecting: ' + err.stack);
    }
    console.log('connected as id ' + connection.threadId);
});


Step 3: Query the Database

You can now execute SQL queries to interact with the database. For example, retrieving users from the database:

javascript
connection.query('SELECT * FROM users', (err, results) => {
    if (err) {
        console.error(err);
    }
    console.log(results);
});


2. Connecting to a NoSQL Database (MongoDB)

To connect to a NoSQL database like MongoDB in Node.js, you’ll use the mongoose library, which simplifies interactions with MongoDB by providing a schema-based solution.

Step 1: Install Mongoose package

Install the mongoose package using npm:

bash
npm install mongoose


Step 2: Create a MongoDB Connection

Here’s how to connect your Node.js app to a MongoDB database:

javascript
const mongoose = require('mongoose');

// Connect to the MongoDB database
mongoose.connect('mongodb://localhost:27017/mydatabase', {
    useNewUrlParser: true,
    useUnifiedTopology: true
}).then(() => {
    console.log('Connected to MongoDB');
}).catch((err) => {
    console.error('Error connecting to MongoDB:', err);
});


Step 3: Define a Mongoose Schema and Model

In MongoDB, data is stored as documents (similar to JSON objects). With Mongoose, you can define a schema to structure your data:

javascript
const userSchema = new mongoose.Schema({
    name: String,
    age: Number,
    email: String
});

// Create a model based on the schema
const User = mongoose.model('User', userSchema);


Step 4: Interact with the Database

You can now interact with the MongoDB database, such as creating and retrieving data:

javascript
// Create a new user document
const newUser = new User({
    name: 'Alice',
    age: 25,
    email: 'alice@example.com'
});

newUser.save().then(() => {
    console.log('User saved');
}).catch((err) => {
    console.error('Error saving user:', err);
});

// Find users in the database
User.find({ age: { $gt: 20 } }).then((users) => {
    console.log(users);
}).catch((err) => {
    console.error('Error retrieving users:', err);
});




Conclusion


By now, you should have a basic understanding of the two major types of databases—SQL and NoSQL—and know how to connect your backend to a database using Node.js. SQL databases are ideal for structured data and complex relationships, while NoSQL databases provide flexibility and scalability for large, unstructured data sets.

In the next section, we’ll cover authentication and security, ensuring that your web application protects sensitive data and offers a secure user experience.