# Local Pg

> SKILL: Postgres data retrieval in VS Code

- Skill: `shotapentaho/local-pg` (Agent Skill, multi-file: 2 files)
- Install (CLI): `npx skillmds@latest add shotapentaho/local-pg`
- Raw SKILL.md: https://api.skillmd.com/api/skills/shotapentaho/local-pg/raw
- Safety review: pending (external: skill-scanner PASS, skillspector CAUTION)
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Coding & Dev Tools
- Author: shotapentaho (https://skillmd.com/u/shotapentaho)
- Updated: 2026-09-22
- Page: https://skillmd.com/skills/shotapentaho/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:
```json
"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)
```javascript
// 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)
```python
# 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:
```json
{
  "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.