Connecting Databases

Persisting data beyond memory - connecting Express to PostgreSQL/MySQL with Prisma, or MongoDB with Mongoose, and the connection pooling pattern.

What it is, in plain English: Every REST API example so far stored data in a plain JavaScript array, which resets every time the server restarts. Real applications persist data in a database. Prisma is the most popular modern ORM (Object-Relational Mapper) for SQL databases (PostgreSQL, MySQL, SQLite) -- it generates a type-safe client from a schema file. Mongoose serves the same role for MongoDB. Both let route handlers read and write data without writing raw SQL or MongoDB driver calls by hand.

From in-memory arrays to a real database

Every REST API example so far stored data in a plain array -- simple for learning, but it resets every time the server restarts and can't be shared across multiple server instances. Real apps connect to an actual database.

Prisma -- the modern standard for SQL databases

# npm install prisma @prisma/client
# npx prisma init   -- creates a schema.prisma file and .env entry

// schema.prisma -- define your data shape:
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

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

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

# Create the actual database table from your schema:
npx prisma migrate dev --name init

Using Prisma in Express routes

import { PrismaClient } from "@prisma/client";
const prisma = new PrismaClient();

app.post("/users", async (req, res) => {
  const user = await prisma.user.create({
    data: { name: req.body.name, email: req.body.email },
  });
  res.status(201).json(user);
});

app.get("/users", async (req, res) => {
  const users = await prisma.user.findMany();
  res.json(users);
});

app.get("/users/:id", async (req, res) => {
  const user = await prisma.user.findUnique({
    where: { id: Number(req.params.id) },
  });
  if (!user) return res.status(404).json({ error: "Not found" });
  res.json(user);
});

app.delete("/users/:id", async (req, res) => {
  await prisma.user.delete({ where: { id: Number(req.params.id) } });
  res.status(204).send();
});

MongoDB with Mongoose -- the document-database alternative

// npm install mongoose
import mongoose from "mongoose";
await mongoose.connect(process.env.MONGODB_URI);

const userSchema = new mongoose.Schema({
  name:  { type: String, required: true },
  email: { type: String, required: true, unique: true },
});
const User = mongoose.model("User", userSchema);

app.post("/users", async (req, res) => {
  const user = await User.create(req.body);
  res.status(201).json(user);
});

Try It Yourself (Node.js logic)

// Connecting Express to a database -- the patterns
// (Prisma/Mongoose need a real database -- code shown is reference + simulated logic)

// BEFORE: in-memory array (what every earlier example used)
let users = [];
let nextId = 1;
function createUserInMemory(data) {
  const user = { id: nextId++, ...data };
  users.push(user);
  return user;
}
console.log("In-memory (resets on restart):", createUserInMemory({ name: "Alice" }));

// AFTER: Prisma with PostgreSQL/MySQL/SQLite
const PRISMA_EXAMPLE = `
// schema.prisma -- define your data model:
model User {
  id    Int    @id @default(autoincrement())
  name  String
  email String @unique
  posts Post[]
}

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

// npx prisma migrate dev --name init   <- creates the actual DB tables

// In your Express app:
import { PrismaClient } from "@prisma/client";
const prisma = new PrismaClient();

app.post("/users", async (req, res) => {
  const user = await prisma.user.create({ data: req.body });
  res.status(201).json(user);
});

app.get("/users/:id", async (req, res) => {
  const user = await prisma.user.findUnique({
    where: { id: Number(req.params.id) },
    include: { posts: true },   // join related data
  });
  if (!user) return res.status(404).json({ error: "Not found" });
  res.json(user);
});
`;

console.log("\nPrisma pattern:");
console.log(PRISMA_EXAMPLE.trim());

// Mongoose with MongoDB
const MONGOOSE_EXAMPLE = `
import mongoose from "mongoose";
await mongoose.connect(process.env.MONGODB_URI);

const userSchema = new mongoose.Schema({
  name:  { type: String, required: true },
  email: { type: String, required: true, unique: true },
});
const User = mongoose.model("User", userSchema);

app.post("/users", async (req, res) => {
  const user = await User.create(req.body);
  res.status(201).json(user);
});

app.get("/users/:id", async (req, res) => {
  const user = await User.findById(req.params.id);
  if (!user) return res.status(404).json({ error: "Not found" });
  res.json(user);
});
`;
console.log("\nMongoose pattern:");
console.log(MONGOOSE_EXAMPLE.trim());

Relations, transactions, and connection management

Relations -- linking related data

// schema.prisma
model User {
  id    Int    @id @default(autoincrement())
  name  String
  posts Post[]   // one user has many posts
}
model Post {
  id       Int    @id @default(autoincrement())
  title    String
  author   User   @relation(fields: [authorId], references: [id])
  authorId Int
}

// Querying with relations included:
app.get("/users/:id", async (req, res) => {
  const user = await prisma.user.findUnique({
    where: { id: Number(req.params.id) },
    include: { posts: true },   // JOIN -- includes the user's posts
  });
  res.json(user);
});

// Creating related records in one call:
app.post("/users", async (req, res) => {
  const user = await prisma.user.create({
    data: {
      name: req.body.name,
      posts: { create: req.body.posts },   // create user AND their posts together
    },
    include: { posts: true },
  });
  res.status(201).json(user);
});

Transactions -- all-or-nothing operations

// When multiple writes must ALL succeed or ALL fail together:
app.post("/transfer", async (req, res) => {
  const { fromId, toId, amount } = req.body;

  await prisma.$transaction([
    prisma.account.update({
      where: { id: fromId },
      data: { balance: { decrement: amount } },
    }),
    prisma.account.update({
      where: { id: toId },
      data: { balance: { increment: amount } },
    }),
  ]);
  // If EITHER update fails, BOTH are rolled back -- balance never gets out of sync

  res.json({ success: true });
});

Connection pooling -- why it matters under load

// Each database connection is expensive to open. A connection pool
// reuses a fixed set of open connections across requests instead of
// opening/closing one per request:

// Prisma manages this automatically -- one PrismaClient instance per app:
const prisma = new PrismaClient();  // create ONCE, reuse across all routes

// ✗ DON'T do this -- creates a new client (and connection pool) per request:
app.get("/users", async (req, res) => {
  const prisma = new PrismaClient();   // wrong -- exhausts connections under load
  // ...
});

Raw SQL escape hatches and migration strategy

// When an ORM query can't express something efficiently, drop to raw SQL:
const results = await prisma.$queryRaw\`
  SELECT u.name, COUNT(p.id) as post_count
  FROM "User" u
  LEFT JOIN "Post" p ON p."authorId" = u.id
  GROUP BY u.id
  ORDER BY post_count DESC
  LIMIT 10
\`;
// Tagged template literal -- Prisma parameterizes values automatically,
// preventing SQL injection even in raw queries.

// Connection string anatomy:
// postgresql://user:password@host:5432/database?schema=public&connection_limit=10
// connection_limit controls the pool size -- tune based on your DB's max_connections
// and how many app instances will be running concurrently.

Migration strategy in production

Schema migrations in production require care beyond prisma migrate dev (which is for local development). The production-safe command is prisma migrate deploy, which applies pending migrations without prompting or generating new ones — typically run as a release step in your CI/CD pipeline, before the new application code starts receiving traffic. For zero-downtime migrations on a live system, follow the same backward-compatible-first principle as any schema change: add new columns as optional first, deploy code that uses them, then later remove old columns in a separate deploy — never combine a destructive schema change with the code that depends on it being already gone.

Official Sources

The Codex links only to official documentation. No third-party tutorials.