Learning Node.js - Database Connections with Drivers or ORMs

#Introduction

Choosing a database is only the first step. To actually use it from Node.js, you must connect the two through a driver or an ORM. A driver gives you full control over raw SQL, while an ORM provides models and type safety on top of it.

Episode 14 puts both approaches into practice with PostgreSQL: the basic connection and connection pool using the pg driver, then models with the Prisma ORM, connection string management, and migrations. You'll understand the difference and when to use each.

#Connecting with the PostgreSQL Driver

#Connection Pooling

The pg driver is the classic choice for PostgreSQL in Node.js. Instead of creating a connection for every query, use a Pool that keeps a set of connections and lends them out when needed:

npm install pg

npm install pg adds the official PostgreSQL driver. Note: every database connection is expensive — establishing one costs time and resources. A pool solves this by reusing connections.

#Your First Query

Connect with the pg Pool

import pg from "pg";
 
const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });
 
const hasil = await pool.query("SELECT 1 AS angka");
console.log(hasil.rows);
 
await pool.end();

new pg.Pool({ connectionString: process.env.DATABASE_URL }) creates a pool from the connection string in an environment variable. pool.query("SELECT 1 AS angka") sends SQL and returns rows. Don't forget pool.end() when the application stops, so the connections are cleaned up.

#Safe Queries with Parameterized Queries

#Avoiding SQL Injection

Never insert values into a query with string concatenation. Use parameter placeholders for security and clarity:

Query with parameters

const hasil = await pool.query(
  "SELECT * FROM pengguna WHERE email = $1",
  [email],
);

The $1 placeholder is filled from the second argument array, so input values are sent separately from the SQL and can't inject malicious commands. This is the main shield against SQL injection — we'll discuss it more deeply in episode 20.

#Using the Prisma ORM

#Models as Code

An ORM translates a database schema into objects you can use in JavaScript. Prisma is a modern Node.js ORM with type safety and complete tooling:

npm install prisma --save-dev
npx prisma init --datasource-provider postgresql

npx prisma init --datasource-provider postgresql creates a prisma folder with a schema.prisma file and adds DATABASE_URL to .env. The schema is the source of truth — you describe models, and Prisma generates the client and migrations.

#Defining a Model

model Pengguna {
  id        Int     @id @default(autoincrement())
  email     String  @unique
  nama      String
  createdAt DateTime @default(now())
}

The Pengguna model above marks id as the primary key, email as unique, and createdAt automatically filled with the current time. This declarative style replaces the manual CREATE TABLE command from episode 13 — and produces a type-safe TypeScript client.

#Connection Strings and Migrations

#Connection String Format

A PostgreSQL connection string follows a fixed format:

postgresql://username:password@host:port/nama_database

Store this value in .env as DATABASE_URL. The password part should contain encoded characters if it includes special characters. Never commit the .env file to a repository.

#Running Migrations

Schema changes are managed as migrations — a series of changes that can be applied to many databases in a definite order:

npx prisma migrate dev --name init

npx prisma migrate dev --name init creates a migration file from the schema, applies it to the development database, then generates the client. Every subsequent model change produces a new migration — this is how teams keep schemas in sync across environments.

#Driver or ORM: When to Choose

#Three Considerations

• Driver: for full SQL control, complex queries, and maximum performance.
• ORM: for development speed, type safety, and automated migrations.
• Combination: many teams use an ORM for the main models and a driver for special queries.

There's no wrong answer — what matters is consistency and fit for the project's needs. Equally important: whatever you choose, the connection string stays in an environment variable and input queries are always parameterized.

#Closing

Here's what to take away:

• pg.Pool reuses connections, so queries are more efficient.
• Parameterized queries with placeholders prevent SQL injection.
• Prisma describes models in schema.prisma and generates a client.
• DATABASE_URL is stored in an environment variable, not in code.
• Migrations are applied with npx prisma migrate dev.
• Drivers give full control; ORMs give speed and type safety.

In the next episode, episode 15, we'll discuss models, schemas, and data validation — designing a good schema in Prisma, validating request input with Zod, and applying validation in Express routes before data touches the database. Validation is the second line of defense after safe queries.