database-connection

Local Database

Extract variables from .env:

grep '^DB_' .env

Parse output, construct postgresql URL, then connect:

psql "postgresql://{DB_USER}:{DB_PASSWORD}@{DB_HOST}:{DB_PORT}/{DB_NAME}"

Default local connection:

PGPASSWORD=postgres psql -h localhost -p 5432 -U postgres -d iota_erp

Staging Database

Extract public database URL from Railway:

railway variables -e staging -s db --kv | grep DATABASE_PUBLIC_URL

Connect using extracted URL:

psql <DATABASE_PUBLIC_URL>

Example for IOTA SDK staging:

# Get connection details from Railway
railway variables -e staging -s db --kv

# Connect (example)
PGPASSWORD=<password> psql -h <host> -U postgres -p <port> -d railway

Helper Queries

Common diagnostics after connecting:

Table Sizes

SELECT schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename))
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 10;

Active Connections

SELECT datname, count(*)
FROM pg_stat_activity
GROUP BY datname;

Recent Migrations

-- Schema migrations (sql-migrate)
SELECT version, applied_at
FROM schema_migrations
ORDER BY applied_at DESC
LIMIT 5;

Database Size

SELECT pg_size_pretty(pg_database_size(current_database()));

Tenant Statistics

-- Count tenants
SELECT COUNT(*) as tenant_count FROM tenants WHERE deleted_at IS NULL;

-- Count users per tenant
SELECT t.name, COUNT(u.id) as user_count
FROM tenants t
LEFT JOIN users u ON u.tenant_id = t.id AND u.deleted_at IS NULL
WHERE t.deleted_at IS NULL
GROUP BY t.id, t.name
ORDER BY user_count DESC
LIMIT 10;

Organization Statistics

-- Count organizations
SELECT COUNT(*) as org_count FROM organizations WHERE deleted_at IS NULL;

-- Organizations per tenant
SELECT t.name, COUNT(o.id) as org_count
FROM tenants t
LEFT JOIN organizations o ON o.tenant_id = t.id AND o.deleted_at IS NULL
WHERE t.deleted_at IS NULL
GROUP BY t.id, t.name
ORDER BY org_count DESC;

Common Troubleshooting Queries

Find Long-Running Queries

SELECT pid, now() - pg_stat_activity.query_start AS duration, query
FROM pg_stat_activity
WHERE state = 'active'
  AND now() - pg_stat_activity.query_start > interval '1 minute'
ORDER BY duration DESC;

Check Table Bloat

SELECT tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as total_size,
       pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) as table_size,
       pg_size_pretty(pg_indexes_size(schemaname||'.'||tablename)) as indexes_size
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 20;

Find Missing Indexes

SELECT schemaname, tablename, attname, n_distinct, correlation
FROM pg_stats
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
  AND n_distinct > 100
  AND correlation < 0.1
ORDER BY n_distinct DESC
LIMIT 20;

Check Lock Conflicts

SELECT blocked_locks.pid AS blocked_pid,
       blocked_activity.usename AS blocked_user,
       blocking_locks.pid AS blocking_pid,
       blocking_activity.usename AS blocking_user,
       blocked_activity.query AS blocked_statement,
       blocking_activity.query AS blocking_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.database IS NOT DISTINCT FROM blocked_locks.database
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

Multi-Tenant Verification

Verify Tenant Isolation

-- Check if tenant_id is consistently applied
SELECT table_name
FROM information_schema.columns
WHERE table_schema = 'public'
  AND table_name NOT IN ('tenants', 'schema_migrations', 'sessions')
  AND table_name NOT LIKE 'pg_%'
  AND table_name NOT IN (
    SELECT table_name
    FROM information_schema.columns
    WHERE table_schema = 'public'
      AND column_name = 'tenant_id'
  );

Verify Organization Isolation

-- Check if organization_id is applied where needed
SELECT table_name, column_name
FROM information_schema.columns
WHERE table_schema = 'public'
  AND column_name = 'organization_id';

Database Maintenance

Vacuum Statistics

SELECT schemaname, tablename,
       last_vacuum, last_autovacuum,
       last_analyze, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY last_autovacuum DESC NULLS LAST;

Reindex Table

-- Check for bloated indexes
REINDEX TABLE table_name;

Analyze Table

-- Update statistics
ANALYZE table_name;

Connection Tips

Using Environment Variables

# Export from .env
export $(grep '^DB_' .env | xargs)

# Connect using exported vars
PGPASSWORD=$DB_PASSWORD psql -h $DB_HOST -p $DB_PORT -U $DB_USER -d $DB_NAME

Connection Pooler

# If using pgbouncer
psql "postgresql://$DB_USER:$DB_PASSWORD@localhost:6432/$DB_NAME"

SSL Connections

# For production/staging with SSL
psql "postgresql://$DB_USER:$DB_PASSWORD@$DB_HOST:$DB_PORT/$DB_NAME?sslmode=require"

Safety Checks

Before Making Changes

-- Always verify you're on the right database
SELECT current_database();

-- Check current schema
SELECT current_schema();

-- Verify tenant context if applicable
-- (Application-level check, not SQL)

Backup Before Destructive Operations

# Dump specific table
pg_dump -h $DB_HOST -U $DB_USER -d $DB_NAME -t table_name > backup.sql

# Dump entire database
pg_dump -h $DB_HOST -U $DB_USER -d $DB_NAME > full_backup.sql

Quick Reference

# Local connection
PGPASSWORD=postgres psql -h localhost -p 5432 -U postgres -d iota_erp

# Staging connection (Railway)
railway variables -e staging -s db --kv | grep DATABASE_PUBLIC_URL
psql <DATABASE_PUBLIC_URL>

# Run query from file
psql -h localhost -U postgres -d iota_erp -f query.sql

# Export to CSV
psql -h localhost -U postgres -d iota_erp -c "COPY (SELECT * FROM users) TO STDOUT WITH CSV HEADER" > users.csv

# List all tables
\dt

# Describe table
\d table_name

# List all indexes
\di

# Quit
\q