postgre-records

A lightweight, type-safe PostgreSQL ORM for TypeScript with advanced query building, full-text search, and comprehensive error handling.

English | 한국어

✨ Features

• 🔒 Full Type Safety - Complete type inference using TypeScript generics
• 🔍 Advanced Query Builder - WHERE conditions with multiple operators
• 🔎 Full-Text Search - LIKE, ILIKE, and PostgreSQL FTS support
• 🛡️ Comprehensive Error Handling - PostgreSQL error code classification with retry logic
• 🔄 Automatic Case Conversion - camelCase ↔ snake_case automatic conversion
• 🚀 UPSERT Support - ON CONFLICT DO UPDATE
• 🎯 Repository Pattern - Clean architecture support
• 📊 Flexible Querying - Support for complex WHERE conditions with IN/NOT IN operators

📦 Installation

npm install postgre-records pg
# or
yarn add postgre-records pg
# or
pnpm add postgre-records pg

Peer Dependencies

npm install pg @types/pg dotenv

🚀 Quick Start

Step 1: Environment Setup

Create a .env file in your project root:

# Database Configuration
DB_HOST=localhost
DB_PORT=5432
DB_NAME=mydb
DB_USER=postgres
DB_PASSWORD=yourpassword

Step 2: Define Your Types

// /interface/User.ts
const = userKeys = ['id', 'email', 'name', 'age', 'status', 'role', 'createdAt', 'updatedAt'] as const;
interface User {
  id: number;
  email: string;
  name: string;
  age: number;
  status: string;
  role: string;
  createdAt: Date;
  updatedAt: Date;
}
type UserAutoSetKeys = "id" | "createdAt" | "updatedAt";

Step 3: Create a Service

// /services/userService.ts
import { createService } from 'postgre-records';
export const userService = createService<User, UserAutoSetKeys>({
  table: 'account.users',  // schema.table format
  keys: userKeys
});

Step 4: Use CRUD Route

// /v1/users
import { Router } from "express";
import { userService } from '../services/userService';

const usersRouter = Router({ mergeParams: true });

// GET / Read
usersRouter.get("/", async (req, res) => {
  const results = await userService.read();
  res.status(200).json(results)
})

// GET :id / Read
usersRouter.get("/:id", async (req, res) => {
  const { id } = req.params;
  const results = await userService.read({ id: +id});
  res.status(200).json(results)
})

// POST / Create
usersRouter.post("/", async (req, res) => {
  const data = req.body;
  // need, type guard
  const results = await userService.create(data);
  res.status(201).json(results)
})

// PATCH / PUT :id / Update
usersRouter.patch("/:id", async (req, res) => {
  const { id } = req.params;
  const data = req.body;
  // need, type guard
  const results = await userService.update({ id: +id}, data);
  res.status(200).json(results)
})

// DELETE :id / Delete
usersRouter.delete("/:id", async (req, res) => {
  const { id } = req.params;
  const results = await userService.delete({ id: +id});
  res.status(200).json(results)
})

Step 5: 📖 Advanced Usage

5-1. Difference between read() and readOne()

read(where?) - Returns an array

// Returns an array (User[])
const users = await userService.read();
// Result: [{ id: 1, name: 'John' }, { id: 2, name: 'Jane' }, ...]

const usersByRole = await userService.read({ role: 'admin' });
// Result: [{ id: 1, name: 'Admin1' }, { id: 3, name: 'Admin2' }, ...]

readOne(where?) - Returns a single object or undefined

// Returns a single object (User | undefined)
const user = await userService.readOne({ id: 1 });
// Result: { id: 1, name: 'John', email: '[email protected]' }

// When using with Primary Key (id), use readOne for better performance
usersRouter.get("/:id", async (req, res) => {
  const { id } = req.params;
  const user = await userService.readOne({ id: +id }); // Returns object or undefined

  if (!user) { return res.status(404).json({ error: "User not found" }) }
  res.status(200).json(user); // Single object, not array
})

💡 Best Practice:

• Use read() when you expect multiple results (returns array)
• Use readOne() when you query by Primary Key (PK) or expect a single result (returns object)

5-2. Advanced Query Conditions with Operators

You can use various operators for complex WHERE conditions:

// Greater than / Less than
const adults = await userService.read({
  age: { '>': 18 } 
});

// Multiple conditions
const activeAdmins = await userService.read({
  role: 'admin',
  status: 'active',
  age: { '>=': 21 }
});

// IN operator - Multiple values
const specificUsers = await userService.read({
  id: { 'IN': [1, 2, 3, 4, 5] }
});

const moderators = await userService.read({
  role: { 'IN': ['admin', 'moderator', 'super_admin'] }
});

// NOT IN operator
const nonDeletedUsers = await userService.read({
  status: { 'NOT IN': ['deleted', 'banned', 'suspended'] }
});

// LIKE / ILIKE for text search
const usersNamedJohn = await userService.read({
  name: { 'LIKE': '%John%' }  // Case-sensitive
});

const usersWithGmail = await userService.read({
  email: { 'ILIKE': '%@gmail.com' }  // Case-insensitive
});

// Not equal
const nonAdminUsers = await userService.read({
  role: { '!=': 'admin' }
});

Supported Operators:

• '=' - Equal (default)
• '!=' or '<>' - Not equal
• '>' - Greater than
• '<' - Less than
• '>=' - Greater than or equal
• '<=' - Less than or equal
• 'LIKE' - Pattern matching (case-sensitive)
• 'ILIKE' - Pattern matching (case-insensitive)
• 'IN' - Match any value in array
• 'NOT IN' - Not match any value in array

5-3. Direct SQL Queries with queryHandler

For complex queries or when you need direct SQL control, use queryHandler:

import { queryHandler } from 'postgre-records';

// Simple query
const users = await queryHandler('SELECT * FROM users');

// Parameterized query with ? (MySQL-style)
const user = await queryHandler('SELECT * FROM users WHERE id = ?', [1]);

// Multiple parameters
const activeUsers = await queryHandler(
  'SELECT * FROM users WHERE age > ? AND status = ?',
  [18, 'active']
);

// PostgreSQL-style ($1, $2) also works
const users = await queryHandler(
  'SELECT * FROM users WHERE age > $1 AND status = $2',
  [18, 'active']
);

// INSERT query
const newUser = await queryHandler(
  'INSERT INTO users (email, name) VALUES (?, ?) RETURNING *',
  ['[email protected]', 'John Doe']
);

// Complex JOIN query
const userOrders = await queryHandler(`
  SELECT u.name, o.total, o.created_at
  FROM users u
  INNER JOIN orders o ON u.id = o.user_id
  WHERE u.status = ? AND o.total > ?
`, ['active', 100]);

// With TypeScript types
interface User {
  id: number;
  email: string;
  name: string;
}

const users = await queryHandler<User>('SELECT * FROM users');

💡 Key Features:

• Supports both ? (MySQL-style) and $1, $2 (PostgreSQL-style) placeholders
• Automatic placeholder conversion from ? to $1, $2
• Type-safe with TypeScript generics
• Perfect for complex queries, JOINs, and custom SQL

📐 Naming Conventions & Best Practices

Database Design Rules

When designing your database and TypeScript interfaces, follow these naming conventions:

1. Database Columns: snake_case

-- ✅ Correct: Use snake_case for PostgreSQL columns
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  first_name VARCHAR(100),
  last_name VARCHAR(100),
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

2. TypeScript Interfaces: PascalCase + camelCase

// ✅ Correct: Interface name in PascalCase
interface User {
  id: number;
  email: string;
  firstName: string;      // camelCase (maps to first_name)
  lastName: string;       // camelCase (maps to last_name)
  createdAt: Date;        // camelCase (maps to created_at)
  updatedAt: Date;        // camelCase (maps to updated_at)
}

// ✅ Correct: Another interface example
interface ProductCategory {
  id: number;
  categoryName: string;    // camelCase (maps to category_name)
  parentId: number | null; // camelCase (maps to parent_id)
  createdAt: Date;
}

3. Automatic Case Conversion

postgre-records automatically converts between naming conventions:

// Your TypeScript code (camelCase)
const user = await userService.create({
  email: '[email protected]',
  firstName: 'John',      // Automatically converts to first_name
  lastName: 'Doe',        // Automatically converts to last_name
});

// PostgreSQL Query (snake_case)
// INSERT INTO users (email, first_name, last_name, created_at, updated_at)
// VALUES ('[email protected]', 'John', 'Doe', NOW(), NOW())

// Response (camelCase)
console.log(user.firstName);  // 'John'
console.log(user.createdAt);  // Date object

4. Keys Array Definition

// ✅ Correct: Use camelCase in keys array
const userKeys = [
  'id', 
  'email', 
  'firstName',    // NOT 'first_name'
  'lastName',     // NOT 'last_name'
  'createdAt',    // NOT 'created_at'
  'updatedAt'     // NOT 'updated_at'
] as const;

Summary Table

| Context | Convention | Example | |---------|-----------|---------| | Database Columns | snake_case | created_at, first_name, user_id | | TypeScript Interface Names | PascalCase | User, ProductCategory, OrderItem | | TypeScript Properties | camelCase | createdAt, firstName, userId | | Keys Array | camelCase | ['id', 'createdAt', 'firstName'] | | Service Variables | camelCase | userService, productService |

💡 Key Points:

• Database schema: Always use snake_case
• TypeScript code: Always use camelCase for properties
• Interface names: Always use PascalCase
• Automatic conversion: The library handles conversion automatically
• No manual conversion needed: Just follow the conventions consistently

📝 Requirements

• Node.js >= 16.0.0
• PostgreSQL >= 10
• TypeScript >= 5.0.0 (if using TypeScript)

🔗 Links

• GitHub Repository
• npm Package
• Issue Tracker