From GUI to CLI: Transforming my query workflow with usql and jq

Lorran Rodrigues — Wed, 15 May 2024 16:57:51 +0000

A couple of months ago I decided to climb the steepest ladder that is using vim as my code editor. It took me some time, but I was able to get that amazing feeling of coding in the speed of thought and making my daily routine feel so more productive. That feeling was kind of addictive, and soon I became really obsessed on how to make other parts of my job, as a data engineer, feel more "vimy".

I usually deal with data spread across multiple databases and my tool for the job of inspecting resources and test some simple queries was dbeaver, which is great, but it can be overwhelming in terms of visual information (specially on my use case). I needed speed, I needed something in the terminal, I needed vim.

Since some of the databases I was interacting were postgres, I started to play around with psql. It felt amazing, it had a great integration with the terminal, no visual pollution, I could also use vim as my query editor, and it was blazing fast. The problem was that only some of the databases I was interacting were postgres. I needed something that could connect to multiple databases, something universal, so I don't have to invest time in new tooling all the time. So I searched on github "universal sql" and I felt in love with usql.

For those who are not familiar, usql is a command-line interface for multiple databases heavily inspired on psql written in go.

From its documentation:

Database administrators and developers that would prefer to work with a tool like psql with non-PostgreSQL databases, will find usql intuitive, easy-to-use, and a great replacement for the command-line clients/tools for other databases.

Why is it so great?

Configuring usql is quite easy. The tools use two files for configuration, .usqlrc and .usqpass. The first one allows us for example to choose the table display format and the preferrer editor

-- my actual .usqlrc
\set EDITOR nvim
\pset format json

And the .usqlpass allow us to configure a pattern matching syntax that will assign a specific user/password depending on the matching database. For example, if I have the following configuration:

mysql:*:*:*:username:mypassword
pg:*:*:*:another_account:mypassword

whenever I connect to a mysql database like

usql mysql://my-database-url.com:3306

usql will try to use the user/password defined on the .usqlpass. This can become super useful, for handling multiple database connections

Another great feature is allowing me to use vim as the query editor. So whenever I need to edit the recent submitted query I can command \e and it will launch and vim buffer, so I can delightfully edit the SQL on vim.

But the real game changer was its piping capabilities. You've noticed that I've set json as my table format, and that's not deliberate. It's because of another tool called jq, which became my favorite way to interact with json data. Mostly because of its elegant syntax and its natural integration with the terminal and also vim.

By configuring the table format as json I can easily pipe the results to jq and do all sort of witchcraft with the query results on the fly.

Want to use the result of a query as input to some python script?

select id from orders \g | jq "[.[].id] | join(", ")" > input.txt | python main.py input.txt

That way I can naturally query and also do a bunch of data wrangling with the results in the process. It takes some time to really get the hang of jq syntax, but once you get it, it's absolute gold.

I can also switch back to the default result format configuration by doing:

\pset format aligned

Switching to usql has revolutionized my workflow, providing a faster, more efficient way to query databases. Combined with jq, it’s transformed how I interact with and process data. I highly recommend giving usql a try if you're looking to streamline your database management. I'd love to hear about your experiences and any tips you might have!

DEV Community: Lorran Rodrigues

From GUI to CLI: Transforming my query workflow with usql and jq

Why is it so great?