Database Setup

The Currency Exchange API uses PostgreSQL 17 as its database engine, storing currency information and exchange rates.

Database Overview

The application uses two main tables:

currencies - Stores currency information (code, name, symbol)
exchangerates - Stores exchange rate pairs between currencies

PostgreSQL Setup

Using Docker Compose

The recommended way to run PostgreSQL is using Docker Compose:

services:
  postgres:
    image: postgres:17
    container_name: currencies-bd
    environment:
      POSTGRES_DB: "currencies"
      POSTGRES_USER: "alexpg"
      POSTGRES_PASSWORD: ${password}
      PGDATA: "/var/lib/postgresql/data/pgdata"
    ports:
      - "5432:5432"
    volumes:
      - ../2. Init Database:/docker-entrypoint-initdb.d
      - currencies:/var/lib/postgresql/data

volumes:
  currencies:

Start the PostgreSQL container

docker-compose up -d postgres

Verify the database is running

docker ps | grep currencies-bd

Check the logs

docker logs currencies-bd

You should see messages indicating the database is ready to accept connections.

Manual PostgreSQL Installation

If you prefer to install PostgreSQL manually:

sudo apt update
sudo apt install postgresql postgresql-contrib
sudo systemctl start postgresql
sudo systemctl enable postgresql

Database Schema

The database schema consists of two tables with relationships and indexes.

Tables

currencies Table

Stores information about different currencies:

CREATE TABLE currencies (
    id SERIAL PRIMARY KEY,
    code VARCHAR(400),
    fullname VARCHAR(400),
    sign VARCHAR(400)
);

Column	Type	Description
`id`	SERIAL	Primary key (auto-increment)
`code`	VARCHAR(400)	Currency code (e.g., USD, EUR, RUB)
`fullname`	VARCHAR(400)	Full currency name (e.g., US Dollar)
`sign`	VARCHAR(400)	Currency symbol (e.g., $, €, ₽)

exchangerates Table

Stores exchange rates between currency pairs:

CREATE TABLE exchangerates (
    id SERIAL PRIMARY KEY,
    basecurrencyid INT REFERENCES currencies(id),
    targetcurrencyid INT REFERENCES currencies(id),
    rate REAL
);

Column	Type	Description
`id`	SERIAL	Primary key (auto-increment)
`basecurrencyid`	INT	Foreign key to currencies table (base currency)
`targetcurrencyid`	INT	Foreign key to currencies table (target currency)
`rate`	REAL	Exchange rate from base to target currency

Indexes

Unique indexes ensure data integrity:

-- Ensure currency codes are unique
CREATE UNIQUE INDEX ix_code ON currencies (code);

-- Ensure each currency pair is unique
CREATE UNIQUE INDEX ix_currencyids ON exchangerates (basecurrencyid, targetcurrencyid);

The unique index on exchangerates prevents duplicate exchange rates for the same currency pair.

Database Initialization

The database is automatically initialized using SQL scripts mounted in the Docker container.

Initial Schema Setup

Create the tables

CREATE TABLE currencies (
    id SERIAL PRIMARY KEY,
    code VARCHAR(400),
    fullname VARCHAR(400),
    sign VARCHAR(400)
);

CREATE TABLE exchangerates (
    id SERIAL PRIMARY KEY,
    basecurrencyid INT REFERENCES currencies(id),
    targetcurrencyid INT REFERENCES currencies(id),
    rate REAL
);

Add indexes

CREATE UNIQUE INDEX ix_code ON currencies (code);
CREATE UNIQUE INDEX ix_currencyids ON exchangerates (basecurrencyid, targetcurrencyid);

Insert initial data

INSERT INTO currencies (code, fullname, sign)
VALUES 
    ('USD', 'US Dollar', '$'),
    ('RUB', 'Russian Ruble', '₽');

INSERT INTO exchangerates (basecurrencyid, targetcurrencyid, rate)
VALUES 
    (1, 2, 0.012),
    (2, 1, 82.25);

Complete Initialization Script

Here’s the complete SQL script from data.sql:

-- Create tables
CREATE TABLE currencies (
    id SERIAL PRIMARY KEY,
    code VARCHAR(400),
    fullname VARCHAR(400),
    sign VARCHAR(400)
);

CREATE TABLE exchangerates (
    id SERIAL PRIMARY KEY,
    basecurrencyid INT REFERENCES currencies(id),
    targetcurrencyid INT REFERENCES currencies(id),
    rate REAL
);

-- Insert test data
INSERT INTO currencies (code, fullname, sign)
VALUES 
    ('USD', 'US Dollar', '$'),
    ('RUB', 'Russian Ruble', '₽');

INSERT INTO exchangerates (basecurrencyid, targetcurrencyid, rate)
VALUES 
    (1, 2, 0.012),
    (2, 1, 82.25);

-- Add unique indexes
CREATE UNIQUE INDEX ix_code ON currencies (code);
CREATE UNIQUE INDEX ix_currencyids ON exchangerates (basecurrencyid, targetcurrencyid);

Running Migrations

The Currency Exchange API uses Entity Framework Core for database access. The application automatically applies migrations at startup.

Automatic Migration

The DbContext is configured in Program.cs (lines 20-23):

builder.Services.AddDbContext<CurrencyExchangeDBContext>(options =>
{
    options.UseNpgsql(builder.Configuration.GetConnectionString(nameof(CurrencyExchangeDBContext)));
});

The connection string is read from appsettings.json and must match your PostgreSQL configuration.

Manual Migration Commands

If you need to manage migrations manually:

dotnet ef migrations add InitialCreate --project CurrencyExchange.Data

Database Connection

Connection String

The connection string is configured in appsettings.json:

"ConnectionStrings": {
  "CurrencyExchangeDBContext": "Host=postgres;Database=currencies;Port=5432;User ID=alexpg;Password=76t34qVBh3;CommandTimeout=1200;"
}

Parameters:

Host=postgres - Database host (Docker service name or hostname)
Database=currencies - Database name
Port=5432 - PostgreSQL port
User ID=alexpg - Database user
Password=76t34qVBh3 - Database password
CommandTimeout=1200 - Command timeout in seconds (20 minutes)

For production, use environment variables to store the connection string securely:

export ConnectionStrings__CurrencyExchangeDBContext="Host=prod-db;Database=currencies;Port=5432;User ID=admin;Password=secure_password;CommandTimeout=1200;"

Testing the Connection

Verify the database connection using psql:

# Connect to the database
docker exec -it currencies-bd psql -U alexpg -d currencies

# List tables
\dt

# Query currencies
SELECT * FROM currencies;

# Query exchange rates
SELECT 
    c1.code AS base_currency,
    c2.code AS target_currency,
    e.rate
FROM exchangerates e
JOIN currencies c1 ON e.basecurrencyid = c1.id
JOIN currencies c2 ON e.targetcurrencyid = c2.id;

# Exit
\q

Data Management

Backup Database

docker exec currencies-bd pg_dump -U alexpg currencies > backup.sql

Restore Database

docker exec -i currencies-bd psql -U alexpg -d currencies < backup.sql

Reset Database

To completely reset the database:

Stop the application

docker stop currencies-backend

Drop and recreate the database

docker exec -it currencies-bd psql -U alexpg -c "DROP DATABASE IF EXISTS currencies;"
docker exec -it currencies-bd psql -U alexpg -c "CREATE DATABASE currencies;"

Restart the database container

docker-compose restart postgres

The initialization scripts will run automatically.

Start the application

docker start currencies-backend

Performance Optimization

Index Optimization

The database uses indexes to improve query performance:

-- Currency code lookups
CREATE UNIQUE INDEX ix_code ON currencies (code);

-- Exchange rate lookups by currency pair
CREATE UNIQUE INDEX ix_currencyids ON exchangerates (basecurrencyid, targetcurrencyid);

Consider adding additional indexes for frequent queries:

-- Optimize queries by target currency
CREATE INDEX ix_targetcurrency ON exchangerates (targetcurrencyid);

-- Optimize queries by currency name
CREATE INDEX ix_fullname ON currencies (fullname);

Connection Pooling

Entity Framework Core automatically manages connection pooling. The CommandTimeout=1200 setting in the connection string ensures long-running queries don’t timeout prematurely.

Monitoring and Maintenance

Check Database Size

SELECT 
    pg_size_pretty(pg_database_size('currencies')) AS database_size;

View Active Connections

SELECT 
    count(*) AS connection_count,
    state
FROM pg_stat_activity
WHERE datname = 'currencies'
GROUP BY state;

Monitor Query Performance

-- Enable query statistics
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- View slow queries
SELECT 
    query,
    calls,
    total_time,
    mean_time
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

Troubleshooting

Connection refused

Check if PostgreSQL is running:

docker ps | grep currencies-bd
docker logs currencies-bd

Verify the connection string matches your configuration.

Authentication failed

Ensure the username and password in appsettings.json match the database configuration:

"ConnectionStrings": {
  "CurrencyExchangeDBContext": "Host=postgres;Database=currencies;Port=5432;User ID=alexpg;Password=YOUR_PASSWORD;CommandTimeout=1200;"
}

Table does not exist

The database may not be initialized. Check if initialization scripts ran:

docker logs currencies-bd | grep "CREATE TABLE"

Manually run the initialization script if needed:

docker exec -i currencies-bd psql -U alexpg -d currencies < data.sql

Duplicate key violation

This occurs when trying to insert duplicate currency codes or exchange rate pairs. The unique indexes prevent duplicates:

Check existing data before inserting
Use ON CONFLICT clause for upserts:

INSERT INTO currencies (code, fullname, sign)
VALUES ('USD', 'US Dollar', '$')
ON CONFLICT (code) DO NOTHING;

Security Best Practices

Strong Passwords

Use strong, unique passwords for database users. Never use default passwords in production.

Limited Privileges

Create application-specific database users with minimal required privileges.

Encrypted Connections

Enable SSL/TLS for database connections in production environments.

Regular Backups

Schedule automated backups and test restore procedures regularly.

Getting Started

Deployment

Core Concepts

Database Overview

PostgreSQL Setup

Using Docker Compose

Manual PostgreSQL Installation

Database Schema

Tables

currencies Table

exchangerates Table

Indexes

Database Initialization

Initial Schema Setup

Complete Initialization Script

Running Migrations

Automatic Migration

Manual Migration Commands

Database Connection

Connection String

Testing the Connection

Data Management

Backup Database

Restore Database

Reset Database

Performance Optimization

Index Optimization

Connection Pooling

Monitoring and Maintenance

Check Database Size

View Active Connections

Monitor Query Performance

Troubleshooting

Security Best Practices

Strong Passwords

Limited Privileges

Encrypted Connections

Regular Backups

Build docs developers (and LLMs) love

Getting Started

Deployment

Core Concepts

​Database Overview

​PostgreSQL Setup

​Using Docker Compose

​Manual PostgreSQL Installation

​Database Schema

​Tables

​currencies Table

​exchangerates Table

​Indexes

​Database Initialization

​Initial Schema Setup

​Complete Initialization Script

​Running Migrations

​Automatic Migration

​Manual Migration Commands

​Database Connection

​Connection String

​Testing the Connection

​Data Management

​Backup Database

​Restore Database

​Reset Database

​Performance Optimization

​Index Optimization

​Connection Pooling

​Monitoring and Maintenance

​Check Database Size

​View Active Connections

​Monitor Query Performance

​Troubleshooting

​Security Best Practices

Strong Passwords

Limited Privileges

Encrypted Connections

Regular Backups

Build docs developers (and LLMs) love

Database Overview

PostgreSQL Setup

Using Docker Compose

Manual PostgreSQL Installation

Database Schema

Tables

currencies Table

exchangerates Table

Indexes

Database Initialization

Initial Schema Setup

Complete Initialization Script

Running Migrations

Automatic Migration

Manual Migration Commands

Database Connection

Connection String

Testing the Connection

Data Management

Backup Database

Restore Database

Reset Database

Performance Optimization

Index Optimization

Connection Pooling

Monitoring and Maintenance

Check Database Size

View Active Connections

Monitor Query Performance

Troubleshooting

Security Best Practices