Working with Databases from Node

After this lesson, you will be able to: Use Prisma ORM, raw SQL with node-postgres, and configure connection pooling.

Most Node apps end up talking to Postgres. Knowing both ORM + raw makes you fluent.

Prisma (TS-native ORM)

Declare schema → generate typed client → query.

python

// prisma/schema.prisma
// datasource db {
//   provider = "postgresql"
//   url      = env("DATABASE_URL")
// }
// model User {
//   id    String  @id @default(cuid())
//   email String  @unique
//   name  String
//   posts Post[]
// }
// model Post {
//   id     String @id @default(cuid())
//   title  String
//   user   User   @relation(fields: [userId], references: [id])
//   userId String
// }

// Generate: npx prisma generate
// Migrate: npx prisma migrate dev --name init

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

// Fully typed
const users = await prisma.user.findMany({
  where: { email: { contains: "@example.com" } },
  include: { posts: true },
  take: 10,
});

Raw SQL with pg

When the ORM is in the way.

python

import { Pool } from "pg";

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

const result = await pool.query(
  "SELECT id, name FROM users WHERE email = $1",
  [email],
);
console.log(result.rows);

// Transactions
const client = await pool.connect();
try {
  await client.query("BEGIN");
  await client.query("UPDATE accounts SET balance = balance - 50 WHERE id = $1", [from]);
  await client.query("UPDATE accounts SET balance = balance + 50 WHERE id = $1", [to]);
  await client.query("COMMIT");
} catch (e) {
  await client.query("ROLLBACK");
  throw e;
} finally {
  client.release();
}

Connection pooling

Each open Postgres connection costs memory (~10MB). With many concurrent requests, you exhaust the DB. Pool: app reuses a small number of connections. `pg` does this automatically with `Pool`. Serverless caveat: each Lambda / Edge invocation can open its own pool, hitting the DB max_connections fast. Solution: RDS Proxy (AWS), Supabase Pooler, PgBouncer. Default for Node app servers: pool of 5-20 connections per app instance.

Common mistakes only experienced backend devs avoid

Concatenating SQL with user input. SQL injection. ALWAYS use $1 / $2 / ... parameters. Forgetting `client.release()`. Pool leaks; eventual exhaustion. Long-running transactions. Block other writes. Not closing the pool on shutdown. Hung processes.

Quick Check

Why use $1 parameters instead of string concatenation in SQL?

Pick the security answer.

←Authentication in Node

Back to Node.js

File Uploads, Email, and Third-Party APIs→