Database Integration

A schema defines the structure, types, and validation rules for documents, while a model is a constructor compiled from a schema that provides all CRUD methods. Schemas support built-in validators, virtual properties, middleware hooks (pre/post), and custom instance/static methods, letting you encapsulate business logic at the data layer. Mongoose automatically maps the model name to a collection (e.g., 'User' → 'users').

const mongoose = require('mongoose');

const userSchema = new mongoose.Schema({
  name:  { type: String, required: true },
  email: { type: String, required: true, unique: true },
  age:   { type: Number, min: 0 },
  role:  { type: String, enum: ['user', 'admin'], default: 'user' },
  createdAt: { type: Date, default: Date.now }
});

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

Why it matters: Schemas enforce data structure at the application layer before data reaches MongoDB, preventing invalid documents from ever being saved.

Real applications: E-commerce products, user accounts, blog posts, and order records all benefit from Mongoose schemas with validation to ensure consistent document structure.

Common mistakes: Forgetting to call mongoose.model() after defining the schema means the model is never registered; and defining models inside route handlers recreates them on every request, causing OverwriteModelError.

Mongoose models provide create, find, findById, findByIdAndUpdate, and findByIdAndDelete for full CRUD operations on MongoDB collections. All these methods return Promises and work seamlessly with async/await. Pass { new: true } to update methods to get the modified document back, and { runValidators: true } to apply schema validators during updates.

// Create
const user = await User.create({ name: 'Alice', email: 'alice@example.com' });

// Read
const all   = await User.find({});
const one   = await User.findById(id);
const query = await User.find({ role: 'admin' }).limit(10);

// Update
await User.findByIdAndUpdate(id, { name: 'Bob' }, { new: true });

// Delete
await User.findByIdAndDelete(id);

Why it matters: Understanding Mongoose CRUD operations is the foundation of any MongoDB-backed Node.js application and is tested in virtually every backend interview.

Real applications: User registration flows, product catalog management, blog post APIs, and order management systems all rely on these base Mongoose operations.

Common mistakes: Omitting { runValidators: true } in update calls causes schema validators to be silently skipped, allowing invalid data to slip through on updates even when validation is defined in the schema.

The pg (node-postgres) library provides a client and connection pool for connecting to PostgreSQL, supporting parameterized queries, promises, and transaction management. Always use parameterized queries with $1, $2 placeholders to prevent SQL injection — never concatenate user input into query strings. The Pool class manages connections automatically, borrowing and returning them as requests are handled.

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

const pool = new Pool({
  host: 'localhost',
  port: 5432,
  database: 'mydb',
  user: 'admin',
  password: process.env.DB_PASSWORD
});

const result = await pool.query(
  'SELECT * FROM users WHERE id = $1',
  [userId]
);
console.log(result.rows);

Why it matters: pg is the most widely used PostgreSQL client in Node.js; using it correctly with parameterized queries is a security requirement to prevent SQL injection.

Real applications: Financial systems, e-commerce platforms, and SaaS back-ends that need relational data, complex joins, and ACID transactions all use PostgreSQL via the pg library.

Common mistakes: Building queries with string concatenation like `SELECT * FROM users WHERE id = ${userId}` is a textbook SQL injection vulnerability; always use the $1 parameterized form.

Sequelize is a promise-based ORM for SQL databases (PostgreSQL, MySQL, SQLite, MSSQL) that maps tables to JavaScript classes with associations, migrations, and a query builder. Models are defined using sequelize.define() with typed columns, constraints, and relationships. In production, always use migrations to manage schema changes rather than sync(), which can destructively drop and recreate tables.

const { Sequelize, DataTypes } = require('sequelize');
const sequelize = new Sequelize('mydb', 'user', 'pass', {
  host: 'localhost',
  dialect: 'postgres'
});

const User = sequelize.define('User', {
  name:  { type: DataTypes.STRING, allowNull: false },
  email: { type: DataTypes.STRING, unique: true },
  age:   { type: DataTypes.INTEGER }
});

await sequelize.sync(); // Create tables

Why it matters: Sequelize is one of the most widely used SQL ORMs in Node.js; understanding model definition, associations, and migrations is key for full-stack SQL development.

Real applications: CMS platforms, admin dashboards, and multi-tenant SaaS apps using PostgreSQL or MySQL use Sequelize for its rich association support (hasMany, belongsTo, belongsToMany).

Common mistakes: Using sequelize.sync({ force: true }) in production drops all tables and recreates them, destroying all data; always use Sequelize CLI migrations for production schema changes.

Connection pooling maintains a cache of database connections that are reused across requests, avoiding the overhead of establishing a new TCP connection for every query. Without pooling, each request opens and closes a connection, causing latency spikes and exhausting database connection limits under load. Always release connections in a finally block to prevent connection leaks that can exhaust the pool.

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

const pool = new Pool({
  max: 20,              // Maximum connections in pool
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000
});

// Connections are borrowed and returned automatically
const res = await pool.query('SELECT NOW()');

// For transactions, explicitly acquire a client
const client = await pool.connect();
try {
  await client.query('BEGIN');
  // ... queries
  await client.query('COMMIT');
} finally {
  client.release(); // Return to pool
}

Why it matters: Connection pool configuration directly impacts application throughput; an undersized pool under-utilizes the database, while an oversized one exhausts PostgreSQL's connection limit.

Real applications: Any production API serving concurrent requests relies on a connection pool; setting max correctly based on max_connections in PostgreSQL is a critical tuning step.

Common mistakes: Not calling client.release() in a finally block after a transaction leaks the connection; eventually the pool is exhausted and all subsequent queries hang indefinitely.

Indexes create efficient B-tree data structures that allow the database to find rows without a full table/collection scan, which becomes critically expensive as data grows. Index fields that appear in WHERE, ORDER BY, and JOIN clauses; both MongoDB (Mongoose) and PostgreSQL support single-field, compound, and unique indexes. Over-indexing slows down writes since every insert and update must maintain all index structures.

// MongoDB — create index with Mongoose
userSchema.index({ email: 1 });           // Single field
userSchema.index({ name: 1, age: -1 });   // Compound index
userSchema.index({ email: 1 }, { unique: true });

// PostgreSQL — create index via SQL
await pool.query('CREATE INDEX idx_users_email ON users (email)');
await pool.query('CREATE UNIQUE INDEX idx_users_username ON users (username)');

Why it matters: Poor indexing is the #1 cause of database performance problems in production; a single missing index on a high-traffic query can cause full table scans on millions of rows.

Real applications: E-mail lookup on a users table, product search by category, and order lookup by customer ID all require indexes to perform at scale.

Common mistakes: Adding an index on every column — each index slows INSERT/UPDATE operations and consumes storage; use EXPLAIN ANALYZE or .explain('executionStats') to identify which queries actually need indexes.

Transactions group multiple database operations into an atomic unit where all operations succeed or all roll back, ensuring data consistency for operations like fund transfers and order processing. Both PostgreSQL (via pg) and MongoDB (Mongoose sessions) support transactions with similar BEGIN/COMMIT/ROLLBACK patterns. Always use finally blocks to release connections and end sessions regardless of success or failure.

// PostgreSQL with pg
const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [100, fromId]);
  await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [100, toId]);
  await client.query('COMMIT');
} catch (e) {
  await client.query('ROLLBACK');
  throw e;
} finally {
  client.release();
}

// Mongoose transactions
const session = await mongoose.startSession();
session.startTransaction();
try {
  await Account.updateOne({ _id: fromId }, { $inc: { balance: -100 } }, { session });
  await Account.updateOne({ _id: toId }, { $inc: { balance: 100 } }, { session });
  await session.commitTransaction();
} catch (e) {
  await session.abortTransaction();
  throw e;
}

Why it matters: Without transactions, partial failures in multi-step operations leave the database in an inconsistent state — a critical bug in financial, e-commerce, and inventory systems.

Real applications: Bank transfers, order checkout (deducting stock + creating order record), and user registration with associated profile records all require transaction-level atomicity.

Common mistakes: MongoDB transactions require a replica set — they fail silently on standalone instances; also, forgetting to pass the session option to each Mongoose call means the operation runs outside the transaction.

Migrations are version-controlled scripts that modify the database schema incrementally using up (apply) and down (revert) methods, ensuring every environment has the same consistent schema. Migration files are committed to version control alongside application code, making schema history trackable and reproducible. Never modify a migration that has already been applied — always create a new one.

// Sequelize CLI migration
npx sequelize-cli migration:generate --name add-users-table

// Generated migration file
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, unique: true }
    });
  },
  down: async (queryInterface) => {
    await queryInterface.dropTable('Users');
  }
};

// Run migrations
npx sequelize-cli db:migrate

Why it matters: Schema migrations are the standard practice for safely evolving database structure across environments; understanding them is expected at any mid-to-senior backend level.

Real applications: Every production app with a relational database uses a migration tool (Sequelize CLI, Flyway, Knex, or Prisma Migrate) to track and apply schema changes during deployments.

Common mistakes: Editing a migration file that has already been run in production causes drift since the migration table marks it as already applied — always create a new migration file for any subsequent change.

Database query optimization reduces response times and server load by minimizing data transfer, leveraging indexes, and avoiding common anti-patterns like N+1 queries. Key techniques include selecting only needed fields, using pagination, applying .lean() in Mongoose for read-only data, and using populate() to avoid multiple sequential queries. Always analyze execution plans with EXPLAIN or .explain() before and after optimizations.

// 1. Select only needed fields
const users = await User.find({}).select('name email');

// 2. Use pagination
const page = await User.find({})
  .skip((pageNum - 1) * pageSize)
  .limit(pageSize);

// 3. Use lean() for read-only queries (Mongoose)
const docs = await User.find({}).lean(); // Returns plain objects

// 4. Use explain() to analyze query plans
const plan = await User.find({ email: 'a@b.com' }).explain('executionStats');

// 5. Avoid N+1 queries — use populate or JOIN
const orders = await Order.find({}).populate('user');

Why it matters: A few slow queries can bottleneck an entire application; query optimization is often the highest-ROI performance improvement available without infrastructure changes.

Real applications: Dashboard APIs, search endpoints, and report generation services routinely need query optimization as data grows from thousands to millions of records.

Common mistakes: The N+1 query problem — fetching a list then making a separate DB call for each item — is extremely common with Mongoose; always use .populate() or aggregation pipelines to fetch related data in one query.

Mongoose validators enforce data integrity at the schema level before documents are saved, using built-in validators like required, min, max, enum, match, and minlength/maxlength. Custom validators allow complex business rules (like price precision) to be enforced directly in the schema. Note that validators run on save() and create() but are skipped on update() unless { runValidators: true } is set.

const productSchema = new mongoose.Schema({
  name: {
    type: String,
    required: [true, 'Product name is required'],
    trim: true,
    minlength: [2, 'Name must be at least 2 characters']
  },
  price: {
    type: Number,
    required: true,
    min: [0, 'Price cannot be negative'],
    validate: {
      validator: (v) => v === Math.round(v * 100) / 100,
      message: 'Price must have at most 2 decimal places'
    }
  },
  email: {
    type: String,
    match: [/^\S+@\S+\.\S+$/, 'Please enter a valid email']
  },
  status: {
    type: String,
    enum: {
      values: ['active', 'inactive', 'archived'],
      message: '{VALUE} is not a valid status'
    }
  }
});

// Validation errors are caught in try/catch
try {
  await Product.create({ name: '', price: -5 });
} catch (err) {
  console.log(err.errors); // Validation error details
}

Why it matters: Schema-level validation catches invalid data at the data layer regardless of which code path writes to the database, providing a reliable last line of defense.

Real applications: User registration forms, product creation APIs, and payment amount fields all use Mongoose schema validators to prevent invalid data from entering MongoDB.

Common mistakes: Relying solely on schema validation without application-level validation (Joi, Zod) means API contract violations aren't caught early enough; use both — schema validation as a safety net, app validation for user-facing error messages.

Database seeding populates a database with initial or test data for development, testing, and demo environments. Seed scripts should be idempotent — running them multiple times produces the same result. They typically clear existing test data with deleteMany({}), then insert fresh records, and should never be run against production databases.

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

const users = [
  { name: 'Admin', email: 'admin@app.com', role: 'admin' },
  { name: 'Alice', email: 'alice@app.com', role: 'user' },
  { name: 'Bob', email: 'bob@app.com', role: 'user' }
];

async function seedUsers() {
  await mongoose.connect(process.env.MONGO_URI);
  
  // Clear existing data (only in dev/test)
  await User.deleteMany({});
  
  // Insert seed data with hashed passwords
  const bcrypt = require('bcrypt');
  const seeded = await Promise.all(
    users.map(async (u) => ({
      ...u,
      password: await bcrypt.hash('password123', 10)
    }))
  );
  
  await User.insertMany(seeded);
  console.log('Seeded', seeded.length, 'users');
  await mongoose.disconnect();
}

seedUsers();

Why it matters: Reliable seed scripts eliminate manual setup steps when onboarding developers or spinning up test environments, making development faster and more consistent.

Real applications: E-commerce demo environments, test database fixtures, role-based access control initial data, and country/currency lookup tables all rely on seed scripts.

Common mistakes: Running seed scripts with deleteMany({}) against a production database destroys live data; use environment checks (if (process.env.NODE_ENV !== 'production')) to guard against this.

Soft deletes mark records as deleted with a deletedAt timestamp instead of permanently removing them, preserving data for auditing, recovery, and compliance. Mongoose middleware hooks (pre(/^find/)) can automatically filter out soft-deleted records from all queries, keeping the pattern transparent to the rest of the codebase. A companion restore() method clears the deletedAt field to un-delete a record.

const userSchema = new mongoose.Schema({
  name: String,
  email: String,
  deletedAt: { type: Date, default: null }
});

// Middleware to exclude soft-deleted records from all queries
userSchema.pre(/^find/, function() {
  this.where({ deletedAt: null });
});

// Soft delete method
userSchema.methods.softDelete = function() {
  this.deletedAt = new Date();
  return this.save();
};

// Restore method
userSchema.methods.restore = function() {
  this.deletedAt = null;
  return this.save();
};

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

// Usage
await user.softDelete(); // Sets deletedAt timestamp
await User.find({}); // Automatically excludes soft-deleted

// Query including soft-deleted (for admin)
await User.find({}).setOptions({ includeDeleted: true });

Why it matters: Hard-deleting records makes data recovery impossible and breaks foreign key references; soft deletes are essential for GDPR audit trails, undo functionality, and regulatory data retention requirements.

Real applications: User account deactivation (GDPR), order cancellations, content moderation (hiding posts without permanent deletion), and employee HR records all use soft delete patterns.

Common mistakes: Forgetting to add the deletedAt: null filter to raw aggregation pipelines and direct queries — the Mongoose pre-find hook only covers find* methods, not aggregate().

Database health checks verify that the connection is alive and responsive, which is critical for load balancers, Kubernetes liveness/readiness probes, and uptime monitoring. A dedicated /health endpoint should test the actual DB connection (not just return 200 OK) and include response time to detect degraded performance before full failure. Return HTTP 503 when the database is unreachable so load balancers can route traffic to healthy instances.

// Health check endpoint
app.get('/health', async (req, res) => {
  const checks = {};
  
  // MongoDB check
  try {
    const start = Date.now();
    await mongoose.connection.db.admin().ping();
    checks.mongodb = {
      status: 'healthy',
      responseTime: Date.now() - start + 'ms'
    };
  } catch (err) {
    checks.mongodb = { status: 'unhealthy', error: err.message };
  }
  
  // PostgreSQL check
  try {
    const start = Date.now();
    await pool.query('SELECT 1');
    checks.postgres = {
      status: 'healthy',
      responseTime: Date.now() - start + 'ms',
      activeConnections: pool.totalCount,
      idleConnections: pool.idleCount
    };
  } catch (err) {
    checks.postgres = { status: 'unhealthy', error: err.message };
  }
  
  const allHealthy = Object.values(checks).every(c => c.status === 'healthy');
  res.status(allHealthy ? 200 : 503).json({ status: allHealthy ? 'ok' : 'degraded', checks });
});

Why it matters: Load balancers and container orchestrators use health check endpoints to determine whether to route traffic to an instance; a broken health endpoint means unhealthy instances keep receiving requests.

Real applications: Kubernetes readiness probes, AWS ALB target group health checks, and Render/Railway health monitoring all call a /health endpoint to determine deployment stability.

Common mistakes: Returning 200 OK from /health without actually querying the database means a DB connection failure goes undetected by the load balancer until requests start failing.

Prisma is a modern type-safe ORM that generates a strongly-typed client from a declarative schema.prisma file, providing excellent TypeScript autocompletion for all database operations. It supports PostgreSQL, MySQL, SQLite, MongoDB, and SQL Server, and uses prisma migrate dev for schema-driven migrations. Prisma's query API is expressive and easy to read, with built-in support for relations, transactions, and raw SQL escape hatches.

// prisma/schema.prisma
// datasource db {
//   provider = "postgresql"
//   url      = env("DATABASE_URL")
// }
// model User {
//   id    Int     @id @default(autoincrement())
//   name  String
//   email String  @unique
//   posts Post[]
// }

// After running: npx prisma generate
const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();

// Create
const user = await prisma.user.create({
  data: { name: 'Alice', email: 'alice@example.com' }
});

// Read with relations
const userWithPosts = await prisma.user.findUnique({
  where: { email: 'alice@example.com' },
  include: { posts: true }
});

// Update
await prisma.user.update({
  where: { id: 1 },
  data: { name: 'Alice Smith' }
});

// Transaction
await prisma.$transaction([
  prisma.account.update({ where: { id: 1 }, data: { balance: { decrement: 100 } } }),
  prisma.account.update({ where: { id: 2 }, data: { balance: { increment: 100 } } })
]);

Why it matters: Prisma has become the dominant ORM in the TypeScript/Node.js ecosystem; its type safety catches query mistakes at compile time rather than runtime.

Real applications: Full-stack TypeScript apps (Next.js, NestJS, Remix) widely use Prisma as their primary ORM for its developer experience, type safety, and migration tooling.

Common mistakes: Instantiating new PrismaClient() multiple times (e.g., inside route handlers) creates too many database connections; always create a single shared Prisma client instance exported from a module.