change db hosting

🚀 Production PostgreSQL Setup with Drizzle ORM

This page outlines how to deploy your application in production environments, using Drizzle ORM and drizzle-kit, with one-click compatibility for:

• ✅ Neon (serverless Postgres)

• ✅ Self-hosted Postgres (VPS)

• ✅ Docker-hosted Postgres

• ✅ Cloud Postgres (AWS RDS, Supabase, Railway, etc.)

---

1. Default Setup: Neon (Production Ready)

A strong choice for most hosted scenarios:

.env.production

DATABASE_URL=postgresql://user:pass@ep-XXXX.neon.tech/dbname?sslmode=require

Drizzle Connection

import { drizzle } from 'drizzle-orm/neon-http';
import { neon } from '@neondatabase/serverless';
import ws from 'ws';
neonConfig.webSocketConstructor = ws; // required for serverless environments
const sql = neon(process.env.DATABASE_URL!);
export const db = drizzle({ client: sql });

Apply Schema

pnpm drizzle-kit push
# or
pnpm drizzle-kit generate && pnpm drizzle-kit migrate

This config uses the Neon serverless HTTP driver for low-latency, serverless & edge compatibility (neon.com, reddit.com, orm.drizzle.team).

---

2. Self-Hosted Postgres on VPS

Perfect for full control or compliance needs.

Install and Setup

sudo apt update && sudo apt install -y postgresql
sudo -u postgres psql -c "CREATE USER produser WITH ENCRYPTED PASSWORD 'prodpass'; CREATE DATABASE proddb OWNER produser;"

Configure Remote Access

• Edit listen_addresses='*' in postgresql.conf

• Add host all all 0.0.0.0/0 md5 in pg_hba.conf

• Restart Postgres.

.env.production

DATABASE_URL=postgresql://produser:prodpass@VPS_IP:5432/proddb

Drizzle Runtime

import { drizzle } from "drizzle-orm/node-postgres";
import { Pool } from "pg";
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
export const db = drizzle(pool);

---

3. Docker-Hosted Postgres

Great for containerized production.

docker-compose.prod.yml

version: '3.8'
services:
  db:
    image: postgres:15-alpine
    restart: unless-stopped
    environment:
      POSTGRES_DB: proddb
      POSTGRES_USER: produser
      POSTGRES_PASSWORD: prodpass
    volumes:
      - pgdata:/var/lib/postgresql/data
    networks:
      - backend
app:
build: .
env_file: .env.production
depends_on:
- db
networks:
- backend
volumes:
pgdata:
networks:
backend:

Deploy

docker-compose -f docker-compose.yml -f docker-compose.prod.yml up -d

.env.production

DATABASE_URL=postgresql://produser:prodpass@db:5432/proddb

Run migrations before container startup (github.com).

---

4. Cloud Postgres: RDS, Supabase, Railway

Managed Postgres with full uptime guarantees.

Setup

1. Provision instance, allow access from your app server.

2. Copy endpoint.

.env.production

DATABASE_URL=postgresql://produser:prodpass@your-rds-endpoint.amazonaws.com:5432/proddb?sslmode=require

Connect using the same Drizzle setup as VPS/Docker.

---

5. Drizzle + drizzle-kit Configuration

Create drizzle.config.ts:

import type { Config } from "drizzle-kit";
import 'dotenv/config';
export default {
schema: "./lib/schema.ts",
out: "./drizzle/migrations",
dialect: "postgresql",
dbCredentials: { url: process.env.DATABASE_URL! },
strict: true,
} satisfies Config;

Add DB Scripts in package.json

"scripts": {
  "db:push": "drizzle-kit push",
  "db:migrate": "drizzle-kit migrate"
}

Run in CI or pre-deploy:

pnpm db:push
# or
pnpm db:migrate

---

6. (Optional) Pre-Deploy Connection Check

Create scripts/db-check.ts:

#!/usr/bin/env tsx
import { Pool } from "pg";
const pool = new Pool({ connectionString: process.env.DATABASE_URL });
pool.query("SELECT 1")
  .then(() => console.log("✅ DB connection OK"))
  .catch(err => { console.error("❌ DB connection failed", err); process.exit(1); })
  .finally(() => pool.end());

Run this in CI/CD before deployment.

---

7. Troubleshooting Guide

Symptom	Potential Fix
ECONNREFUSED	DB offline or wrong host/port
SSL errors	Add ?sslmode=require
Auth failed	Verify credentials and roles
Docker db down	Check docker-compose ps and logs

---

🚧 Next Enhancements

• ✅ Add GitHub Action to run db:push on merge to main

• ✅ Create pre-deploy health check step

• ✅ Add support for high availability (PgBouncer, read replicas)

• ✅ Add automated backups

---

Let me know if you'd like me to provide:

• A GitHub Actions workflow snippet for auto-migrations

• A refined docker-compose.prod.yml

• Or CI/CD integration examples

Happy to help you level up!