Connect VS Code to PostgreSQL with SQLTools (Step-by-Step)

Most beginners start learning SQL in web playgrounds with immediate "Run" buttons. But on production analytics teams and in real-world portfolio projects, you are handed raw database credentials (host, port, user, password) and expected to query a live database, save queries into version-controlled .sql files, and deliver documented business findings.

This guide walks you through setting up Visual Studio Code with the SQLTools extension as your all-in-one data analytics workstation.

Why VS Code is the Ideal Workbench for Data Analysts

Instead of juggling three separate tools (a database GUI like DBeaver, a text editor for notes, and a Git terminal), VS Code lets you handle the entire analytical lifecycle in one interface:

Step 1: Install VS Code and the Required Extensions

If you haven't already, download and install Visual Studio Code.

Once installed:

1. Open VS Code.
2. Open the Extensions View by clicking the Extensions icon on the left Activity Bar or pressing Ctrl+Shift+X (Windows/Linux) or Cmd+Shift+X (macOS).
3. Search for and install these two extensions:
   • SQLTools (by Matheus Teixeira)
   • SQLTools PostgreSQL/Cockroach Driver (by Matheus Teixeira)

[!NOTE] SQLTools is modular. You need both the core extension and the PostgreSQL driver plugin for Postgres connections to work.

Step 2: Configure Your Database Connection

Once both extensions are installed, you will see a new Database icon (SQLTools) on the left Activity Bar.

1. Click the Database icon in the left sidebar.
2. Under the Connections panel, click Add New Connection (or the plug icon with a +).
3. Select PostgreSQL from the list of database drivers.

Field-by-Field Connection Settings

Fill out the connection form using your database credentials (if you are doing a Topfolio project, grab these from your workspace's Credentials tab):

4. Click Test Connection at the bottom. You should see a green notification: "Connection test successful!"
5. Click Save Connection.
6. In the sidebar under Connections, click on your saved connection (or click the plug icon next to it) to open the active session.

Step 3: The PostgreSQL Schema Gotcha (SET search_path TO ecom;)

In multi-tenant or enterprise PostgreSQL databases, tables are grouped into schemas (e.g. ecom, saas, analytics) rather than the default public schema.

If you connect and immediately run:

SELECT * FROM orders LIMIT 10;

You will receive an error:

ERROR: relation "orders" does not exist

The Fix: Set Your Search Path

Always put this statement at the very top of your .sql file or run it once in your session:

SET search_path TO ecom;

After executing SET search_path TO ecom;, PostgreSQL will automatically look inside the ecom schema, and queries like SELECT * FROM customers; or SELECT * FROM orders; will resolve instantly without needing ecom. prefixes!

Step 4: Writing, Executing & Inspecting Queries

1. In VS Code, open your project folder (e.g. ecommerce-sales-analysis-sql).
2. Open or create a SQL file in your folder, such as sql/01_exploration.sql.
3. At the top right of the editor, ensure the active connection is selected (e.g., Topfolio Ecom).
4. Type your SQL queries:

-- 01_exploration.sql
-- Step 1: Set schema search path
SET search_path TO ecom;
 
-- Step 2: Discover all tables in the ecom schema
SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'ecom'
ORDER BY table_name;
 
-- Step 3: Check order volume and date range
SELECT 
    COUNT(*) AS total_orders,
    COUNT(DISTINCT customer_id) AS total_customers,
    MIN(order_date) AS earliest_order,
    MAX(order_date) AS latest_order
FROM orders;

Keyboard Shortcuts for Running Queries

• Run Current Query: Place your cursor on the query and press Ctrl+E (Windows/Linux) or Cmd+E (Mac).
• Run Entire File: Press Ctrl+Shift+E / Cmd+Shift+E.
• CodeLens Click: You can also click the small "Run on active connection" text floating above each query block.

The results grid will open in a new tab right inside VS Code, allowing you to sort columns, inspect data types, and copy outputs.

Step 5: Documenting Findings in Markdown (findings/*.md)

As a data analyst, writing the query is only 50% of the job. Communicating the business insight to stakeholders is the other 50%.

In VS Code:

1. Create your findings file: findings/01_business_overview.md.
2. Write your analysis using Markdown headings, bullet points, and tables.
3. Open live preview by pressing Ctrl+Shift+V / Cmd+Shift+V (or Ctrl+K V for side-by-side preview).

# Business Overview & Scale Analysis
 
## 1. Executive Summary
* **Total Customer Base:** 12,450 unique customers across 3 active years (2023–2025).
* **Order Volume:** 48,210 completed transactions totaling $4.2M in gross merchandise value.
* **Key Observation:** The top 20% of customers account for 68% of total revenue.

Step 6: Committing & Pushing to GitHub Inside VS Code

Once your .sql files and .md reports are ready:

1. Open the Source Control tab (Ctrl+Shift+G / Cmd+Shift+G).
2. Click + (Stage All Changes).
3. Type a descriptive commit message (e.g. feat: add milestone 1 exploration queries and findings).
4. Click Commit (or checkmark), then click Sync Changes / Push.
5. Your public GitHub repository is updated, ready for mentor review!

Summary Checklist

• VS Code installed with SQLTools + PostgreSQL Driver.
• Connection saved with host, port 5432, user, password, and SSL disabled.
• SET search_path TO ecom; included at top of SQL files.
• Queries executed via Ctrl+E / Cmd+E and results inspected in grid.
• Findings documented in findings/*.md with Markdown preview.
• Changes committed and pushed to GitHub via VS Code Source Control.