vscode-sql

This is a plugin to facilitate interactive EDA using SQL.

Installation

Navigate to Releases and download the latest release.
In VSCode, open the command palette (cmd+shift+p) and type Extensions: Install from VSIX ..., or click "Install from VISX" from the extensions sidebar.
Select the downloaded .visx to install
Voila -- reload and it's installed.

Features

Running a Query

vscode-sql runs highlighted queries in the background and inserts the results inline. You can do this by hilighting a query (or queries) and using the command palette Execute SQL command or pressing shift+enter.

Histograms:

You can insert a histogram instead of a table using cmd+shift+h or the Execute SQL Command (histogram) command.

SELECT * FROM(
SELECT 'men', 'age 25-50', 423
UNION
SELECT 'women', 'age 25-50', 102
UNION
SELECT 'men', 'age 50-75', 32
UNION
SELECT 'women', 'age 50-75', 222
) ORDER BY 1,2
;

    men age 25-50 | ############################################################ | 423
    men age 50-75 | #####                                                        | 32
  women age 25-50 | ##############                                               | 102
  women age 50-75 | ###############################                              | 222

Clearing results

You can clear the results of multiple queries after highlighting them using cmd+shift+k or the VS-SQL: Clear Results command.

Find table and columns

cmd+shift+t: find and insert table name
cmd_shift_c: find and insert column name
(Command palette) VS-SQL: List All Columns: list all column names of a table, separated by commas

Database Connections

vcode-sql can manage multiple connections. However, only one connection can be active at any given time.

Adding a connection

If you are adding a Bigquery connection, make sure you have logged in using gcloud auth application-default login before proceeding.

Open the command palette and run VS-SQL: Add Connection
Enter a connection name (only alphanumrics, dashes, and underscores allowed)
Select a connection type
(Postgres/Redshift only) Enter other connection parameters
Select the newly added connection as the active connection

Note: To edit a connection, add a new connection with the same name to overwrite it.

Importing a connection from db-facts

vscode-sql allows importing database credentials from db-facts (recommended for BlueLabs users).

Check that the connection exists in db-facts by running db-facts config <connection-name> in the terminal
Open the command palette and run VS-SQL: Import Connection from db-facts
Enter the db-facts connection name.
(Postgres/Redshift only) Select Enable SSL option.
Select the newly added connection as the active connection

Selecting the active connection

You can change the current active connection by running VS-SQL: Select Active Connection in the command palette or by clicking on the vs-sql status bar item in the bottom left. This is particularly useful when working with multiple databases.

Deleting a connection

Run VS-SQL: Delete Connection and select the connection to delete

Development

Navigate into the vscode-sql directory.
Make sure you've got nodejs installed (and npm).
Run npm install to install the plugin and development dependencies.
Hit F5 to build the plugin and open a new window for testing.
Hit cmd+shift+F5 to refresh code changes in the debugger window.

Rebuilding

Navigate into the vscode-sql directory.
Make sure vsce is installed:

npm install -g vsce

Package the package:

vsce package --baseImagesUrl https://github.com/benjamcalvin/vscode-sql/blob/main/

Install the .vslx file that is generated (vscode-sql-x.x.x.vslx) using the Install from VSLX option or from the terminal using code --install-extension vscode-sql-X.X.X.vsix.

For more information, see: https://code.visualstudio.com/api/working-with-extensions/publishing-extension

Adding support for a new type of database connection

To add support for a new database:

Create a new file <myDb>.ts
This file should contain the following 4 functions with the appropriate return types. See athena.ts or postgres.ts for example.
- runQueryMyDb
- listSchemasMyDb
- listTablesMyDb
- listColumnsMyDb
Add your myDb functions to db-selection.ts using if statements
Add new database type to connection.ts

Known Issues

Leaving the Tab

If you leave the tab to go to another tab (you can switch to a different window), you'll get this error and the query will fail to insert when it's done.

rejected promise not handled within 1 second: Error: TextEditor#edit not possible on closed editors
stack trace: Error: TextEditor#edit not possible on closed editors
	at t.ExtHostTextEditor.edit (/home/ubuntu/.vscode-server/bin/93c2f0fbf16c5a4b10e4d5f89737d9c2c25488a3/out/vs/server/remoteExtensionHostProcess.js:672:475)
	at /home/ubuntu/repos/vscode-sql/out/extension.js:46:24
	at Generator.next (<anonymous>)
	at fulfilled (/home/ubuntu/repos/vscode-sql/out/extension.js:5:58)
	at runMicrotasks (<anonymous>)
	at processTicksAndRejections (internal/process/task_queues.js:94:5)

Histogram Sometimes Fails


SELECT 'bob', 2
UNION
SELECT 'tom  ', 3 ;

    bob | ########################################                     | 2
  tom   | ############################################################ | 3


SELECT 'this is a string' as my_string, 1223.4 as value
UNION
SELECT 'this is another string' as my_string, 844.4 as value;

RangeError: Invalid array length
	at histogram (/home/ubuntu/repos/vscode-sql/node_modules/bars/index.js:49:12)
	at Query.format_histogram (/home/ubuntu/repos/vscode-sql/out/query.js:81:25)
	at /home/ubuntu/repos/vscode-sql/out/extension.js:81:88
	at t.ExtHostTextEditor.edit (/home/ubuntu/.vscode-server/bin/93c2f0fbf16c5a4b10e4d5f89737d9c2c25488a3/out/vs/server/remoteExtensionHostProcess.js:672:588)
	at /home/ubuntu/repos/vscode-sql/out/extension.js:80:24
	at Generator.next (<anonymous>)
	at fulfilled (/home/ubuntu/repos/vscode-sql/out/extension.js:5:58)
	at processTicksAndRejections (internal/process/task_queues.js:94:5)

BigQuery findColumn

Using the findColumn command (cmd+shift+c) actually sends a query to INFORMATION_SCHEMA.COLUMNS. Each execution counts as one query toward your billing quotas. The size of the query is quite negligible - on the order of a few MBs.

Running getTables (cmd+shift+t) does not count toward billing quotas.

Release Notes

0.1.3

Patch SSL Certificate issue #12.

0.1.2

Enable SSL Connection.

0.1.1

Fix db-facts restart issue. Add caching.

0.1.0

Add support for databases

Postgres/Redshift
Big Query

Add clear results functionality

Add database connection manager

Add a new database conneciton (or overwite an existing connection)
Remove a database connection
Import a connection from db-facts

0.0.2

Add metadata functionality:

cmd+shift+t (Find Table) to open picker for tables.
cmd+shift+c (Find Column) to open picker for columns.
List all Columns command to insert all columns.

0.0.1

Minimum functionality. Uses the AWS Athena Javascript SDK to run queries.

Enjoy!

crowdskout/vscode-sql