Node.js Database: Complete Theory & Practice Guide

Table of Contents

1. Theory: Database Fundamentals
2. Theory: SQL vs NoSQL
3. Theory: Connection Management
4. Theory: Query Optimization
5. Basic: PostgreSQL with node-postgres
6. Basic: MongoDB with Mongoose
7. Basic: MySQL with mysql2
8. Advanced: Transaction & ACID
9. Advanced: Migration & Schema Management
10. Best Practices
11. Interview Q&A + MCQ
12. Contextual Learning Links

1. Theory: Database Fundamentals

A database is an organized collection of structured information stored electronically. In Node.js applications, databases persist data beyond the application's lifecycle.

const databaseConcepts = {
  Index: { definition: 'Faster lookup', tradeoff: 'Read speed vs write overhead' },
  Transaction: { definition: 'All-or-nothing operations', properties: 'ACID' },
  ConnectionPool: { definition: 'Reusable connections', benefit: 'Lower latency' },
  QueryPlan: { definition: 'Optimizer execution strategy', tool: 'EXPLAIN' }
};

2. Theory: SQL vs NoSQL

const databaseSelectionGuide = {
  chooseSQLWhen: ['Complex joins', 'Strict transactions', 'Predictable schema'],
  chooseNoSQLWhen: ['Rapid schema change', 'Document-heavy data', 'Horizontal scale'],
  hybridApproach: ['PostgreSQL + JSONB', 'Polyglot persistence']
};

3. Theory: Connection Management

const poolSizingTheory = {
  formula: 'maxPoolSize = (coreCount × 2) + spindleCount',
  warning: '(maxPoolSize × instances) < database max_connections'
};

const connectionStrings = {
  PostgreSQL: 'postgresql://user:password@host:5432/db?ssl=true&pool_size=10',
  MySQL: 'mysql://user:password@host:3306/db?connectionLimit=10',
  MongoDB: 'mongodb://user:password@host:27017/db?maxPoolSize=10'
};

4. Theory: Query Optimization

-- Useful index patterns
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);
CREATE INDEX idx_active_users ON users(id) WHERE active = true;

const optimizationTechniques = {
  good: 'SELECT id,name,email FROM users WHERE id = $1',
  avoid: 'SELECT *',
  use: ['EXPLAIN', 'Parameterized queries', 'Batch queries']
};

5. Basic: PostgreSQL with node-postgres

npm install pg

const { Pool } = require('pg');
const pool = new Pool({
  host: process.env.DB_HOST || 'localhost',
  port: process.env.DB_PORT || 5432,
  database: process.env.DB_NAME || 'myapp',
  user: process.env.DB_USER || 'postgres',
  password: process.env.DB_PASSWORD,
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000
});

6. Basic: MongoDB with Mongoose

npm install mongoose

const userSchema = new mongoose.Schema({
  name: { type: String, required: true, trim: true },
  email: { type: String, required: true, unique: true, lowercase: true },
  passwordHash: { type: String, required: true, select: false },
  role: { type: String, enum: ['user', 'moderator', 'admin'], default: 'user' }
}, { timestamps: true });

7. Basic: MySQL with mysql2

npm install mysql2

const mysql = require('mysql2');
const pool = mysql.createPool({
  host: process.env.MYSQL_HOST || 'localhost',
  port: process.env.MYSQL_PORT || 3306,
  database: process.env.MYSQL_DATABASE || 'myapp',
  user: process.env.MYSQL_USER || 'root',
  password: process.env.MYSQL_PASSWORD,
  waitForConnections: true,
  connectionLimit: 10
}).promise();

8. Advanced: Transaction & ACID

const acidProperties = {
  Atomicity: 'All operations succeed or all fail',
  Consistency: 'Data remains valid',
  Isolation: 'Transactions do not interfere',
  Durability: 'Committed writes survive crashes'
};

// PostgreSQL transfer transaction (condensed)
await client.query('BEGIN');
await client.query('UPDATE accounts SET balance = balance - $1 WHERE user_id = $2', [amount, fromUserId]);
await client.query('UPDATE accounts SET balance = balance + $1 WHERE user_id = $2', [amount, toUserId]);
await client.query('COMMIT');

9. Advanced: Migration & Schema Management

npm install node-pg-migrate pg

// migrations/001_create_users_table.js
exports.up = async (pgm) => {
  pgm.createTable('users', {
    id: { type: 'serial', primaryKey: true },
    email: { type: 'varchar(255)', notNull: true, unique: true },
    created_at: { type: 'timestamp', default: pgm.func('NOW()') }
  });
  pgm.createIndex('users', 'email');
};
exports.down = async (pgm) => pgm.dropTable('users');

// MongoDB migration shape
async function up(db) {
  await db.createCollection('users');
  await db.collection('users').createIndex({ email: 1 }, { unique: true });
}
async function down(db) {
  await db.collection('users').drop();
}

10. Best Practices

const queryBestPractices = {
  good: [
    'Use pooling',
    'Use parameterized queries',
    'Index WHERE/JOIN columns',
    'Monitor slow queries with EXPLAIN',
    'Set query and connection timeouts'
  ]
};

// Quick commands
// PostgreSQL: psql -U postgres -d myapp
// MongoDB: mongosh
// MySQL: mysql -u root -p myapp
// Migrations: npm run migrate up

10 Interview Questions + 10 MCQs

Contextual Learning Links

• Node.js Authentication
• Node.js Security
• Node.js Performance & Scaling
• Node.js REST API