SQL Vertical Output Format
Dolt is a database that has Git-like version control features and MySQL-like database handling features. Every day we come closer to achieving full functionality of both Git and MySQL. We support the main features of Git including branch, merge, diff, push and pull and MySQL compatible server and client functions.
In MySQL there are SQL Client commands and
Output formats. Today,
we announce support for a couple new client commands, \g and \G. Weirdly, \G is also an output
format. So, we have a new one of those as well to go along with already supported json and csv.
Entering the world of MySQL client commands
Up until now, a semicolon (or your choice of DELIMITER) was the only option to terminate and execute an SQL
statement in Dolt SQL. You can now use the equivalent client command \g to terminate your query.
But sometimes, the output of these statements are hard for humans to read — this is where \G comes in.
The query statements in Dolt SQL currently prints in tabular format by default but can be set to either
csv or json. They can be very useful in some specific cases, but it is often unreadable. Besides
these formats, SQL vertical format is very different from any of these options in Dolt SQL. A simple
example of tabular and vertical formats look like this:
shell$ dolt sql
# Welcome to the DoltSQL shell.
# Statements must be terminated with ';'.
# "exit" or "quit" (or Ctrl-D) to exit.
us_housing_prices> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| us_housing_prices |
+--------------------+
us_housing_prices> SHOW DATABASES\G
*************************** 1. row ***************************
Database: information_schema
*************************** 2. row ***************************
Database: us_housing_pricesWith a few columns and rows of data, it does not make much sense why this format would be useful.
Why is it useful?
When I was implementing this feature, one specific question bothered me: "What is it used for?". I did not have an answer. I didn't find out until much later, when I had already been working on a different project that dealt with many columns and rows within a table.
SQL vertical format can be very helpful when reading data from huge tables. To show the difference between tabular and vertical
formats, I will use the current bounty database, US Housing Prices.
It looks like this on my terminal. Without decreasing the font size a lot, it is very hard to read data with
many columns. Here is 2 rows of data from sales table.
us_housing_prices> select * from sales limit 2;
+-------+-------+--------------------+-------------+-------------+-------------+-------------------------------+-------
------------------+------------+-------------+------------+-----------+------------+-----------------------------------
---------------------------+------+------+
| state | zip5 | physical_address | city | county | property_id | sale_date | proper
ty_type | sale_price | seller_name | buyer_name | num_units | year_built | source_url
| book | page |
+-------+-------+--------------------+-------------+-------------+-------------+-------------------------------+-------
------------------+------------+-------------+------------+-----------+------------+-----------------------------------
---------------------------+------+------+
| CA | 90241 | 10717 WOODRUFF AVE | DOWNEY | LOS ANGELES | 6285028005 | 1991-08-19 00:00:00 +0000 UTC | SINGLE
FAMILY RESIDENCE | 247500 | NULL | NULL | 1 | 1955 | https://portal.assessor.lacounty.g
ov/parceldetail/6285028005 | NULL | NULL |
| CA | 90241 | 10717 WOODRUFF AVE | DOWNEY | LOS ANGELES | 6285028005 | 1999-10-28 00:00:00 +0000 UTC | SINGLE
FAMILY RESIDENCE | 229000 | NULL | NULL | 1 | 1955 | https://portal.assessor.lacounty.g
ov/parceldetail/6285028005 | NULL | NULL |
+-------+-------+--------------------+-------------+-------------+-------------+-------------------------------+-------
------------------+------------+-------------+------------+-----------+------------+-----------------------------------
---------------------------+------+------+The above sure looks messy, but some people could probably read it. Now, here is the vertical format of 2 rows of data of the same table.
us-housing-prices> select * from sales limit 2\G
*************************** 1. row ***************************
state: CA
zip5: 91801
physical_address: 1105 N STONEMAN AVE, UNIT G
city: ALHAMBRA
county: LOS ANGELES
property_id: 5322011067
sale_date: 1980-12-15 00:00:00 +0000 UTC
property_type: CONDOMINIUM
sale_price: 87500
seller_name: NULL
buyer_name: NULL
num_units: 1
year_built: 1972
source_url: https://portal.assessor.lacounty.gov/parceldetail/5322011067
book: NULL
page: NULL
*************************** 2. row ***************************
state: CA
zip5: 91801
physical_address: 1105 N STONEMAN AVE, UNIT G
city: ALHAMBRA
county: LOS ANGELES
property_id: 5322011067
sale_date: 1992-08-14 00:00:00 +0000 UTC
property_type: CONDOMINIUM
sale_price: 158000
seller_name: NULL
buyer_name: NULL
num_units: 1
year_built: 1972
source_url: https://portal.assessor.lacounty.gov/parceldetail/5322011067
book: NULL
page: NULLAh, much better. It's kind of like "record" format. As in each row looks more like a card in a card catalog or rolodex.
SQL vertical format is now available on Dolt SQL shell, as well as any cli commands that accept an
optional -r or --result-format flag.
shell$ dolt sql -r vertical -q "show tables"
*************************** 1. row ***************************
Table: sales
*************************** 2. row ***************************
Table: states\G and \g are just the beginning. We intend to support the full list of
SQL Client commands like \c for
clear and \e to launch and editor in the Dolt SQL shell. We started with \G because of the
output format utility. We now support all the MySQL output formats
except tab delimited.
Try out Dolt!
If you have any questions, we are here to help on Discord. You can also learn more about how to set up and use Dolt on documentation page