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