Node.js

Database Integration in Node.js 🗄️

1. Introduction 👋

Almost every real-world Node.js application needs to persist data somewhere. This tutorial covers database fundamentals, the most popular databases and drivers in the Node.js ecosystem, and the ORMs that make working with them safer and more productive — from raw connections all the way to migrations and query optimization.

Information

This tutorial assumes basic TypeScript familiarity. See the "Node.js with TypeScript" tutorial for foundational typing concepts.

2. Database Fundamentals 🧱

Databases fall broadly into a few categories, each optimized for different data shapes and access patterns.

Relational (SQL) — structured tables with fixed schemas and strong relationships.
Document (NoSQL) — flexible, JSON-like documents with dynamic schemas.
Key-value — extremely fast lookups by key, often used for caching (e.g. Redis).
Embedded — lightweight, file-based databases requiring no separate server (e.g. SQLite).

Tip

Choose your database based on your data shape and access patterns first — popularity or familiarity should be secondary factors.

3. SQL vs NoSQL ⚖️

Aspect	SQL	NoSQL
Schema	Fixed, enforced	Flexible, dynamic
Relationships	Strongvia joins & foreign keys	Often denormalized
Consistency	ACID by default	Varies (often eventual)
Scaling	Primarily vertical	Often horizontal
Examples	PostgreSQL, MySQL	MongoDB, Redis

Best Practice

When in doubt, start with a relational database — it enforces data integrity by default and handles most application needs well.

4. Database Drivers 🔌

A database driver is the low-level library that handles the actual network protocol and communication with the database server.

Terminal

npm install pg          # PostgreSQL
npm install mysql2      # MySQL
npm install mongodb     # MongoDB
npm install ioredis     # Redis

Note

ORMs are built on top of drivers — you can always drop down to the raw driver for performance-critical or highly custom queries.

5. Connection Management 🔗

Establishing a database connection is relatively expensive, so connections should be created once and reused across requests.

src/db/client.ts

import { Client } from "pg";

const client = new Client({ connectionString: process.env.DATABASE_URL });

async function connect(): Promise<void> {
  await client.connect();
  console.log("Connected to PostgreSQL ✅");
}

Warning

A single shared Client works for scripts, but production servers should use a connection pool instead (see Section 6).

6. Connection Pooling 🏊

A connection pool maintains a set of reusable, open connections, avoiding the overhead of establishing a new one per request.

src/db/pool.ts

import { Pool } from "pg";

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

export async function query<T>(text: string, params: unknown[] = []): Promise<T[]> {
  const { rows } = await pool.query(text, params);
  return rows;
}

Tip

Set the pool's max size based on your database's connection limit divided across all running app instances.

7. MongoDB 🍃

MongoDB is a document-oriented NoSQL database storing data as flexible, JSON-like BSON documents.

src/db/mongo.ts

import { MongoClient } from "mongodb";

const client = new MongoClient(process.env.MONGO_URL as string);
await client.connect();

const db = client.db("myapp");
const users = db.collection("users");

const user = await users.findOne({ email: "jane@example.com" });
await users.insertOne({ name: "Jane", email: "jane@example.com" });

Information

MongoDB documents don't require a fixed schema, which gives flexibility but shifts validation responsibility to the application layer.

8. PostgreSQL 🐘

PostgreSQL is a powerful, open-source relational database known for standards compliance, extensibility, and reliability.

src/db/postgres.ts

import { Pool } from "pg";

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

const { rows } = await pool.query(
  "SELECT id, name FROM users WHERE active = $1",
  [true]
);

Tip

PostgreSQL supports advanced features like JSONB columns, full-text search, and window functions — often removing the need for a separate NoSQL database.

9. MySQL 🐬

MySQL is another widely used open-source relational database, popular for its simplicity and broad hosting support.

src/db/mysql.ts

import mysql from "mysql2/promise";

const pool = mysql.createPool({
  host: "localhost",
  user: "root",
  database: "myapp",
  connectionLimit: 10,
});

const [rows] = await pool.query("SELECT * FROM users WHERE id = ?", [1]);

Note

mysql2 is the preferred modern driver over the older mysql package — it supports promises and prepared statements natively.

10. SQLite 🪶

SQLite is a lightweight, file-based, embedded database — ideal for local development, testing, or small applications with no separate server.

src/db/sqlite.ts

import Database from "better-sqlite3";

const db = new Database("app.db");

db.exec("CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)");
const insert = db.prepare("INSERT INTO users (name) VALUES (?)");
insert.run("Jane");

Tip

better-sqlite3 is synchronous by design, which is fine for SQLite's use cases since it involves no network I/O.

11. Redis 🔴

Redis is an in-memory key-value store, commonly used for caching, session storage, pub/sub messaging, and rate limiting.

src/db/redis.ts

import Redis from "ioredis";

const redis = new Redis(process.env.REDIS_URL as string);

await redis.set("session:abc123", JSON.stringify({ userId: 1 }), "EX", 3600);
const session = await redis.get("session:abc123");

Important

Redis persists data optionally, but it's primarily an in-memory store — don't treat it as your primary system of record for critical data.

12. Prisma ORM 💎

Prisma generates a fully typed client from a declarative schema file, giving excellent autocomplete and compile-time safety.

prisma/schema.prisma

model User {
  id    String @id @default(uuid())
  name  String
  email String @unique
}

src/db/prisma-client.ts

import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

const user = await prisma.user.create({
  data: { name: "Jane", email: "jane@example.com" },
});

Best Practice

Run npx prisma generate after every schema change to keep the generated client in sync with your models.

13. Drizzle ORM 🌊

Drizzle is a lightweight, SQL-first ORM that stays close to raw SQL while providing full TypeScript type inference.

src/db/schema.ts

import { pgTable, serial, text, varchar } from "drizzle-orm/pg-core";

export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  name: text("name").notNull(),
  email: varchar("email", { length: 255 }).unique().notNull(),
});

src/db/queries.ts

import { db } from "./client.js";
import { users } from "./schema.js";
import { eq } from "drizzle-orm";

const user = await db.select().from(users).where(eq(users.email, "jane@example.com"));

Tip

Drizzle's query syntax mirrors SQL closely, making it a great choice when you want fine-grained control without writing raw query strings.

14. Mongoose 🦫

Mongoose is the most popular ODM (Object-Document Mapper) for MongoDB, providing schemas, validation, and middleware hooks.

src/models/User.ts

import { Schema, model } from "mongoose";

interface IUser {
  name: string;
  email: string;
}

const userSchema = new Schema<IUser>({
  name: { type: String, required: true },
  email: { type: String, required: true, unique: true },
});

export const User = model<IUser>("User", userSchema);

src/index.ts

const user = await User.create({ name: "Jane", email: "jane@example.com" });
const found = await User.findOne({ email: "jane@example.com" });

15. Sequelize 📘

Sequelize is a mature, promise-based ORM supporting PostgreSQL, MySQL, SQLite, and more, with a rich feature set for relational data.

src/models/User.ts

import { DataTypes, Model } from "sequelize";
import { sequelize } from "../db/connection.js";

class User extends Model {
  declare id: number;
  declare name: string;
  declare email: string;
}

User.init(
  {
    name: { type: DataTypes.STRING, allowNull: false },
    email: { type: DataTypes.STRING, unique: true, allowNull: false },
  },
  { sequelize, modelName: "User" }
);

16. TypeORM 🅃

TypeORM uses decorators to define entities, closely resembling patterns familiar to developers coming from Java or C# backgrounds.

src/entities/User.ts

import { Entity, PrimaryGeneratedColumn, Column } from "typeorm";

@Entity()
export class User {
  @PrimaryGeneratedColumn("uuid")
  id!: string;

  @Column()
  name!: string;

  @Column({ unique: true })
  email!: string;
}

Note

TypeORM requires experimentalDecorators and emitDecoratorMetadata to be enabled in tsconfig.json.

17. CRUD Operations 🔄

Create, Read, Update, and Delete form the foundation of nearly all database interactions.

Prisma

Mongoose

CRUD with Prisma

await prisma.user.create({ data: { name: "Jane", email: "jane@x.com" } });
await prisma.user.findMany();
await prisma.user.update({ where: { id }, data: { name: "Janet" } });
await prisma.user.delete({ where: { id } });

CRUD with Mongoose

await User.create({ name: "Jane", email: "jane@x.com" });
await User.find();
await User.updateOne({ _id: id }, { name: "Janet" });
await User.deleteOne({ _id: id });

18. Transactions 🔁

Transactions group multiple operations into a single atomic unit — either all succeed, or none take effect.

src/db/transaction.ts

await prisma.$transaction(async (tx) => {
  await tx.account.update({ where: { id: fromId }, data: { balance: { decrement: 100 } } });
  await tx.account.update({ where: { id: toId }, data: { balance: { increment: 100 } } });
});

Important

Any error thrown inside a transaction callback automatically triggers a rollback of all operations within it.

19. Migrations 🚚

Migrations track and apply incremental changes to your database schema in a version-controlled, repeatable way.

Terminal

npx prisma migrate dev --name add_users_table
npx prisma migrate deploy   # production

prisma

schema.prisma

migrations

20240101_add_users_table

migration.sql

Best Practice

Never edit a migration file that's already been applied to production — create a new migration to make further changes.

20. Seeding 🌱

Seed scripts populate a database with initial or sample data, useful for local development and testing environments.

prisma/seed.ts

import { PrismaClient } from "@prisma/client";

const prisma = new PrismaClient();

async function main() {
  await prisma.user.createMany({
    data: [
      { name: "Alice", email: "alice@example.com" },
      { name: "Bob", email: "bob@example.com" },
    ],
    skipDuplicates: true,
  });
}

main().finally(() => prisma.$disconnect());

21. Query Optimization 🔍

Poorly written queries are a leading cause of slow applications. A few habits go a long way toward keeping queries fast.

Select only the columns you need instead of SELECT *.
Avoid N+1 queries by batching or using JOINs / include.
Use EXPLAIN ANALYZE to inspect a query's execution plan.
Paginate large result sets instead of fetching everything at once.

src/db/n-plus-one-fix.ts

// ❌ N+1 — one query per user
for (const user of users) {
  user.orders = await getOrdersByUserId(user.id);
}

// ✅ Single batched query with Prisma's include
const usersWithOrders = await prisma.user.findMany({ include: { orders: true } });

22. Indexing 📇

Indexes dramatically speed up lookups on frequently queried columns, at the cost of slightly slower writes.

Create an index

CREATE INDEX idx_users_email ON users(email);

prisma/schema.prisma

model User {
  id    String @id @default(uuid())
  email String @unique
  @@index([email])
}

Tip

Index columns used in WHERE, JOIN, and ORDER BY clauses — but avoid over-indexing tables that are written to very frequently.

23. Data Validation ✅

Database constraints alone aren't enough — validating data at the application layer catches errors earlier with clearer messages.

src/validation/user.ts

import { z } from "zod";

const userSchema = z.object({
  name: z.string().min(1),
  email: z.string().email(),
});

function createUser(input: unknown) {
  const data = userSchema.parse(input);
  return prisma.user.create({ data });
}

Best Practice

Validate at the application layer for good UX, and enforce constraints at the database layer as the ultimate safety net.

24. Error Handling ⚠️

Database errors — constraint violations, connection failures, timeouts — must be caught and handled gracefully.

src/db/errors.ts

import { Prisma } from "@prisma/client";

try {
  await prisma.user.create({ data: { name: "Jane", email: "jane@example.com" } });
} catch (error) {
  if (error instanceof Prisma.PrismaClientKnownRequestError && error.code === "P2002") {
    throw new Error("Email already in use");
  }
  throw error;
}

Caution

Never expose raw database error messages directly to end users — they can leak internal schema details.

25. Performance Optimization ⚡

Use connection pooling in all production environments.
Cache frequently-read, rarely-changed data with Redis or an in-memory cache.
Batch writes where possible instead of issuing many individual INSERT statements.
Monitor slow queries and add indexes based on real query patterns, not guesses.

Reference

See the dedicated "Performance & Optimization" tutorial for a deeper dive into caching, pooling, and profiling techniques.

26. Best Practices ✅

Always use parameterized queries or an ORM to prevent SQL injection.
Wrap multi-step writes in transactions to preserve consistency.
Keep schema changes in version-controlled migrations.
Validate data at the application layer before it reaches the database.
Index columns based on actual, observed query patterns.

27. Common Mistakes ⚠️

Mistake	Consequence
Creating a new connection per request	Connection exhaustion under load
Fetching related data in a loop (N+1)	Severe performance degradation
Skipping migrations, editing the DB manually	Schema drift across environments
No indexes on frequently filtered columns	Slow queries as data grows
Trusting client input without validation	Data corruption or injection vulnerabilities

28. Frequently Asked Questions ❓

Question

Should I use an ORM or raw SQL?

Answer

ORMs like Prisma speed up development and reduce bugs for most CRUD-heavy apps; raw SQL or Drizzle gives more control for complex, performance-critical queries.

Question

Is MongoDB a good replacement for SQL databases?

Answer

Not universally — it excels with flexible, document-shaped data, but relational databases are usually a better fit when strong relationships and consistency matter.

Question

How many connections should my pool have?

Answer

Size it based on your database's max connection limit divided by the number of running app instances, not an arbitrarily large number.

Question

Do I need Redis if I already use PostgreSQL?

Answer

Often yes for caching and sessions — PostgreSQL is excellent for persistent data, but Redis is faster for ephemeral, high-frequency reads.

29. Summary 📋

Database integration in Node.js spans choosing the right database for your data, managing connections efficiently, and using ORMs like Prisma, Drizzle, Mongoose, Sequelize, or TypeORM to write safer, more maintainable queries.

Choose SQL or NoSQL based on your data shape and relationships.
Use connection pooling in every production deployment.
Wrap multi-step writes in transactions for consistency.
Track schema changes with migrations, and seed data for development.
Optimize with indexes and batched queries as your data grows.

Summary

A well-integrated database layer is the backbone of any reliable Node.js application — invest in getting connections, schemas, and queries right early. 🗄️

1. Introduction 👋

ℹ️Information

2. Database Fundamentals 🧱

💡Tip

3. SQL vs NoSQL ⚖️

⭐Best Practice

4. Database Drivers 🔌

Terminal

📝Note

5. Connection Management 🔗

src/db/client.ts

⚠️Warning

6. Connection Pooling 🏊

src/db/pool.ts

💡Tip

7. MongoDB 🍃

src/db/mongo.ts

ℹ️Information

8. PostgreSQL 🐘

src/db/postgres.ts

💡Tip

9. MySQL 🐬

src/db/mysql.ts

📝Note

10. SQLite 🪶

src/db/sqlite.ts

💡Tip

11. Redis 🔴

src/db/redis.ts

❗Important

12. Prisma ORM 💎

prisma/schema.prisma

src/db/prisma-client.ts

⭐Best Practice

13. Drizzle ORM 🌊

src/db/schema.ts

src/db/queries.ts

💡Tip

14. Mongoose 🦫

src/models/User.ts

src/index.ts

15. Sequelize 📘

src/models/User.ts

16. TypeORM 🅃

src/entities/User.ts

📝Note

17. CRUD Operations 🔄

CRUD with Prisma

CRUD with Mongoose

18. Transactions 🔁

src/db/transaction.ts

❗Important

19. Migrations 🚚

Terminal

⭐Best Practice

20. Seeding 🌱

prisma/seed.ts

21. Query Optimization 🔍

src/db/n-plus-one-fix.ts

22. Indexing 📇

Create an index

prisma/schema.prisma

💡Tip

23. Data Validation ✅

src/validation/user.ts

⭐Best Practice

24. Error Handling ⚠️

src/db/errors.ts

🟡Caution

25. Performance Optimization ⚡

🔗Reference

26. Best Practices ✅

27. Common Mistakes ⚠️

28. Frequently Asked Questions ❓

❓Question

💬Answer

❓Question

💬Answer

❓Question

💬Answer

Information

Tip

Best Practice

Note

Warning

Tip

Information

Tip

Note

Tip

Important

Best Practice

Tip

Note

Important

Best Practice

Tip

Best Practice

Caution

Reference

Question

Answer

Question

Answer

Question

Answer

Question

Answer

Summary