Skip to content

SQL Tabs documentation

Jump to bottom Edit New page
Sasha Aliashkevich edited this page Apr 8, 2019 · 12 revisions

SQL Tabs

Table of Contents

Connecting to database

In order to connect to a database you need to enter connection sting in URL format:

protocol://user:password@hostname:port/dbname?options

Depending on the database vendor the protocol value should be the following:

  • Postgresql - postgres
  • MySQL / MariaDB - mysql
  • MS SQL - mssql
  • Amazon Redshift - redshift
  • Google Firebase - https
  • AlaSQL - alasql

It's not recommended to enter a password in connection string. If you omit a password it will be prompted after you press Enter.

Connection options are different for different database vendors and depend on drivers and implementation.

To connect to AlaSQL (built-in inmemory database) you can type alasql:// as a connection string or leave it empty.

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 id_rsa. 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

Connections aliases

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

Running SQL

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 myschema.

Tables

Table is a default renderer for SQL blocks. You can define it explicitly by --- table tag or omit it. Table renderer accepts an option hlr=<N> where N is the number of column which contains row highlighting values. This is basically a column whith numeric values -1, 0, 1, null. Depending on the value row will be highlighted with a certain color:

  • -1 - red
  • 0 - green
  • 1 - blue
  • null - no highlighting

A highlighting column itself excluded from rendering. Take a look at example.

Charts

In order to visualize query result you need to prepend query with special block annotation:

--- chart <chart_type> [pivot]

and press Execute Block. See examples below.

At the moment the following chart types supported:

Row charts:

  • pie
  • donut

Column charts:

  • line
  • spline
  • bar
  • area
  • step
  • area-spline
  • area-step

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. Column charts can accept a pivot parameter which means that a dataset should be transformed to a crosstable. In this case a first column is taken as axis X and the rest of crosstable columns are taken as categories.

Chart Examples

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

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

Markdown

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.

Example:

/**
## 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/)
**/

Share

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

Add a custom footer
Add a custom sidebar
Clone this wiki locally