Database Integration: MongoDB, PostgreSQL

Database integration is crucial for building data-driven applications. This article covers MongoDB and PostgreSQL integration.

Introduction

Database integration provides:

Data persistence
Query capabilities
Transaction support
Data validation
Scalability

Understanding database integration helps you:

Store and retrieve data
Build data models
Optimize queries
Handle migrations
Scale applications

MongoDB Integration

MongoDB Setup and Connection

// ✅ Good: Install MongoDB driver
// npm install mongodb

// ✅ Good: Connect to MongoDB
const { MongoClient } = require('mongodb');

const uri = process.env.MONGODB_URI || 'mongodb://localhost:27017';
const client = new MongoClient(uri);

async function connectDB() {
  try {
    await client.connect();
    console.log('Connected to MongoDB');
    return client.db('myapp');
  } catch (err) {
    console.error('Connection error:', err);
    process.exit(1);
  }
}

module.exports = connectDB;

// ✅ Good: Use Mongoose for schema validation
// npm install mongoose

const mongoose = require('mongoose');

async function connectDB() {
  try {
    await mongoose.connect(process.env.MONGODB_URI);
    console.log('Connected to MongoDB');
  } catch (err) {
    console.error('Connection error:', err);
    process.exit(1);
  }
}

module.exports = connectDB;

Mongoose Models and Schemas

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

const userSchema = new mongoose.Schema({
  name: {
    type: String,
    required: true,
    trim: true
  },
  email: {
    type: String,
    required: true,
    unique: true,
    lowercase: true
  },
  password: {
    type: String,
    required: true,
    minlength: 8
  },
  role: {
    type: String,
    enum: ['user', 'admin'],
    default: 'user'
  },
  createdAt: {
    type: Date,
    default: Date.now
  }
});

// ✅ Good: Add methods to schema
userSchema.methods.toJSON = function() {
  const user = this.toObject();
  delete user.password;
  return user;
};

// ✅ Good: Add static methods
userSchema.statics.findByEmail = function(email) {
  return this.findOne({ email });
};

// ✅ Good: Add middleware
userSchema.pre('save', async function(next) {
  if (!this.isModified('password')) return next();
  this.password = await hashPassword(this.password);
  next();
});

const User = mongoose.model('User', userSchema);

module.exports = User;

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

const postSchema = new mongoose.Schema({
  title: {
    type: String,
    required: true
  },
  content: {
    type: String,
    required: true
  },
  author: {
    type: mongoose.Schema.Types.ObjectId,
    ref: 'User',
    required: true
  },
  tags: [String],
  createdAt: {
    type: Date,
    default: Date.now
  }
});

const Post = mongoose.model('Post', postSchema);

module.exports = Post;

MongoDB Queries

// ✅ Good: Create documents
const User = require('./models/User');

async function createUser(userData) {
  const user = new User(userData);
  await user.save();
  return user;
}

// ✅ Good: Find documents
async function getUsers() {
  return User.find();
}

async function getUserById(id) {
  return User.findById(id);
}

async function getUserByEmail(email) {
  return User.findOne({ email });
}

// ✅ Good: Update documents
async function updateUser(id, updates) {
  return User.findByIdAndUpdate(id, updates, { new: true });
}

// ✅ Good: Delete documents
async function deleteUser(id) {
  return User.findByIdAndDelete(id);
}

// ✅ Good: Complex queries
async function searchUsers(query) {
  return User.find({
    $or: [
      { name: { $regex: query, $options: 'i' } },
      { email: { $regex: query, $options: 'i' } }
    ]
  });
}

// ✅ Good: Aggregation
async function getUserStats() {
  return User.aggregate([
    {
      $group: {
        _id: '$role',
        count: { $sum: 1 }
      }
    }
  ]);
}

// ✅ Good: Populate references
async function getUserWithPosts(userId) {
  return User.findById(userId).populate('posts');
}

PostgreSQL Integration

PostgreSQL Setup and Connection

// ✅ Good: Install PostgreSQL driver
// npm install pg

const { Pool } = require('pg');

const pool = new Pool({
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  host: process.env.DB_HOST,
  port: process.env.DB_PORT,
  database: process.env.DB_NAME
});

pool.on('error', (err) => {
  console.error('Unexpected error on idle client', err);
});

module.exports = pool;

// ✅ Good: Use ORM - Sequelize
// npm install sequelize

const { Sequelize } = require('sequelize');

const sequelize = new Sequelize(
  process.env.DB_NAME,
  process.env.DB_USER,
  process.env.DB_PASSWORD,
  {
    host: process.env.DB_HOST,
    dialect: 'postgres'
  }
);

module.exports = sequelize;

Sequelize Models

// models/User.js
const { DataTypes } = require('sequelize');
const sequelize = require('../db');

const User = sequelize.define('User', {
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  name: {
    type: DataTypes.STRING,
    allowNull: false
  },
  email: {
    type: DataTypes.STRING,
    allowNull: false,
    unique: true
  },
  password: {
    type: DataTypes.STRING,
    allowNull: false
  },
  role: {
    type: DataTypes.ENUM('user', 'admin'),
    defaultValue: 'user'
  }
}, {
  timestamps: true
});

module.exports = User;

// models/Post.js
const { DataTypes } = require('sequelize');
const sequelize = require('../db');
const User = require('./User');

const Post = sequelize.define('Post', {
  id: {
    type: DataTypes.INTEGER,
    primaryKey: true,
    autoIncrement: true
  },
  title: {
    type: DataTypes.STRING,
    allowNull: false
  },
  content: {
    type: DataTypes.TEXT,
    allowNull: false
  },
  userId: {
    type: DataTypes.INTEGER,
    references: {
      model: User,
      key: 'id'
    }
  }
}, {
  timestamps: true
});

Post.belongsTo(User);
User.hasMany(Post);

module.exports = Post;

PostgreSQL Queries

const pool = require('../db');

// ✅ Good: Execute queries
async function getUsers() {
  const result = await pool.query('SELECT * FROM users');
  return result.rows;
}

async function getUserById(id) {
  const result = await pool.query('SELECT * FROM users WHERE id = $1', [id]);
  return result.rows[0];
}

// ✅ Good: Insert data
async function createUser(name, email, password) {
  const result = await pool.query(
    'INSERT INTO users (name, email, password) VALUES ($1, $2, $3) RETURNING *',
    [name, email, password]
  );
  return result.rows[0];
}

// ✅ Good: Update data
async function updateUser(id, name, email) {
  const result = await pool.query(
    'UPDATE users SET name = $1, email = $2 WHERE id = $3 RETURNING *',
    [name, email, id]
  );
  return result.rows[0];
}

// ✅ Good: Delete data
async function deleteUser(id) {
  const result = await pool.query(
    'DELETE FROM users WHERE id = $1 RETURNING *',
    [id]
  );
  return result.rows[0];
}

// ✅ Good: Transactions
async function transferFunds(fromId, toId, amount) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query(
      'UPDATE accounts SET balance = balance - $1 WHERE id = $2',
      [amount, fromId]
    );
    await client.query(
      'UPDATE accounts SET balance = balance + $1 WHERE id = $2',
      [amount, toId]
    );
    await client.query('COMMIT');
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

Sequelize Queries

const User = require('./models/User');
const Post = require('./models/Post');

// ✅ Good: Create records
async function createUser(userData) {
  return User.create(userData);
}

// ✅ Good: Find records
async function getUsers() {
  return User.findAll();
}

async function getUserById(id) {
  return User.findByPk(id);
}

// ✅ Good: Update records
async function updateUser(id, updates) {
  const user = await User.findByPk(id);
  return user.update(updates);
}

// ✅ Good: Delete records
async function deleteUser(id) {
  const user = await User.findByPk(id);
  return user.destroy();
}

// ✅ Good: Complex queries
async function getUserWithPosts(userId) {
  return User.findByPk(userId, {
    include: [Post]
  });
}

// ✅ Good: Filtering and pagination
async function getUsers(page = 1, limit = 10) {
  const offset = (page - 1) * limit;
  return User.findAndCountAll({
    limit,
    offset,
    order: [['createdAt', 'DESC']]
  });
}

Database Migrations

Sequelize Migrations

# ✅ Good: Initialize Sequelize
npx sequelize-cli init

# ✅ Good: Create migration
npx sequelize-cli migration:create --name create-users-table

# ✅ Good: Run migrations
npx sequelize-cli db:migrate

# ✅ Good: Undo migrations
npx sequelize-cli db:migrate:undo

// migrations/20240101000000-create-users-table.js
module.exports = {
  up: async (queryInterface, Sequelize) => {
    await queryInterface.createTable('Users', {
      id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        autoIncrement: true
      },
      name: {
        type: Sequelize.STRING,
        allowNull: false
      },
      email: {
        type: Sequelize.STRING,
        allowNull: false,
        unique: true
      },
      password: {
        type: Sequelize.STRING,
        allowNull: false
      },
      createdAt: {
        type: Sequelize.DATE,
        defaultValue: Sequelize.NOW
      },
      updatedAt: {
        type: Sequelize.DATE,
        defaultValue: Sequelize.NOW
      }
    });
  },

  down: async (queryInterface) => {
    await queryInterface.dropTable('Users');
  }
};

Best Practices

Use connection pooling:

// ✅ Good: Connection pool
const pool = new Pool({ max: 20 });

// ❌ Bad: New connection per query
const client = new Client();

Use parameterized queries:

// ✅ Good: Parameterized
pool.query('SELECT * FROM users WHERE id = $1', [id]);

// ❌ Bad: String concatenation
pool.query(`SELECT * FROM users WHERE id = ${id}`);

Handle errors properly:

// ✅ Good: Error handling
try {
  const user = await User.findById(id);
} catch (err) {
  console.error('Database error:', err);
}

// ❌ Bad: No error handling
const user = await User.findById(id);

Summary

Database integration is essential. Key takeaways:

Connect to databases properly
Use ORMs for schema validation
Write efficient queries
Use transactions for data consistency
Implement migrations
Handle errors properly
Use connection pooling
Optimize performance

Next Steps

Learn about Building REST APIs
Explore Authentication
Study Error Handling
Practice database integration
Build data-driven applications