Skip to content

13.-Integrating-SQLcl-Project-with-GitHub-Copilot.md

Latest commit

 

History

History
168 lines (116 loc) · 8.49 KB

13.-Integrating-SQLcl-Project-with-GitHub-Copilot.md

File metadata and controls

168 lines (116 loc) · 8.49 KB

13. Integrating SQLcl Project with GitHub Copilot

13.1. Overview

This guide explains how to integrate the SQLcl Project Copilot instructions into your own repository so that GitHub Copilot (Agent mode) understands your SQLcl Liquibase-based deployment pipeline and can assist with it effectively.

The instructions encode transferable patterns for project stage, project export, and project stage add-custom workflows so Copilot behaves correctly — running only what is safe to run autonomously and prompting you for steps that must remain under your control.

13.2. What You Get

With this integration, Copilot can:

  • Check for an active database connection before attempting any SQLcl project commands.
  • Export database objects directly via MCP on SQLcl 26.1+, or provide the command for you to copy-paste on older versions. Includes smart APEX app handling (pre-deletes stale files before export).
  • Run project stage automatically via MCP on SQLcl 26.1+, then immediately split the generated changeset files. On older versions, defers to you and walks you through what to do next.
  • Create custom DML files (seed data, reference data) using project stage add-custom directly via MCP.
  • Populate DML files with properly formatted Liquibase changesets following the correct template (initial population vs. incremental updates).
  • Split changeset files per schema object — each DDL statement into its own numbered changeset.
  • Deploy to target environments — run @install.sql, analyze output, validate object counts, and troubleshoot failures.
  • Set up APEX workspaces and admin users via project stage add-custom scripts.
  • Respect branch immutability — never editing changeset files from deployed branches.

Copilot will not:

  • Create files under dist/releases/ manually (always uses project stage or project stage add-custom).
  • Run project commands without a verified active database connection.
  • Guess which schema to use — if it cannot determine the schema from copilot-instructions.md or .dbtools/project.config.json, it stops and asks.

13.3. Prerequisites

  • SQLcl installed and available as sql on your PATH.
    • SQLcl 26.1 or later is strongly recommended. On older versions, Copilot cannot run project export or project stage directly and will defer those commands to you.
  • VS Code with GitHub Copilot extension (Agent mode / MCP support).
  • Your SQLcl Project repository set up per 1. Installation and Configuration.

13.4. Setup

13.4.1. Copy the Copilot Instructions File

Copy .github/sqlcl_project_copilot_instructions.md from this sample repo into your own repo's .github/ folder:

your-repo/
└── .github/
    └── sqlcl_project_copilot_instructions.md   ← copy this file here

This file contains the core instructions Copilot reads to understand your SQLcl Project workflows. You should not need to edit it — project-specific settings go in your copilot-instructions.md (see 13.5. Project-Specific Configuration).

13.4.2. Link the Instructions in Your Repo

In your own .github/copilot-instructions.md, add a link to the instructions file so Copilot picks them up:

See [SQLcl Project Copilot Instructions](sqlcl_project_copilot_instructions.md)

Your copilot-instructions.md is also where you add project-specific settings like the database connection name. For example:

# My Project

## Project Configuration

**Database Connection:** This project uses the `my_connection` connection for all SQLcl operations.

See [SQLcl Project Copilot Instructions](sqlcl_project_copilot_instructions.md)

13.4.3. Configure the SQLcl MCP Server

Add the following to .vscode/mcp.json in your repo:

{
  "servers": {
    "sqlcl.local": {
      "type": "stdio",
      "command": "sql",
      "args": ["-R", "0", "-mcp"]
    }
  }
}

This tells VS Code to start SQLcl as an MCP server, allowing Copilot to run SQLcl commands (queries, project stage add-custom, etc.) on your behalf without leaving the editor.

13.5. Project-Specific Configuration

After linking the shared instructions, customize your own .github/copilot-instructions.md with any project-specific settings:

Setting Where to Add Example
Database connection name copilot-instructions.md **Database Connection:** This project uses the \my_connection` connection.`
Schema names copilot-instructions.md List your tracked schemas (e.g. **Schemas:** MYAPP, MYAPP_API). Copilot uses this to set current_schema before queries — if it can't determine the schema, it will stop and ask rather than guess.
Any project-specific overrides copilot-instructions.md Additional rules or context Copilot should know.

Copilot reads both files and merges the context. If schema names are not in copilot-instructions.md, Copilot will fall back to inspecting .dbtools/project.config.json. For projects with a single schema this is sufficient; for multi-schema projects, listing them explicitly in copilot-instructions.md avoids Copilot stopping to ask.

13.6. Verifying Your Setup

After completing setup, open GitHub Copilot Chat in Agent mode and try:

List my current SQLcl connections.

Copilot should use the MCP server to call list-connections and return the result. If you see an error or Copilot has no tools available, check:

  1. .vscode/mcp.json exists and sql is on your PATH (run sql -V in a terminal to verify).
  2. VS Code has loaded the MCP server — check Output → MCP for startup messages.
  3. The Copilot extension is updated to a version that supports MCP/Agent mode.

13.7. How Copilot Behaves

The instructions encode a deliberate division of responsibility:

Action Copilot behaviour
project export -o <OBJECTS> SQLcl ≥ 26.1: Runs directly via MCP (async), polls until complete. Older SQLcl: Provides the command for you to copy-paste, with an upgrade nudge.
project stage add-custom Runs automatically via MCP, then populates the generated file.
project stage SQLcl ≥ 26.1: Runs via MCP after confirming your work is committed — then immediately proceeds to split the generated changesets. Older SQLcl: Defers to you and guides you through the next steps.
Changeset splitting Applies automatically after project stage, or on demand when you ask. Each DDL statement becomes its own numbered changeset.
Deployment (@install.sql) Connects to the target environment, runs the install script, analyzes output, validates object counts, and reports or troubleshoots failures. Always confirms the target environment before deploying.
Editing deployed branch changesets Never does this — branch immutability is enforced.
Running commands without a connection Refuses — always checks for an active connection first.
Schema selection Reads from copilot-instructions.md, falls back to .dbtools/project.config.json. If still unclear — stops and asks, never guesses.

13.8. Testing Your Configuration

See copilot/copilot-testing-sqlclproject.md for a set of test cases you can run against your own configured repo to verify Copilot is behaving correctly. The test cases cover:

  • Exporting tables to the repository
  • Creating and populating DML files
  • Adding records and regenerating DML
  • Splitting changeset files
  • Multi-step feature branch workflows

Run through these scenarios (substituting your own schema/table names) to confirm your setup is working end to end.