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
|RSS FeedSenior Developer Advocate
Subscribe for the latest news and insights on open source, Aiven offerings, and more.
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:
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:
Loading code...
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.
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.
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:
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:
Start the server and enter the database connection information manually.
Loading code...
Start the server by providing connection flags:
Loading code...
Using a connection URI to start the server:
Loading code...
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.
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:
Loading code...
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:
Loading code...
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...:
Loading code...
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!
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:
Loading code...
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.
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.
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:
If you're looking for a fully managed and hosted PostgreSQL, check out Aiven for PostgreSQL® and reach out if you have any questions.
psql 'postgres://avnadmin:PASSWORD@demo-pg-dev-advocates.aivencloud.com:13039/defaultdb?sslmode=require'pgwebpgweb --host $HOSTNAME --user $USERNAME --db $DBNAMEpgweb --url postgres://$USERNAME:$PASSWORD@$HOSTNAME:$PORTNUM/$DBNAME?sslmode=[mode]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;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;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;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;