I’ve recently started a new job so I’ve been spending a bit of time trying to become familiar with how the relational databases are structured. Usually when I’m doing any database work, I tend to use the CLI clients like mysql or pg_sql. I tend to prefer them, not only as they’re usually easy to use via SSH, but the REPL is a nice interaction model when querying data: you type a query, and the results appear directly below it. The CLI tools do have a few drawbacks though. Dealing with large result sets or browsing the schema tend to be harder, which makes it difficult when dealing with an unfamiliar database.

So I’ve been finding myself using the GUI database browsers more, like DataGrip or MySQL Workbench. It is much easier and nicer to navigate the schema using these, along with deal with large result sets, but they do remove the connection between a query and the associated results. The queries are usually entered in an editor-like console, like those used to enter code, and the results are usually in another window panel, or in a separate tab. This mode of interaction has nothing like the recency or locality between the query and the results that you get from a CLI.

While working with both of these tools and seeing their shortcomings, I’ve been casually wondering what a perfect decent database client would have. I think it will need these attributes in some prominent way (this covers the complaints listed above but also addresses some others I think would also help):

Results appearing below queries: I think this tool will need an interaction model similar to the CLI tools. There is so much benefit in seeing the results directly below the query that produce it. Anything other than this is likely to result in a situation where I’ll be looking at seven different queries and wondering which of them produced the single result set that I see.

An easy way to view, filter and export large result sets: Although the interaction should be is closer to the CLI, there must be a way to deal with large queries and result sets, something that the GUI tools do really well. There should also be a way to export them to CSV files or something similar without having to remember the appropriate copy command.

Some sort of snippet support and persistent scroll-back: This one can be best summarised as “whatever you find yourself copy and pasting into notepad”. The ability to store snippets and saved queries will save time trying to find or rewrite the big complex queries. And the persistent scroll-back of previously executed queries, with their results, will help with maintaining my train of thought while investigating something. This can come in handy especially when the investigation spans multiple days.

A quick way to annotate queries or results: Big long SQL queries eventually look the same to me after a while, so it would also be nice to add inline comments or notes to remind myself what the results actually represent.

An easy way to browse the schema: This could be a tree-like structure similar to all the GUI tools, which will make browsing the schema really easy. I think at a minimum, it should be a consistent set of meta-commands such as listing tables in a database or describing a tables columns, etc.

An easy way to run automation tasks: Finally, some form of scripting language to be able to “orchestrate” multiple queries without having to formulate one large SQL query, or copy and paste result sets around. It’s true that writing an external tool to do this is also possible, but avoiding the context switch would be a huge benefit if this was available from within the app. Doesn’t have to be full featured either, in fact it’s probably better if it isn’t.

It would be interesting exploring this further. I think the last thing I need now is another project to work on, but maybe over the weekend I might start prototyping this to see if the workflow makes sense.