kaxuna1/postgresql

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

// 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

// 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

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

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 - Query patterns and anti-patterns
• workflows - 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