Databases: MongoDB, SQL and Redis Interview Questions for Full Stack with Node.js

Show answer

SQL databases such as PostgreSQL and MySQL store data in tables with fixed schemas, support joins and strong ACID transactions, and suit relational data like orders and payments. NoSQL databases such as MongoDB, Redis or Cassandra use documents, key-value or wide-column models with flexible schemas and easier horizontal scaling. The choice depends on data relationships, consistency needs and query patterns.

What is Mongoose and why use it with MongoDB?

Show answer

Mongoose is an ODM for MongoDB in Node.js. It adds schemas with types, validation, defaults, middleware hooks such as pre save, virtuals, population of references and a convenient query API. This brings structure to schemaless MongoDB collections and reduces bugs. For very high-performance or simple cases, some teams use the native MongoDB driver directly to avoid the overhead.

Define a Mongoose schema for a user with validation and an index.

Show answer

const { Schema, model } = require('mongoose'); const userSchema = new Schema({ name: { type: String, required: true, trim: true }, email: { type: String, required: true, unique: true, lowercase: true }, password: { type: String, required: true, select: false }, role: { type: String, enum: ['user', 'admin'], default: 'user' }, city: String }, { timestamps: true }); userSchema.index({ city: 1, createdAt: -1 }); module.exports = model('User', userSchema);

What is an index and how does it improve query performance?

Show answer

An index is a data structure, usually a B-tree, that lets the database find rows without scanning the whole table or collection. Indexes speed up reads on filtered, sorted and joined fields but slow down writes and use storage. Create them based on real query patterns, check usage with EXPLAIN or explain('executionStats'), and remove unused ones.

Explain ACID properties of a database transaction.

Show answer

Atomicity means all operations in a transaction succeed or none do. Consistency means the database moves from one valid state to another, respecting constraints. Isolation means concurrent transactions do not see each other's partial work, controlled by isolation levels. Durability means committed data survives crashes. Money transfers rely on ACID so a debit never happens without the matching credit.

Write a parameterised query with node-postgres to fetch a user's recent orders.

Show answer

const { Pool } = require('pg'); const pool = new Pool({ connectionString: process.env.DATABASE_URL, max: 10 }); async function recentOrders(userId, limit = 10) { const { rows } = await pool.query( `SELECT id, amount, status, created_at FROM orders WHERE user_id = $1 ORDER BY created_at DESC LIMIT $2`, [userId, limit] ); return rows; }

What is Redis and how is it used with Node.js backends?

Show answer

Redis is an in-memory key-value store with data types like strings, hashes, lists, sets, sorted sets and streams. Node apps use it for caching database results, session storage, rate limiting counters, distributed locks, leaderboards, pub/sub between instances and job queues through BullMQ. Data lives in memory, so use TTLs and eviction policies and do not treat it as the only copy of important data.

What is a compound index and why does field order matter?

Show answer

A compound index covers multiple fields, such as { userId: 1, createdAt: -1 }. It supports queries that use a prefix of the fields, so it helps queries on userId alone or userId plus createdAt, but not createdAt alone. A good rule in MongoDB is ESR: put equality fields first, then sort fields, then range fields, to avoid in-memory sorts.

What is the N+1 query problem and how do you fix it?

Show answer

N+1 happens when you run one query to fetch a list and then one extra query per item, for example loading 100 posts and then each author separately, causing 101 queries. Fix it with joins or eager loading in SQL ORMs like include in Prisma or Sequelize, populate or $lookup in MongoDB, batching with an IN query, or DataLoader in GraphQL.

What is the MongoDB aggregation pipeline? Give an example.

Show answer

// Top 5 cities by revenue in the last 30 days const since = new Date(Date.now() - 30 * 864e5); const result = await Order.aggregate([ { $match: { status: 'paid', createdAt: { $gte: since } } }, { $group: { _id: '$city', revenue: { $sum: '$amount' }, orders: { $sum: 1 } } }, { $sort: { revenue: -1 } }, { $limit: 5 } ]); // Each stage transforms documents; put $match early to use indexes.

When should you embed documents versus reference them in MongoDB?

Show answer

Embed when data is read together, owned by the parent and bounded in size, such as addresses inside a user or line items inside an order. Reference when the related data is large, grows without limit, is shared across many documents or changes independently, such as products referenced by orders or comments on a viral post. Remember the 16 MB document size limit.

What is connection pooling and why is it important in Node.js?

Show answer

Opening a database connection is slow, so a pool keeps a set of open connections that requests borrow and return. pg Pool, mysql2 and the MongoDB driver all pool by default. Size the pool based on database limits and instance count; too small causes waiting, too large overloads the database. In serverless functions, reuse the client across invocations or use a proxy like RDS Proxy.

Compare Prisma, Sequelize and TypeORM for SQL databases in Node.js.

Show answer

Prisma uses a schema file to generate a fully typed client and has excellent TypeScript support and migrations, though complex raw SQL needs escape hatches. Sequelize is mature, promise-based and widely used in older JavaScript projects. TypeORM uses decorators and entity classes, familiar to Java and NestJS developers. Drizzle is a newer lightweight, SQL-like typed option. Pick based on TypeScript use and team familiarity.

Implement the cache-aside pattern with Redis for a product lookup.

Show answer

async function getProduct(id) { const key = `product:${id}`; const cached = await redis.get(key); if (cached) return JSON.parse(cached); const product = await Product.findById(id).lean(); if (product) { await redis.set(key, JSON.stringify(product), { EX: 300 }); } return product; } async function updateProduct(id, data) { await Product.updateOne({ _id: id }, data); await redis.del(`product:${id}`); // invalidate }

What is database normalisation and when would you denormalise?

Show answer

Normalisation organises tables to remove duplicate data and update anomalies, typically up to third normal form, for example keeping customers and orders in separate tables linked by foreign keys. You denormalise for read performance, such as storing a product name on an order line or a precomputed order count on a user, when joins become expensive. Denormalised data must be kept in sync.

How do you manage database schema migrations in a Node.js project?

Show answer

Use a migration tool such as Prisma Migrate, Knex, Sequelize CLI or TypeORM migrations, and commit migration files to Git. Each migration has up and ideally down steps and runs in CI and before deployment. For zero downtime, use expand and contract: add new columns first, deploy code that writes both, backfill, switch reads, then drop old columns in a later release.

Users report seeing stale prices after an update because of caching. How do you solve it?

Show answer

Invalidate or update the cache key in the same code path that updates the price, preferably after the database commit. Use reasonable TTLs as a safety net, and version keys if needed. If several services update prices, publish a price-changed event and have consumers delete the cache. For CDN-cached responses, purge the relevant URLs or use short max-age with stale-while-revalidate.

A Mongoose query returns large documents and the API is slow. What optimisations can you apply?

Show answer

Use .select() to fetch only needed fields, and .lean() to return plain objects instead of full Mongoose documents, which cuts memory and CPU. Make sure the filter and sort use an index, checked with explain. Paginate results, avoid populating deep references, and cache frequently read data. For large exports, use a cursor with for await instead of loading everything.

What is soft delete and what are its trade-offs?

Show answer

Soft delete marks a record as deleted with a flag or deletedAt timestamp instead of removing it, so data can be restored and audited. Trade-offs: every query must filter out deleted rows, unique constraints need care, tables grow over time and privacy laws such as India's DPDP Act may require real deletion of personal data on request. Use partial indexes and periodic purging.

Tell me about a time a database change caused an issue and what you learned.

Show answer

Situation: I added a NOT NULL column with a default to a large MySQL table during business hours. Task: the migration locked the table and checkout slowed down. Action: I rolled back, rewrote it as add nullable column, backfill in batches, then add the constraint, and ran it at low traffic after testing on a production-size copy. Result: no downtime, and we added a migration review checklist.

How do you use a MongoDB transaction in Mongoose?

Show answer

An API listing orders became slow after the table reached 50 million rows. How do you investigate?

Show answer

Run EXPLAIN ANALYZE on the slow query to see whether it does a sequential scan or a costly sort. Add a composite index matching the filter and sort, for example (user_id, created_at DESC). Replace OFFSET pagination with cursor pagination, select only needed columns, and consider partitioning by date and archiving old rows. Monitor slow query logs after the change.

Your app gets 'too many connections' errors from PostgreSQL after scaling to 20 pods. What would you do?

Show answer

Each pod opens its own pool, so 20 pods with max 20 connections need 400, beyond the database limit. Reduce the per-pod pool size, put PgBouncer or RDS Proxy in front of the database for transaction pooling, and make sure connections are released, for example client.release() in finally blocks. Also check for long-running idle transactions holding connections.

You need to choose a database for a food delivery app's orders, live rider locations and menus. What would you pick?