Building a Full-Stack Application with MERN and SQL

In the evolving landscape of web development, building full-stack applications that are efficient, scalable, and maintainable is crucial. The MERN stack, comprising MongoDB, Express.js, React.js, and Node.js, is a popular choice among developers for its JavaScript-based ecosystem and powerful capabilities. However, incorporating SQL databases into a MERN stack can leverage the relational data modeling strengths of SQL, offering the best of both worlds. This blog explores how to build a full-stack application using the MERN stack with an SQL database.

Why Combine MERN with SQL?

Traditionally, the MERN stack uses MongoDB, a NoSQL database known for its flexibility and scalability. However, SQL databases like PostgreSQL and MySQL offer robust data integrity, complex querying capabilities, and mature transaction management. By combining these technologies, you can benefit from the ease of front-end development with React, the flexibility of Express and Node.js for your server-side code, and the structured, relational data management provided by SQL databases.

Setting Up Your Development Environment

Before we dive into the code, ensure you have the necessary tools installed:

1. Node.js and npm: Node.js is essential for running your JavaScript code server-side, and npm (Node Package Manager) is needed for managing dependencies.
2. React: Create your React app using `create-react-app`.
3. Express: Install Express.js to handle your server logic.
4. SQL Database: Install and set up your preferred SQL database (PostgreSQL, MySQL, etc.).
5. Sequelize: An ORM (Object-Relational Mapper) for SQL databases, making it easier to work with your database in JavaScript.

Building the Back-End

Step 1: Setting Up Express

First, create a new directory for your project and initialize it with npm:

mkdir mern-sql-app
cd mern-sql-app
npm init -y

Next, install Express and other necessary dependencies:

npm install express sequelize pg pg-hstore

Create a file named server.js and set up a basic Express server:

const express = require('express');
const app = express();
const port = 5000;
app.get('/', (req, res) => {
 res.send('Hello World!');
});
app.listen(port, () => {
 console.log(`Server is running on http://localhost:${port}`);
});

Step 2: Configuring Sequelize

Set up Sequelize to connect to your SQL database. Create a config directory with a config.json file to store your database credentials:

{
 "development": {
 "username": "your-username",
 "password": "your-password",
 "database": "database_name",
 "host": "127.0.0.1",
 "dialect": "postgres"
 }
}

Initialize Sequelize in your project:

npx sequelize-cli init

Configure Sequelize in server.js:

const { Sequelize } = require('sequelize');
const sequelize = new Sequelize('database_name', 'your-username', 'your-password', {
 host: '127.0.0.1',
 dialect: 'postgres'
});
sequelize.authenticate()
 .then(() => console.log('Database connected…'))
 .catch(err => console.log('Error: ' + err));

Step 3: Creating Models and Migrations

Create a User model and migration:

npx sequelize-cli model:generate - name User - attributes firstName:string,lastName:string,email:string

Run the migration to create the User table in your database:

npx sequelize-cli db:migrate

Building the Front-End

Step 4: Setting Up React

Create your React application:

npx create-react-app client

Navigate to the client directory and start the React development server:

cd client
npm start

Step 5: Connecting React to the Express API

Modify your React app to fetch data from your Express server. In App.js, you can use fetch to make HTTP requests to your API:

import React, { useEffect, useState } from 'react';
function App() {
 const [users, setUsers] = useState([]);
useEffect(() => {
 fetch('/api/users')
 .then(response => response.json())
 .then(data => setUsers(data));
 }, []);
return (
 <div className="App">
 <h1>Users</h1>
 <ul>
 {users.map(user => (
 <li key={user.id}>{user.firstName} {user.lastName}</li>
 ))}
 </ul>
 </div>
 );
}
export default App;

Step 6: Creating the API Routes

In server.js, set up routes to handle API requests. For instance, to fetch users:

const User = require('./models').User;
app.get('/api/users', (req, res) => {
 User.findAll()
 .then(users => res.json(users))
 .catch(err => console.log(err));
});

Conclusion

Building a full-stack application with MERN and SQL combines the strengths of both technologies, providing a robust and flexible framework for your projects. By setting up an Express server with Sequelize and a React front-end, you can create powerful applications that leverage the advantages of both NoSQL and SQL databases. With this setup, you can build scalable, maintainable, and high-performance web applications. Happy coding!