# Templates

## Overview

Sling uses **database templates** to generate SQL statements for operations like creating tables, inserting data, querying metadata, and type mappings. These templates are built-in for each database connector (PostgreSQL, Snowflake, MySQL, SQL Server, etc.).

Starting in *v1.5.3*, you can customize these templates by creating **user override files** in `~/.sling/templates/` directory (or env var `$SLING_HOME_DIR/templates`). This allows you to:

* Customize table creation with database-specific options (e.g., compression, partitioning)
* Modify metadata queries for special schema configurations
* Override type mappings for your data types
* Add database-specific optimizations

## File Location and Naming

User template override files must be placed in:

```
~/.sling/templates/{database_type}.yaml
```

Where `{database_type}` matches your database connection type exactly.

### Examples

* PostgreSQL: `~/.sling/templates/postgres.yaml`
* Snowflake: `~/.sling/templates/snowflake.yaml`
* MySQL: `~/.sling/templates/mysql.yaml`
* SQL Server: `~/.sling/templates/sqlserver.yaml`
* BigQuery: `~/.sling/templates/bigquery.yaml`
* Redshift: `~/.sling/templates/redshift.yaml`

To find your database type, run:

```bash
sling conns list
```

## Template Structure

Each template file contains up to 7 sections. You only need to include the sections you want to override:

### 1. `core` - SQL DDL/DML Templates

Defines SQL statements for basic operations:

```yaml
core:
  drop_table: drop table if exists {table}
  create_table: create table if not exists {table} ({col_types})
  insert: insert into {table} ({fields}) values ({values})
  replace: insert into {table} ({fields}) values ({values}) on conflict do update
  truncate: truncate table {table}
  update_temp: update {table} set {set_fields} where {pk_fields_equal}
```

### 2. `metadata` - Schema Information Queries

Queries to retrieve database metadata:

```yaml
metadata:
  tables: select table_name from information_schema.tables where table_schema = '{schema}'
  columns: select column_name, data_type from information_schema.columns where table_name = '{table}'
  primary_keys: select column_name from information_schema.key_column_usage where table_name = '{table}' and constraint_name like 'PK%'
```

### 3. `analysis` - Analytical Queries

Used for data profiling and validation:

```yaml
analysis:
  count: select count(*) as cnt from {table}
  sample: select {fields} from {table} limit {n}
```

### 4. `function` - Database-Specific Functions

Custom functions available in templates:

```yaml
function:
  cast_to_date: "cast({val} as date)"
  cast_to_string: "cast({val} as varchar)"
```

### 5. `general_type_map` - Generic to Database Type Mapping

Maps generic data types to database-specific types:

```yaml
general_type_map:
  string: varchar(500)
  integer: bigint
  decimal: decimal(38,10)
  boolean: boolean
  datetime: timestamp
```

### 6. `native_type_map` - Database Type to Generic Mapping

Maps database-specific types back to generic types:

```yaml
native_type_map:
  varchar: string
  text: string
  int: integer
  bigint: integer
  numeric: decimal
  timestamp: datetime
```

### 7. `variable` - Database Variables

Database-specific configuration variables:

```yaml
variable:
  quote_char: '"'
  schema_separator: '.'
```

## Examples

### Example 1: Custom PostgreSQL Table Creation with Compression

Add compression and optimization to PostgreSQL table creation:

**File: `~/.sling/templates/postgres.yaml`**

```yaml
core:
  create_table: create table if not exists {table} ({col_types}) with (compression = lz4)
```

This overrides only the `create_table` template. All other PostgreSQL templates remain unchanged.

### Example 2: Custom Snowflake Type Mappings

Override type mappings for Snowflake to use specific precision:

**File: `~/.sling/templates/snowflake.yaml`**

```yaml
general_type_map:
  decimal: number(38,10)
  integer: number(38,0)
  string: varchar(4000)
```

### Example 3: Modified Metadata Query for Custom Schemas

Customize the columns query for a specific schema configuration:

**File: `~/.sling/templates/snowflake.yaml`**

```yaml
metadata:
  columns: |
    select
      column_name,
      data_type,
      ordinal_position
    from information_schema.columns
    where table_catalog = '{database}'
      and table_schema = '{schema}'
      and table_name = '{table}'
    order by ordinal_position
```

### Example 4: MySQL with Specific Collation

**File: `~/.sling/templates/mysql.yaml`**

```yaml
core:
  create_table: create table if not exists {table} ({col_types}) collate utf8mb4_unicode_ci
```

## How It Works

Templates are loaded in a layered hierarchy, with each layer overwriting nested keys from the previous:

1. **Base Template**: Sling first loads [`base.yaml`](https://github.com/slingdata-io/sling-cli/blob/main/core/dbio/templates/base.yaml), which contains common SQL templates shared across all databases
2. **Database-Specific Template**: The database-specific template (e.g., `postgres.yaml`, `snowflake.yaml`) is loaded next, overwriting any nested keys from the base template with database-specific implementations
3. **User Override**: If a user template exists at `~/.sling/templates/{database_type}.yaml`, it is merged last, overwriting any nested keys from the previous layers
4. **Partial Overrides**: Only the keys you define override the built-in templates - all other values remain unchanged
5. **Caching**: The merged template is cached for performance
6. **Debug Logging**: With `--debug` flag, you can see when overrides are loaded

{% hint style="info" %}
**Built-in Templates Reference**

View the default built-in templates at <https://github.com/slingdata-io/sling-cli/tree/main/core/dbio/templates>. The [`base.yaml`](https://github.com/slingdata-io/sling-cli/blob/main/core/dbio/templates/base.yaml) file contains the foundational templates, while database-specific files (e.g., `postgres.yaml`, `snowflake.yaml`, `mysql.yaml`) show how each database overrides the base. These serve as examples when creating your own custom overrides.
{% endhint %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.slingdata.io/concepts/replication/templates.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.