SQL Tabs documentation
Clone this wiki locally
Table of Contents
- Table of Contents
- Connecting to database
- Connecting via SSH
- Connections aliases
- Running SQL
- Info about database objects
- CSV and hidden blocks
- Export Results to CSV and JSON
Connecting to database
In order to connect to a database you need to enter connection sting in URL format:
Depending on the database vendor the protocol value should be the following:
- Postgresql -
- MySQL / MariaDB -
- MS SQL -
- Amazon Redshift -
It's not recommended to enter a password in connection string. If you omit a password it will be prompted after you press
In this case a password will be remembered for a current session only.
Connection options are different for different database vendors and depend on drivers and implementation.
Connecting via SSH
It is possible to connect to the database via SSH. For that you need to use a special connection string syntax:
ssh://user@host:port | protocol://user@host:port/dbname
In that case connection string is divided into two parts by a pipe sign
The first part of the connection string contains the options for connecting to the host via ssh.
The second part contains options for connecting to the database as you would be connecting being on the remote host.
ssh://osuser@remote_host | postgres://dbuser@localhost/dbname
An ssh password authentication is not supported,
so in order to get authenticated on remote host you need to have configured key-based authentication.
It is possible to use an alternative ssh private key rather than default
Here is an example of connecting to a local vagrant box via ssh:
ssh://vagrant@localhost:2222/?identity_file=~/myproject/.vagrant/machines/myhost/virtualbox/private_key | postgres://postgres@localhost:5432
When you have multiple connection stings in your dropdown list it's often not convenient to distinguish them visually. For better identification you can mark connection strings with aliases. This way they are better recognizable in the list. In order to set an alias for a connection string just add to the end of the string 3 dashes and alias:
postgres://user@/dbname --- My Local DB
SQL Tabs can run single SQL statements as well as scripts. When you choose in menu
Run Query the whole content of editor area is sent to Postgresql server as a single script. In case there were multiple statements the result area will display query result for each of them the similar way psql does that.
When you want to execute only part of script just select a needed area and press
Run Query. Another way of partial execution is to split script to blocks by lines started with three dashes like this:
SELECT 1; --- block separator SELECT 2; --- one more block SELECT 3;
If you do so and press
Execute Block menu item then the statements of block where cursor is placed will be run.
Info about database objects
The summary info about database and its schemas is accessible after pressing menu item
Database -> Database Info. There are clickable objects so you can navigate between object without any typing.
In order to get information about particular database object you can write its name in the editor and place the cursor on it. Then pick an
Object Info menu item under
Database menu or press a corresponding shortcut.
In case there were no object under cursor the database summary will be displayed.
In order to list all objects of particular schema you can type the schema name with dot in the end and press
Object Info shortcut. Like
In order to visualize query result you need to prepend query with special block annotation:
--- chart <chart_type>
Execute Block. See examples below.
At the moment the following chart types supported:
Row charts are the ones where first column of resultset represents a title of the value to be displayed and further columns the numeric values themselves.
Column charts take a column name as a title and rows as a values to be displayed.
Donut is a row oriented chart, so each record should represent a title and a numeric value:
--- chart donut SELECT relname, pg_total_relation_size(oid) FROM pg_class ORDER BY 2 DESC LIMIT 10
By default a record number is used for axis X of column oriented charts. In order to set a column as an axis X use the following expression:
chart <type> x=<N>
Where N is the number of column in the resultset.
--- chart bar x=2 select random() as value, (now() - n * interval '1 day')::date as day from generate_series(1, 10) n
CSV and hidden blocks
It's possible to change the display of dataset from tabular way to CSV just by adding an annotation in front of a query:
--- csv select * from pg_class
If you want to hide a part of output of a SQL script just create a hidden SQL block with the corresponding annotation:
show DateStyle; select now(); --- hidden set DateStyle = 'German' --- show DateStyle; select now(); --- hidden set DateStyle = 'default';
Crosstable annotation displays the result set as a pivot table. Currently supported only 3 columns resultsets. The first column is taken as a vertical axis, the second column is taken as a horizontal axis and the third column is a cross value.
For example let's take a dataset with three columns:
--- table select * from departments (15 rows) # department month employees 1 Department A Jan 10 2 Department A Feb 12 3 Department A Mar 12 4 Department A Apr 11 5 Department A May 11 6 Department B Jan 20 7 Department B Feb 22 8 Department B Mar 25 9 Department B Apr 26 10 Department B May 26 11 Department C Jan 33 12 Department C Feb 33 13 Department C Mar 33 14 Department C Apr 33 15 Department C May 33
This is how it looks like as a crosstable:
--- crosstable select * from departments Jan Feb Mar Apr May Department A 10 12 12 11 11 Department B 20 22 25 26 26 Department C 33 33 33 33 33
SQL Tabs can add to the output an arbitrary formatted content. Thus you can add any comments, titles, links and images. For these purposes in the beginning or in the end of each sql block you can add a special comments embraced into
/** **/. The inside content of such comments should follow a Markdown syntax.
In order to render a markdown content execute your SQL via "Execute Block" or "Execute All Blocks" menu items or corresponding keyboard shortcuts.
/** ## Top 10 relations **/ SELECT relname, pg_total_relation_size(oid) FROM pg_class ORDER BY 2 DESC LIMIT 10 /** For more information visit [www.sqltabs.com](http://www.sqltabs.com/) **/
In order to share the output with anybody press a "Share" button placed in the top right corner of output area. After pressing it SQL Tabs will publish your document on the publicly available web site and display the URL.
Here is an example of shared document: http://www.sqltabs.com/api/1.0/docs/7cc003a1fc830d8fdc58b4b2ac509517
Export Results to CSV and JSON
In order to export currently selected datasets into CSV or JSON files press menu item
File -> Export to JSON or
File -> Export to CSV