Allow inclusion of Certain files to be explicitly executed by pgschema / included in plan file

[PadenZach]

What

There's some things that pgschema doesnt currently do, for my use cases these are:

• Extensions
• User Management
• Misc side effect calls (EG: something like CALLing a sproc, or running a select statement cron.schedule('nightly-vacuum', '0 10 * * *', 'VACUUM');

Currently, these can be stored separately outside of pgschema context, and then just applied manually via cicd; eg:

psql before-migration/*.sql
pgschema apply ...
psql after-migration/*.sql

While this is okay, I'd prefer that this sort of functionality be available in pgschema itself. One way to get this into the plan.json file is to use a bit of jq.

Details

Quick AI generated bash function that got this working for me. Prepends files as a group. Luckily plan.json isn't too strongly type-checked :)

add-steps-from-files() {
  # Check if files were provided
  if [ $# -eq 0 ]; then
    echo "Usage: add-steps-from-files file1.sql file2.sql ..."
    return 1
  fi
  
  # Create a temporary JSON file for the new steps array
  TEMP_STEPS=$(mktemp)
  echo "[]" > "$TEMP_STEPS"
  
  # Process each file
  for sql_file in "$@"; do
    if [ ! -f "$sql_file" ]; then
      echo "Warning: File $sql_file not found, skipping"
      continue
    fi
    
    # Read the SQL content and properly escape it for JSON
    SQL_CONTENT=$(cat "$sql_file" | jq -Rs .)
    
    # Add this file as a step to our temporary steps array
    jq --arg sql "$SQL_CONTENT" '
      . += [{
        "sql": $sql,
        "type": "call",
        "operation": "alter",
        "path": ""
      }]
    ' "$TEMP_STEPS" > "${TEMP_STEPS}.new" && mv "${TEMP_STEPS}.new" "$TEMP_STEPS"
  done
  
  # Check if we have any steps to add
  STEPS_COUNT=$(jq 'length' "$TEMP_STEPS")
  if [ "$STEPS_COUNT" -eq 0 ]; then
    echo "No valid files were processed."
    rm "$TEMP_STEPS"
    return 1
  fi
  
  # Add a new group with all steps to plan.json
  jq --slurpfile steps "$TEMP_STEPS" '
    .groups += [{
      "steps": $steps[]
    }]
  ' plan.json > plan.json.new && mv plan.json.new plan.json
  
  echo "Added $STEPS_COUNT steps from SQL files to plan.json"
  
  # Clean up
  rm "$TEMP_STEPS"
}

Why

• It makes teaching pgschema easier. Everything goes through the same process.
• Before/After hooks that would be called show up in the plan file, and can be reviewed.
• Creates better escape hatch, pgschema already does a great job handling the most difficult part of declarative schemas, most other things can be expressed as either with a if not exists or DO block.

How

• Possibly could introduce a new directive, something like \ix for directly executing a sql file during plans.
• Alternatively, a CLI call such as --before-migration and --after-migration
• Nice to have: a step type/operation to represent work being done here. Likely wont include fine grain changes in the way that normal migrations do, but a simple way for the human readable summary to call out "hey, we're also running $file"

[tianzhou]

@PadenZach The add-steps-from-files() method to augument the existing plan is the expected usage. So I am thinking of enhancing the experience for that direction.

Nice to have: a step type/operation to represent work being done here. Likely wont include fine grain changes in the way that normal migrations do, but a simple way for the human readable summary to call out "hey, we're also running $file"

Could you please elaborate a bit more about this? (e.g. are you expecting a SQL statement / a SQL file / a shell script)?

[PadenZach]

While having summaries of every possible change would be nice, I feel like it would be really hard to have so many types (EG: SPROC called, INSERT statement ran, USER created, arbitrary plpgsql ran?

Instead, just listing the filenames may be sufficient

For example, a main.sql

\ix user-management.sql
\ix database/extensions.sql

-- Normal Migration stuff
CREATE TABlE ...

eg:

Plan: 2 to add, 3 to drop.

Summary by type:
  types: 1 to drop
  procedures: 1 to add
  tables: 1 to add
  views: 2 to drop

Types:

Procedures:

Tables:

Views:

Files:
 2 to be executed (after/before?) migration
  user-management.sql
  database/extensions.sql