MCP with Postgres – Querying my data in plain English

{
  "mcpServers": {
    "mcp_demo_db_mcp_server": {
    "command": "npx",
      "args": [
        "-y",
        "@modelcontextprotocol/server-postgres",
        "postgresql://<user>:<password>@<host>:<port>/<database>"
      ]
    }
  }
}

#!/bin/bash

# Customize these:
DB_NAME="<databaesname>"
DB_USER="<databaseuser>"

# Output file
OUTFILE="<basepath>/db-schema.sql"

echo "-- === Tables and Columns ===" > "$OUTFILE"
psql -U "$DB_USER" -d "$DB_NAME" -Atc "
SELECT
  '-- Table: ' || table_name || E'\n-- Columns: ' ||
  string_agg(column_name || ' (' || data_type || ')', ', ')
FROM information_schema.columns
WHERE table_schema = 'public'
GROUP BY table_name
ORDER BY table_name;" >> "$OUTFILE"

echo -e "\n-- === Foreign Keys ===" >> "$OUTFILE"
psql -U "$DB_USER" -d "$DB_NAME" -Atc "
SELECT
  '-- ' || tc.table_name || '.' || kcu.column_name || ' → ' || ccu.table_name || '.' || ccu.column_name
FROM
  information_schema.table_constraints AS tc
  JOIN information_schema.key_column_usage AS kcu
    ON tc.constraint_name = kcu.constraint_name AND tc.table_schema = kcu.table_schema
  JOIN information_schema.constraint_column_usage AS ccu
    ON ccu.constraint_name = tc.constraint_name AND ccu.table_schema = tc.table_schema
WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_schema = 'public'
ORDER BY tc.table_name;" >> "$OUTFILE"

echo -e "\n-- === Indexes ===" >> "$OUTFILE"
psql -U "$DB_USER" -d "$DB_NAME" -Atc "
SELECT
  '-- ' || t.relname || ': ' || i.relname ||
  CASE ix.indisunique WHEN true THEN ' (UNIQUE)' ELSE '' END ||
  ' on columns: ' || string_agg(a.attname, ', ')
FROM pg_class t
JOIN pg_index ix ON t.oid = ix.indrelid
JOIN pg_class i ON i.oid = ix.indexrelid
JOIN pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(ix.indkey)
JOIN pg_namespace ns ON ns.oid = t.relnamespace
WHERE ns.nspname = 'public'
GROUP BY t.relname, i.relname, ix.indisunique
ORDER BY t.relname, i.relname;" >> "$OUTFILE"

echo -e "\n-- === JSONB Field Structures (sample-based) ===" >> "$OUTFILE"

# Find all jsonb fields and loop through them.
# This uses a `while read` loop which is more portable than `mapfile` (which fails on default macOS bash).
psql -U "$DB_USER" -d "$DB_NAME" -Atc "
SELECT table_name, column_name
FROM information_schema.columns
WHERE data_type = 'jsonb' AND table_schema = 'public'
ORDER BY table_name, column_name;" |
while IFS='|' read -r table column; do
  # Skip any empty lines that might result from the pipe
  if [ -z "$table" ]; then continue; fi

  echo -e "\n-- $table.$column (jsonb) sample structure:" >> "$OUTFILE"

  # This advanced query explores the JSONB structure down to level 2,
  # and outputs a special format that the awk script below can parse for pretty-printing.
  psql -U "$DB_USER" -d "$DB_NAME" -Atc "
    WITH source_rows AS MATERIALIZED (
      SELECT \"$column\"
      FROM \"$table\"
      WHERE \"$column\" IS NOT NULL
      AND jsonb_typeof(\"$column\") IN ('object', 'array')
      LIMIT 500 -- Process up to 500 rows to find a good sample
    ),
    unnested_items AS (
      -- Handle rows where the column is a single JSON object
      SELECT \"$column\" AS item FROM source_rows WHERE jsonb_typeof(\"$column\") = 'object'
      UNION ALL
      -- Handle rows where the column is a JSON array, and unnest it
      SELECT jsonb_array_elements(\"$column\") AS item FROM source_rows WHERE jsonb_typeof(\"$column\") = 'array'
    ),
    level_1_pairs AS (
      -- Get all level 1 key-value pairs by using jsonb_each() on each item.
      SELECT key, value
      FROM unnested_items, jsonb_each(item)
      WHERE item IS NOT NULL AND jsonb_typeof(item) = 'object'
    )
    -- Start final output generation
    SELECT DISTINCT path || '|' || is_expandable::text FROM (
      -- Level 1 Keys
      SELECT
        key AS path,
        (jsonb_typeof(value) IN ('object', 'array')) AS is_expandable
      FROM level_1_pairs
      UNION ALL
      -- Level 2 Keys from nested objects
      SELECT
        p1.key || '.' || p2 AS path,
        false AS is_expandable -- Stop at level 2
      FROM level_1_pairs p1, jsonb_object_keys(p1.value) p2
      WHERE jsonb_typeof(p1.value) = 'object'
      UNION ALL
      -- Level 2 keys from nested arrays of objects
      SELECT
        p1.key || '.' || p2 AS path,
        false AS is_expandable
      FROM
        level_1_pairs p1,
        jsonb_array_elements(p1.value) AS arr_item,
        jsonb_object_keys(arr_item) AS p2
      WHERE jsonb_typeof(p1.value) = 'array' AND jsonb_typeof(arr_item) = 'object'
    ) AS final_paths
    ORDER BY 1;
  " | awk '
    BEGIN { FS = "|"; }
    {
        split($1, parts, ".");
        parent = parts[1];
        is_parent_line = (length(parts) == 1);
        is_expandable = ($2 == "t");

        if (is_parent_line) {
            if (!processed_parents[parent]) {
                if (is_expandable) {
                    print "--   - " parent ":";
                    processed_parents[parent] = 1;
                } else {
                    print "--   - " parent;
                }
            }
        } else { # This is a child line
            child = parts[2];
            # Ensure parent is printed with a colon
            if (!processed_parents[parent]) {
                print "--   - " parent ":";
                processed_parents[parent] = 1;
            }
            print "--     - " child;
        }
    }
  ' >> "$OUTFILE"
done

echo "✅ Schema with JSONB structure saved to: $OUTFILE"

-- === Tables and Columns ===
-- Table: tasks
-- Columns: input_info (jsonb), expired_at (timestamp with time zone), status (jsonb), user_id (character varying), task_name (character varying), transfer_in_progress (boolean), id (integer)
-- Table: users
-- Columns: display_name (character varying), user_id (character varying), email (character varying), id (integer)

-- === Foreign Keys ===
-- tasks.user_id → users.user_id

-- === Indexes ===
-- tasks: idx_tasks_user_id on columns: user_id
-- users: users_user_id_key (UNIQUE) on columns: user_id

-- === JSONB Field Structures (sample-based) ===

-- tasks.input_info (jsonb) sample structure:
--   - dType
--   - href

-- tasks.status (jsonb) sample structure:
--   - error
--   - internalError
--   - internalErrorAdditionalInfo
--   - userError
--   - queueJobId
--   - queueJobInfo
--   - output:
--     - additionalInfoType
--     - additionalInfo
--     - clusterResult
--     - firstloadTimeTaken
--     - enabledAdditionalInfoType
--     - enabledAdditionalInfo
--     - enabledConfigs
--     - enabledHref
--     - href
--   - state

-- Query to get URLs for all the failed tasks
SELECT id, created_at, input_info->>'href' AS url FROM tasks
WHERE status ? 'error' order by id desc;

-- Query to fetch URLs for all tasks run by a certain user
SELECT id, created_at, input_info->>'href' AS url FROM tasks 
where user_id = (select id from users where email = '<email-address>')