Database Configuration

Overview

Argos Mesh uses PostgreSQL as the primary relational database for the Orders service. The database stores product inventory, pricing, and stock information.

PostgreSQL Configuration

Docker Setup

The database runs as a PostgreSQL 15 container:

docker-compose.yml

db:
  image: postgres:15-alpine
  container_name: shop_db
  restart: always
  environment:
    - POSTGRES_USER=user_shop
    - POSTGRES_PASSWORD=secretPassword
    - POSTGRES_DB=shop_db
  volumes:
    - postgres_data:/var/lib/postgresql/data
  ports:
    - "5432:5432"
  healthcheck:
    test: ["CMD-SHELL", "pg_isready -U user_shop -d shop_db"]
    interval: 10s
    timeout: 5s
    retries: 5
  networks:
    - argos-network

volumes:
  postgres_data:

Environment Variables

POSTGRES_USER

string

required

Database superuser usernameDefault: user_shopDocker: Set via environment variable

POSTGRES_PASSWORD

string

required

Database superuser passwordDefault: secretPassword

This is a test password only! Change it in production.

POSTGRES_DB

string

required

Initial database nameDefault: shop_db

Spring Datasource Configuration

Connection Settings

spring.datasource.url

string

required

JDBC connection URLDefault: jdbc:postgresql://db:5432/shop_dbFormat: jdbc:postgresql://{host}:{port}/{database}Local Development: jdbc:postgresql://localhost:5432/shop_db

spring.datasource.username

string

required

Database authentication usernameDefault: user_shop

spring.datasource.password

string

required

Database authentication passwordDefault: secretPassword

spring.datasource.driver-class-name

string

required

JDBC driver classDefault: org.postgresql.Driver

Application Properties

spring.application.name=argos-orders

# ==========================
# DATASOURCE (PostgreSQL)
# ==========================
spring.datasource.url=jdbc:postgresql://db:5432/shop_db
spring.datasource.username=user_shop
spring.datasource.password=secretPassword
spring.datasource.driver-class-name=org.postgresql.Driver

# PostgreSQL dialect
spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect

# Schema management
spring.jpa.hibernate.ddl-auto=update
spring.jpa.defer-datasource-initialization=true
spring.sql.init.mode=always

JPA/Hibernate Configuration

Schema Management

spring.jpa.hibernate.ddl-auto

string

Hibernate schema generation strategyDefault: updateOptions:

none - No schema management
validate - Validate schema, make no changes
update - Update schema if needed
create - Create schema, destroying existing data
create-drop - Create schema, drop on shutdown

Use validate or none in production. Never use create or create-drop!

spring.jpa.database-platform

string

Hibernate SQL dialectDefault: org.hibernate.dialect.PostgreSQLDialectPurpose: Enables PostgreSQL-specific optimizations and SQL features

spring.jpa.defer-datasource-initialization

boolean

Defer initialization until after entity scanningDefault: truePurpose: Ensures Hibernate creates tables before data.sql runs

spring.sql.init.mode

string

SQL initialization modeDefault: alwaysOptions:

always - Always execute data.sql
embedded - Only for embedded databases
never - Disable SQL initialization

Database Schema

Products Table

The core table for product inventory management:

CREATE TABLE products (
    product_id BIGSERIAL PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    product_price DECIMAL(19,2) NOT NULL CHECK (product_price > 0),
    product_stock INTEGER NOT NULL CHECK (product_stock >= 0)
);

Entity Mapping

Product.java

@Entity
@Table(name = "products")
@Getter @Setter
@NoArgsConstructor @AllArgsConstructor
public class Product {
    
    @Id
    @Column(name = "product_id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long productID;
    
    @NotNull
    @NotBlank
    @Column(name = "product_name", nullable = false)
    private String productName;
    
    @NotNull
    @Positive
    @Column(name = "product_price", nullable = false)
    private BigDecimal productPrice;
    
    @NotNull
    @PositiveOrZero
    @Column(name = "product_stock")
    private Integer productStock;
}

Column Descriptions

product_id

BIGSERIAL

Primary key with auto-incrementStrategy: IDENTITY (PostgreSQL SERIAL)Java Type: Long

product_name

VARCHAR(255)

Product display nameConstraints: NOT NULL, NOT BLANKExample: "Remera Oversize Black"

product_price

DECIMAL(19,2)

Product price with 2 decimal placesConstraints: NOT NULL, must be positiveJava Type: BigDecimalExample: 15900.00

product_stock

INTEGER

Available inventory quantityConstraints: NOT NULL, must be >= 0Java Type: IntegerExample: 100

Initial Data Loading

The system loads seed data from data.sql on startup:

data.sql

INSERT INTO products (product_name, product_price, product_stock) VALUES
('Remera Oversize Black', 15900.00, 100),
('Jean Slim Fit Blue', 35000.50, 20),
('Hoodie Vintage Grey', 28000.00, 15),
('Tote Bag Eco', 5200.00, 100),
('Campera de Cuero Pro', 85000.00, 1),
('Gorra Snapback Urban', 9500.00, 30),
('Pantalón Cargo Olive', 32000.00, 12),
('Remera Basic White', 12000.00, 200),
('Short Deportivo Dry', 18500.00, 45),
('Zapatillas Street v1', 55000.00, 8),
('Medias Pack x3', 4500.00, 150),
('Bermuda Chino Beige', 22000.00, 25),
('Camisa Formal Slim', 29500.00, 18),
('Cinturón Cuero Marrón', 8900.00, 40),
('Mochila Tech 20L', 42000.00, 10);

Idempotency: This script runs on every startup. In production, use Flyway or Liquibase for versioned migrations.

Connection Pool Configuration

Spring Boot uses HikariCP as the default connection pool (no configuration needed for basic usage).

Advanced Tuning (Optional)

application.properties

# Maximum pool size
spring.datasource.hikari.maximum-pool-size=10

# Minimum idle connections
spring.datasource.hikari.minimum-idle=5

# Connection timeout (milliseconds)
spring.datasource.hikari.connection-timeout=30000

# Idle timeout (milliseconds)
spring.datasource.hikari.idle-timeout=600000

# Maximum lifetime (milliseconds)
spring.datasource.hikari.max-lifetime=1800000

Connection Pool Best Practices

Maximum Pool Size:

Formula: (CPU cores * 2) + effective spindle count
For most applications: 10-20 connections is sufficient
More connections != better performance

Minimum Idle:

Keep 5-10 connections ready for burst traffic
Reduces latency on sudden load increases

Connection Timeout:

30 seconds is reasonable for most applications
Increase if database is slow to respond

Idle Timeout:

10 minutes (600000ms) allows reuse during quiet periods
Prevents holding unnecessary connections

Health Checks

Docker Health Check

PostgreSQL container uses pg_isready for health verification:

docker-compose.yml

healthcheck:
  test: ["CMD-SHELL", "pg_isready -U user_shop -d shop_db"]
  interval: 10s
  timeout: 5s
  retries: 5

Application Health Check

Spring Boot Actuator automatically exposes database health:

curl http://localhost:8080/actuator/health

Response:

{
  "status": "UP",
  "components": {
    "db": {
      "status": "UP",
      "details": {
        "database": "PostgreSQL",
        "validationQuery": "isValid()"
      }
    }
  }
}

Database Operations

Connect to Database

# Connect via psql
docker exec -it shop_db psql -U user_shop -d shop_db

Common SQL Queries

SELECT * FROM products ORDER BY product_id;

Backup and Recovery

Data Persistence

PostgreSQL data is stored in a Docker volume:

docker-compose.yml

volumes:
  - postgres_data:/var/lib/postgresql/data

Persistence Guarantees:

Data survives container restarts
Data survives docker-compose down
Data is lost only with docker-compose down -v (volume removal)

Backup Commands

# Backup to SQL file
docker exec shop_db pg_dump -U user_shop shop_db > backup.sql

# Compressed backup
docker exec shop_db pg_dump -U user_shop shop_db | gzip > backup.sql.gz

Troubleshooting

Connection Refused

Symptoms: Application can’t connect to databaseSolutions:

Verify PostgreSQL is running: docker ps | grep shop_db
Check logs: docker logs shop_db
Verify health check: docker inspect shop_db | grep Health
Ensure database is ready before app starts (use depends_on with condition: service_healthy)

Schema Not Updated

Symptoms: New entity fields don’t appear in databaseSolutions:

Verify spring.jpa.hibernate.ddl-auto=update
Check for Hibernate errors in logs
Manually create columns if needed
Consider using Flyway/Liquibase for production

Data Initialization Fails

Symptoms: data.sql doesn’t execute or failsSolutions:

Ensure spring.sql.init.mode=always
Set spring.jpa.defer-datasource-initialization=true
Check for SQL syntax errors
Verify table exists before INSERT

Connection Pool Exhausted

Symptoms: HikariPool - Connection is not availableSolutions:

Increase maximum-pool-size
Check for connection leaks (unclosed connections)
Reduce connection-timeout
Monitor active connections: SELECT * FROM pg_stat_activity;

Production Recommendations

Use Managed Databases

Consider Amazon RDS, Google Cloud SQL, or Azure Database for PostgreSQL

Enable SSL/TLS

Configure ssl=true in JDBC URL and provide certificates

Implement Migrations

Use Flyway or Liquibase instead of ddl-auto=update

Monitor Performance

Use pg_stat_statements and connection pool metrics

Configure Backups

Automated daily backups with point-in-time recovery

Optimize Queries

Add indexes, use EXPLAIN ANALYZE, monitor slow queries

Getting Started

Configuration

Database Configuration

Overview

PostgreSQL Configuration

Docker Setup

Environment Variables

Spring Datasource Configuration

Connection Settings

Application Properties

JPA/Hibernate Configuration

Schema Management

Database Schema

Products Table

Entity Mapping

Column Descriptions

Initial Data Loading

Connection Pool Configuration

Advanced Tuning (Optional)

Health Checks

Docker Health Check

Application Health Check

Database Operations

Connect to Database

Common SQL Queries

Backup and Recovery

Data Persistence

Backup Commands

Troubleshooting

Production Recommendations

Use Managed Databases

Enable SSL/TLS

Implement Migrations

Monitor Performance

Configure Backups

Optimize Queries

Build docs developers (and LLMs) love

Getting Started

Configuration

​Overview

​PostgreSQL Configuration

​Docker Setup

​Environment Variables

​Spring Datasource Configuration

​Connection Settings

​Application Properties

​JPA/Hibernate Configuration

​Schema Management

​Database Schema

​Products Table

​Entity Mapping

​Column Descriptions

​Initial Data Loading

​Connection Pool Configuration

​Advanced Tuning (Optional)

​Health Checks

​Docker Health Check

​Application Health Check

​Database Operations

​Connect to Database

​Common SQL Queries

​Backup and Recovery

​Data Persistence

​Backup Commands

​Troubleshooting

​Production Recommendations

Use Managed Databases

Enable SSL/TLS

Implement Migrations

Monitor Performance

Configure Backups

Optimize Queries

Build docs developers (and LLMs) love

Overview

PostgreSQL Configuration

Docker Setup

Environment Variables

Spring Datasource Configuration

Connection Settings

Application Properties

JPA/Hibernate Configuration

Schema Management

Database Schema

Products Table

Entity Mapping

Column Descriptions

Initial Data Loading

Connection Pool Configuration

Advanced Tuning (Optional)

Health Checks

Docker Health Check

Application Health Check

Database Operations

Connect to Database

Common SQL Queries

Backup and Recovery

Data Persistence

Backup Commands

Troubleshooting

Production Recommendations