Express Database Connection

Introduction

When building web applications with Express.js, one of the most fundamental tasks is connecting your application to a database to store and retrieve data. A properly established database connection allows your Express application to perform create, read, update, and delete (CRUD) operations on your data.

In this tutorial, we'll explore different methods to connect an Express application to databases like MongoDB, MySQL, and PostgreSQL. We'll cover both direct connections and using Object-Relational Mapping (ORM) libraries to make database interactions more intuitive.

Why Database Connections Matter

Before diving into the code, let's understand why properly managing database connections is crucial:

Performance: Efficient connection management improves application response times
Reliability: Proper error handling prevents application crashes
Security: Safe connection practices protect your data
Scalability: Well-implemented connections support application growth

Prerequisites

Before we begin, make sure you have:

Node.js and npm installed
Basic knowledge of Express.js
A database system installed (MongoDB, MySQL, or PostgreSQL)

Connecting to MongoDB

MongoDB is a popular NoSQL database that works well with JavaScript-based applications.

Setting Up MongoDB Connection

First, install the required packages:

bash
npm install express mongoose dotenv

Next, create a connection file (e.g., database.js):

javascript
const mongoose = require('mongoose');
require('dotenv').config();

// Connection URI
const connectionString = process.env.MONGODB_URI || 'mongodb://localhost:27017/myapp';

// Connection function
const connectDB = async () => {
  try {
    await mongoose.connect(connectionString, {
      useNewUrlParser: true,
      useUnifiedTopology: true
    });
    console.log('MongoDB connected successfully');
  } catch (error) {
    console.error('MongoDB connection error:', error.message);
    // Exit process with failure
    process.exit(1);
  }
};

module.exports = connectDB;

In your main application file (app.js or server.js):

javascript
const express = require('express');
const connectDB = require('./database');

// Initialize Express
const app = express();

// Connect to Database
connectDB();

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

// Routes
app.get('/', (req, res) => {
  res.send('API Running');
});

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

Creating a Schema and Model

Once connected, you can define schemas and models:

javascript
// models/User.js
const mongoose = require('mongoose');

const UserSchema = new mongoose.Schema({
  name: {
    type: String,
    required: true
  },
  email: {
    type: String,
    required: true,
    unique: true
  },
  date: {
    type: Date,
    default: Date.now
  }
});

module.exports = mongoose.model('User', UserSchema);

Using the Model in Routes

javascript
// routes/users.js
const express = require('express');
const router = express.Router();
const User = require('../models/User');

// Create a user
router.post('/', async (req, res) => {
  try {
    const newUser = new User({
      name: req.body.name,
      email: req.body.email
    });

    const user = await newUser.save();
    res.json(user);
  } catch (err) {
    console.error(err.message);
    res.status(500).send('Server Error');
  }
});

// Get all users
router.get('/', async (req, res) => {
  try {
    const users = await User.find();
    res.json(users);
  } catch (err) {
    console.error(err.message);
    res.status(500).send('Server Error');
  }
});

module.exports = router;

Connecting to MySQL

MySQL is a widely-used relational database. Let's see how to connect it with Express.

Setting Up MySQL Connection

Install the required packages:

bash
npm install express mysql2 dotenv

Create a database connection file:

javascript
// database.js
const mysql = require('mysql2/promise');
require('dotenv').config();

// Connection pool
const pool = mysql.createPool({
  host: process.env.DB_HOST || 'localhost',
  user: process.env.DB_USER || 'root',
  password: process.env.DB_PASSWORD || '',
  database: process.env.DB_NAME || 'myapp',
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

// Test connection
const testConnection = async () => {
  try {
    const connection = await pool.getConnection();
    console.log('MySQL connected successfully');
    connection.release();
  } catch (error) {
    console.error('MySQL connection error:', error);
    process.exit(1);
  }
};

module.exports = { pool, testConnection };

In your main application file:

javascript
const express = require('express');
const { testConnection } = require('./database');

// Initialize Express
const app = express();

// Test Database Connection
testConnection();

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

// Routes
app.get('/', (req, res) => {
  res.send('API Running');
});

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

Example Query Using MySQL

javascript
// routes/products.js
const express = require('express');
const router = express.Router();
const { pool } = require('../database');

// Get all products
router.get('/', async (req, res) => {
  try {
    const [rows] = await pool.query('SELECT * FROM products');
    res.json(rows);
  } catch (err) {
    console.error(err.message);
    res.status(500).send('Server Error');
  }
});

// Add a product
router.post('/', async (req, res) => {
  try {
    const { name, price } = req.body;
    const [result] = await pool.query(
      'INSERT INTO products (name, price) VALUES (?, ?)',
      [name, price]
    );
    
    res.json({
      id: result.insertId,
      name,
      price
    });
  } catch (err) {
    console.error(err.message);
    res.status(500).send('Server Error');
  }
});

module.exports = router;

Connecting to PostgreSQL

PostgreSQL is a powerful open-source relational database system. Here's how to connect it with Express:

Setting Up PostgreSQL Connection

Install the required packages:

bash
npm install express pg dotenv

Create a database connection file:

javascript
// database.js
const { Pool } = require('pg');
require('dotenv').config();

const pool = new Pool({
  user: process.env.PG_USER || 'postgres',
  host: process.env.PG_HOST || 'localhost',
  database: process.env.PG_DATABASE || 'myapp',
  password: process.env.PG_PASSWORD || '',
  port: process.env.PG_PORT || 5432,
});

// Test connection
const testConnection = async () => {
  try {
    const client = await pool.connect();
    console.log('PostgreSQL connected successfully');
    client.release();
  } catch (error) {
    console.error('PostgreSQL connection error:', error);
    process.exit(1);
  }
};

module.exports = { pool, testConnection };

In your main application file:

javascript
const express = require('express');
const { testConnection } = require('./database');

// Initialize Express
const app = express();

// Test Database Connection
testConnection();

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

// Routes
app.get('/', (req, res) => {
  res.send('API Running');
});

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

Example Query Using PostgreSQL

javascript
// routes/tasks.js
const express = require('express');
const router = express.Router();
const { pool } = require('../database');

// Get all tasks
router.get('/', async (req, res) => {
  try {
    const result = await pool.query('SELECT * FROM tasks');
    res.json(result.rows);
  } catch (err) {
    console.error(err.message);
    res.status(500).send('Server Error');
  }
});

// Add a task
router.post('/', async (req, res) => {
  try {
    const { title, description } = req.body;
    const result = await pool.query(
      'INSERT INTO tasks (title, description) VALUES ($1, $2) RETURNING *',
      [title, description]
    );
    
    res.json(result.rows[0]);
  } catch (err) {
    console.error(err.message);
    res.status(500).send('Server Error');
  }
});

module.exports = router;

Using ORMs for Database Connection

Object-Relational Mapping (ORM) libraries provide a higher-level abstraction for database interactions. Here are two popular options:

Sequelize (for SQL databases)

Sequelize is a promise-based ORM for Node.js, supporting PostgreSQL, MySQL, SQLite, and more.

bash
npm install express sequelize pg pg-hstore dotenv

Setting up Sequelize:

javascript
// models/index.js
const { Sequelize } = require('sequelize');
require('dotenv').config();

const sequelize = new Sequelize(
  process.env.DB_NAME || 'myapp',
  process.env.DB_USER || 'postgres',
  process.env.DB_PASSWORD || '',
  {
    host: process.env.DB_HOST || 'localhost',
    dialect: 'postgres', // or 'mysql', 'sqlite', 'mssql'
    logging: false
  }
);

const testConnection = async () => {
  try {
    await sequelize.authenticate();
    console.log('Database connection established successfully');
  } catch (error) {
    console.error('Unable to connect to the database:', error);
    process.exit(1);
  }
};

module.exports = { sequelize, testConnection };

Creating a model with Sequelize:

javascript
// models/Product.js
const { DataTypes } = require('sequelize');
const { sequelize } = require('./index');

const Product = sequelize.define('Product', {
  name: {
    type: DataTypes.STRING,
    allowNull: false
  },
  price: {
    type: DataTypes.DECIMAL(10, 2),
    allowNull: false
  },
  description: {
    type: DataTypes.TEXT
  }
});

module.exports = Product;

Using Sequelize in routes:

javascript
// routes/products.js
const express = require('express');
const router = express.Router();
const Product = require('../models/Product');

// Get all products
router.get('/', async (req, res) => {
  try {
    const products = await Product.findAll();
    res.json(products);
  } catch (err) {
    console.error(err.message);
    res.status(500).send('Server Error');
  }
});

// Add a product
router.post('/', async (req, res) => {
  try {
    const { name, price, description } = req.body;
    const product = await Product.create({
      name,
      price,
      description
    });
    
    res.json(product);
  } catch (err) {
    console.error(err.message);
    res.status(500).send('Server Error');
  }
});

module.exports = router;

Best Practices for Database Connections

To ensure your database connections are secure, efficient, and reliable:

Use Environment Variables: Never hardcode database credentials in your application code
javascript
```
// .env file
DB_HOST=localhost
DB_USER=myuser
DB_PASSWORD=mypassword
DB_NAME=myapp
```

Connection Pooling: Use connection pools for better performance

javascript
// For MySQL
const pool = mysql.createPool({
  connectionLimit: 10,
  // other connection parameters
});

Error Handling: Always implement proper error handling for database operations

javascript
try {
  // Database operations
} catch (error) {
  console.error('Database error:', error.message);
  // Handle the error appropriately
}

Close Unused Connections: Release connections when they're no longer needed

javascript
// For direct database connections
const connection = await pool.getConnection();
try {
  // Use the connection
} finally {
  connection.release(); // Always release the connection
}

Validate Input Data: Always sanitize and validate user input before using it in queries to prevent SQL injection

Real-World Application: Building a RESTful API

Let's build a simple RESTful API for a blog application using Express and MongoDB:

javascript
// app.js
const express = require('express');
const connectDB = require('./database');
const postRoutes = require('./routes/posts');

// Initialize Express
const app = express();

// Connect to Database
connectDB();

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

// Routes
app.use('/api/posts', postRoutes);

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

javascript
// models/Post.js
const mongoose = require('mongoose');

const PostSchema = new mongoose.Schema({
  title: {
    type: String,
    required: true
  },
  content: {
    type: String,
    required: true
  },
  author: {
    type: String,
    required: true
  },
  date: {
    type: Date,
    default: Date.now
  }
});

module.exports = mongoose.model('Post', PostSchema);

javascript
// routes/posts.js
const express = require('express');
const router = express.Router();
const Post = require('../models/Post');

// Get all posts
router.get('/', async (req, res) => {
  try {
    const posts = await Post.find().sort({ date: -1 });
    res.json(posts);
  } catch (err) {
    console.error(err.message);
    res.status(500).send('Server Error');
  }
});

// Get single post
router.get('/:id', async (req, res) => {
  try {
    const post = await Post.findById(req.params.id);
    
    if (!post) {
      return res.status(404).json({ msg: 'Post not found' });
    }
    
    res.json(post);
  } catch (err) {
    console.error(err.message);
    if (err.kind === 'ObjectId') {
      return res.status(404).json({ msg: 'Post not found' });
    }
    res.status(500).send('Server Error');
  }
});

// Create a post
router.post('/', async (req, res) => {
  try {
    const newPost = new Post({
      title: req.body.title,
      content: req.body.content,
      author: req.body.author
    });

    const post = await newPost.save();
    res.json(post);
  } catch (err) {
    console.error(err.message);
    res.status(500).send('Server Error');
  }
});

// Update a post
router.put('/:id', async (req, res) => {
  try {
    const post = await Post.findById(req.params.id);
    
    if (!post) {
      return res.status(404).json({ msg: 'Post not found' });
    }
    
    post.title = req.body.title || post.title;
    post.content = req.body.content || post.content;
    post.author = req.body.author || post.author;
    
    await post.save();
    res.json(post);
  } catch (err) {
    console.error(err.message);
    if (err.kind === 'ObjectId') {
      return res.status(404).json({ msg: 'Post not found' });
    }
    res.status(500).send('Server Error');
  }
});

// Delete a post
router.delete('/:id', async (req, res) => {
  try {
    const post = await Post.findById(req.params.id);
    
    if (!post) {
      return res.status(404).json({ msg: 'Post not found' });
    }
    
    await post.remove();
    res.json({ msg: 'Post removed' });
  } catch (err) {
    console.error(err.message);
    if (err.kind === 'ObjectId') {
      return res.status(404).json({ msg: 'Post not found' });
    }
    res.status(500).send('Server Error');
  }
});

module.exports = router;

Summary

In this tutorial, we've covered:

How to connect Express applications to popular databases (MongoDB, MySQL, PostgreSQL)
Using ODM/ORM libraries like Mongoose and Sequelize
Best practices for managing database connections
A real-world example of building a RESTful API with database integration

By understanding these database connection techniques, you can build robust Express applications that efficiently interact with your data layer.

Additional Resources

Practice Exercises

Create an Express API for a todo list application using MongoDB
Build a user authentication system with MySQL and Express
Create a product inventory system with PostgreSQL and Sequelize
Implement connection pooling for a high-traffic Express application
Build a database migration system for your Express application

Happy coding!

If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)

Introduction​

Why Database Connections Matter​

Prerequisites​

Connecting to MongoDB​

Setting Up MongoDB Connection​

Creating a Schema and Model​

Using the Model in Routes​

Connecting to MySQL​

Setting Up MySQL Connection​

Example Query Using MySQL​

Connecting to PostgreSQL​

Setting Up PostgreSQL Connection​

Example Query Using PostgreSQL​

Using ORMs for Database Connection​

Sequelize (for SQL databases)​

Best Practices for Database Connections​

Real-World Application: Building a RESTful API​

Summary​

Additional Resources​

Practice Exercises​