# RelSpec Template Mode

The `templ` command allows you to transform database schemas using custom Go text templates. It provides powerful template functions and flexible execution modes for generating any type of output from your database schema.

## Table of Contents

- [Quick Start](#quick-start)
- [Execution Modes](#execution-modes)
- [Template Functions](#template-functions)
  - [String Utilities](#string-utilities)
  - [Type Conversion](#type-conversion)
  - [Filtering](#filtering)
  - [Formatting](#formatting)
  - [Loop Helpers](#loop-helpers)
  - [Sorting Helpers](#sorting-helpers)
  - [Safe Access](#safe-access)
  - [Utility Functions](#utility-functions)
- [Data Model](#data-model)
- [Examples](#examples)

## Quick Start

```bash
# Generate documentation from a database
relspec templ --from pgsql --from-conn "postgres://user:pass@localhost/db" \
              --template docs.tmpl --output schema-docs.md

# Generate TypeScript models (one file per table)
relspec templ --from dbml --from-path schema.dbml \
              --template model.tmpl --mode table \
              --output ./models/ \
              --filename-pattern "{{.Name | toCamelCase}}.ts"

# Output to stdout
relspec templ --from json --from-path schema.json \
              --template report.tmpl
```

## Execution Modes

The `--mode` flag controls how the template is executed:

| Mode | Description | Output | When to Use |
|------|-------------|--------|-------------|
| `database` | Execute once for entire database | Single file | Documentation, reports, overview files |
| `schema` | Execute once per schema | One file per schema | Schema-specific documentation |
| `domain` | Execute once per domain | One file per domain | Domain-based documentation, domain exports |
| `script` | Execute once per script | One file per script | Script processing |
| `table` | Execute once per table | One file per table | Model generation, table docs |

### Filename Patterns

For multi-file modes (`schema`, `domain`, `script`, `table`), use `--filename-pattern` to control output filenames:

```bash
# Default pattern
--filename-pattern "{{.Name}}.txt"

# With transformations
--filename-pattern "{{.Name | toCamelCase}}.ts"

# Nested directories
--filename-pattern "{{.Schema}}/{{.Name}}.md"

# Complex patterns
--filename-pattern "{{.ParentSchema.Name}}/models/{{.Name | toPascalCase}}Model.java"
```

## Template Functions

### String Utilities

Transform and manipulate strings in your templates.

| Function | Description | Example | Output |
|----------|-------------|---------|--------|
| `toUpper` | Convert to uppercase | `{{ "hello" \| toUpper }}` | `HELLO` |
| `toLower` | Convert to lowercase | `{{ "HELLO" \| toLower }}` | `hello` |
| `toCamelCase` | Convert to camelCase | `{{ "user_name" \| toCamelCase }}` | `userName` |
| `toPascalCase` | Convert to PascalCase | `{{ "user_name" \| toPascalCase }}` | `UserName` |
| `toSnakeCase` | Convert to snake_case | `{{ "UserName" \| toSnakeCase }}` | `user_name` |
| `toKebabCase` | Convert to kebab-case | `{{ "UserName" \| toKebabCase }}` | `user-name` |
| `pluralize` | Convert to plural | `{{ "user" \| pluralize }}` | `users` |
| `singularize` | Convert to singular | `{{ "users" \| singularize }}` | `user` |
| `title` | Capitalize first letter | `{{ "hello world" \| title }}` | `Hello World` |
| `trim` | Trim whitespace | `{{ "  hello  " \| trim }}` | `hello` |
| `trimPrefix` | Remove prefix | `{{ trimPrefix "tbl_users" "tbl_" }}` | `users` |
| `trimSuffix` | Remove suffix | `{{ trimSuffix "users_old" "_old" }}` | `users` |
| `replace` | Replace occurrences | `{{ replace "hello" "l" "L" -1 }}` | `heLLo` |
| `stringContains` | Check if contains substring | `{{ stringContains "hello" "ell" }}` | `true` |
| `hasPrefix` | Check if starts with | `{{ hasPrefix "hello" "hel" }}` | `true` |
| `hasSuffix` | Check if ends with | `{{ hasSuffix "hello" "llo" }}` | `true` |
| `split` | Split by separator | `{{ split "a,b,c" "," }}` | `[a b c]` |
| `join` | Join with separator | `{{ join (list "a" "b") "," }}` | `a,b` |

### Type Conversion

Convert SQL types to various programming language types.

| Function | Parameters | Description | Example |
|----------|------------|-------------|---------|
| `sqlToGo` | `sqlType`, `nullable` | SQL to Go | `{{ sqlToGo "varchar" true }}` → `string` |
| `sqlToTypeScript` | `sqlType`, `nullable` | SQL to TypeScript | `{{ sqlToTypeScript "integer" false }}` → `number \| null` |
| `sqlToJava` | `sqlType`, `nullable` | SQL to Java | `{{ sqlToJava "varchar" true }}` → `String` |
| `sqlToPython` | `sqlType` | SQL to Python | `{{ sqlToPython "integer" }}` → `int` |
| `sqlToRust` | `sqlType`, `nullable` | SQL to Rust | `{{ sqlToRust "varchar" false }}` → `Option<String>` |
| `sqlToCSharp` | `sqlType`, `nullable` | SQL to C# | `{{ sqlToCSharp "integer" false }}` → `int?` |
| `sqlToPhp` | `sqlType`, `nullable` | SQL to PHP | `{{ sqlToPhp "varchar" false }}` → `?string` |

**Supported SQL Types:**
- Integer: `integer`, `int`, `smallint`, `bigint`, `serial`, `bigserial`
- String: `text`, `varchar`, `char`, `character`, `citext`
- Boolean: `boolean`, `bool`
- Float: `real`, `float`, `double precision`, `numeric`, `decimal`
- Date/Time: `timestamp`, `date`, `time`, `timestamptz`
- Binary: `bytea`
- Special: `uuid`, `json`, `jsonb`, `array`

### Filtering

Filter and select specific database objects.

| Function | Description | Example |
|----------|-------------|---------|
| `filterTables` | Filter tables by pattern | `{{ filterTables .Schema.Tables "user_*" }}` |
| `filterTablesByPattern` | Alias for filterTables | `{{ filterTablesByPattern .Schema.Tables "temp_*" }}` |
| `filterColumns` | Filter columns by pattern | `{{ filterColumns .Table.Columns "*_id" }}` |
| `filterColumnsByType` | Filter by SQL type | `{{ filterColumnsByType .Table.Columns "varchar" }}` |
| `filterPrimaryKeys` | Get primary key columns | `{{ filterPrimaryKeys .Table.Columns }}` |
| `filterForeignKeys` | Get foreign key constraints | `{{ filterForeignKeys .Table.Constraints }}` |
| `filterUniqueConstraints` | Get unique constraints | `{{ filterUniqueConstraints .Table.Constraints }}` |
| `filterCheckConstraints` | Get check constraints | `{{ filterCheckConstraints .Table.Constraints }}` |
| `filterNullable` | Get nullable columns | `{{ filterNullable .Table.Columns }}` |
| `filterNotNull` | Get non-nullable columns | `{{ filterNotNull .Table.Columns }}` |

**Pattern Matching:**
- `*` - Match any characters
- `?` - Match single character
- Example: `user_*` matches `user_profile`, `user_settings`

### Formatting

Format output and add structure to generated code.

| Function | Description | Example |
|----------|-------------|---------|
| `toJSON` | Convert to JSON | `{{ .Database \| toJSON }}` |
| `toJSONPretty` | Pretty-print JSON | `{{ toJSONPretty .Table "  " }}` |
| `toYAML` | Convert to YAML | `{{ .Schema \| toYAML }}` |
| `indent` | Indent by spaces | `{{ indent .Column.Description 4 }}` |
| `indentWith` | Indent with prefix | `{{ indentWith .Comment "  " }}` |
| `escape` | Escape special chars | `{{ escape .Column.Default }}` |
| `escapeQuotes` | Escape quotes only | `{{ escapeQuotes .String }}` |
| `comment` | Add comment prefix | `{{ comment .Description "//" }}` |
| `quoteString` | Add quotes | `{{ quoteString "value" }}` → `"value"` |
| `unquoteString` | Remove quotes | `{{ unquoteString "\"value\"" }}` → `value` |

**Comment Styles:**
- `//` - C/Go/JavaScript style
- `#` - Python/Shell style
- `--` - SQL style
- `/* */` - Block comment style

### Loop Helpers

Iterate and manipulate collections.

| Function | Description | Example |
|----------|-------------|---------|
| `enumerate` | Add index to items | `{{ range enumerate .Tables }}{{ .Index }}: {{ .Value.Name }}{{ end }}` |
| `batch` | Split into chunks | `{{ range batch .Columns 3 }}...{{ end }}` |
| `chunk` | Alias for batch | `{{ range chunk .Columns 5 }}...{{ end }}` |
| `reverse` | Reverse order | `{{ range reverse .Tables }}...{{ end }}` |
| `first` | Get first N items | `{{ range first .Tables 5 }}...{{ end }}` |
| `last` | Get last N items | `{{ range last .Tables 3 }}...{{ end }}` |
| `skip` | Skip first N items | `{{ range skip .Tables 2 }}...{{ end }}` |
| `take` | Take first N (alias) | `{{ range take .Tables 10 }}...{{ end }}` |
| `concat` | Concatenate slices | `{{ $all := concat .Schema1.Tables .Schema2.Tables }}` |
| `unique` | Remove duplicates | `{{ $unique := unique .Items }}` |
| `sortBy` | Sort by field | `{{ $sorted := sortBy .Tables "Name" }}` |
| `groupBy` | Group by field | `{{ $grouped := groupBy .Tables "Schema" }}` |

### Sorting Helpers

Sort database objects by name or sequence number. All sort functions modify the slice in-place.

**Schema Sorting:**

| Function | Description | Example |
|----------|-------------|---------|
| `sortSchemasByName` | Sort schemas by name | `{{ sortSchemasByName .Database.Schemas false }}` |
| `sortSchemasBySequence` | Sort schemas by sequence | `{{ sortSchemasBySequence .Database.Schemas false }}` |

**Table Sorting:**

| Function | Description | Example |
|----------|-------------|---------|
| `sortTablesByName` | Sort tables by name | `{{ sortTablesByName .Schema.Tables false }}` |
| `sortTablesBySequence` | Sort tables by sequence | `{{ sortTablesBySequence .Schema.Tables true }}` |

**Column Sorting:**

| Function | Description | Example |
|----------|-------------|---------|
| `sortColumnsMapByName` | Convert column map to sorted slice by name | `{{ $cols := sortColumnsMapByName .Table.Columns false }}` |
| `sortColumnsMapBySequence` | Convert column map to sorted slice by sequence | `{{ $cols := sortColumnsMapBySequence .Table.Columns false }}` |
| `sortColumnsByName` | Sort column slice by name | `{{ sortColumnsByName $columns false }}` |
| `sortColumnsBySequence` | Sort column slice by sequence | `{{ sortColumnsBySequence $columns true }}` |

**Other Object Sorting:**

| Function | Description | Example |
|----------|-------------|---------|
| `sortViewsByName` | Sort views by name | `{{ sortViewsByName .Schema.Views false }}` |
| `sortViewsBySequence` | Sort views by sequence | `{{ sortViewsBySequence .Schema.Views false }}` |
| `sortSequencesByName` | Sort sequences by name | `{{ sortSequencesByName .Schema.Sequences false }}` |
| `sortSequencesBySequence` | Sort sequences by sequence | `{{ sortSequencesBySequence .Schema.Sequences false }}` |
| `sortIndexesMapByName` | Convert index map to sorted slice by name | `{{ $idx := sortIndexesMapByName .Table.Indexes false }}` |
| `sortIndexesMapBySequence` | Convert index map to sorted slice by sequence | `{{ $idx := sortIndexesMapBySequence .Table.Indexes false }}` |
| `sortIndexesByName` | Sort index slice by name | `{{ sortIndexesByName $indexes false }}` |
| `sortIndexesBySequence` | Sort index slice by sequence | `{{ sortIndexesBySequence $indexes false }}` |
| `sortConstraintsMapByName` | Convert constraint map to sorted slice by name | `{{ $cons := sortConstraintsMapByName .Table.Constraints false }}` |
| `sortConstraintsByName` | Sort constraint slice by name | `{{ sortConstraintsByName $constraints false }}` |
| `sortRelationshipsMapByName` | Convert relationship map to sorted slice by name | `{{ $rels := sortRelationshipsMapByName .Table.Relationships false }}` |
| `sortRelationshipsByName` | Sort relationship slice by name | `{{ sortRelationshipsByName $relationships false }}` |
| `sortScriptsByName` | Sort scripts by name | `{{ sortScriptsByName .Schema.Scripts false }}` |
| `sortEnumsByName` | Sort enums by name | `{{ sortEnumsByName .Schema.Enums false }}` |

**Sort Parameters:**
- Second parameter: `false` = ascending, `true` = descending
- Example: `{{ sortTablesByName .Schema.Tables true }}` sorts descending (Z-A)

### Safe Access

Safely access nested data without panicking.

| Function | Description | Example |
|----------|-------------|---------|
| `get` | Get map value | `{{ get .Metadata "key" }}` |
| `getOr` | Get with default | `{{ getOr .Metadata "key" "default" }}` |
| `getPath` | Nested access | `{{ getPath .Config "database.host" }}` |
| `getPathOr` | Nested with default | `{{ getPathOr .Config "db.port" 5432 }}` |
| `safeIndex` | Safe array access | `{{ safeIndex .Tables 0 }}` |
| `safeIndexOr` | Safe with default | `{{ safeIndexOr .Tables 0 nil }}` |
| `has` | Check key exists | `{{ if has .Metadata "key" }}...{{ end }}` |
| `hasPath` | Check nested path | `{{ if hasPath .Config "db.host" }}...{{ end }}` |
| `keys` | Get map keys | `{{ range keys .Metadata }}...{{ end }}` |
| `values` | Get map values | `{{ range values .Table.Columns }}...{{ end }}` |
| `merge` | Merge maps | `{{ $merged := merge .Map1 .Map2 }}` |
| `pick` | Select keys | `{{ $subset := pick .Metadata "name" "desc" }}` |
| `omit` | Exclude keys | `{{ $filtered := omit .Metadata "internal" }}` |
| `sliceContains` | Check contains | `{{ if sliceContains .Names "admin" }}...{{ end }}` |
| `indexOf` | Find index | `{{ $idx := indexOf .Names "admin" }}` |
| `pluck` | Extract field | `{{ $names := pluck .Tables "Name" }}` |

### Utility Functions

General-purpose template helpers.

| Function | Description | Example |
|----------|-------------|---------|
| `add` | Add numbers | `{{ add 5 3 }}` → `8` |
| `sub` | Subtract | `{{ sub 10 3 }}` → `7` |
| `mul` | Multiply | `{{ mul 4 5 }}` → `20` |
| `div` | Divide | `{{ div 10 2 }}` → `5` |
| `mod` | Modulo | `{{ mod 10 3 }}` → `1` |
| `default` | Default value | `{{ default "unknown" .Name }}` |
| `dict` | Create map | `{{ $m := dict "key1" "val1" "key2" "val2" }}` |
| `list` | Create list | `{{ $l := list "a" "b" "c" }}` |
| `seq` | Number sequence | `{{ range seq 1 5 }}{{ . }}{{ end }}` → `12345` |

## Data Model

The data available in templates depends on the execution mode:

### Database Mode
```go
.Database          // *models.Database - Full database
.ParentDatabase    // *models.Database - Same as .Database
.FlatColumns       // []*models.FlatColumn - All columns flattened
.FlatTables        // []*models.FlatTable - All tables flattened
.FlatConstraints   // []*models.FlatConstraint - All constraints
.FlatRelationships // []*models.FlatRelationship - All relationships
.Summary           // *models.DatabaseSummary - Statistics
.Metadata          // map[string]interface{} - User metadata
```

### Schema Mode
```go
.Schema            // *models.Schema - Current schema
.ParentDatabase    // *models.Database - Parent database context
.FlatColumns       // []*models.FlatColumn - Schema's columns flattened
.FlatTables        // []*models.FlatTable - Schema's tables flattened
.FlatConstraints   // []*models.FlatConstraint - Schema's constraints
.FlatRelationships // []*models.FlatRelationship - Schema's relationships
.Summary           // *models.DatabaseSummary - Statistics
.Metadata          // map[string]interface{} - User metadata
```

### Domain Mode
```go
.Domain            // *models.Domain - Current domain
.ParentDatabase    // *models.Database - Parent database context
.Metadata          // map[string]interface{} - User metadata
```

### Table Mode
```go
.Table             // *models.Table - Current table
.ParentSchema      // *models.Schema - Parent schema
.ParentDatabase    // *models.Database - Parent database context
.Metadata          // map[string]interface{} - User metadata
```

### Script Mode
```go
.Script            // *models.Script - Current script
.ParentSchema      // *models.Schema - Parent schema
.ParentDatabase    // *models.Database - Parent database context
.Metadata          // map[string]interface{} - User metadata
```

### Model Structures

**Database:**
- `.Name` - Database name
- `.Schemas` - List of schemas
- `.Domains` - List of domains (business domain groupings)
- `.Description`, `.Comment` - Documentation

**Schema:**
- `.Name` - Schema name
- `.Tables` - List of tables
- `.Views`, `.Sequences`, `.Scripts` - Other objects
- `.Enums` - Enum types

**Domain:**
- `.Name` - Domain name
- `.Tables` - List of DomainTable references
- `.Description`, `.Comment` - Documentation
- `.Metadata` - Custom metadata map

**DomainTable:**
- `.TableName` - Name of the table
- `.SchemaName` - Schema containing the table
- `.RefTable` - Pointer to actual Table object (if loaded)

**Table:**
- `.Name` - Table name
- `.Schema` - Schema name
- `.Columns` - Map of columns (use `values` function to iterate)
- `.Constraints` - Map of constraints
- `.Indexes` - Map of indexes
- `.Relationships` - Map of relationships
- `.Description`, `.Comment` - Documentation

**Column:**
- `.Name` - Column name
- `.Type` - SQL type
- `.NotNull` - Is NOT NULL
- `.IsPrimaryKey` - Is primary key
- `.Default` - Default value
- `.Description`, `.Comment` - Documentation

## Examples

### Example 1: TypeScript Interfaces (Table Mode)

**Template:** `typescript-interface.tmpl`
```typescript
// Generated from {{ .ParentDatabase.Name }}.{{ .ParentSchema.Name }}.{{ .Table.Name }}

export interface {{ .Table.Name | toPascalCase }} {
{{- range .Table.Columns | values }}
  {{ .Name | toCamelCase }}: {{ sqlToTypeScript .Type .NotNull }};
{{- end }}
}

{{- $fks := filterForeignKeys .Table.Constraints }}
{{- if $fks }}

// Foreign Keys:
{{- range $fks }}
// - {{ .Name }}: references {{ .ReferencedTable }}
{{- end }}
{{- end }}
```

**Command:**
```bash
relspec templ --from pgsql --from-conn "..." \
              --template typescript-interface.tmpl \
              --mode table \
              --output ./src/types/ \
              --filename-pattern "{{.Name | toCamelCase}}.ts"
```

### Example 2: Markdown Documentation (Database Mode)

**Template:** `database-docs.tmpl`
```markdown
# Database: {{ .Database.Name }}

{{ if .Database.Description }}{{ .Database.Description }}{{ end }}

**Statistics:**
- Schemas: {{ len .Database.Schemas }}
- Tables: {{ .Summary.TotalTables }}
- Columns: {{ .Summary.TotalColumns }}

{{ range .Database.Schemas }}
## Schema: {{ .Name }}

{{ range .Tables }}
### {{ .Name }}

{{ if .Description }}{{ .Description }}{{ end }}

**Columns:**

| Column | Type | Nullable | PK | Description |
|--------|------|----------|----|----|
{{- range .Columns | values }}
| {{ .Name }} | `{{ .Type }}` | {{ if .NotNull }}No{{ else }}Yes{{ end }} | {{ if .IsPrimaryKey }}✓{{ end }} | {{ .Description }} |
{{- end }}

{{- $fks := filterForeignKeys .Constraints }}
{{- if $fks }}

**Foreign Keys:**

{{ range $fks }}
- `{{ .Name }}`: {{ join .Columns ", " }} → {{ .ReferencedTable }}({{ join .ReferencedColumns ", " }})
{{- end }}
{{- end }}

{{ end }}
{{ end }}
```

### Example 3: Python SQLAlchemy Models (Table Mode)

**Template:** `python-model.tmpl`
```python
"""{{ .Table.Name | toPascalCase }} model for {{ .ParentDatabase.Name }}.{{ .ParentSchema.Name }}"""

from sqlalchemy import Column
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class {{ .Table.Name | toPascalCase }}(Base):
    """{{ if .Table.Description }}{{ .Table.Description }}{{ else }}{{ .Table.Name }} table{{ end }}"""

    __tablename__ = "{{ .Table.Name }}"
    __table_args__ = {"schema": "{{ .ParentSchema.Name }}"}

    {{- range .Table.Columns | values }}
    {{ .Name }} = Column({{ sqlToPython .Type }}{{ if .IsPrimaryKey }}, primary_key=True{{ end }}{{ if .NotNull }}, nullable=False{{ end }})
    {{- end }}
```

### Example 4: GraphQL Schema (Schema Mode)

**Template:** `graphql-schema.tmpl`
```graphql
"""{{ .Schema.Name }} schema"""

{{ range .Schema.Tables }}
type {{ .Name | toPascalCase }} {
{{- range .Columns | values }}
  {{ .Name | toCamelCase }}: {{ sqlToTypeScript .Type .NotNull | replace " | null" "" }}{{ if not .NotNull }}{{ end }}
{{- end }}
}

input {{ .Name | toPascalCase }}Input {
{{- $cols := filterNotNull .Columns | filterPrimaryKeys }}
{{- range $cols }}
  {{ .Name | toCamelCase }}: {{ sqlToTypeScript .Type true | replace " | null" "" }}!
{{- end }}
}

{{ end }}
```

### Example 5: SQL Migration (Database Mode)

**Template:** `migration.tmpl`
```sql
-- Migration for {{ .Database.Name }}
-- Generated: {{ .Metadata.timestamp }}

BEGIN;

{{ range .Database.Schemas }}
-- Schema: {{ .Name }}
CREATE SCHEMA IF NOT EXISTS {{ .Name }};

{{ range .Tables }}
CREATE TABLE {{ $.Database.Name }}.{{ .Schema }}.{{ .Name }} (
{{- range $i, $col := .Columns | values }}
{{- if $i }},{{ end }}
  {{ $col.Name }} {{ $col.Type }}{{ if $col.NotNull }} NOT NULL{{ end }}{{ if $col.Default }} DEFAULT {{ $col.Default }}{{ end }}
{{- end }}
);

{{- $pks := filterPrimaryKeys .Columns }}
{{- if $pks }}

ALTER TABLE {{ $.Database.Name }}.{{ .Schema }}.{{ .Name }}
  ADD PRIMARY KEY ({{ range $i, $pk := $pks }}{{ if $i }}, {{ end }}{{ $pk.Name }}{{ end }});
{{- end }}

{{ end }}
{{ end }}

COMMIT;
```

## Best Practices

1. **Use Hyphen for Whitespace Control:**
   ```
   {{- removes whitespace before
   -}} removes whitespace after
   ```

2. **Store Intermediate Results:**
   ```
   {{ $pks := filterPrimaryKeys .Table.Columns }}
   {{ if $pks }}...{{ end }}
   ```

3. **Check Before Accessing:**
   ```
   {{ if .Table.Description }}{{ .Table.Description }}{{ end }}
   ```

4. **Use Safe Access for Maps:**
   ```
   {{ getOr .Metadata "key" "default-value" }}
   ```

5. **Iterate Map Values:**
   ```
   {{ range .Table.Columns | values }}...{{ end }}
   ```

## Troubleshooting

**Error: "wrong type for value"**
- Check function parameter order (e.g., `sqlToGo .Type .NotNull` not `.NotNull .Type`)

**Error: "can't evaluate field"**
- Field doesn't exist on the object
- Use `{{ if .Field }}` to check before accessing

**Empty Output:**
- Check your mode matches your template expectations
- Verify data exists (use `{{ .Database | toJSON }}` to inspect)

**Whitespace Issues:**
- Use `{{-` and `-}}` to control whitespace
- Run output through a formatter if needed

## Additional Resources

- [Go Template Documentation](https://pkg.go.dev/text/template)
- [RelSpec Documentation](../README.md)
- [Model Structure Reference](../pkg/models/)
- [Example Templates](../examples/templates/)
