Connect to an Aurora PostgreSQL Database

Create and query an Aurora PostgreSQL Serverless database using CloudShell.

Introduction

30 Second Summary

Every app you use stores information somewhere. Your Netflix watchlist, your online orders, your social media posts all live in databases that organize and retrieve data in milliseconds.

In this project, you will create your own cloud database using Amazon Aurora, connect to it directly from your browser, and write SQL queries to build and search a movie catalog.

What You'll Build

You spin up a database in seconds, connect without any passwords, and run queries that instantly answer questions like "What are the top-rated sci-fi movies?" and "How many movies per genre?"

By the end of this project, you'll have:

  • A live cloud database that you created in under 30 seconds using AWS's express configuration.
  • A browser-based connection to your database via CloudShell with automatic IAM authentication. No passwords needed.
  • A movie catalog you built with SQL, complete with queries that filter, sort, and summarize your data to answer real questions.
  • Secret Mission: Add a reviews table and perform a JOIN to connect movies with their reviews.

Are there any prerequisites?

You need an AWS account that is Free Tier eligible. All work in this project happens in the AWS Console and CloudShell, so you do not need to install anything on your computer.

Aurora PostgreSQL Serverless is included in the AWS Free plan with up to 4 ACUs and 1 GiB of storage per cluster.

Before We Start

Before We Start...

Before we dive in, let's lock in what you're about to build and why it matters.

Create Your Aurora PostgreSQL Database

You need a running database before you can store or query any data. Normally, setting up a database involves configuring networking, security groups, and authentication. That is a lot of infrastructure work before you can even write your first query.

In this step, you will create an Aurora PostgreSQL Serverless database using AWS's express configuration. Express configuration handles all that setup automatically, so your database is ready in seconds.

In this step, get ready to:
  • Sign in to the AWS Management Console and open the RDS console.
  • Create an Aurora PostgreSQL Serverless database using express configuration.
  • Verify the database status shows "Available."
Sign in and open the RDS console
  • Open your web browser and navigate to the AWS Management Console at https://console.aws.amazon.com.
  • Sign in with your AWS account credentials.
  • In the search bar at the top of the console, type RDS and select RDS from the results to open the Amazon RDS console.
  • In the navigation pane on the left, choose Databases.

You should see the Welcome to Aurora and RDS page with a section titled Create with express configuration in seconds.

What is Amazon Aurora?

Amazon Aurora is a managed relational database service built for the cloud. The Serverless version automatically scales compute capacity up and down based on demand, and scales to zero when idle.

You are using the PostgreSQL-compatible edition, which means you interact with it using standard SQL. AWS handles backups, patching, and high availability behind the scenes.

Create the database with express configuration
  • In the Create with express configuration in seconds section, choose Create.
  • A dialog titled Create with express configuration appears with pre-configured settings.
  • In the DB cluster identifier field, replace the default value with my-movies-db.
  • Leave the capacity range at its default setting.
  • Choose Create database.

What does express configuration handle for you?

Express configuration creates your database without a VPC and includes an internet access gateway for secure connections. This means you skip all networking and security group setup.

It also enables IAM authentication by default, so you connect with your AWS identity instead of managing database passwords. The default admin user is postgres.

Verify your database is available

A success banner appears at the top of the page confirming the creation. Your database should be ready in seconds.

Before you check, what status do you expect to see next to your cluster name?

  • On the Databases page, locate your cluster named my-movies-db.
  • Confirm the Status column shows Available.
  • Confirm the Engine column shows aurora-postgresql.

You now have a production-ready Aurora PostgreSQL Serverless database running in the cloud.

Database not showing Available?

If the status still shows Creating, wait a few more seconds and refresh the page. Express configuration databases typically become available within 30 seconds.

If you do not see your cluster at all, check that you are looking at the correct AWS Region in the top-right corner of the console.

help me troubleshoot why my Aurora database is not showing Available

Your database is live and ready to accept connections. Next up, you will connect to it directly from your browser using CloudShell.

Connect to Your Database via CloudShell

Your database is live in the cloud, but you have no way to interact with it yet. Right now it is an empty box waiting for instructions.

In this step, you'll open a browser-based terminal called CloudShell and connect to your database using IAM authentication. No passwords to remember, no local tools to install.

In this step, get ready to:
  • Navigate to your cluster's connection settings.
  • Launch CloudShell and connect to your database with IAM authentication.
  • Verify the connection works by listing tables.
Open your cluster's connectivity details

To connect to your database, you first need to find the connection options on your cluster's detail page.

  • In the RDS console, choose Databases in the left navigation pane.
  • Click your cluster name my-movies-db to open its details page.
  • Select the Connectivity & security tab.

You should see connection details for your cluster, including a section labeled Connect to database.

Launch CloudShell and connect

CloudShell is a browser-based terminal built into the AWS Console. It connects to your database using IAM authentication, which means AWS handles the credentials automatically through a temporary token.

  • In the Connect to database section, select CloudShell as the connection method.
  • Click Launch CloudShell.

A terminal panel opens at the bottom of your browser. The console pre-fills the connection command with your cluster's endpoint details and runs it automatically.

What is IAM authentication?

Instead of a username and password stored in the database, IAM authentication generates a temporary token that proves your identity. The token is valid for 15 minutes at a time.

Express configuration enabled this by default when you created your cluster. CloudShell handles the token exchange behind the scenes, so you never see or manage a password.

After a few seconds, you should see the postgres=> prompt. This confirms you are connected to your database and ready to run SQL commands.

Not seeing the postgres=> prompt?

If the shell shows an error or times out, your cluster might still be starting up. Return to the Databases page and confirm your cluster status is Available before trying again.

If CloudShell opens but the connection command fails, close the shell panel and click Launch CloudShell again. The pre-filled command should connect on retry.

Help me troubleshoot my CloudShell database connection

Verify your connection by listing tables

Before you run this next command, what do you think you'll see when you ask a brand-new database to list its tables?

  • At the postgres=> prompt, list all tables by running:
\dt

You should see the message Did not find any relations. This confirms two things: your connection is working, and the database is completely empty.

What does \dt mean?

The \dt command is a PostgreSQL shortcut that lists all tables in your current database. The backslash tells psql this is a meta-command (about the database) rather than a SQL query (against the data).

Your connection is live and your database is ready for data. Next up, you'll create a table structure and fill it with movie records to query against.

Create a Table and Populate It with Data

You have a live connection to your Aurora database. The postgres=> prompt is ready and waiting for commands.

Right now, the database is completely empty. You need to define what kind of data it will hold by creating a table with a specific structure, then fill it with records so you have something meaningful to query.

In this step, get ready to:
  • Create a movies table with a defined schema.
  • Insert 10 movie records spanning multiple genres and decades.
  • Verify your data is stored correctly with a query.
Create the movies table

Before you can store any data, you need to tell the database what shape that data will take. In SQL, this shape is called a schema. It defines the columns, their data types, and any constraints.

  • At the postgres=> prompt in CloudShell, create your movies table by running this command:
CREATE TABLE movies (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    genre VARCHAR(50) NOT NULL,
    release_year INTEGER NOT NULL,
    rating DECIMAL(2,1)
);

What does this code do?

  • id SERIAL PRIMARY KEY creates an auto-incrementing unique identifier for each movie. You never have to set this manually.
  • VARCHAR(200) and VARCHAR(50) are text columns with maximum character limits. NOT NULL means these columns cannot be left empty.
  • DECIMAL(2,1) stores a number with 2 total digits and 1 decimal place, perfect for ratings like 8.7 or 9.3.
  • rating is the only column without NOT NULL. A movie could exist in the table before it has a rating.

PostgreSQL responds with CREATE TABLE to confirm the table was created successfully.

  • Confirm the table exists by running:
\dt

You should see a table listing with movies in the Name column, table as the Type, and public as the Schema.

Seeing a syntax error?

  • Check for missing commas between column definitions. Each column line (except the last) needs a comma at the end.
  • Make sure the closing ); is on its own line. Without the semicolon, PostgreSQL waits for more input instead of executing.
  • If you see relation "movies" already exists, you already ran this command successfully. Run \dt to confirm and move on.

Need help? Why is my CREATE TABLE failing?

Populate the table with movie data

An empty table with no rows is just a structure. You need actual records to query against.

  • Insert 10 movies into your table by running this command:
INSERT INTO movies (title, genre, release_year, rating) VALUES
('The Matrix', 'Sci-Fi', 1999, 8.7),
('Inception', 'Sci-Fi', 2010, 8.8),
('The Shawshank Redemption', 'Drama', 1994, 9.3),
('The Dark Knight', 'Action', 2008, 9.0),
('Forrest Gump', 'Drama', 1994, 8.8),
('Interstellar', 'Sci-Fi', 2014, 8.6),
('The Godfather', 'Drama', 1972, 9.2),
('Mad Max: Fury Road', 'Action', 2015, 8.1),
('Parasite', 'Thriller', 2019, 8.5),
('Dune', 'Sci-Fi', 2021, 8.0);

What does this code do?

This single INSERT INTO statement adds 10 rows at once. Each set of parentheses is one movie record.

Notice you do not provide an id value. PostgreSQL fills that in automatically because the column is defined as SERIAL. The data spans four genres (Sci-Fi, Drama, Action, Thriller) and decades from 1972 to 2021. This variety makes queries more interesting in the next step.

PostgreSQL responds with INSERT 0 10 to confirm all 10 rows were inserted successfully.

Before you run the next command, what do you think you will see when you ask the database to show you everything in the movies table?

  • Verify all your movie data by running:
SELECT * FROM movies;

You should see a formatted table with 10 rows. Each row displays the auto-generated id, the movie title, genre, release_year, and rating columns filled with the values you inserted.

  • Run \dt one more time to confirm the movies table is listed.

Not seeing 10 rows?

  • If you see fewer rows, check that every line in your INSERT statement ends with a comma except the last one (which ends with a semicolon).
  • If you see an error about the table not existing, scroll up and make sure your CREATE TABLE command succeeded first. Look for the CREATE TABLE confirmation response.
  • If you accidentally ran INSERT twice and see 20 rows, you can reset by running DELETE FROM movies; and then running the INSERT again.

Need help? Why isn't my INSERT showing 10 rows?

✔️ Awesome, I've got everything!

Great. Confirm you see 10 rows when you run SELECT * FROM movies; and that \dt lists the movies table.

ⓧ I'd like to double check the full code

Here is the complete SQL you should have run in this step. Compare it against what you entered at the postgres=> prompt.

CREATE TABLE movies (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    genre VARCHAR(50) NOT NULL,
    release_year INTEGER NOT NULL,
    rating DECIMAL(2,1)
);

INSERT INTO movies (title, genre, release_year, rating) VALUES
('The Matrix', 'Sci-Fi', 1999, 8.7),
('Inception', 'Sci-Fi', 2010, 8.8),
('The Shawshank Redemption', 'Drama', 1994, 9.3),
('The Dark Knight', 'Action', 2008, 9.0),
('Forrest Gump', 'Drama', 1994, 8.8),
('Interstellar', 'Sci-Fi', 2014, 8.6),
('The Godfather', 'Drama', 1972, 9.2),
('Mad Max: Fury Road', 'Action', 2015, 8.1),
('Parasite', 'Thriller', 2019, 8.5),
('Dune', 'Sci-Fi', 2021, 8.0);

Your database now has real data you can search through. Next up, you will write queries that filter, sort, and summarize your movies to answer specific questions.

Query and Analyze Your Data

Your movies table is loaded with 10 records spanning four genres and nearly 50 years of cinema history. Now it is time to ask your data real questions.

Running SELECT * FROM movies dumps every row but answers no specific question. Real database work means filtering, sorting, and summarizing data to extract insights.

In this step, get ready to:
  • Filter movies by genre and year using WHERE and ORDER BY.
  • Find the top-rated movies using ORDER BY and LIMIT.
  • Summarize your catalog by genre using GROUP BY and aggregate functions.
Filter your movies with WHERE

The WHERE clause lets you pick out only the rows that match a condition. Combined with ORDER BY, you can filter and rank your results in one statement.

  • Find all Sci-Fi movies released after the year 2000 by running this query at the postgres=> prompt:
SELECT title, release_year, rating FROM movies
WHERE genre = 'Sci-Fi' AND release_year > 2000
ORDER BY rating DESC;

What does this query do?

  • WHERE genre = 'Sci-Fi' AND release_year > 2000 filters rows to only Sci-Fi movies from 2001 onward.
  • ORDER BY rating DESC sorts the remaining results from highest to lowest rating.
  • Only the title, release_year, and rating columns are returned, not the full row.

You should see three movies: Inception (2010, 8.8), Interstellar (2014, 8.6), and Dune (2021, 8.0), listed from highest to lowest rating.

Seeing zero rows?

  • Check that you typed 'Sci-Fi' with the exact hyphen and capitalization. SQL string matching is case-sensitive.
  • Make sure the query ends with a semicolon. Without it, PostgreSQL waits for more input and you will see postgres-# instead of results.
  • Why is my WHERE query returning no results?
Find the top-rated movies with ORDER BY and LIMIT

The LIMIT clause cuts your result set to a specific number of rows. Paired with ORDER BY, it answers questions like "What are the top 3?" instantly.

  • Find the top 3 highest-rated movies across all genres by running:
SELECT title, genre, rating FROM movies
ORDER BY rating DESC
LIMIT 3;

What does this query do?

  • ORDER BY rating DESC sorts all 10 movies by rating, highest first.
  • LIMIT 3 cuts the result to just the first three rows after sorting.
  • Together they answer "What are the three best-rated movies in my catalog?"

You should see The Shawshank Redemption (9.3), The Godfather (9.2), and The Dark Knight (9.0).

Results look wrong?

  • If you see more than 3 rows, check that you included LIMIT 3 at the end of your query.
  • If the order is ascending (lowest first), make sure you wrote DESC after ORDER BY rating to sort in descending order.
  • Help me fix my ORDER BY and LIMIT query.
Summarize your catalog with GROUP BY

Aggregate functions let you compute statistics across groups of rows. Instead of looking at individual movies, you can answer questions like "How many movies per genre?" and "What is the average rating for each genre?"

Before you run the next query, predict: which genre do you think has the highest average rating in your catalog?

  • Run this aggregate query to find out:
SELECT genre, COUNT(*) AS total, ROUND(AVG(rating), 1) AS avg_rating
FROM movies
GROUP BY genre
ORDER BY avg_rating DESC;

What does this query do?

  • GROUP BY genre groups all rows by their genre value, creating one bucket per genre.
  • COUNT(*) counts how many movies exist in each group.
  • AVG(rating) calculates the average rating per group.
  • ROUND(..., 1) rounds the average to one decimal place for readability.
  • AS total and AS avg_rating give the computed columns readable names in your output.

You should see a summary table with four rows. Drama leads with an average rating of 9.1 across 3 movies, followed by Action (8.6, 2 movies), Sci-Fi (8.5, 4 movies), and Thriller (8.5, 1 movie).

Seeing an error about GROUP BY?

  • Every column in your SELECT that is not inside an aggregate function (like COUNT or AVG) must appear in the GROUP BY clause.
  • Make sure genre appears after both SELECT and GROUP BY.
  • Help me fix my GROUP BY query.

Secret mission

Add a Reviews Table and Perform a JOIN

Your movie database has one table, but real databases connect multiple tables together. Can you create a reviews table and use JOIN queries to bring related data together across tables?

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project uses Aurora PostgreSQL Serverless under the AWS Free Tier, which provides up to 4 ACUs and 1 GiB of storage per cluster for up to 6 months.

Cost warning

While your database is within Free Tier limits, leaving the cluster running indefinitely will eventually consume your Free Tier credits. Aurora Serverless scales to zero ACUs when idle, but the cluster still occupies your Free Tier slot.

For a learning project, deleting the cluster after you finish is recommended. You can always create a new one in seconds with express configuration.

Resources you used:

  • 1 Aurora PostgreSQL Serverless cluster (my-movies-db) consuming Free Tier ACUs while running.

Keep everything running

No action needed. Choose this if you are still actively building or plan to continue experimenting with your database right away.

  • Your my-movies-db cluster stays available in the RDS console.
  • Aurora Serverless automatically scales down to zero ACUs when idle, minimizing credit usage.
  • Monitor your Free Tier usage in the AWS Billing and Cost Management dashboard to avoid unexpected charges.

Pause - I'll come back to this later

Aurora Serverless scales to zero automatically when idle, so there is no explicit pause action required. Close your CloudShell session and the cluster will stop consuming ACUs on its own.

  • Close the CloudShell tab in your browser.
  • Your cluster remains in the RDS console with status Available and will scale to zero when no connections are active.
  • When you return, open the cluster in the RDS console and launch CloudShell again from the Connectivity & security tab.

Delete - I don't want to use this again

Remove the Aurora cluster entirely. This permanently deletes your database and stops all charges.

  • Close CloudShell if it is still open.
  • In the RDS console, choose Databases in the navigation pane.
  • Select your my-movies-db cluster.
  • Choose Actions, then Delete.
  • When prompted, uncheck Create final snapshot. For a learning database, you do not need a snapshot.
  • Confirm the deletion. The cluster and all its data (your movies and reviews tables) are permanently removed.

That's a Wrap!

That's a Wrap!

Nice work! You just created a fully functional cloud database, connected to it from your browser, and wrote SQL to build and query a movie catalog.

You've learned how to:

  • Created an Aurora PostgreSQL Serverless database in seconds using express configuration, with networking and IAM authentication handled automatically.
  • Connected to your cloud database via CloudShell with automatic IAM authentication, no passwords or local tools needed.
  • Wrote SQL to create a table schema, insert data, and run queries using WHERE, ORDER BY, LIMIT, and aggregate functions like COUNT, AVG, and GROUP BY.
  • Secret Mission: Built a second related table with foreign keys and used JOIN to connect data across tables.

Ready to quiz yourself?