Database Integration

Choosing a Database

Node.js works well with both SQL and NoSQL databases. Your choice depends on your data structure, scalability needs, and query patterns.

MongoDB with Mongoose

const mongoose = require('mongoose');

// Connect to MongoDB
mongoose.connect(process.env.MONGODB_URI, {
  maxPoolSize: 10,
  serverSelectionTimeoutMS: 5000,
  socketTimeoutMS: 45000,
});

mongoose.connection.on('connected', () => {
  console.log('Connected to MongoDB');
});

mongoose.connection.on('error', (err) => {
  console.error('MongoDB error:', err);
});

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

// Add methods
userSchema.methods.toJSON = function() {
  const obj = this.toObject();
  delete obj.password;
  return obj;
};

// Add statics
userSchema.statics.findByEmail = function(email) {
  return this.findOne({ email: email.toLowerCase() });
};

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

// CRUD operations
async function examples() {
  // Create
  const user = await User.create({
    email: 'test@example.com',
    password: hashedPassword,
    name: 'Test User'
  });

  // Read
  const users = await User.find({ role: 'user' })
    .select('name email')
    .sort({ createdAt: -1 })
    .limit(10);

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

  // Delete
  await User.findByIdAndDelete(id);
}

PostgreSQL with pg

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

// Create a connection pool
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,                    // Max connections
  idleTimeoutMillis: 30000,   // Close idle connections
  connectionTimeoutMillis: 2000,
});

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

// Query helper
async function query(text, params) {
  const start = Date.now();
  const result = await pool.query(text, params);
  const duration = Date.now() - start;
  console.log('Query executed:', { text, duration, rows: result.rowCount });
  return result;
}

// CRUD operations
async function examples() {
  // Create table
  await query(`
    CREATE TABLE IF NOT EXISTS users (
      id SERIAL PRIMARY KEY,
      email VARCHAR(255) UNIQUE NOT NULL,
      name VARCHAR(255) NOT NULL,
      created_at TIMESTAMP DEFAULT NOW()
    )
  `);

  // Insert (parameterized - prevents SQL injection!)
  const { rows } = await query(
    'INSERT INTO users (email, name) VALUES ($1, $2) RETURNING *',
    ['test@example.com', 'Test User']
  );

  // Select
  const users = await query(
    'SELECT * FROM users WHERE created_at > $1 ORDER BY created_at DESC LIMIT $2',
    [new Date('2024-01-01'), 10]
  );

  // Update
  await query(
    'UPDATE users SET name = $1 WHERE id = $2',
    ['New Name', 1]
  );

  // Delete
  await query('DELETE FROM users WHERE id = $1', [1]);
}

// Transaction example
async function transferMoney(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 (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
}

Prisma ORM (Modern Choice)

# Install Prisma
npm install prisma @prisma/client
npx prisma init

# schema.prisma
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}

model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String
  posts     Post[]
  createdAt DateTime @default(now())
}

model Post {
  id        Int      @id @default(autoincrement())
  title     String
  content   String?
  published Boolean  @default(false)
  author    User     @relation(fields: [authorId], references: [id])
  authorId  Int
}

# Generate client after schema changes
npx prisma generate

# Run migrations
npx prisma migrate dev --name init

// Using Prisma Client
const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();

async function main() {
  // Create user with related posts
  const user = await prisma.user.create({
    data: {
      email: 'alice@example.com',
      name: 'Alice',
      posts: {
        create: [
          { title: 'First Post', content: 'Hello World' },
          { title: 'Second Post', content: 'Prisma is great!' }
        ]
      }
    },
    include: { posts: true }
  });

  // Find with relations
  const users = await prisma.user.findMany({
    where: {
      email: { contains: '@example.com' }
    },
    include: {
      posts: {
        where: { published: true },
        orderBy: { createdAt: 'desc' }
      }
    }
  });

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

  // Delete
  await prisma.user.delete({ where: { id: 1 } });

  // Transaction
  const [post, user] = await prisma.$transaction([
    prisma.post.create({ data: { title: 'New', authorId: 1 } }),
    prisma.user.update({ where: { id: 1 }, data: { name: 'Updated' } })
  ]);
}

main()
  .catch(console.error)
  .finally(() => prisma.$disconnect());

Redis for Caching

const { createClient } = require('redis');

const redis = createClient({ url: process.env.REDIS_URL });

redis.on('error', (err) => console.error('Redis error:', err));
redis.on('connect', () => console.log('Connected to Redis'));

await redis.connect();

// Basic operations
await redis.set('key', 'value');
await redis.set('key', 'value', { EX: 3600 });  // Expires in 1 hour

const value = await redis.get('key');

// Cache-aside pattern
async function getCachedUser(id) {
  const cacheKey = `user:${id}`;
  
  // Check cache first
  const cached = await redis.get(cacheKey);
  if (cached) {
    return JSON.parse(cached);
  }
  
  // Fetch from database
  const user = await prisma.user.findUnique({ where: { id } });
  
  // Store in cache
  if (user) {
    await redis.set(cacheKey, JSON.stringify(user), { EX: 300 });
  }
  
  return user;
}

// Invalidate cache on update
async function updateUser(id, data) {
  const user = await prisma.user.update({ where: { id }, data });
  await redis.del(`user:${id}`);  // Invalidate cache
  return user;
}

// Rate limiting with Redis
async function checkRateLimit(ip) {
  const key = `ratelimit:${ip}`;
  const requests = await redis.incr(key);
  
  if (requests === 1) {
    await redis.expire(key, 60);  // 1 minute window
  }
  
  return requests <= 100;  // 100 requests per minute
}

Connection Pooling Best Practices

// PostgreSQL pool configuration
const pool = new Pool({
  max: 20,                        // Max connections
  min: 5,                         // Min connections to keep
  idleTimeoutMillis: 30000,       // Close idle after 30s
  connectionTimeoutMillis: 5000,  // Connection timeout
  
  // For serverless (Vercel, Lambda)
  maxUses: 7500,  // Close connection after N uses
});

// Mongoose connection options
mongoose.connect(uri, {
  maxPoolSize: 10,
  minPoolSize: 5,
  maxIdleTimeMS: 30000,
  serverSelectionTimeoutMS: 5000,
});

// Prisma with connection limit
// In schema.prisma or via URL
// DATABASE_URL="postgresql://...?connection_limit=10"

// Graceful shutdown
process.on('SIGTERM', async () => {
  console.log('Shutting down...');
  
  await pool.end();           // PostgreSQL
  await mongoose.disconnect(); // MongoDB
  await prisma.$disconnect();  // Prisma
  await redis.quit();          // Redis

  

    
📚 Learn More
    

      

        
          📖 Mongoose Documentation →
        
      
      

        
          📖 Prisma Documentation →
        
      
    
  
  
  process.exit(0);
});