Databases, Testing, and DeploymentLesson 6.1

Connecting Node.js to PostgreSQL with pg or Prisma

pg library, connection pools, parameterized queries, SQL injection prevention, Prisma ORM setup, Prisma schema, migrations, Prisma Client

Two Ways to Talk to PostgreSQL

Use pg for direct SQL control. Use Prisma for a type-safe ORM with migrations. Prisma is the modern choice for new projects.

npm install @prisma/client
npx prisma init

// schema.prisma
model User {
  id        Int      @id @default(autoincrement())
  email     String   @unique
  name      String
  createdAt DateTime @default(now())
}

npx prisma migrate dev --name init
npx prisma generate

const { PrismaClient } = require('@prisma/client');
const prisma = new PrismaClient();

const user = await prisma.user.create({
  data: { email: 'alice@example.com', name: 'Alice' }
});

const users = await prisma.user.findMany({
  where: { name: { contains: 'Alice' } },
  orderBy: { createdAt: 'desc' },
  take: 10
});

await prisma.user.update({
  where: { id: 1 },
  data: { name: 'Alice Updated' }
});