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

Designing For Real Applications

Database code becomes easier to trust when it treats connection management, error handling, and query shape as first-class parts of the design. A pool is not just a performance trick. It is also a way to keep your app from opening more connections than the database can comfortably handle. Once you think about queries as shared resources instead of throwaway calls, the rest of the code starts to look different. You begin to ask where the pool lives, how it is closed, and what happens if a query times out or fails halfway through.

That same mindset applies to the schema. A table or collection should reflect how the application really reads and writes data. If the app fetches users by email all day, make that lookup easy. If a relationship matters, model it in a way that keeps joins or references understandable. Good database code does not just store information. It makes the common paths predictable, which helps both performance and maintainability.

Query Safety And Shape

Parameterized queries are one of the most important habits in any Node.js database app. They keep user input out of SQL syntax and make the intent of the query clear. Once you get used to passing values separately, it becomes natural to think about the query itself and the data that fills it. That separation also makes tests simpler because the structure of the query can be checked without building a string by hand.

It is also smart to keep result shape narrow. Fetch only the columns you need, and map the result into a shape that matches the rest of the app. That reduces data transfer and keeps the service layer from depending on database-specific details everywhere. If one function returns raw rows and another returns a normalized object, the rest of the code has to remember two formats. A consistent boundary makes the rest of the application much easier to read.

Operational Habits

Database work does not end when the query succeeds. Long-running services need timeouts, shutdown logic, and a plan for connection failures. If the process is terminating, close the pool cleanly so work can finish in order. If a query fails, log the useful part of the error and decide whether the caller should retry or show a message. These small decisions matter because production issues usually happen at the edges, not during the happy path demo.

The other habit worth keeping is to test against realistic data shapes. A query that looks fine with a tiny fixture may behave differently with empty strings, long text, missing fields, or duplicate values. When you keep the model close to how the app is used, the code tends to stay honest. That saves time later because you are less likely to discover a mismatch only after the feature is already in front of users.

Both PostgreSQL and MongoDB are excellent choices for Node.js applications. PostgreSQL offers strong 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.

Final Database Note

It is often useful to think of the database layer as a contract between your app and its data. The contract should be clear about what gets written, what gets read, and what happens when the data is not there. When that contract stays simple, the rest of the application can move faster because the data shape is easier to trust and easier to test.

Keeping the contract narrow also makes migrations easier later. If the app depends on a few predictable query paths, changing the storage engine or schema does not force a rewrite everywhere else.

See Also

• Node.js Essentials: Building a Web App with Express
• Working with the File System in Node.js
• Getting Started with Node.js