8 Sept 2022

Solve a DVD rental mystery with pgweb

Working with command line tools and SQL can be intimidating. Read on to learn how to use the pgweb GUI to provide useful views of PostgreSQL® data.

dewan-ahmed

Dewan Ahmed

|

RSS Feed

Senior Developer Advocate

Solve a DVD rental mystery with pgweb illustration

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.

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:
shell psql 'postgres://avnadmin:PASSWORD@demo-pg-dev-advocates.aivencloud.com:13039/defaultdb?sslmode=require'

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:

pgweb connection page

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

  1. 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.

  2. 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.

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.

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
  2. Start the server by providing connection flags:

    pgweb --host $HOSTNAME --user $USERNAME --db $DBNAME
  3. Using a connection URI to start the server:

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

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

Aiven for Postgres connection page

Note This blog uses PostgreSQL version 14.4.

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

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;

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

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;

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.

Query for customers with no rental return date

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;

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.

View of customers with no rental return date

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

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;

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

Sales by film category

Inventory by film category

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.

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:

Query history

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.

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.

Related blogs

All things open source, plus our product updates and news in a monthly newsletter.

Subscribe to the Aiven newsletter

Loading...