Run SQL Queries Like a Unix Veteran

Every day, software engineers engage with database systems, often writing and executing SQL queries of varying complexity. These queries serve multiple purposes: some are integrated into application code, while others are crafted to explore database structures, troubleshoot issues, or conduct testing and debugging. After repeatedly rewriting similar queries or wasting time searching through your SQL client’s history for a complex and useful one, you may find it beneficial to create a library of your most valuable SQL queries.

There are several ways to organize and manage your SQL queries. You might use a graphical database browser like DBeaver, a web-based tool such as pgAdmin, or IDE plugins like those available for VS Code.

Alternatively, you can keep things simple by using plain text files, a text editor, and the database CLI application.

Here’s a quick overview of how I efficiently manage my queries using the PostgreSQL client, psql.

By adopting these techniques, you’ll not only streamline your workflow but also impress your colleagues with your ability to generate clear, concise reports from your database in no time 💪

Configuration

Inspired by the excellent book The Art of PostgreSQL, I’ve incorporated these configuration lines into my .psqlrc file for enhanced output. This setup ensures that NULL values are clearly represented with a ¤ symbol and improves the readability of tables by optimizing the display of separation lines between rows and columns.

\pset null '¤'
\pset linestyle 'unicode'
\pset unicode_border_linestyle single
\pset unicode_column_linestyle single
\pset unicode_header_linestyle double
set intervalstyle to 'postgres_verbose';

Shell aliases and password management

I frequently run queries across multiple environments, including local, remote development servers, and production replicas. To streamline this process, I set up specific aliases in my shell configuration file. Each network port is SSH-forwarded to the appropriate remote server, except for the local environment.

psql_local='psql -q postgresql://user_local@localhost:5432/db_local'
psql_dev='psql -q postgresql://user_dev@localhost:5451/db_dev'
psql_prod='psql -q postgresql://user_prod_ro@localhost:5499/db_prod_ro'

I also use a .pgpass file to avoid typing database passwords repeatedly. However, it’s important to check with your organization’s security team to ensure this practice is allowed. Even so, it’s generally advisable not to store passwords for accessing production data, even if the access is read-only.

localhost:5432:user_local:db_local:XXX
localhost:5451:user_dev:db_dev:XXX

SQL files organization

This is the essence of the topic: I organize my SQL queries in simple .sql files, neatly arranged in directories. To track changes and maintain a history of updates, you can also use a version control system. Below is an example of a fictional customer_order_summary.sql file.

Yes, I use lowercase SQL keywords. While I appreciate the old-school ways of doing things, I take full advantage of modern tools — letting my text editor handle proper syntax highlighting of keywords. After all, we’re no longer in the ’80s with black-and-white monitors! 😉

select c.customer_id as id,
       c.first_name,
       c.last_name,
       c.email,
       count(o.order_id) as orders,
       sum(o.amount) as spent,
       max(o.order_date) as last_order_date,
       case
           when sum(o.amount) > 300 then 'VIP'
           else 'Regular'
       end as statusfrom customers cleft
join orders o on c.customer_id = o.customer_idgroup by c.customer_id,
                                                       c.first_name,
                                                       c.last_name,
                                                       c.emailhaving count(o.order_id) > 0order by spent desc;

Demo time !

Impressive, right?

While it may seem like a basic setup, having a well-configured psql environment and neatly organized SQL files that can be easily edited with your favorite text editor can significantly boost your efficiency. This approach allows you to quickly retrieve data and reuse previously crafted SQL queries, enhancing your workflow and productivity when it matters most.