Solve a DVD rental mystery with pgweb

Why do humans build software? The answer is deceptively simple - to move data. While our applications process data, they are not the only places data resides. And when it comes to handling and visualizing that data, PostgreSQL® and command-line tools might be great, but they are also daunting. In this blog, I will discuss the need for Graphical User Interface (GUI) tools for databases and go over an open-source tool, pgweb, to help solve a DVD mystery, and we might visualize some PostgreSQL data in the process.

GUI - not just for applications

Before we start using the tool, it's important to note: a database GUI tool is not a replacement for a command-line tool. However, a GUI tool allows you to connect to the application or database quickly and easily. Here are the three use cases where a database GUI tool would complement the command-line options:

1. Cut the learning curve: Whether connecting to a database or running queries on tables, a GUI tool shows intuitive ways to perform the task. For example, here is the documented way to connect to a PostgreSQL database using the command line:

   psql 'postgres://avnadmin:PASSWORD@demo-pg-dev-advocates.aivencloud.com:13039/defaultdb?sslmode=require'
   Copy to clipboard

   A new user might not know how to assemble all the information in that command, and whether to use quotes or not.

   Here is an example from a GUI tool:

   

   The user has explicit places to enter host, port, username, password and database name, and can then click Connect.

2. Increase productivity: If your day-to-day tasks include switching between multiple databases and looking at some tables, you can do this faster from the clicks of a mouse than executing commands. The GUI tool would display various databases, tables, and schemas as folder or file hierarchies, allowing for faster navigation.

3. Additional options: While these features vary from tool to tool, some database GUI tools allow full text search, performance tuning on queries, a complete history of SQL commands, etc.

With that out of the way, let's focus on the DVD mystery we have to solve as a team. Crab Rental is a fictitious DVD rental store that exists in a parallel world where Netflix doesn't exist yet. Crab Rental is facing some challenges around inventory and IT security. The management hired and tasked you with these challenges:

1. Due to a glitch in the system, some customers are not returning their DVDs. Let's find the glitch.
2. Customer satisfaction is dropping. Is the film inventory reflecting customer demand?
3. A lot of people have access to the Crab Rental database. Let's find a way to get details on the requests being made to this database.

Pgweb - a cross-platform open-source tool for PostgreSQL

Since management doesn’t know SQL, you choose pgweb to provide a low-code access to the data that can run on a browser. Pgweb is a free and open-source tool written in GoLang.

If you don't have a PostgreSQL server ready, you can sign-up with Aiven and deploy one using the available free credits.

Follow the installation steps to install pgweb on your machine.

Note This blog uses pgweb version 0.11.11. Some features and appearances might differ in future versions.

Connect to the database

You have multiple options to start the pgweb server and connect to the PostgreSQL server. You can start the server in one of these ways:

1. Start the server and enter the database connection information manually.

   pgweb
   Copy to clipboard

2. Start the server by providing connection flags:

   pgweb --host $HOSTNAME --user $USERNAME --db $DBNAME
   Copy to clipboard

3. Using a connection URI to start the server:

   pgweb --url postgres://$USERNAME:$PASSWORD@$HOSTNAME:$PORTNUM/$DBNAME?sslmode=[mode]
   Copy to clipboard

If you're using Aiven for PostgreSQL, the connection information can be found under the Overview tab.

Note This blog uses PostgreSQL version 14.4.

Once the pgweb server has started, navigate to http://localhost:8081 to interact with the tool.

Load sample data

Pagila is a PostgreSQL port of the Sakila Sample Database. Load Pagila to your PostgreSQL database by following this guide.

Let's put pgweb to the test. While you're connected to the Pagila database, click on the Query tab on pgweb and run the following query:

select
    customer.first_name,
    customer.last_name,
    count(customer.first_name) rentals_count
from
    customer
left join rental on
    customer.customer_id = rental.customer_id
group by
    customer.first_name,
    customer.last_name
order by rentals_count desc;
Copy to clipboard

Who do you see as the person with the most rented DVDs? If it's ELEANOR HUNT, you've loaded the database correctly.

Solve DVD mystery using pgweb

Customers are not returning DVDs

Your initial task is to investigate why some customers are not returning DVDs. You quickly find out that some customers do not know the return date of their rentals.

You can see this by clicking on the Query tab and running the following query:

select
    rental.rental_id, rental.customer_id, rental.rental_date, customer.email
    FROM rental
    left join customer on
    customer.customer_id = rental.customer_id
    WHERE return_date IS NULL;
Copy to clipboard

All of these customers have a NULL value as their rental.return_date. We can use pgweb to export the list and details of customers as JSON, CSV, or XML. Let’s send this information to the IT department so that they can email these customers to return their rentals.

Let's create a view so that management can monitor Crab Rental's business performance without having to worry about the technical details of writing and running SQL queries. In SQL, a view is a virtual table based on the result-set of an SQL statement. To create a view, we run the same SQL statement after prepending a CREATE VIEW...:

CREATE VIEW customers_no_return_rental_date AS
select
    rental.rental_id, rental.customer_id, rental.rental_date, customer.email
    FROM rental
    left join customer on
    customer.customer_id = rental.customer_id
    WHERE return_date IS NULL;
Copy to clipboard

To see this view ourselves, let’s log on to the pgweb dashboard, expand the Views dropdown, and click on customers_no_return_rental_date to see the same information; without writing any SQL.

Our first mystery is solved, and Crab Rental has a new source of revenue in the form of late fees!

Film inventory reflecting demand (or not)

With our newly-gained confidence, let's dive into solving the second mystery. For this task, let's make use of two views: one for categories of movies making the most sales (indicating demand) and the other for inventory count based on categories. The first view, sales_by_film_category, already exists in the sample database. Execute the following SQL statement in the Query tab to generate inventory_by_film_category view:

CREATE VIEW inventory_by_film_category AS
select
    category.name,
    count(category.name) inventory_count
from
    category
left join film_category on
    category.category_id = film_category.category_id
left join inventory on
    film_category.film_id = inventory.film_id
group by
    category.name
order by
    inventory_count desc;
Copy to clipboard

Now management can check sales by film category, and inventory count by film category, by clicking on the views:

Based on the above information, let’s make a recommendation to the management to order more drama and comedy movies. Because these views can be monitored at any time without any technical expertise, the Crab Rental management can rebalance their inventory without requiring a data analyst.

Audit the access to the Crab Rental database

Crab Rental has hired an external company to perform a security audit across the data platform. Crab Rental uses a PostgreSQL database, and a number of employees have access to the database credentials. Let’s make sure that our PostgreSQL database is in order and there’s no unneccessary access. PostreSQL has built-in audit features and pgweb has a convenient way to access these. Let’s check who has been accessing our database using the SQL command history. From the History tab, we can see the following sample query list:

Based on the timestamp, they can check details on the activity from the Activity tab, which shows the username, application/client name, client IP/port, and other details that can be critical when an audit happens.

Wrap up

Thanks to our collective investigation, Crab Rental will survive the collapse of the DVD rental industry for at least a little longer. Although this was a fictitious example, a lot of companies use these techniques to make smart data-driven decisions. Here are three takeaways from this blog:

1. A GUI tool for databases is not a replacement, but rather a complement to the command-line tools.
2. A GUI tool can increase productivity, help reduce the learning curve, and offer other features like full text search, performance tuning, etc.
3. Pgweb is just one of the options when it comes to GUI tools for databases. You can choose the tool that makes sense for your use case.

If you're looking for a fully managed and hosted PostgreSQL, check out Aiven for PostgreSQL® and reach out if you have any questions.