PostgreSQL with Node.js — pg and Drizzle ORM

npm install pg
npm install --save-dev @types/pg

import { Pool } from "pg";

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

// Test connection
pool.query("SELECT NOW()", (err, result) => {
  if (err) {
    console.error("Connection error:", err);
  } else {
    console.log("Connected at:", result.rows[0]);
  }
});

const pool = new Pool({
  user: "postgres",
  password: process.env.DB_PASSWORD,
  host: process.env.DB_HOST || "localhost",
  port: 5432,
  database: process.env.DB_NAME,
  max: 20, // Maximum connections
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});

// Get connection from pool
const query = async (text: string, params?: any[]) => {
  const start = Date.now();
  try {
    const result = await pool.query(text, params);
    const duration = Date.now() - start;
    console.log(`Executed query (${duration}ms): ${text}`);
    return result;
  } catch (error) {
    console.error("Database error:", error);
    throw error;
  }
};

export default query;

interface User {
  id: number;
  name: string;
  email: string;
  created_at: Date;
}

const getUserById = async (id: number): Promise<User | null> => {
  const result = await pool.query(
    "SELECT * FROM users WHERE id = $1",
    [id]
  );
  return result.rows[0] || null;
};

const createUser = async (name: string, email: string): Promise<User> => {
  const result = await pool.query(
    "INSERT INTO users (name, email, created_at) VALUES ($1, $2, NOW()) RETURNING *",
    [name, email]
  );
  return result.rows[0];
};

const updateUser = async (
  id: number,
  name: string,
  email: string
): Promise<User | null> => {
  const result = await pool.query(
    "UPDATE users SET name = $1, email = $2 WHERE id = $3 RETURNING *",
    [name, email, id]
  );
  return result.rows[0] || null;
};

const deleteUser = async (id: number): Promise<boolean> => {
  const result = await pool.query(
    "DELETE FROM users WHERE id = $1",
    [id]
  );
  return result.rowCount! > 0;
};

const getAllUsers = async (): Promise<User[]> => {
  const result = await pool.query("SELECT * FROM users");
  return result.rows;
};

const transferMoney = async (
  fromUserId: number,
  toUserId: number,
  amount: number
) => {
  const client = await pool.connect();

  try {
    await client.query("BEGIN");

    // Deduct from sender
    await client.query(
      "UPDATE accounts SET balance = balance - $1 WHERE user_id = $2",
      [amount, fromUserId]
    );

    // Add to receiver
    await client.query(
      "UPDATE accounts SET balance = balance + $1 WHERE user_id = $2",
      [amount, toUserId]
    );

    // Record transaction
    await client.query(
      "INSERT INTO transactions (from_user_id, to_user_id, amount) VALUES ($1, $2, $3)",
      [fromUserId, toUserId, amount]
    );

    await client.query("COMMIT");
    console.log("Transfer complete");
  } catch (error) {
    await client.query("ROLLBACK");
    console.error("Transfer failed:", error);
    throw error;
  } finally {
    client.release();
  }
};

npm install drizzle-orm pg
npm install -D drizzle-kit

import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";
import { pgTable, serial, varchar, text, timestamp } from "drizzle-orm/pg-core";

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

export const db = drizzle(pool);

// Define schema
export const users = pgTable("users", {
  id: serial("id").primaryKey(),
  name: varchar("name", { length: 256 }).notNull(),
  email: varchar("email", { length: 256 }).unique().notNull(),
  createdAt: timestamp("created_at").defaultNow(),
});

export const posts = pgTable("posts", {
  id: serial("id").primaryKey(),
  title: varchar("title", { length: 256 }).notNull(),
  content: text("content"),
  userId: serial("user_id").references(() => users.id),
  createdAt: timestamp("created_at").defaultNow(),
});

import { db, users, posts } from "./schema";
import { eq, like } from "drizzle-orm";

// Select all
const allUsers = await db.select().from(users);

// Select with where
const user = await db
  .select()
  .from(users)
  .where(eq(users.id, 1));

// Insert
const newUser = await db.insert(users).values({
  name: "Alice",
  email: "alice@example.com",
}).returning();

// Update
await db
  .update(users)
  .set({ name: "Bob" })
  .where(eq(users.id, 1));

// Delete
await db.delete(users).where(eq(users.id, 1));

// Join
const userWithPosts = await db
  .select()
  .from(users)
  .leftJoin(posts, eq(users.id, posts.userId))
  .where(eq(users.id, 1));

// Aggregate
const count = await db
  .select({ count: sql`count(*)` })
  .from(users);

# Generate migration
drizzle-kit generate:pg

# Apply migration
drizzle-kit migrate:pg

// drizzle.config.ts
import type { Config } from "drizzle-kit";

export default {
  schema: "./src/schema.ts",
  out: "./drizzle",
  driver: "pg",
  dbCredentials: {
    connectionString: process.env.DATABASE_URL!,
  },
} satisfies Config;

const pool = new Pool({
  max: 20,
  idleTimeoutMillis: 30000,
  statement_timeout: 30000, // 30 second query timeout
});

// Monitor pool
pool.on("error", (err) => {
  console.error("Unexpected error on idle client", err);
});