# postgresql

> PostgreSQL database design, queries, and management for INVOOPAY
Use when: designing schemas, writing complex queries, optimizing database performance, managing migrations, or debugging database issues

- Author: Kakha Gelashvili
- Repository: kaxuna1/ecomsite
- Version: 20260124232936
- Stars: 0
- Forks: 0
- Last Updated: 2026-02-06
- Source: https://github.com/kaxuna1/ecomsite
- Web: https://mule.run/skillshub/@@kaxuna1/ecomsite~postgresql:20260124232936

---

---
name: postgresql
description: |
  PostgreSQL database design, queries, and management for INVOOPAY
  Use when: designing schemas, writing complex queries, optimizing database performance, managing migrations, or debugging database issues
allowed-tools: Read, Edit, Write, Glob, Grep, Bash
---

# PostgreSQL Skill

Provides guidance for PostgreSQL database operations using the `pg` library with connection pooling. This codebase uses raw SQL with parameterized queries rather than an ORM, requiring careful attention to SQL injection prevention and transaction management.

## Quick Start

### Connection Pool Setup

```typescript
// backend/src/db/client.ts
import pg from 'pg';
const { Pool } = pg;

export const pool = new Pool({
  host: env.dbHost,
  port: env.dbPort,
  database: env.dbName,
  user: env.dbUser,
  password: env.dbPassword,
  max: 20,                    // Maximum connections
  idleTimeoutMillis: 30000,   // Close idle connections after 30s
  connectionTimeoutMillis: 2000
});

export const query = async (text: string, params?: any[]) => {
  return await pool.query(text, params);
};
```

### Basic CRUD Operations

```typescript
// Read with parameterized query
const result = await pool.query(
  'SELECT * FROM products WHERE id = $1',
  [productId]
);

// Insert with RETURNING
const insertResult = await pool.query(
  `INSERT INTO products (name, price, inventory)
   VALUES ($1, $2, $3)
   RETURNING *`,
  [name, price, inventory]
);

// Update
await pool.query(
  'UPDATE products SET inventory = inventory - $1 WHERE id = $2',
  [quantity, productId]
);
```

## Key Concepts

| Concept | Usage | Example |
|---------|-------|---------|
| Parameterized Queries | Prevent SQL injection | `$1, $2, $3` placeholders |
| JSONB | Flexible schema storage | `categories JSONB NOT NULL` |
| Connection Pooling | Reuse connections | `pool.query()` vs `pool.connect()` |
| Transactions | Multi-statement atomicity | `BEGIN`, `COMMIT`, `ROLLBACK` |
| COALESCE | Translation fallbacks | `COALESCE(pt.name, p.name)` |

## Common Patterns

### Transactions for Multi-Step Operations

**When:** Creating orders with inventory decrements

```typescript
const client = await pool.connect();
try {
  await client.query('BEGIN');
  
  // Multiple operations...
  await client.query('INSERT INTO orders...', [...]);
  await client.query('UPDATE products SET inventory...', [...]);
  
  await client.query('COMMIT');
} catch (error) {
  await client.query('ROLLBACK');
  throw error;
} finally {
  client.release();  // CRITICAL: Always release
}
```

### UPSERT with ON CONFLICT

**When:** Create or update translations

```typescript
await pool.query(
  `INSERT INTO product_translations (product_id, language_code, name)
   VALUES ($1, $2, $3)
   ON CONFLICT (product_id, language_code)
   DO UPDATE SET name = EXCLUDED.name, updated_at = CURRENT_TIMESTAMP
   RETURNING *`,
  [productId, languageCode, name]
);
```

## See Also

- [patterns](references/patterns.md) - Query patterns and anti-patterns
- [workflows](references/workflows.md) - Migrations and schema management

## Related Skills

- See the **prisma** skill for ORM-based projects
- See the **redis** skill for caching query results
- See the **node** skill for async database operations