Database Integration (SQL and NoSQL)

Modern Node.js applications rarely exist in isolation. Whether you are building a REST API, a real-time chat application, or a data processing pipeline, you need a way to store and retrieve data. This tutorial covers how to connect Node.js to both SQL databases (using PostgreSQL) and NoSQL databases (using MongoDB), with practical examples you can run in your own projects.

Understanding Database Options in Node.js

Node.js has access to an extensive ecosystem of database drivers and ORMs. The choice between SQL and NoSQL depends on your data structure requirements, query complexity, and scalability needs.

SQL databases like PostgreSQL and MySQL excel when your data has well-defined relationships and you need ACID compliance. They use structured schemas with tables, rows, and columns, and queries are written in SQL.

NoSQL databases like MongoDB and Redis offer flexible schemas and horizontal scaling. Data is stored as documents (similar to JSON), making them ideal for rapidly evolving data models or hierarchical structures.

Both approaches have merit, and many applications use both types for different purposes.

Connecting to PostgreSQL with the pg Driver

The pg library is a popular pure JavaScript PostgreSQL client for Node.js. It provides a straightforward API for connecting to databases and executing queries.

Installing the Driver

npm install pg

Connecting to a Database

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

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

async function query(sql, params) {
  const client = await pool.connect();
  try {
    const result = await client.query(sql, params);
    return result;
  } finally {
    client.release();
  }
}

const result = await query("SELECT $1 AS number", ["1"]);
console.log(result.rows[0]);

Using Connection Pooling

Always use connection pooling in production:

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

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

module.exports = pool;

Connecting to MongoDB with Mongoose

Mongoose provides a schema-based solution for modeling MongoDB data.

Installing Mongoose

npm install mongoose

Connecting to MongoDB

const mongoose = require("mongoose");

async function connect() {
  await mongoose.connect(process.env.MONGODB_URI, {
    useNewUrlParser: true,
    useUnifiedTopology: true,
  });
  console.log("Connected to MongoDB");
}

connect();

Defining a Schema

const mongoose = require("mongoose");

const userSchema = new mongoose.Schema({
  name: String,
  email: { type: String, required: true, unique: true },
  age: Number,
  createdAt: { type: Date, default: Date.now },
});

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

Best Practices

1. Use Environment Variables

Never hardcode credentials. Use environment variables:

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

2. Handle Errors Properly

Always wrap database operations in try-catch:

async function getUser(id) {
  try {
    const result = await pool.query("SELECT * FROM users WHERE id = $1", [id]);
    return result.rows[0];
  } catch (error) {
    console.error("Database error:", error);
    throw error;
  }
}

3. Use Parameterized Queries

Prevent SQL injection by using parameterized queries:

// Good
await pool.query("SELECT * FROM users WHERE id = $1", [userId]);

// Bad - do not do this!
await pool.query("SELECT * FROM users WHERE id = " + userId);

4. Close Connections Gracefully

async function shutdown() {
  await pool.end();
  await mongoose.connection.close();
  console.log("Connections closed");
}

process.on("SIGINT", shutdown);

Summary

Both PostgreSQL and MongoDB are excellent choices for Node.js applications. PostgreSQL offers robust ACID compliance and complex query capabilities, while MongoDB provides flexibility and scalability for evolving data models. Use the right tool for your specific use case, and always follow security best practices like using environment variables and parameterized queries.

When to Use Which

Use PostgreSQL when:

• Your data has complex relationships that require joins
• You need ACID transactions for data integrity
• You have structured, predictable data schemas
• You need complex queries and aggregations

Use MongoDB when:

• Your data structure is flexible or evolving
• You are building a prototype or MVP quickly
• You need horizontal scalability
• Your data is document-oriented and naturally hierarchical

Many production applications use both - PostgreSQL for transactional data and MongoDB for flexible document storage.