Add migration template generator command

[vlavrynovych]

Problem

Users must manually:

1. Calculate the correct timestamp for new migrations
2. Create files in the correct directory structure
3. Remember the naming convention (e.g., V{timestamp}_{description}.ts)
4. Set up the basic migration structure (up/down functions or class)

This is error-prone and slows down development. Competitors like Knex.js, Sequelize, Rails, Alembic all provide generators.

---

Proposed Solution: Comprehensive Migration Generator Module

After detailed analysis, we've identified that this feature should be a full-fledged generator module with template registry, variable substitution, and content injection capabilities. This addresses enterprise needs for standardization and team collaboration.

High-Level Architecture

// Simple API
await msr.generator.create('add_user_email_index');

// Module structure
msr.generator = {
  create()          // Generate migration from template
  list()            // List available templates
  register()        // Register custom template (with override)
  unregister()      // Remove template
  get()             // Get template info
  setDefault()      // Set default template for file type
  getDefault()      // Get default template ID
  validate()        // Validate template without generating
  preview()         // Preview output without creating files
  export()          // Export template to JSON
  import()          // Import template from JSON
}

---

Core Features

1. Template Registry System

Registry manages templates by file type with override support:

interface IMigrationTemplate {
  readonly id: string;
  readonly name: string;
  readonly description: string;
  readonly fileExtensions: string[];     // ['.ts'] or ['.up.sql', '.down.sql']
  readonly metadata?: TemplateMetadata;
  readonly variables?: Record<string, TemplateVariable>;
  
  generate(
    description: string,
    timestamp: number,
    variables?: Record<string, any>
  ): Map<string, string>;  // extension -> content
}

Built-in templates:

• TypeScriptBasicTemplate - Functions-based TS migrations
• TypeScriptClassTemplate - Class-based TS migrations
• JavaScriptBasicTemplate - Functions-based JS migrations
• SqlBasicTemplate - Basic SQL (generates .up.sql and .down.sql)
• SqlPostgreSQLTemplate - PostgreSQL best practices
• SqlTransactionalTemplate - Wrapped in BEGIN/COMMIT

Template registration:

// Register new template
msr.generator.register('sql', myTemplate);

// Override existing template
msr.generator.register('sql', betterTemplate, { override: true });

// Set as default
msr.generator.register('sql', myTemplate, { setAsDefault: true });

2. Variable System with Content Injection

Templates support variables for dynamic content:

{
  "id": "team-postgres-ddl",
  "name": "Team PostgreSQL DDL",
  "fileExtensions": [".up.sql", ".down.sql"],
  "variables": {
    "jiraTicket": {
      "type": "string",
      "required": true,
      "pattern": "^[A-Z]+-\\d+$",
      "prompt": "Enter JIRA ticket number:"
    },
    "content": {
      "type": "text",
      "description": "SQL content for up migration",
      "required": true,
      "multiline": true
    },
    "rollbackContent": {
      "type": "text",
      "description": "SQL content for down migration",
      "required": false
    },
    "author": {
      "type": "string",
      "default": "{{env:USER}}"
    },
    "timeout": {
      "type": "number",
      "default": 30,
      "min": 1,
      "max": 300
    },
    "changeType": {
      "type": "enum",
      "options": ["DDL", "DML", "INDEX"],
      "default": "DDL"
    }
  },
  "templates": {
    ".up.sql": "-- Migration: {{description}}\n-- Author: {{author}}\n-- Ticket: {{jiraTicket}}\n-- Type: {{changeType}}\n\nBEGIN;\n\nSET LOCAL statement_timeout = '{{timeout}}s';\n\n{{content}}\n\nCOMMIT;\n",
    ".down.sql": "-- Rollback: {{description}}\n-- Ticket: {{jiraTicket}}\n\nBEGIN;\n\n{{#if rollbackContent}}\n{{rollbackContent}}\n{{else}}\n-- TODO: Manual rollback required\n{{/if}}\n\nCOMMIT;\n"
  }
}

Variable types:

• string - Single-line text
• text - Multi-line text (for SQL content injection)
• number - Numeric value with min/max bounds
• boolean - True/false flag
• enum - Fixed set of options
• array - List of values
• object - Nested structure

Variable features:

• Required/optional validation
• Default values (including environment variables)
• Pattern/regex validation
• Interactive prompts for CLI
• Type checking
• Transforms (lowercase, uppercase, trim, slug)

3. Template Syntax (Handlebars-based)

{{variableName}}                           <!-- Simple substitution -->
{{variableName | default: 'fallback'}}     <!-- With default -->
{{variableName | uppercase}}               <!-- With transform -->

{{#if condition}}                          <!-- Conditional -->
  Content when true
{{else}}
  Content when false
{{/if}}

{{#each items}}                            <!-- Loop -->
  {{this.name}}
{{/each}}

{{! Comment }}                             <!-- Comment -->

4. JSON Template Support

Templates can be defined in JSON files and shared across team:

// Import template from JSON
await msr.generator.import('sql', './templates/team-ddl.json');

// Use imported template
await msr.generator.create('add_index', {
  fileType: 'sql',
  template: 'team-postgres-ddl',
  variables: {
    jiraTicket: 'PROJ-123',
    content: 'CREATE INDEX CONCURRENTLY idx_users_email ON users(email);',
    rollbackContent: 'DROP INDEX CONCURRENTLY IF EXISTS idx_users_email;'
  }
});

// Export template to JSON for sharing
await msr.generator.export('sql', 'team-postgres-ddl', './templates/export.json');

5. SQL Up/Down Auto-Generation

SQL templates automatically generate both .up.sql and .down.sql files:

await msr.generator.create('add_indexes', {
  fileType: 'sql',
  template: 'basic'
});

// Generates:
// - V202511291234_add_indexes.up.sql
// - V202511291234_add_indexes.down.sql

6. Content Injection for Automated Workflows

Key feature: Inject generated/detected SQL directly into templates

// CI/CD: Schema diff detection
const schemaDiff = await detectSchemaDrift();
const sqlChanges = convertDiffToSQL(schemaDiff);

await msr.generator.create('sync schema changes', {
  fileType: 'sql',
  template: 'team-postgres-ddl',
  variables: {
    jiraTicket: 'AUTO-SYNC',
    content: sqlChanges.up,           // Inject generated SQL
    rollbackContent: sqlChanges.down, // Inject rollback SQL
    changeType: 'DDL'
  }
});

// AI-assisted generation
const aiSQL = await generateSQLWithAI('add full text search');

await msr.generator.create('add full text search', {
  variables: {
    content: aiSQL.up,
    rollbackContent: aiSQL.down
  }
});

---

Use Cases

1. Team-Wide Project Templates

Scenario: Entire team uses standardized migration templates

project/
├── templates/
│   ├── postgres-ddl.json      # Schema changes
│   ├── postgres-dml.json      # Data migrations
│   └── typescript-class.json  # Complex TS migrations
├── migrations/
└── package.json

// package.json
{
  "scripts": {
    "migrate:generate:ddl": "tsx scripts/generate-migration.ts ddl",
    "migrate:generate:dml": "tsx scripts/generate-migration.ts dml"
  }
}

Team member usage:

$ npm run migrate:generate:ddl

Migration description: create users table
Ticket number: PROJ-456

Generated:
  - V202511291234_create_users_table.up.sql
  - V202511291234_create_users_table.down.sql

2. CI/CD Automated Migration Generation

Scenario: Detect schema drift and auto-generate migrations

# .github/workflows/schema-check.yml
- name: Detect schema changes
  run: npm run schema:diff > schema-changes.txt
  
- name: Generate migration from schema changes
  run: |
    node scripts/auto-generate-migration.js
    
- name: Commit generated migration
  uses: stefanzweifel/git-auto-commit-action@v4
  with:
    file_pattern: migrations/*.sql

// scripts/auto-generate-migration.js
const changes = fs.readFileSync('schema-changes.txt', 'utf-8');
const sqlChanges = parseAndConvertToSQL(changes);

await msr.generator.create('auto-sync schema', {
  fileType: 'sql',
  template: 'team-postgres-ddl',
  variables: {
    jiraTicket: process.env.GITHUB_REF,
    author: 'github-actions[bot]',
    content: sqlChanges.up,           // Inject detected changes
    rollbackContent: sqlChanges.down  // Inject rollback
  }
});

3. Migration-as-a-Service API

Scenario: Centralized service for generating migrations across microservices

// POST /api/migrations/generate
app.post('/api/migrations/generate', async (req, res) => {
  const { service, description, type, variables } = req.body;
  
  const preview = await msr.generator.preview(description, {
    fileType: 'sql',
    template: type,
    variables: { ...variables, service, author: req.user.email }
  });
  
  const artifacts = await storeArtifacts(service, preview);
  const prUrl = await createPullRequest(service, artifacts);
  
  res.json({ files: Array.from(preview.keys()), pullRequest: prUrl });
});

4. IDE Extension/Plugin

VSCode extension "MSR Migration Generator":

• Right-click in migrations folder → "Generate Migration"
• Template picker with preview
• Variable input forms
• Preview before generating

5. Database GUI Tool Integration

DBeaver/TablePlus plugin:

• User modifies schema in GUI
• Tool captures generated SQL
• Plugin auto-generates MSR migration with captured SQL

6. Slack Bot for Team Collaboration

/msr-generate create users table

Bot response:
✓ Generated V202511291234_create_users_table
  📄 .up.sql
  📄 .down.sql
  🔗 PR: https://github.com/company/repo/pull/1234

7. AI-Assisted Generation

class AIMigrationTemplate implements IMigrationTemplate {
  async generate(description, timestamp, variables) {
    const prompt = `Generate PostgreSQL migration for: ${description}`;
    const completion = await openai.chat.completions.create({
      model: 'gpt-4',
      messages: [{ role: 'user', content: prompt }]
    });
    
    const generated = parseAIResponse(completion);
    return new Map([
      ['.up.sql', wrapSQL(generated.up)],
      ['.down.sql', wrapSQL(generated.down)]
    ]);
  }
}

---

Implementation Plan

Phase 1: Core Generator (Milestone v0.4.0)

Files to create:

• src/interface/generator/IMigrationGenerator.ts - Main module interface
• src/interface/generator/IMigrationTemplate.ts - Template interface
• src/model/TemplateVariable.ts - Variable definition types
• src/model/GenerateOptions.ts - Generation options
• src/service/generator/MigrationGeneratorService.ts - Main service
• src/service/generator/MigrationTemplateRegistry.ts - Template registry
• src/service/generator/TemplateRenderer.ts - Handlebars rendering

Built-in templates:

• src/service/generator/templates/TypeScriptBasicTemplate.ts
• src/service/generator/templates/SqlBasicTemplate.ts

Integration:

• Add generator: IMigrationGenerator property to MigrationScriptExecutor
• Export from main index

Tests:

• Unit tests for registry, renderer, templates
• Integration tests for generation workflow
• Test fixtures with sample templates

Estimated effort: Medium (3-5 days)

Phase 2: Variable System (Milestone v0.4.1)

Features:

• Variable validation
• Default value resolution
• Environment variable support
• Type checking
• Interactive prompts for CLI

Files:

• src/service/generator/VariableValidator.ts
• src/service/generator/VariableResolver.ts
• src/service/generator/InteractivePrompt.ts

Estimated effort: Medium (2-3 days)

Phase 3: JSON Template Support (Milestone v0.4.2)

Features:

• JSON schema for templates
• JSON template loader
• Import/export functionality
• Template validation

Files:

• src/service/generator/JsonTemplateLoader.ts
• src/service/generator/TemplateExporter.ts
• templates/schema.json - JSON schema definition

Estimated effort: Low (1-2 days)

Phase 4: Additional Templates (Milestone v0.4.3)

Add more built-in templates:

• SqlPostgreSQLTemplate - PostgreSQL best practices
• SqlTransactionalTemplate - Wrapped in transactions
• TypeScriptClassTemplate - Class-based migrations
• JavaScriptBasicTemplate - JS migrations

Estimated effort: Low (1-2 days)

Phase 5: Documentation (Milestone v0.4.4)

Documentation to create:

• docs/features/migration-generator.md - Main guide
• docs/features/custom-templates.md - Template authoring guide
• docs/features/json-templates.md - JSON template reference
• docs/examples/team-templates.md - Team workflow examples
• docs/examples/ci-cd-generation.md - Automation examples
• Update getting-started guide

Estimated effort: Medium (2-3 days)

---

Technical Specifications

API Design

interface IMigrationGenerator {
  create(description: string, options?: GenerateOptions): Promise<string[]>;
  list(fileType?: string): TemplateInfo[];
  register(fileType: string, template: IMigrationTemplate, options?: RegisterOptions): void;
  unregister(fileType: string, templateId: string): void;
  get(fileType: string, templateId?: string): TemplateInfo | undefined;
  setDefault(fileType: string, templateId: string): void;
  getDefault(fileType: string): string | undefined;
  validate(fileType: string, templateId: string): ValidationResult;
  preview(description: string, options?: GenerateOptions): Promise<Map<string, string>>;
  export(fileType: string, templateId: string, outputPath: string): Promise<void>;
  import(fileType: string, inputPath: string, options?: RegisterOptions): Promise<void>;
}

interface GenerateOptions {
  fileType?: 'ts' | 'js' | 'sql';
  template?: string;
  directory?: string;
  variables?: Record<string, any>;
}

interface RegisterOptions {
  override?: boolean;
  setAsDefault?: boolean;
  builtIn?: boolean;
}

interface TemplateInfo {
  id: string;
  name: string;
  description: string;
  fileType: string;
  fileExtensions: string[];
  isDefault: boolean;
  isBuiltIn: boolean;
  metadata?: TemplateMetadata;
}

Template Variable Types

interface TemplateVariable {
  type: 'string' | 'text' | 'number' | 'boolean' | 'enum' | 'array' | 'object';
  description?: string;
  required?: boolean;
  default?: any;
  example?: any;
  prompt?: string;
  pattern?: string;          // Regex for validation
  multiline?: boolean;       // For text type
  options?: string[];        // For enum type
  min?: number;              // For number type
  max?: number;              // For number type
  transform?: 'lowercase' | 'uppercase' | 'trim' | 'slug';
  validate?: ValidationRule[];
}

JSON Template Schema

{
  "id": "unique-template-id",
  "name": "Human Readable Name",
  "description": "Template description",
  "fileExtensions": [".up.sql", ".down.sql"],
  "metadata": {
    "author": "Platform Team",
    "version": "1.0.0",
    "created": "2025-11-29T12:00:00Z"
  },
  "variables": {
    "variableName": {
      "type": "string",
      "required": true,
      "pattern": "^[A-Z]+-\\d+$",
      "prompt": "Enter value:"
    }
  },
  "templates": {
    ".up.sql": "-- Template content with {{variables}}",
    ".down.sql": "-- Rollback content"
  }
}

---

Benefits

For Individual Developers

• Faster development: No manual timestamp calculation
• Consistent naming: Enforces project conventions
• Reduced errors: Correct structure from start
• Better DX: Matches expectations from other tools

For Teams

• Standardization: Shared templates ensure consistency
• Best practices: Templates encode team standards
• Onboarding: New developers immediately productive
• Quality: Pre-validated structures prevent mistakes

For Enterprises

• Governance: Enforce migration standards across teams
• Compliance: Templates can include required metadata (tickets, approvals)
• Automation: Integrate with existing workflows (Jira, Jenkins, etc.)
• Audit trail: Generated migrations include full context

For Adapters

• Extensibility: Can provide DBMS-specific templates
• Differentiation: Custom templates add value
• Best practices: Encode database-specific patterns

---

Related Issues

• Complements Add basic CLI commands to core package #59 (CLI commands) - Generator will be accessible via CLI
• Uses Add validation to prevent duplicate migration timestamps #82 (duplicate timestamp validation) - Validates no collisions
• Supports simplified up/down interface from competitive analysis
• Inspired by Research top 10 migration tools: features, gaps, and opportunities #74 (Rails convention over configuration)

---

Future Enhancements (Post v0.4.x)

Advanced Features

• Template marketplace: Share templates across community
• Template versioning: Upgrade templates over time
• Template testing: Validate templates before use
• Template inheritance: Base templates with overrides
• Multi-language support: Templates in different languages

Integrations

• GitHub/GitLab integration: Auto-create PRs
• Jira integration: Link to tickets
• Slack integration: Bot commands
• IDE plugins: VSCode, IntelliJ extensions

AI Features

• AI template generation: Generate templates from description
• Smart suggestions: Recommend variables based on description
• Rollback generation: Auto-generate down migrations

---

Notes

• This is a significant feature that positions MSR as enterprise-ready
• Implementation should be incremental across multiple milestones
• Focus on core functionality first (Phase 1-2), then enhance
• JSON templates are key differentiator - enables team collaboration
• Content injection enables automation scenarios (CI/CD, AI-assisted)
• Consider this a module/sub-system within MSR, not just a helper function

---

Questions to Resolve

1. Should we support template inheritance/composition?
2. Should templates be able to call external APIs (for dynamic content)?
3. Should we support custom Handlebars helpers registration?
4. Should there be a separate @migration-script-runner/generator package?
5. How to handle template versioning and migrations?
6. Should we support liquid/mustache/other template engines?

---

Success Criteria

• ✅ Simple API: msr.generator.create('description') works out-of-box
• ✅ Template registry with override support
• ✅ Variable validation and substitution
• ✅ JSON template import/export
• ✅ Auto-generates SQL up/down files
• ✅ 100% test coverage maintained
• ✅ Comprehensive documentation with examples
• ✅ Works with all supported file types (TS, JS, SQL)
• ✅ Content injection for automation scenarios

[vlavrynovych]

Additional Feature: Template Inheritance & Composition

Concept

Templates should support inheritance/composition, allowing multi-layer template hierarchies:

1. Base Layer - Basic structure and syntax
2. DBMS Layer - Database-specific configurations and patterns
3. Business Layer - Project/team-specific standards

Use Case Example

Layer 1: Base SQL Template

{
  "id": "base-sql",
  "name": "Base SQL Template",
  "fileExtensions": [".up.sql", ".down.sql"],
  "variables": {
    "description": {
      "type": "string",
      "required": true
    },
    "content": {
      "type": "text",
      "required": true
    }
  },
  "templates": {
    ".up.sql": "-- Migration: {{description}}\n-- Generated: {{timestamp}}\n\n{{content}}\n",
    ".down.sql": "-- Rollback: {{description}}\n\n{{rollbackContent}}\n"
  }
}

Layer 2: PostgreSQL Template (extends base-sql)

{
  "id": "postgresql-ddl",
  "name": "PostgreSQL DDL",
  "extends": "base-sql",
  "variables": {
    "timeout": {
      "type": "number",
      "default": 30,
      "description": "Statement timeout in seconds"
    },
    "lockTimeout": {
      "type": "number",
      "default": 5,
      "description": "Lock timeout in seconds"
    },
    "transactional": {
      "type": "boolean",
      "default": true
    }
  },
  "templates": {
    ".up.sql": "-- Migration: {{description}}\n-- Generated: {{timestamp}}\n-- DBMS: PostgreSQL\n\n{{#if transactional}}BEGIN;\n\nSET LOCAL statement_timeout = '{{timeout}}s';\nSET LOCAL lock_timeout = '{{lockTimeout}}s';\n\n{{/if}}{{content}}\n{{#if transactional}}\n\nCOMMIT;{{/if}}\n"
  }
}

Layer 3: Team Business Template (extends postgresql-ddl)

{
  "id": "team-postgres-ddl",
  "name": "Team PostgreSQL DDL",
  "extends": "postgresql-ddl",
  "variables": {
    "jiraTicket": {
      "type": "string",
      "required": true,
      "pattern": "^[A-Z]+-\\d+$",
      "prompt": "Enter JIRA ticket:"
    },
    "author": {
      "type": "string",
      "default": "{{env:USER}}"
    },
    "reviewedBy": {
      "type": "string",
      "required": true,
      "prompt": "Reviewed by:"
    },
    "changeType": {
      "type": "enum",
      "options": ["DDL", "DML", "INDEX", "CONSTRAINT"],
      "required": true
    }
  },
  "templates": {
    ".up.sql": "-- Migration: {{description}}\n-- Generated: {{timestamp}}\n-- DBMS: PostgreSQL\n-- Author: {{author}}\n-- Reviewed By: {{reviewedBy}}\n-- Ticket: {{jiraTicket}}\n-- Type: {{changeType}}\n\n{{#if transactional}}BEGIN;\n\nSET LOCAL statement_timeout = '{{timeout}}s';\nSET LOCAL lock_timeout = '{{lockTimeout}}s';\n\n{{/if}}{{content}}\n{{#if transactional}}\n\nCOMMIT;{{/if}}\n"
  }
}

Inheritance Behavior

Variable Inheritance:

• Child inherits parent's variables
• Child can override parent variable definitions
• Child can add new variables
• Variable defaults are inherited unless overridden

Template Inheritance:

• Child inherits parent's template content
• Child can completely override template (if provided)
• Child can extend template using placeholders (future: {{super}} keyword)

Metadata Inheritance:

• Child inherits parent's metadata
• Child can override specific metadata fields

API Design

interface IMigrationTemplate {
  readonly id: string;
  readonly name: string;
  readonly description: string;
  
  /** Parent template ID (for inheritance) */
  readonly extends?: string;
  
  readonly fileExtensions: string[];
  readonly metadata?: TemplateMetadata;
  readonly variables?: Record<string, TemplateVariable>;
  
  generate(
    description: string,
    timestamp: number,
    variables?: Record<string, any>
  ): Map<string, string>;
}

interface MigrationTemplateRegistry {
  /**
   * Register template with inheritance support.
   * Validates that parent exists if 'extends' is specified.
   */
  register(
    fileType: string,
    template: IMigrationTemplate,
    options?: RegisterOptions
  ): void;
  
  /**
   * Resolve template with full inheritance chain.
   * Returns merged template with all inherited variables and content.
   */
  resolve(fileType: string, templateId: string): ResolvedTemplate;
  
  /**
   * Get inheritance chain for a template.
   */
  getInheritanceChain(fileType: string, templateId: string): string[];
}

interface ResolvedTemplate {
  /** Resolved template */
  template: IMigrationTemplate;
  
  /** Merged variables from entire inheritance chain */
  variables: Record<string, TemplateVariable>;
  
  /** Inheritance chain (base to child) */
  chain: string[];
}

Usage Examples

Example 1: Simple Inheritance

// Register base template
await msr.generator.import('sql', './templates/base-sql.json');

// Register PostgreSQL template (extends base)
await msr.generator.import('sql', './templates/postgresql-ddl.json');

// Register team template (extends PostgreSQL)
await msr.generator.import('sql', './templates/team-postgres-ddl.json');

// Use most specific template
await msr.generator.create('add index', {
  fileType: 'sql',
  template: 'team-postgres-ddl',
  variables: {
    jiraTicket: 'PROJ-123',
    author: 'john.doe',
    reviewedBy: 'jane.smith',
    changeType: 'INDEX',
    content: 'CREATE INDEX CONCURRENTLY idx_users_email ON users(email);',
    timeout: 60  // Override default from parent
  }
});

// Inheritance chain: base-sql → postgresql-ddl → team-postgres-ddl

Example 2: Override Parent at Different Layers

// Base template: 30s timeout
// PostgreSQL template: inherits 30s
// Team template: overrides to 60s default

const info = msr.generator.get('sql', 'team-postgres-ddl');
console.log(info.variables.timeout.default); // 60 (overridden)

Example 3: Adapter Provides DBMS Layer

// msr-postgresql adapter
class PostgreSQLAdapter {
  registerTemplates(generator: IMigrationGenerator) {
    // Register PostgreSQL templates that extend base
    generator.register('sql', new PostgreSQLDDLTemplate(), { 
      extends: 'base-sql',
      setAsDefault: true 
    });
    generator.register('sql', new PostgreSQLDMLTemplate(), { 
      extends: 'base-sql' 
    });
  }
}

// User registers team template on top
await msr.generator.register('sql', teamTemplate, {
  extends: 'postgresql-ddl'  // Extends adapter's template
});

Example 4: View Inheritance Chain

const chain = msr.generator.getInheritanceChain('sql', 'team-postgres-ddl');
console.log(chain);
// → ['base-sql', 'postgresql-ddl', 'team-postgres-ddl']

const resolved = msr.generator.resolve('sql', 'team-postgres-ddl');
console.log(resolved.variables);
// → All variables from base-sql + postgresql-ddl + team-postgres-ddl (merged)

Benefits

Separation of Concerns:

• Base layer: Generic structure
• DBMS layer: Database-specific patterns
• Business layer: Company/team standards

Reusability:

• Define common patterns once
• Compose specialized templates
• Share base templates across teams

Maintainability:

• Update base template → all children inherit changes
• Override only what's different
• Clear hierarchy of customization

Flexibility:

• Teams can extend adapter-provided templates
• Projects can extend team templates
• Easy to see what's customized at each layer

Implementation Considerations

Phase: Add to Phase 2 (Variable System) or separate Phase 2.5

Complexity: Medium

• Template resolution algorithm
• Circular dependency detection
• Variable merging strategy
• Template content composition

Files to Add:

• src/service/generator/TemplateResolver.ts - Resolves inheritance
• src/service/generator/VariableMerger.ts - Merges variable definitions
• Update MigrationTemplateRegistry with resolution logic

Tests:

• Template inheritance chain resolution
• Variable overriding at different layers
• Circular dependency detection
• Invalid parent template error handling

Estimated Effort: +2-3 days

Open Questions

1. Multiple inheritance? Should templates support multiple parents? (e.g., extends: ['base', 'mixin'])
2. {{super}} keyword? Should child templates be able to reference parent content? (e.g., Jinja2's {{ super() }})
3. Validation order? Should parent variables be validated before child variables?
4. Override rules? What if parent requires variable but child makes it optional?
5. Deep merging? How to handle nested object variables in inheritance?

JSON Schema Update

{
  "id": "template-id",
  "name": "Template Name",
  "extends": "parent-template-id",  // NEW: Parent template
  "fileExtensions": [".up.sql"],
  "variables": {
    // Child variables + overrides
  },
  "templates": {
    // Child can override parent templates
  }
}

---

This feature would make the template system enterprise-grade by enabling:

• Layered customization without duplication
• Adapter ecosystem where adapters provide DBMS templates
• Team flexibility to build on adapter foundations
• Clear separation between infrastructure and business concerns

[vlavrynovych]

Moved to Backlog for strategic reasons:

This feature is substantial enough to warrant its own package: @migration-script-runner/generator

Benefits of separate package:

• Modular architecture - users can opt-in if needed
• Independent versioning and release cycle
• Can evolve separately from core
• Easier to develop and test in isolation
• Could become a separate repository in MSR org
• Reduces core package complexity

Potential approach:

1. Create new repo: migration-script-runner/generator
2. Package: @migration-script-runner/generator
3. Integration with core via optional dependency
4. Could target v0.6.0 or v0.7.0 as flagship feature

This is one of the most exciting features planned - giving it proper space to shine! 🎯