PostgreSQL: From Raw SQL to Type-Safe Production

PostgreSQL (often called Postgres) is a relational SQL database. That means your data lives in tables made up of rows and columns, with a strong focus on data integrity, constraints, and correctness.

Why PostgreSQL?

• Open-source and battle-tested
• ACID compliant (data safety matters)
• Excellent performance for complex queries
• Works extremely well with Node.js backends

Using PostgreSQL with Node.js

In Node.js, the most common and reliable way to connect to PostgreSQL is the pg package.

Install pg

npm install pg

Database Connection Setup

Create a reusable database connection using Pool.

const { Pool } = require("pg");

const pool = new Pool({
  user: "postgres",
  password: "your-password",
  host: "localhost",
  port: 5432,
  database: "your-dbname",
});

module.exports = pool;

Why Pool?

• Manages multiple connections efficiently
• Prevents unnecessary reconnections
• Scales better than single-client connections

Writing SQL Queries in Node.js

All queries are executed using pool.query().

Select all rows

pool.query("SELECT * FROM sample");

Select with condition (parameterized query)

pool.query("SELECT * FROM sample WHERE name = $1", [name]);

Update data

pool.query(
  "UPDATE sample SET description = $1 WHERE name = $2 RETURNING *",
  [description, name]
);

Delete data

pool.query("DELETE FROM sample WHERE name = $1", [name]);

Important: Always use parameterized queries ($1, $2) to avoid SQL injection. If you’re not doing this, you’re doing it wrong.

Creating Tables in PostgreSQL

Before using a table, you need to define it.

Simple table

CREATE TABLE sample (
  name VARCHAR(25),
  description VARCHAR(50)
);

Common PostgreSQL Data Types

Postgres supports many powerful data types. Here’s a practical example:

CREATE TABLE products (
  product_id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  price NUMERIC(10, 2),
  is_active BOOLEAN,
  created_date DATE,
  description TEXT
);

Insert data

INSERT INTO products (name, price, is_active, created_date, description)
VALUES ('Laptop', 999.99, TRUE, '2025-01-17', 'A new laptop model.');

Express API with PostgreSQL

A simple Express route that fetches data from PostgreSQL.

const express = require("express");
const app = express();
const pool = require("./db");

app.get("/todos", async (req, res) => {
  try {
    const result = await pool.query("SELECT * FROM sample");
    res.json(result.rows);
  } catch (error) {
    console.error(error);
    res.status(500).json({ error: "Server error" });
  }
});

What’s happening here?

• Query runs asynchronously
• result.rows contains actual data
• Errors are handled cleanly

Final Notes

• PostgreSQL is not case-sensitive for keywords, but identifiers can be if quoted.
• Always use proper data types instead of forcing everything into VARCHAR.
• Use indexes when your tables grow.
• If you skip parameterized queries, you’re inviting security issues.

PostgreSQL isn’t trendy. It’s reliable, boring, and powerful — exactly what you want in a database.

Difficulties When Using PostgreSQL with Node.js

Using PostgreSQL directly with Node.js is powerful, but it’s not perfect. Let’s be honest about the pain points.

1. Writing Raw SQL in Strings (No Type Safety)

When you use pg, you write raw SQL inside JavaScript strings.

pool.query("SELECT * FROM users WHERE id = $1", [id]);

Problems with this approach:

• ❌ No compile-time type checking
• ❌ Typos in column names fail only at runtime
• ❌ SQL errors surface late (usually in production if you’re careless)
• ❌ Refactoring is risky and manual

Your editor and TypeScript cannot help you much here. You’re basically trusting yourself to not screw up.

ORMs – Why They Exist

To solve these issues, ORMs (Object Relational Mappers) exist. They sit between your app and the database and reduce direct SQL usage.

ORMs / Query Builders to Fix This

To reduce these problems, developers use ORMs or type-safe query builders instead of raw SQL.

Two Modern Approaches (What You Actually See Today)

1. Prisma A schema-first, fully type-safe ORM that generates a client from your database schema.

   One line: You define the schema once, Prisma generates safe queries for you.

2. Drizzle ORM A SQL-first, lightweight ORM / query builder with strong TypeScript inference.

   One line: You write SQL-like code, and TypeScript infers everything.

Comparison

Note

• pg + raw SQL → fastest, most error-prone
• Prisma → safe, productive, but hides SQL
• Drizzle → nearly raw SQL with safety

If you don’t understand SQL, Prisma will save you. If you understand SQL and want control without shooting yourself, Drizzle is the better tool.

Pick based on how much control you actually need, not hype.

NEON

Neon is a fully managed, serverless PostgreSQL that separates compute from storage. This gives you:

• Instant database branching
• Autoscaling
• Scale-to-zero (no idle cost)

In short: Postgres without babysitting servers.

Using Neon with Drizzle ORM

Here, we’ll use Drizzle ORM because it keeps you close to SQL while still giving type safety. This is not magic — it’s just safer SQL.

Install Required Packages

npm install drizzle-orm pg

Neon Connection Setup

Neon provides a connection string. You don’t manage hosts, ports, or pooling manually.

db.ts

import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";

const pool = new Pool({
  connectionString: process.env.DATABASE_URL, // Neon connection URL
  ssl: true,
});

export const db = drizzle(pool);

Reality check:

• Neon requires SSL
• Pooling still matters, even in serverless

Define Schema (Type-Safe Tables)

Drizzle is SQL-first, but schemas live in TypeScript.

schema.ts

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

export const sample = pgTable("sample", {
  name: varchar("name", { length: 25 }).notNull(),
  description: text("description"),
});

This is the single source of truth. If you typo a column later, TypeScript will stop you immediately.

Querying with Drizzle

Select all rows

import { db } from "./db";
import { sample } from "./schema";

const result = await db.select().from(sample);

Select with condition

import { eq } from "drizzle-orm";

const result = await db
  .select()
  .from(sample)
  .where(eq(sample.name, "Kishore"));

Insert data

await db.insert(sample).values({
  name: "Task 1",
  description: "Learning Drizzle with Neon",
});

Update data

await db
  .update(sample)
  .set({ description: "Updated description" })
  .where(eq(sample.name, "Task 1"));

Delete data

await db.delete(sample).where(eq(sample.name, "Task 1"));

Express API Example (Neon + Drizzle)

import express from "express";
import { db } from "./db";
import { sample } from "./schema";

const app = express();

app.get("/todos", async (req, res) => {
  try {
    const todos = await db.select().from(sample);
    res.json(todos);
  } catch (err) {
    res.status(500).json({ error: "Database error" });
  }
});

Why This Stack Actually Makes Sense

• Neon → Zero infra headache
• Drizzle → Type-safe SQL, no abstraction nonsense
• PostgreSQL → Still real SQL, not toy queries
• Prisma hides SQL — good for speed, bad for understanding
• Drizzle forces you to know SQL, but won’t let you shoot yourself
• Neon removes ops pain, not database responsibility

Source code: @kishore-sv/postgre-sql

😊 Thanks for reading