Local Pg

SKILL: Postgres data retrieval in VS Code

Purpose

This document shows practical steps and examples to connect to PostgreSQL from Visual Studio Code, run queries, and retrieve data for development tasks. It covers:

• Recommended VS Code extensions
• Secure configuration (env vars, .pgpass)
• Quick usage: psql, SQLTools
• Example code snippets (Node.js + Python)
• VS Code task to run SQL files
• Troubleshooting and tips

Prerequisites

• PostgreSQL server accessible (local or remote)
• VS Code (latest stable)
• psql CLI installed (optional but recommended)
• Node.js / Python installed if using the snippets below

Recommended VS Code extensions

• SQLTools (core) + SQLTools PostgreSQL/Redshift Driver
  • Provides connections, query runner, results view.
• (Optional) "PostgreSQL" extension or any other DB extension you prefer.
• Dotenv support extension if you keep credentials in .env locally.

Security note: do NOT commit secrets (passwords, connection strings) to source control. Use .env, .pgpass, or environment variables and add them to .gitignore.

Configuration patterns

1. Environment variables (recommended)

• .env (local, not committed) Example:

  PGHOST=localhost
  PGPORT=5432
  PGDATABASE=mydb
  PGUSER=myuser
  PGPASSWORD=supersecret
  

• In code or tasks, load these via process.env (Node) or os.getenv (Python) or use a dotenv loader.

2. .pgpass (psql convenience)

• File: ~/.pgpass (mode 600) Format: host:port:database:username:password
• psql will read it automatically.

psql (CLI) usage in VS Code

• Open an integrated terminal in VS Code (View → Terminal).
• Connect:
  • Using env: Linux/macOS: export PGHOST=localhost; export PGUSER=myuser; export PGPASSWORD=supersecret; psql -d mydb Windows PowerShell: $env:PGPASSWORD="supersecret"; psql -h localhost -U myuser -d mydb
  • Single connection string: psql "postgresql://myuser:supersecret@localhost:5432/mydb"
• Run a SQL file: psql "postgresql://user:pass@host:5432/db" -f ./migrations/001-create-table.sql
• Useful psql tips:
  • \dt list tables
  • \d+ table_name show schema
  • \x on toggle expanded output
  • \copy for bulk import/export

SQLTools (VS Code) quickstart

1. Install SQLTools and the PostgreSQL driver.
2. Add a connection (via the SQLTools UI or settings.json).
3. Example settings.json connection snippet:

"sqltools.connections": [
  {
    "name": "Local Postgres",
    "driver": "Postgres",
    "previewLimit": 50,
    "server": "localhost",
    "port": 5432,
    "database": "mydb",
    "username": "myuser",
    "password": "supersecret"
  }
]

(Prefer environment vars -> some users store only a placeholder and enter password when connecting.)

4. Use the SQLTools Explorer to open the connection, expand schemas, right-click a table, or open a .sql file and click “Run Query” to see a results grid and export CSV/JSON.

Example: retrieving data with SQLTools query

• Open queries/my-query.sql: SELECT id, name, created_at FROM users WHERE active = true ORDER BY created_at DESC LIMIT 100;
• Run query → results in a table; click "Save Results" to export.

Example code snippets

Node.js (using pg)

// name=examples/get_users.js
// Run: node examples/get_users.js
require('dotenv').config(); // if using .env
const { Pool } = require('pg');

const pool = new Pool({
  host: process.env.PGHOST || 'localhost',
  port: Number(process.env.PGPORT) || 5432,
  database: process.env.PGDATABASE || 'mydb',
  user: process.env.PGUSER || 'myuser',
  password: process.env.PGPASSWORD || undefined,
  max: 10,
});

async function getActiveUsers(limit = 100) {
  const sql = `SELECT id, name, email, created_at FROM users WHERE active = true ORDER BY created_at DESC LIMIT $1`;
  const { rows } = await pool.query(sql, [limit]);
  return rows;
}

(async () => {
  try {
    const users = await getActiveUsers(50);
    console.log('Got', users.length, 'users');
    console.table(users.slice(0, 10));
  } catch (err) {
    console.error('DB error', err);
  } finally {
    await pool.end();
  }
})();

Python (using psycopg)

# name=examples/get_users.py
# Run: python examples/get_users.py
import os
import psycopg
from psycopg.rows import dict_row

conninfo = {
    "host": os.getenv("PGHOST", "localhost"),
    "port": os.getenv("PGPORT", 5432),
    "dbname": os.getenv("PGDATABASE", "mydb"),
    "user": os.getenv("PGUSER", "myuser"),
    "password": os.getenv("PGPASSWORD")
}

def get_active_users(limit=100):
    with psycopg.connect(**conninfo, row_factory=dict_row) as conn:
        with conn.cursor() as cur:
            cur.execute(
                "SELECT id, name, email, created_at FROM users WHERE active = true ORDER BY created_at DESC LIMIT %s",
                (limit,)
            )
            return cur.fetchall()

if __name__ == "__main__":
    users = get_active_users(50)
    print(f"Got {len(users)} users")
    for u in users[:10]:
        print(u)

VS Code task: run current SQL file with psql

Add a task to .vscode/tasks.json that runs psql against the open file:

{
  "version": "2.0.0",
  "tasks": [
    {
      "label": "psql: run current file",
      "type": "shell",
      "command": "psql",
      "args": [
        "${input:connString}",
        "-f",
        "${file}"
      ],
      "problemMatcher": [],
      "presentation": {
        "reveal": "always",
        "panel": "new"
      }
    }
  ],
  "inputs": [
    {
      "id": "connString",
      "description": "Postgres connection string (postgresql://user:pass@host:port/db)",
      "default": "postgresql://myuser:mysecret@localhost:5432/mydb",
      "type": "promptString"
    }
  ]
}

Run the task when a .sql file is focused to execute it via psql.

Tips for development workflow

• Use migrations (Flyway, Liquibase, Sqitch, node-pg-migrate, or Knex migrations) to track schema changes.
• Keep test/dev databases isolated from production; use different env files.
• Use connection pooling (pg Pool or equivalent) in server apps.
• Use parameterized queries or prepared statements to avoid SQL injection.
• For large result sets, stream rows or use cursors instead of reading everything into memory.

Debugging & troubleshooting

• If connection fails:
  • Ensure host/port are reachable (telnet/ nc).
  • Check pg_hba.conf on server for allowed auth methods.
  • Use psql from terminal to isolate VS Code issues.
• If extensions can’t connect:
  • Try temporarily pasting the connection string into SQLTools connection and test.
  • Check firewall / cloud DB network settings (allowed IPs).
• Inspect server logs for authentication or connection errors.

Useful commands summary

• psql connect: psql "postgresql://user:pass@host:5432/db"
• list tables: \dt
• describe table: \d+ table_name
• run file: psql -f path/to/file.sql
• export CSV via SQLTools results grid or use COPY TO STDOUT

Further customization

• Add query snippets (VS Code user snippets or extension snippets) for repetitive queries.
• Use tasks for automated test data load.
• Integrate tests using testcontainers (for Node/Python) or ephemeral DBs for CI.

If you want, I can:

• Generate a concrete SQLTools connection entry using your connection values (paste them or tell me where the repo is).
• Create a ready-to-run Node/Python example tailored to your schema.
• Add a sample VS Code launch configuration for debugging a Node app that queries Postgres.