Database Integration in Express.js

Back to Express.js
Arjit Sharma
02 Mins

Most backend applications need to store and retrieve data from a database. Express.js can integrate with different database systems such as MongoDB, MySQL, PostgreSQL, and more.

In this article, we explore two popular approaches:

  • MongoDB with Mongoose
  • MySQL with Prisma ORM

MongoDB with Mongoose

MongoDB is a NoSQL document database that stores data in flexible JSON-like documents.

MongoDB

Mongoose is an ODM (Object Data Modeling) library for MongoDB that provides:

  • Schema validation
  • Data modeling
  • Middleware/hooks
  • Query helpers

Mongoose

Installing Dependencies

npm install mongoose dotenv

Creating a Mongoose Model

// models/User.js
import mongoose from 'mongoose';

const userSchema = new mongoose.Schema({
  name: String,
  email: { type: String, required: true, unique: true },
  age: Number,
});

export default mongoose.model('User', userSchema);

Connecting to MongoDB

// db.js
import mongoose from 'mongoose';

const connectDB = async () => {
  try {
    await mongoose.connect(process.env.MONGODB_URI || 'mongodb://localhost:27017/myapp', {
      useNewUrlParser: true,
      useUnifiedTopology: true,
    });
    console.log('MongoDB connected');
  } catch (err) {
    console.error('MongoDB connection error:', err.message);
    process.exit(1); // Exit process if DB fails
  }
};

export default connectDB;

The database connection string is usually stored inside environment variables.

Example: .env file

MONGODB_URI=mongodb://localhost:27017/myapp

Creating Express Routes

// routes/users.routes.js 
import express from 'express';
import User from '../models/User.js';

const router = express.Router();

// Get all users
router.get('/', async (req, res) => {
  try {
    const users = await User.find().select('name email age');
    res.json(users);
  } catch (err) {
    res.status(500).json({ error: 'Failed to fetch users' });
  }
});

// Create user
router.post('/', async (req, res) => {
  try {
    const user = await User.create(req.body);
    res.status(201).json(user);
  } catch (err) {
    res.status(400).json({ error: err.message });
  }
});

export default router;

Starting the Express Server

// server.js
import express from 'express';
import dotenv from 'dotenv';
import connectDB from './db.js';
import userRoutes from './routes/users.routes.js';

dotenv.config();

const app = express();

// Middleware
app.use(express.json());

// Routes
app.use('/users', userRoutes);

// Connect DB and start server
connectDB();

const PORT = process.env.PORT || 5000;
app.listen(PORT, () => console.log(`🚀 Server running on port ${PORT}`));

MySQL with Prisma

MySQL is a relational database that stores data in structured tables.

MySQL

Prisma is a modern ORM that simplifies working with relational databases using a type-safe API.

Prisma

Prisma supports:

  • MySQL
  • PostgreSQL
  • SQLite

Installing Prisma

npm install prisma @prisma/client

# Initialize Prisma
npx prisma init

Defining the Prisma Schema

// prisma/schema.prisma
datasource db {
  provider = "mysql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

model User {
  id        Int      @id @default(autoincrement())
  name      String?
  email     String   @unique
  age       Int?
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

Creating the Prisma Client

// db.js
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient();

export default prisma;

Creating Routes with Prisma

// routes/users.routes.js
import express from "express";
import prisma from "../db.js";

const router = express.Router();

// Get users
router.get("/", async (req, res) => {
    try {
        const users = await prisma.user.findMany({
            select: {
                id: true,
                name: true,
                email: true,
                age: true,
            },
        });

        res.json(users);

    } catch (err) {
        res.status(500).json({
            error: "Failed to fetch users",
        });
    }
});

// Create user
router.post("/", async (req, res) => {
    try {
        const user = await prisma.user.create({
            data: req.body,
        });

        res.status(201).json(user);

    } catch (err) {
        res.status(400).json({
            error: err.message,
        });
    }
});

export default router;

Starting the Server

// server.js
import express from 'express';
import dotenv from 'dotenv';
import userRoutes from './routes/users.routes.js';

dotenv.config();
const app = express();

// Middleware
app.use(express.json());

// Routes
app.use('/users', userRoutes);

const PORT = process.env.PORT || 5000;
app.listen(PORT, () => console.log(`🚀 Server running on port ${PORT}`));

Running Database Migrations

After updating the Prisma schema:

npx prisma migrate dev --name init

This creates database tables based on your schema.

Next →