Skip to main content

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

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

-- 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:SECURE_PASSWORD@prod-host.example.com:5432/app_cr_prod?sslmode=require"
​
sslmode
string
default:"require"
SSL/TLS mode for encrypted connectionsValues:
  • disable - No SSL (development only)
  • require - Require SSL
  • verify-ca - Require and verify CA
  • verify-full - Full certificate verification

​
Security Best Practices

  • Minimum 16 characters
  • Mix of uppercase, lowercase, numbers, symbols
  • Use password manager or secrets management service
Always use encrypted connections in production:
DATABASE_URL="...?sslmode=require"
  • Use firewall rules to limit access
  • Create application-specific database user (not superuser)
  • Grant minimal required permissions
Store DATABASE_URL in:
  • AWS Secrets Manager
  • HashiCorp Vault
  • GitHub Secrets (for CI/CD)
  • Never hardcode in application code
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

Build docs developers (and LLMs) love

Get started for freeTalk to us