Database Setup

Configure PostgreSQL database connection and environment for App CR

Overview

App CR uses PostgreSQL 15 as the database, managed through Docker Compose for development and Prisma ORM for database operations.

Prerequisites

Docker & Docker Compose

Required to run PostgreSQL container

Node.js & npm

Required to run Prisma CLI commands

---

Quick Start

1. Start PostgreSQL

   Launch the PostgreSQL container:

   cd ~/workspace/source
   docker-compose up -d postgres
   

   Verify the container is running:

   docker-compose ps
   
2. Configure Environment

   Create .env file in the backend directory:

   cd ~/workspace/source/backend
   touch .env
   

   Add the database connection URL:

   DATABASE_URL="postgresql://user_admin:password123@localhost:5432/mi_db_crud"
   
3. Run Migrations

   Apply database schema:

   npx prisma migrate deploy
   
4. Generate Prisma Client

   Generate TypeScript types:

   npx prisma generate
   

---

Docker Configuration

PostgreSQL Container

The docker-compose.yml defines the PostgreSQL service:

services:
  postgres:
    image: postgres:15
    restart: always
    environment:
      POSTGRES_USER: user_admin
      POSTGRES_PASSWORD: password123
      POSTGRES_DB: mi_db_crud
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data

volumes:
  postgres_data:

Configuration Details

image · string · default: "postgres:15"

PostgreSQL version 15 official Docker image

restart · string · default: "always"

Automatically restart container if it stops

POSTGRES_USER · string · default: "user_admin" · required

Database superuser username

POSTGRES_PASSWORD · string · default: "password123" · required

Database superuser password

Change this in production environments

POSTGRES_DB · string · default: "mi_db_crud" · required

Initial database name

ports · array · default: "5432:5432"

Maps container port 5432 to host port 5432

volumes · array

Persists database data in Docker volume postgres_data

Docker Commands

Start Database

# Start PostgreSQL in background
docker-compose up -d postgres

---

Environment Variables

DATABASE_URL Format

The DATABASE_URL follows PostgreSQL connection string format:

DATABASE_URL="postgresql://USER:PASSWORD@HOST:PORT/DATABASE?schema=SCHEMA"

Default Configuration

DATABASE_URL="postgresql://user_admin:password123@localhost:5432/mi_db_crud"

URL Components

Protocol · string · required

postgresql:// - PostgreSQL protocol identifier

User · string · default: "user_admin" · required

Database username from POSTGRES_USER

Password · string · default: "password123" · required

Database password from POSTGRES_PASSWORD

Host · string · default: "localhost" · required

Database host

• localhost for local development
• postgres when connecting from another Docker container
• Production hostname/IP in deployment

Port · number · default: "5432" · required

PostgreSQL port (default: 5432)

Database · string · default: "mi_db_crud" · required

Database name from POSTGRES_DB

Schema · string · default: "public"

Optional schema name (defaults to public)

Example: ?schema=app_schema

Environment File Location

~/workspace/source/backend/.env

Never commit .env files to version control. Add to .gitignore:

.env
.env.local
.env.*.local

---

Database Connection

Using Prisma Client

Initialize Client

// backend/src/db.ts
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient({
  log: ['query', 'error', 'warn'],
});

export default prisma;

Connection Pooling

Prisma automatically manages connection pooling. Configure pool size via environment:

DATABASE_URL="postgresql://user_admin:password123@localhost:5432/mi_db_crud?connection_limit=10"

connection_limit · number · default: "10"

Maximum number of database connections in the pool

---

Testing Database Connection

Using Prisma Studio

Open the interactive database browser:

cd ~/workspace/source/backend
npx prisma studio

Access at: http://localhost:5555

Using psql CLI

Connect directly to PostgreSQL:

# Using docker-compose exec
docker-compose exec postgres psql -U user_admin -d mi_db_crud

# Or using psql if installed locally
psql postgresql://user_admin:password123@localhost:5432/mi_db_crud

Test Queries

List Tables

-- View all tables
\dt

-- Expected output:
-- User
-- Task
-- _prisma_migrations

---

Production Setup

Environment Configuration

For production, use secure environment variables:

DATABASE_URL="postgresql://prod_user:[e-mail]:5432/app_cr_prod?sslmode=require"

sslmode · string · default: "require"

SSL/TLS mode for encrypted connections

Values:

• disable - No SSL (development only)
• require - Require SSL
• verify-ca - Require and verify CA
• verify-full - Full certificate verification

Security Best Practices

Use strong passwords

• Minimum 16 characters
• Mix of uppercase, lowercase, numbers, symbols
• Use password manager or secrets management service

Enable SSL/TLS

Always use encrypted connections in production:

DATABASE_URL="...?sslmode=require"

Restrict database access

• Use firewall rules to limit access
• Create application-specific database user (not superuser)
• Grant minimal required permissions

Use secrets management

Store DATABASE_URL in:

• AWS Secrets Manager
• HashiCorp Vault
• GitHub Secrets (for CI/CD)
• Never hardcode in application code

Enable connection pooling

Use connection pooler like PgBouncer for high-traffic applications:

DATABASE_URL="postgresql://user:pass@pgbouncer:6432/db?pgbouncer=true"

---

Troubleshooting

Connection Refused

Error: ECONNREFUSED 127.0.0.1:5432

1. Check PostgreSQL is running

   docker-compose ps postgres
   
2. Start PostgreSQL if stopped

   docker-compose up -d postgres
   
3. Verify port mapping

   docker-compose port postgres 5432
   # Should output: 0.0.0.0:5432
   

Authentication Failed

Error: password authentication failed for user

1. Verify credentials

   Check .env file matches docker-compose.yml:

   • User: user_admin
   • Password: password123
   • Database: mi_db_crud
2. Recreate container if changed

   docker-compose down
   docker-compose up -d postgres
   

Database Does Not Exist

Error: database "mi_db_crud" does not exist

1. Check database name in URL

   Ensure DATABASE_URL uses correct database name:

   DATABASE_URL="postgresql://user_admin:password123@localhost:5432/mi_db_crud"
   
2. Create database manually

   docker-compose exec postgres createdb -U user_admin mi_db_crud
   

Schema Out of Sync

Error: The database schema is not in sync with your Prisma schema

1. Run migrations

   npx prisma migrate deploy
   
2. Regenerate client

   npx prisma generate
   

---

Next Steps

Database Schema

Explore the complete database schema

Run Migrations

Learn how to manage database migrations