Accessing and Managing Your RDS Database

This guide will walk you through accessing your PostgreSQL database, getting the connection details needed for the Neon setup, and seeding the database with sample data.

🔑 Getting Your Database Connection Details

Before connecting to the database, you’ll need to get your connection details. These are stored as environment variables.
💡 Pro Tip: Copy these values somewhere safe - you’ll need them later!

  1. Open a new terminal in VSCode Server.
  2. Run these commands to view your connection details in your VSCode Terminal:
echo $DB_ENDPOINT     # Shows your database endpoint  
echo $DB_USERNAME     # Shows your database username  
echo $DB_PASSWORD     # Shows your database password  
echo $DB_NAME         # Shows your database name  
echo $PROD_DATABASE_URL    # Shows the full connection string

Screenshot of Environment Variable Output

🔄 Seeding the Database

Once you have confirmed your database connection details, follow these steps to seed the database with sample data:

  1. Decompress and load the dataset into the database:
echo "Decompressing dataset..."  
pg_restore -O -U $DB_USERNAME -d $DB_NAME -h $DB_ENDPOINT /tmp/employees.sql.gz  
echo "Dataset decompressed"

Screenshot of Dataset Decompression

  1. Set up schema access for easier database usage:
echo "Setting up schema access..."  
psql -h $DB_ENDPOINT -U $DB_USERNAME -d $DB_NAME
echo "Schema access setup complete"

Set search path for easier access

ALTER DATABASE employees SET search_path TO employees, public;

Screenshot of Schema Setup

  1. Enter the following command while connected to the database to set the current session’s search path:
-- Set current session's search path  
SET search_path TO employees, public;

🔍 Exploring the Database

Once the dataset is loaded, you can explore it using these commands:

  • List all Schemas and tables:
\d

Screenshot of List Tables Output Note: You may have to exit (Enter: \q) and reenter the Database if no relations are found initially.

  • View detailed table information for the employee table:
\d+ employee

Screenshot of Table Information

  • Run a sample query:
SELECT COUNT(*) FROM department;

Screenshot of Sample Query Output

Exit the database:

\q

🔧 Troubleshooting

Can’t see your environment variables? Try these steps:

  1. Close and reopen your terminal.

  2. Check if the variables are in /etc/environment:

cat /etc/environment
  1. Source the environment file:
source /etc/environment

🛑 Database Issues: If you’re having trouble connecting to the database or the employees data isn’t loading properly, run:

sudo /usr/local/bin/setup_db.sh

This script will reload the sample database and set up all necessary configurations.

⚠️ Security Note: Keep your database credentials secure and never share them with others!

🎯 Next Steps: Once you have your connection details and the database is seeded, proceed to the Neon setup section.