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:

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:

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:

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:

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:

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:

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:

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

7. variable - Database Variables

Database-specific configuration variables:

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

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

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

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

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, 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

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 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.