Skip to content

FOLDER_STRUCTURE.md

Latest commit

 

History

History
273 lines (217 loc) · 9.24 KB

FOLDER_STRUCTURE.md

File metadata and controls

273 lines (217 loc) · 9.24 KB
Raw

Folder Structure & Best Practices

Overview

This project follows database migration best practices with a clear separation between source objects, working files, and generated outputs.

Directory Structure

SQLtoPostgresMigration/
├── .github/
│   └── prompts/                    # GitHub Copilot migration prompts
├── scripts/                        # Permanent migration scripts
│   ├── extract_dacpac_objects.ps1  # Extract & organize DACPAC
│   ├── migrate_code_objects.ps1    # Code migration orchestrator
│   ├── run_migration_pipeline.py   # 3-stage AI pipeline
│   └── reset_workspace.ps1         # Cleanup utility
├── migrations/
│   └── AdventureWorks/
│       ├── Input/                  # Source SQL Server objects
│       │   └── DatabaseName/       # Organized by DACPAC extraction
│       │       ├── Tables/         # Table DDL (no constraints)
│       │       ├── Constraints/    # Separated for deployment order
│       │       │   ├── PrimaryKey/ # Deploy first
│       │       │   ├── Check/
│       │       │   ├── Default/
│       │       │   ├── Unique/
│       │       │   └── ForeignKey/ # Deploy last (dependencies)
│       │       ├── Indexes/        # Apply after data load
│       │       ├── Views/          # Programmability objects
│       │       ├── Functions/
│       │       ├── StoredProcedures/
│       │       ├── Triggers/
│       │       └── Types/
│       └── Output/                 # Generated PostgreSQL files
│           └── DatabaseName/       # Same structure as Input
├── temp/                           # Temporary/working files (gitignored)
├── Utilities/
│   └── SqlPackage/                 # Auto-downloaded (gitignored)
└── .env                            # Azure OpenAI config (gitignored)

Folder Purposes

Input Folder - SQL Server Objects

Purpose: Extracted and organized SQL Server database objects (schema and code)

Created by: extract_dacpac_objects.ps1

Structure: Organized like SQL Server Management Studio (SSMS)

  • Each object type in its own folder
  • Constraints separated by type for proper deployment order
  • Schema-based subfolders (dbo/, humanresources/, sales/, etc.)
  • Individual files per object

Best Practice: This structure allows:

  • ✅ Partial migrations (deploy tables first, code later)
  • ✅ Proper dependency order (PKs before FKs)
  • ✅ Granular version control
  • ✅ Easy object location (matches SSMS structure)

Output Folder - PostgreSQL Objects

Purpose: Converted PostgreSQL-compatible DDL and PL/pgSQL code

Created by:

  • GitHub Copilot schema migration (schema DDL files)
  • migrate_code_objects.ps1 (code objects)

Structure:

Output/
├── 01_extensions.sql          # CREATE EXTENSION (auto-detected: uuid-ossp, pgcrypto, ltree, etc.)
├── 02_schemas.sql             # CREATE SCHEMA
├── 03_sequences.sql           # CREATE SEQUENCE
├── 04_tables.sql              # All CREATE TABLE (no constraints)
├── 05_primary_keys.sql        # All PRIMARY KEY constraints
├── 06_unique_constraints.sql  # All UNIQUE constraints
├── 07_check_constraints.sql   # All CHECK constraints
├── 08_default_constraints.sql # All DEFAULT constraints
├── 09_foreign_keys.sql        # All FOREIGN KEY constraints
├── 10_indexes.sql             # All CREATE INDEX
└── Views/                     # Individual code objects
    Functions/
    StoredProcedures/
    Triggers/

Best Practice:

  • ✅ Numbered schema files ensure correct deployment order
  • ✅ Grouped objects (all tables in one file) easier to manage than 574 individual files
  • ✅ Individual code files for easier maintenance and version control
  • ✅ Follows industry standards (Flyway, Liquibase patterns)

temp Folder - Working Files

Purpose: Temporary files created during migration process

Contains:

  • Test outputs (test_output*)
  • Intermediate conversion artifacts
  • AI-generated utility scripts (if Copilot creates them)

Best Practice:

  • ✅ Gitignored - not tracked in version control
  • ✅ Can be safely deleted (use reset_workspace.ps1)
  • ✅ Keeps permanent scripts separate from temporary ones

scripts Folder - Permanent Scripts

Purpose: Core migration scripts (reusable, version controlled)

Contains:

  • DACPAC extraction scripts
  • Migration orchestration scripts
  • AI pipeline scripts
  • Workspace reset utility

Best Practice:

  • ✅ Only permanent, reusable scripts
  • ✅ Version controlled
  • ✅ Documented with help text

Why This Object Breakdown?

Constraint Separation

SQL Server constraints are extracted into separate folders by type:

Constraints/
├── PrimaryKey/    # Step 1: Create PKs first
├── Check/         # Step 2: Add check constraints
├── Default/       # Step 3: Add defaults
├── Unique/        # Step 4: Add unique constraints
└── ForeignKey/    # Step 5: Add FKs last (require PKs to exist)

Why?

  • ✅ Enforces proper deployment order
  • ✅ Prevents circular dependency errors
  • ✅ Allows partial deployment (tables without FKs for data load)
  • ✅ Standard database migration practice

Index Separation

Indexes are in a separate folder from tables:

Why?

  • ✅ Can be applied AFTER data load (better performance)
  • ✅ Allows index tuning without touching table DDL
  • ✅ Standard practice: load data first, then add indexes

Schema vs. Code Separation

Structural objects (Tables, Constraints, Indexes) separate from code (Views, Functions, Procedures, Triggers):

Why?

  • ✅ Different migration strategies
    • Schema: Interactive review via GitHub Copilot
    • Code: Automated batch via AI pipeline
  • ✅ Different change frequency (schema stable, code changes often)
  • ✅ Allows partial migrations (deploy schema first, code when ready)

Deployment Order

Schema Files (Run in Order 01-10)

The numbered schema files are executed sequentially:

# Schema deployment (before data load)
psql -d mydb -f 01_extensions.sql
psql -d mydb -f 02_schemas.sql
psql -d mydb -f 03_sequences.sql
psql -d mydb -f 04_tables.sql
psql -d mydb -f 05_primary_keys.sql
psql -d mydb -f 06_unique_constraints.sql
psql -d mydb -f 07_check_constraints.sql
psql -d mydb -f 08_default_constraints.sql

# --- LOAD DATA HERE ---

psql -d mydb -f 09_foreign_keys.sql  # After data (to avoid FK violations)
psql -d mydb -f 10_indexes.sql       # After data (for performance)

Code Objects (Deploy After Schema)

# Views, Functions, Procedures, Triggers
for file in Views/**/*.sql; do psql -d mydb -f "$file"; done
for file in Functions/**/*.sql; do psql -d mydb -f "$file"; done
for file in StoredProcedures/**/*.sql; do psql -d mydb -f "$file"; done
for file in Triggers/**/*.sql; do psql -d mydb -f "$file"; done

Why This Order?

  • PKs must exist before FKs can reference them
  • FKs added after data load to avoid constraint violations during bulk inserts
  • Indexes added after data load for better performance
  • Views/Functions/Procedures deployed after tables exist

Cleanup & Reset

Standard Reset (Removes Generated Files)

.\scripts\reset_workspace.ps1

Removes:

  • Temporary files (temp/, test_output*)
  • AI-generated scripts (convert_*.ps1)
  • PostgreSQL output (Output folders)
  • Generated reports (.md files)
  • Extracted DACPAC objects

Keep Extracted Objects

.\scripts\reset_workspace.ps1 -KeepExtracted

Removes everything except extracted DACPAC objects in Input folders.

Full Reset (Including SqlPackage)

.\scripts\reset_workspace.ps1 -IncludeSqlPackage

Removes everything including downloaded SqlPackage utility.

.gitignore Strategy

Tracked (Version Controlled):

  • ✅ Source scripts (scripts/)
  • ✅ GitHub Copilot prompts (.github/prompts/)
  • ✅ Example DACPAC files (migrations/*/Input/*.dacpac)
  • ✅ Documentation

Ignored (Not Tracked):

  • ❌ .env (contains API keys)
  • ❌ temp/ (working files)
  • ❌ test_output* (test artifacts)
  • ❌ scripts/convert_*.ps1 (AI-generated)
  • ❌ Utilities/SqlPackage/ (can be re-downloaded)
  • ❌ reports/ (generated during migration)

Optional (Project Decision):

  • ⚠️ migrations/*/Output/ - Generated PostgreSQL files
    • Track if you want to version control the converted output
    • Ignore if regenerating from source each time

Best Practices Summary

✅ DO:

  • Keep source (Input) and output (Output) separate
  • Use temp/ for all temporary/working files
  • Organize objects by type and schema (matches SSMS)
  • Separate constraints by type for proper deployment order
  • Version control permanent scripts only
  • Document your folder structure

❌ DON'T:

  • Mix permanent scripts with AI-generated temp scripts
  • Put working files in root directory
  • Track secrets (.env) in version control
  • Bundle constraints with table DDL
  • Apply indexes before data load

References

This structure follows:

  • SQL Server Management Studio (SSMS) object organization
  • Standard database migration practices (Flyway, Liquibase patterns)
  • Infrastructure-as-Code principles (separate source/output/temp)