🔗 Setting up DB Connection and Seed

Connecting to PostgreSQL properly is crucial for production applications. In this lesson, we'll set up database connections with connection pooling, handle development server restarts gracefully, and create seed scripts to populate our database with meaningful test data.

Connect to DB

1. Go to https://neon.new/
2. Create new DB in the browser and copy the DB URL
3. paste as DATABASE_URL in your .env file

Database Connection Strategy

Connection Pooling: What It Is

Connection pooling is a performance optimization technique that maintains a cache of database connections. Instead of establishing a new connection for every request, an application can borrow a pre-existing connection from this "pool," use it, and then return it once finished, making it available for other requests.

Without Pooling 🚫

// Creating a new connection is expensive
const client = new Client({ connectionString })
await client.connect()  // Expensive!
const result = await client.query('SELECT * FROM users')
await client.end()      // Wasteful!

Problems:

• New TCP connection per query
• Authentication overhead each time
• Database connection limits hit quickly
• Poor performance under load

With Pooling ✅

// Pool manages connections for you
const pool = new Pool({ connectionString })

// Just use the pool directly
const result = await pool.query('SELECT * FROM users')
// Connection is automatically returned to pool

Benefits:

• Connections reused automatically
• Multiple queries can run in parallel
• No manual connection management
• Much better performance

<aside> 📊

Connection Pool Benefits:

• Performance: Eliminates connection setup/teardown overhead
• Resource Management: Limits concurrent database connections
• Reliability: Handles connection failures automatically
• Scalability: Supports high-concurrency applications </aside>

The Development Problem

Hot Reloading vs Connection Pools

Development servers restart frequently, but pools don't get cleaned up:

// Every restart creates a new pool
const pool = new Pool({ connectionString })

// File change → restart → new pool → old pool still exists!
// After 10 restarts = 10 pools = connections exhausted

Solution: Singleton saved to global

The @epic-web/remember creates that global singleton cache for us

import { remember } from '@epic-web/remember'

// In development, reuse the same pool across restarts
const pool = remember('dbPool', () => new Pool({ connectionString }))