Database Access via Docker CLI: Complete Guide

Managing databases through Docker containers is a common practice in modern development. While graphical tools like DBeaver and pgAdmin are powerful, knowing how to access and manage databases directly via command line is an essential skill for any developer. This guide shows you how to efficiently work with databases using Docker CLI.

Why Use Command Line?

• Speed: Instant access without opening heavy applications
• Lightweight: Doesn't consume extra resources
• Practical: Ideal for quick checks and debugging
• Professional: Essential for production environments
• Learning: Master pure SQL and native commands
• Automation: Easy to script and integrate into CI/CD pipelines

PostgreSQL

Connecting to the Database

# Basic syntax
docker exec -it <container_name> psql -U <username> -d <database>

# Example
docker exec -it postgres psql -U monitor -d monitordb
  

Essential psql Commands

Listing and Navigation

\l                    -- List all databases
\c database_name      -- Connect to another database
\dt                   -- List all tables
\d table_name         -- Show table structure
\d+ table_name        -- Detailed table structure
\dn                   -- List schemas
\du                   -- List users/roles
\df                   -- List functions
\dv                   -- List views
  

SQL Queries

-- Query data
SELECT * FROM "Metrics";
SELECT * FROM "AlertRules" LIMIT 10;
SELECT COUNT(*) FROM "MonitorTargets";

-- Filter data
SELECT * FROM "Metrics" WHERE "Type" = 'CPU';
SELECT * FROM "Metrics" ORDER BY "Timestamp" DESC LIMIT 5;

-- Insert data
INSERT INTO "MonitorTargets" ("Name", "Url", "IsActive") 
VALUES ('Test Server', 'http://localhost:8080', true);

-- Update data
UPDATE "MonitorTargets" SET "IsActive" = false WHERE "Name" = 'Test Server';

-- Delete data
DELETE FROM "Metrics" WHERE "Timestamp" < NOW() - INTERVAL '30 days';
  

Administration

-- View active connections
SELECT * FROM pg_stat_activity;

-- Database size
SELECT pg_size_pretty(pg_database_size('monitordb'));

-- Table sizes
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

-- Clear cache
VACUUM ANALYZE;

-- View configurations
SHOW ALL;
SHOW max_connections;
SHOW shared_buffers;

-- Exit psql
\q
  

Execute SQL Directly (Without Entering psql)

# Execute single command
docker exec -it postgres psql -U monitor -d monitordb -c "SELECT COUNT(*) FROM \"Metrics\";"

# Execute SQL file
docker exec -i postgres psql -U monitor -d monitordb < script.sql

# Export data to CSV
docker exec -it postgres psql -U monitor -d monitordb -c "COPY \"Metrics\" TO STDOUT WITH CSV HEADER" > metrics.csv
  

Backup and Restore

# Full backup
docker exec -t postgres pg_dump -U monitor monitordb > backup.sql

# Compressed backup
docker exec -t postgres pg_dump -U monitor monitordb | gzip > backup.sql.gz

# Restore
docker exec -i postgres psql -U monitor -d monitordb < backup.sql

# Restore compressed
gunzip -c backup.sql.gz | docker exec -i postgres psql -U monitor -d monitordb
  

SQL Server

Connecting to the Database

# Basic syntax
docker exec -it <container_name> /opt/mssql-tools/bin/sqlcmd -S localhost -U <username> -P <password>

# Example
docker exec -it sqlserver /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P "YourStrong@Passw0rd"
  

Essential sqlcmd Commands

-- Select database
USE monitordb;
GO

-- List tables
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE';
GO

-- Table structure
EXEC sp_help 'Metrics';
GO

-- Query data
SELECT * FROM Metrics;
GO

-- Exit
EXIT
  

Execute SQL Directly

# Single command
docker exec -it sqlserver /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P "YourStrong@Passw0rd" -Q "SELECT COUNT(*) FROM monitordb.dbo.Metrics"

# Execute SQL file
docker exec -i sqlserver /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P "YourStrong@Passw0rd" -i script.sql
  

MongoDB

Connecting to the Database

# Basic syntax
docker exec -it <container_name> mongosh -u <username> -p <password> --authenticationDatabase admin

# Example
docker exec -it mongodb mongosh -u admin -p admin123 --authenticationDatabase admin
  

Essential mongosh Commands

// List databases
show dbs

// Use database
use monitordb

// List collections
show collections

// Query documents
db.metrics.find()
db.metrics.find().limit(10)
db.metrics.find({ type: "CPU" })
db.metrics.find().sort({ timestamp: -1 }).limit(5)

// Count documents
db.metrics.countDocuments()

// Insert document
db.metrics.insertOne({ type: "CPU", value: 75.5, timestamp: new Date() })

// Update document
db.metrics.updateOne({ type: "CPU" }, { \$set: { value: 80 } })

// Delete document
db.metrics.deleteMany({ timestamp: { \$lt: new Date("2024-01-01") } })

// Exit
exit
  

Redis

Connecting to Redis

# Basic syntax
docker exec -it <container_name> redis-cli -a <password>

# Example
docker exec -it redis redis-cli -a redis123
  

Essential redis-cli Commands

# List all keys
KEYS *

# Get value of a key
GET metric:cpu:server1

# Set value
SET metric:cpu:server1 "75.5"

# Set with expiration (TTL in seconds)
SETEX metric:cpu:server1 3600 "75.5"

# View remaining TTL
TTL metric:cpu:server1

# Delete key
DEL metric:cpu:server1

# Clear entire database
FLUSHDB

# Server information
INFO

# Monitor commands in real-time
MONITOR

# Exit
EXIT
  

RabbitMQ

Management via CLI

# List queues
docker exec -it rabbitmq rabbitmqctl list_queues

# List exchanges
docker exec -it rabbitmq rabbitmqctl list_exchanges

# List bindings
docker exec -it rabbitmq rabbitmqctl list_bindings

# List connections
docker exec -it rabbitmq rabbitmqctl list_connections

# List users
docker exec -it rabbitmq rabbitmqctl list_users

# Cluster status
docker exec -it rabbitmq rabbitmqctl cluster_status

# Purge queue
docker exec -it rabbitmq rabbitmqctl purge_queue queue_name
  

Useful Docker Commands

Container Management

# List running containers
docker ps

# List all containers
docker ps -a

# View container logs
docker logs <container_name>
docker logs -f <container_name>  # Follow (real-time)
docker logs --tail 100 <container_name>  # Last 100 lines

# Stop container
docker stop <container_name>

# Start container
docker start <container_name>

# Restart container
docker restart <container_name>

# Remove container
docker rm <container_name>
docker rm -f <container_name>  # Force removal

# View resource usage
docker stats

# Inspect container
docker inspect <container_name>
  

Volume Management

# List volumes
docker volume ls

# Inspect volume
docker volume inspect <volume_name>

# Remove volume
docker volume rm <volume_name>

# Remove unused volumes
docker volume prune
  

Docker Compose

# Start all services
docker-compose up -d

# View logs of all services
docker-compose logs -f

# View logs of specific service
docker-compose logs -f postgres

# Stop all services
docker-compose stop

# Stop and remove containers
docker-compose down

# Stop, remove containers and volumes
docker-compose down -v

# Recreate containers
docker-compose up -d --force-recreate

# View service status
docker-compose ps
  

Tips and Best Practices

When to Use Command Line

• Quick data checks
• Development debugging
• Automation scripts
• Production environments (SSH)
• CI/CD pipelines

When to Use GUI Tools (DBeaver, pgAdmin)

• Exploratory data analysis
• Complex queries with multiple JOINs
• Relationship visualization
• Bulk data editing
• ER diagram generation

Important Precautions

-- ❌ NEVER do this in production without backup
DROP TABLE Metrics;
DELETE FROM Metrics;  -- Without WHERE
TRUNCATE TABLE Metrics;

-- ✅ ALWAYS backup first
docker exec -t postgres pg_dump -U monitor monitordb > backup_\$(date +%Y%m%d).sql

-- ✅ Use transactions for critical operations
BEGIN;
DELETE FROM Metrics WHERE Timestamp < '2024-01-01';
-- Check result
SELECT COUNT(*) FROM Metrics;
-- If OK: COMMIT; If not: ROLLBACK;
  

Useful Aliases (Optional)

Add to your .bashrc or .zshrc:

# PostgreSQL
alias pgcli='docker exec -it postgres psql -U monitor -d monitordb'
alias pgdump='docker exec -t postgres pg_dump -U monitor monitordb'

# Redis
alias rediscli='docker exec -it redis redis-cli -a redis123'

# MongoDB
alias mongocli='docker exec -it mongodb mongosh -u admin -p admin123'

# Docker Compose
alias dcup='docker-compose up -d'
alias dcdown='docker-compose down'
alias dclogs='docker-compose logs -f'
alias dcps='docker-compose ps'
  

Then simply use:

pgcli
rediscli
mongocli
  

Practical Exercises

Basic Level

1. Connect to PostgreSQL and list all tables
2. Query the last 10 records from the Metrics table
3. Count how many MonitorTargets are active

Intermediate Level

4. Create a database backup
5. Export the Metrics table to CSV
6. Calculate the average CPU for the last 7 days

Advanced Level

7. Create a script that automates daily backups
8. Implement a query that identifies anomalous metrics
9. Configure a custom alias for quick access

Real-World Scenarios

Scenario 1: Quick Production Debug

# SSH into production server
ssh user@production-server

# Check if database is responding
docker exec -it postgres psql -U app -d appdb -c "SELECT 1;"

# Check recent errors
docker exec -it postgres psql -U app -d appdb -c "SELECT * FROM error_logs ORDER BY created_at DESC LIMIT 10;"

# Check active connections
docker exec -it postgres psql -U app -d appdb -c "SELECT count(*) FROM pg_stat_activity;"
  

Scenario 2: Data Migration

# Export from old database
docker exec -t old_postgres pg_dump -U user -d olddb > migration.sql

# Import to new database
docker exec -i new_postgres psql -U user -d newdb < migration.sql

# Verify data
docker exec -it new_postgres psql -U user -d newdb -c "SELECT COUNT(*) FROM users;"
  

Scenario 3: Performance Analysis

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

-- Find missing indexes
SELECT 
    schemaname,
    tablename,
    attname,
    n_distinct,
    correlation
FROM pg_stats
WHERE schemaname = 'public'
ORDER BY n_distinct DESC;

-- Check table bloat
SELECT 
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
  

Automation Scripts

Daily Backup Script

#!/bin/bash
# backup.sh

DATE=\$(date +%Y%m%d_%H%M%S)
BACKUP_DIR="/backups"
CONTAINER="postgres"
USER="monitor"
DATABASE="monitordb"

# Create backup
docker exec -t \$CONTAINER pg_dump -U \$USER \$DATABASE | gzip > \$BACKUP_DIR/backup_\$DATE.sql.gz

# Keep only last 7 days
find \$BACKUP_DIR -name "backup_*.sql.gz" -mtime +7 -delete

echo "Backup completed: backup_\$DATE.sql.gz"
  

Health Check Script

#!/bin/bash
# health_check.sh

CONTAINER="postgres"
USER="monitor"
DATABASE="monitordb"

# Check if container is running
if ! docker ps | grep -q \$CONTAINER; then
    echo "❌ Container \$CONTAINER is not running"
    exit 1
fi

# Check database connectivity
if docker exec -it \$CONTAINER psql -U \$USER -d \$DATABASE -c "SELECT 1;" > /dev/null 2>&1; then
    echo "✅ Database is healthy"
else
    echo "❌ Database connection failed"
    exit 1
fi

# Check disk space
SIZE=\$(docker exec -it \$CONTAINER psql -U \$USER -d \$DATABASE -t -c "SELECT pg_database_size('\$DATABASE');" | tr -d ' ')
echo "📊 Database size: \$(numfmt --to=iec \$SIZE)"
  

Common Issues and Solutions

Issue: Permission Denied

# Problem
docker exec -it postgres psql -U monitor -d monitordb
# psql: FATAL: role "monitor" does not exist

# Solution: Check existing users
docker exec -it postgres psql -U postgres -c "\du"

# Create user if needed
docker exec -it postgres psql -U postgres -c "CREATE USER monitor WITH PASSWORD 'password';"
docker exec -it postgres psql -U postgres -c "GRANT ALL PRIVILEGES ON DATABASE monitordb TO monitor;"
  

Issue: Container Not Found

# Problem
docker exec -it postgres psql
# Error: No such container: postgres

# Solution: List containers
docker ps -a

# Use correct container name or ID
docker exec -it actual_container_name psql
  

Issue: Too Many Connections

-- Check current connections
SELECT count(*) FROM pg_stat_activity;

-- Kill idle connections
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE state = 'idle' 
AND state_change < NOW() - INTERVAL '10 minutes';

-- Increase max_connections (requires restart)
ALTER SYSTEM SET max_connections = 200;
  

Resources and Documentation

• PostgreSQL Documentation
• SQL Server Documentation
• MongoDB Documentation
• Redis Documentation
• Docker Documentation
• RabbitMQ Documentation

Conclusion

Mastering database access via Docker CLI is an essential skill for modern developers. While GUI tools have their place, command-line proficiency enables faster debugging, easier automation, and better understanding of database operations.

Start by practicing the basic commands daily. Create aliases for frequently used operations. Build automation scripts for repetitive tasks. Over time, you'll find that command-line database management becomes second nature and significantly improves your productivity.

Remember: the goal isn't to replace GUI tools entirely, but to have both options available and choose the right tool for each situation. Command line for quick operations and automation, GUI tools for complex analysis and visualization.

Practice these commands regularly, and you'll become proficient in database management via Docker CLI!