Database Integration Patterns

Master database connections, query patterns, repositories, and transaction handling.

When to Use This Skill

Setting up database connections (PostgreSQL, MongoDB)
Implementing connection pooling
Writing parameterized queries
Creating repository pattern for data access
Managing database migrations
Handling transactions
Implementing caching strategies
Optimizing queries with indexes

PostgreSQL Connection Setup

// config/database.ts
import { Pool, PoolClient, PoolConfig } from 'pg';

const poolConfig: PoolConfig = {
  host: process.env.DB_HOST || 'localhost',
  port: parseInt(process.env.DB_PORT || '5432'),
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  // Connection pooling
  max: 20,                        // Max connections
  idleTimeoutMillis: 30000,      // Close idle connections
  connectionTimeoutMillis: 2000, // Connection timeout
  statement_timeout: 30000       // Query timeout
};

export const pool = new Pool(poolConfig);

// Connection events
pool.on('connect', () => {
  console.log('Database connected');
});

pool.on('error', (err) => {
  console.error('Unexpected error on idle client', err);
  process.exit(-1);
});

// Graceful shutdown
export const closeDatabase = async () => {
  await pool.end();
  console.log('Database pool closed');
};

// Test connection on startup
export async function testConnection() {
  try {
    const result = await pool.query('SELECT NOW()');
    console.log('Database test query successful:', result.rows[0]);
  } catch (error) {
    console.error('Database connection failed:', error);
    process.exit(1);
  }
}

Query Patterns (PostgreSQL)

Always use parameterized queries to prevent SQL injection:

// ✅ SAFE - Parameterized query
const result = await pool.query(
  'SELECT * FROM users WHERE email = $1',
  [email]
);

// ❌ DANGEROUS - String interpolation
const result = await pool.query(
  `SELECT * FROM users WHERE email = '${email}'`
);

Common Query Patterns:

// SELECT single row
const result = await pool.query(
  'SELECT * FROM users WHERE id = $1',
  [userId]
);
const user = result.rows[0];

// SELECT multiple rows
const result = await pool.query(
  'SELECT * FROM users WHERE status = $1 LIMIT $2 OFFSET $3',
  [status, limit, offset]
);
const users = result.rows;

// INSERT returning generated ID
const result = await pool.query(
  'INSERT INTO users (name, email) VALUES ($1, $2) RETURNING id, created_at',
  [name, email]
);
const { id, created_at } = result.rows[0];

// UPDATE
const result = await pool.query(
  'UPDATE users SET name = $1, updated_at = NOW() WHERE id = $2 RETURNING *',
  [name, userId]
);

// DELETE
const result = await pool.query(
  'DELETE FROM users WHERE id = $1 RETURNING id',
  [userId]
);

// COUNT
const result = await pool.query(
  'SELECT COUNT(*) FROM users WHERE status = $1',
  [status]
);
const count = parseInt(result.rows[0].count);

Repository Pattern

// repositories/user.repository.ts
import { Pool } from 'pg';
import { User, CreateUserInput, UpdateUserInput } from '../types/user.types';
import { NotFoundError } from '../utils/errors';

export class UserRepository {
  constructor(private db: Pool) {}

  async findById(id: string): Promise<User> {
    const result = await this.db.query(
      'SELECT * FROM users WHERE id = $1',
      [id]
    );

    if (!result.rows[0]) {
      throw new NotFoundError('User not found');
    }

    return result.rows[0];
  }

  async findByEmail(email: string): Promise<User | null> {
    const result = await this.db.query(
      'SELECT * FROM users WHERE email = $1',
      [email]
    );
    return result.rows[0] || null;
  }

  async findAll(limit: number, offset: number): Promise<{
    users: User[];
    total: number;
  }> {
    const [usersResult, countResult] = await Promise.all([
      this.db.query(
        'SELECT * FROM users ORDER BY created_at DESC LIMIT $1 OFFSET $2',
        [limit, offset]
      ),
      this.db.query('SELECT COUNT(*) FROM users')
    ]);

    return {
      users: usersResult.rows,
      total: parseInt(countResult.rows[0].count)
    };
  }

  async create(input: CreateUserInput): Promise<User> {
    const result = await this.db.query(
      `INSERT INTO users (name, email, password, created_at, updated_at)
       VALUES ($1, $2, $3, NOW(), NOW())
       RETURNING *`,
      [input.name, input.email, input.password]
    );
    return result.rows[0];
  }

  async update(id: string, input: Partial<UpdateUserInput>): Promise<User> {
    const fields = Object.keys(input);
    const values = Object.values(input);

    if (fields.length === 0) {
      return this.findById(id);
    }

    const setClause = fields
      .map((field, idx) => `${field} = $${idx + 2}`)
      .join(', ');

    const result = await this.db.query(
      `UPDATE users SET ${setClause}, updated_at = NOW()
       WHERE id = $1 RETURNING *`,
      [id, ...values]
    );

    if (!result.rows[0]) {
      throw new NotFoundError('User not found');
    }

    return result.rows[0];
  }

  async delete(id: string): Promise<boolean> {
    const result = await this.db.query(
      'DELETE FROM users WHERE id = $1',
      [id]
    );
    return result.rowCount > 0;
  }
}

Transactions

export class OrderRepository {
  constructor(private db: Pool) {}

  async createOrder(userId: string, items: OrderItem[]): Promise<string> {
    const client = await this.db.connect();

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

      // Create order
      const orderResult = await client.query(
        `INSERT INTO orders (user_id, total, status, created_at)
         VALUES ($1, $2, $3, NOW())
         RETURNING id`,
        [userId, calculateTotal(items), 'pending']
      );
      const orderId = orderResult.rows[0].id;

      // Create order items
      for (const item of items) {
        await client.query(
          `INSERT INTO order_items (order_id, product_id, quantity, price)
           VALUES ($1, $2, $3, $4)`,
          [orderId, item.productId, item.quantity, item.price]
        );

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

      await client.query('COMMIT');
      return orderId;
    } catch (error) {
      await client.query('ROLLBACK');
      throw error;
    } finally {
      client.release();
    }
  }
}

MongoDB with Mongoose

// config/mongoose.ts
import mongoose from 'mongoose';

export async function connectMongoDB() {
  try {
    await mongoose.connect(process.env.MONGODB_URI!, {
      maxPoolSize: 10,
      serverSelectionTimeoutMS: 5000,
      socketTimeoutMS: 45000
    });
    console.log('MongoDB connected');
  } catch (error) {
    console.error('MongoDB connection failed:', error);
    process.exit(1);
  }
}

mongoose.connection.on('disconnected', () => {
  console.log('MongoDB disconnected');
});

mongoose.connection.on('error', (err) => {
  console.error('MongoDB error:', err);
});

export async function closeDatabase() {
  await mongoose.disconnect();
}

// models/user.model.ts
import { Schema, model, Document } from 'mongoose';

interface IUser extends Document {
  name: string;
  email: string;
  password: string;
  createdAt: Date;
  updatedAt: Date;
}

const userSchema = new Schema<IUser>(
  {
    name: { type: String, required: true },
    email: { type: String, required: true, unique: true },
    password: { type: String, required: true }
  },
  { timestamps: true }
);

// Indexes for performance
userSchema.index({ email: 1 });
userSchema.index({ createdAt: -1 });

export const User = model<IUser>('User', userSchema);

// Repository pattern with Mongoose
export class UserMongoRepository {
  async findById(id: string): Promise<IUser | null> {
    return User.findById(id);
  }

  async findByEmail(email: string): Promise<IUser | null> {
    return User.findOne({ email });
  }

  async findAll(skip: number, limit: number) {
    const [users, total] = await Promise.all([
      User.find().skip(skip).limit(limit).sort({ createdAt: -1 }),
      User.countDocuments()
    ]);
    return { users, total };
  }

  async create(data: Partial<IUser>): Promise<IUser> {
    return User.create(data);
  }

  async update(id: string, data: Partial<IUser>): Promise<IUser | null> {
    return User.findByIdAndUpdate(id, data, { new: true });
  }

  async delete(id: string): Promise<boolean> {
    const result = await User.findByIdAndDelete(id);
    return !!result;
  }
}

Query Optimization with Indexes

// Always add indexes for frequently queried fields
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_status ON users(status);
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);

// Composite indexes for common queries
CREATE INDEX idx_users_status_created ON users(status, created_at DESC);

// Verify index usage
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'test@example.com';

Connection Pooling Best Practices

// ✅ Good - Reuse connection pool
const db = new Pool(config);

app.get('/users/:id', async (req, res) => {
  const result = await db.query(
    'SELECT * FROM users WHERE id = $1',
    [req.params.id]
  );
  res.json(result.rows[0]);
});

// ❌ Bad - Create new connection per request
app.get('/users/:id', async (req, res) => {
  const client = new Client(config);
  await client.connect();
  const result = await client.query('...');
  await client.end(); // Slow!
});

Caching Strategy

// Cache frequently accessed data
export class UserCache {
  constructor(
    private repo: UserRepository,
    private redis: Redis
  ) {}

  async findById(id: string): Promise<User> {
    const cached = await this.redis.get(`user:${id}`);
    if (cached) return JSON.parse(cached);

    const user = await this.repo.findById(id);
    await this.redis.set(
      `user:${id}`,
      JSON.stringify(user),
      'EX',
      3600 // 1 hour
    );

    return user;
  }

  async invalidate(id: string): Promise<void> {
    await this.redis.del(`user:${id}`);
  }

  async invalidateList(): Promise<void> {
    const keys = await this.redis.keys('users:*');
    if (keys.length > 0) {
      await this.redis.del(...keys);
    }
  }
}

Best Practices

Always parameterize queries - Prevent SQL injection
Use connection pooling - Never create per-request
Set timeouts - Prevent hanging connections
Add indexes strategically - For frequently queried columns
Use transactions - For multi-step operations
Implement repositories - Abstract database access
Cache appropriately - Reduce database load
Handle null results - Check for rows before accessing
Monitor slow queries - Use EXPLAIN ANALYZE
Test migrations - Test schema changes before production

Common Errors

Error	Cause	Solution
"relation does not exist"	Table not created	Run migrations
"column does not exist"	Field not in schema	Check schema/migration
"no more connections available"	Pool exhausted	Increase pool size
"query timeout"	Slow query	Optimize with indexes
"deadlock detected"	Transaction conflict	Retry with exponential backoff

database-integration

Database Integration Patterns

When to Use This Skill

PostgreSQL Connection Setup

Query Patterns (PostgreSQL)

Repository Pattern

Transactions

MongoDB with Mongoose

Query Optimization with Indexes

Connection Pooling Best Practices

Caching Strategy

Best Practices

Common Errors

See Also

Similar Skills