Schema Migration

Examples of using Sling to migrate database schema attributes along with data

Schema migration (v1.5.7) is a powerful feature that allows Sling to replicate not just data, but also important schema attributes like primary keys, foreign keys, indexes, default values, nullable constraints, auto-increment columns, and column/table descriptions from source to target databases.

CLI Pro / Platform Feature: Schema migration requires a CLI Pro Max token or Advanced Platform Plan.

Schema migration is controlled via the SLING_SCHEMA_MIGRATION environment variable and is disabled by default.

Overview

When migrating databases, preserving schema attributes is often critical for maintaining data integrity and application compatibility. Sling's schema migration feature extracts extended metadata from source tables and applies the corresponding constraints and properties to target tables.

Supported Attributes

Attribute

Description

Example

primary_key

Primary key constraints

id INT PRIMARY KEY

foreign_key

Foreign key relationships

REFERENCES customers(id)

indexes

Non-primary key indexes

CREATE INDEX idx_name ON table(col)

auto_increment

Identity/auto-increment columns

IDENTITY(1,1) or SERIAL

nullable

NOT NULL constraints

email VARCHAR(255) NOT NULL

default_value

Column default values

DEFAULT CURRENT_TIMESTAMP

description

Column and table comments

COMMENT ON COLUMN...

Supported Databases

Schema migration works between any combination of these databases. The level of support varies by database—see the compatibility matrix below for details.

Full Support

PostgreSQL
MySQL / MariaDB
SQL Server / Azure SQL
Oracle
Redshift
Databricks

Partial Support

Snowflake (no indexes)
BigQuery (no auto-increment, no default values, no indexes)
SQLite (no descriptions)
DuckDB (no auto-increment, no default values)
ClickHouse (primary key, nullable, description only)

Database Compatibility Matrix

The following matrix shows which schema attributes are supported for each database:

Database

Auto-Increment

Primary Key

Foreign Key

Default Value

Nullable

Indexes

Description

PostgreSQL

✓

MySQL / MariaDB

✓

✓¹

✓

SQL Server / Azure SQL

✓

Oracle

✓

✓²

✓

Redshift

✓

✓³

✓

—⁴

✓

Databricks

✓⁵

✓⁶

✓

—⁷

✓

Snowflake

✓

✓⁸

✓

—

✓

BigQuery

—

✓⁹

—

✓

—

✓

SQLite

✓¹⁰

✓

✓¹¹

✓

—

DuckDB

—

✓

—

✓

ClickHouse

—

✓¹²

—

✓

—

✓

Legend: ✓ = Supported | — = Not supported

Notes:

MySQL/MariaDB: Foreign keys require InnoDB engine; MyISAM ignores FK constraints
Oracle: Supports ON DELETE actions but not ON UPDATE actions for foreign keys
Redshift: Primary keys and foreign keys are informational only (not enforced)
Redshift: Does not support traditional indexes; use SORTKEY/DISTKEY instead
Databricks: Auto-increment only supports BIGINT column type
Databricks: Primary keys and foreign keys are informational only (not enforced); only NOT NULL is enforced
Databricks: Does not support traditional indexes; use Z-ordering instead
Snowflake: Primary keys and foreign keys are not enforced in standard tables (metadata only); only NOT NULL is enforced
BigQuery: Primary keys and foreign keys use NOT ENFORCED syntax (metadata only for query optimization)
SQLite: Auto-increment only works with INTEGER PRIMARY KEY columns
SQLite: Foreign keys require PRAGMA foreign_keys = ON per connection (disabled by default)
ClickHouse: Primary key defines sort order and sparse index; does not enforce uniqueness

Constraint Enforcement Matrix

Important: Not all databases enforce constraints at runtime. Some databases accept constraint DDL for metadata/documentation purposes but do not validate data against those constraints. This matrix shows which constraints are actually enforced.

Database

PK Enforced

FK Enforced

NOT NULL Enforced

Default Enforced

PostgreSQL

✓ Yes

MySQL / MariaDB

✓ Yes

✓ Yes (InnoDB)

✓ Yes

SQL Server

✓ Yes

Oracle

✓ Yes

Redshift

✗ No

✓ Yes

Databricks

✗ No

✓ Yes

Snowflake

✗ No

✓ Yes

BigQuery

✗ No

✓ Yes

Partial

SQLite

✓ Yes

✓ Yes*

✓ Yes

DuckDB

✓ Yes

ClickHouse

✗ No

N/A

✗ No

✓ Yes

Legend: ✓ Yes = Enforced at runtime | ✗ No = Metadata only (not enforced) | * = Requires configuration

SQLite Note: Foreign key enforcement requires PRAGMA foreign_keys = ON to be set on each database connection. This is disabled by default for backwards compatibility.

Analytical Databases: Redshift, Snowflake, BigQuery, and Databricks are optimized for analytical workloads. They accept constraint definitions for documentation and query optimization hints, but do not enforce them at runtime. Data integrity must be validated upstream in ETL pipelines.

What You Can Use for Each Database

All features supported and enforced. PostgreSQL is fully compatible with schema migration.

env:
  SLING_SCHEMA_MIGRATION: all

Attribute

Supported

Enforced

Primary Key

✓

✓ Yes

Foreign Key

✓

✓ Yes

Auto-increment

✓

✓ Yes

Default Value

✓

✓ Yes

NOT NULL

✓

✓ Yes

Indexes

✓

Functional

Description

✓

Metadata

Auto-increment uses GENERATED BY DEFAULT AS IDENTITY
Full foreign key support with ON DELETE and ON UPDATE actions
Column/table comments via COMMENT ON syntax

All features supported and enforced (with InnoDB engine).

env:
  SLING_SCHEMA_MIGRATION: all

Attribute

Supported

Enforced

Primary Key

✓

✓ Yes

Foreign Key

✓

✓ Yes (InnoDB only)

Auto-increment

✓

✓ Yes

Default Value

✓

✓ Yes

NOT NULL

✓

✓ Yes

Indexes

✓

Functional

Description

✓

Metadata

Auto-increment uses AUTO_INCREMENT keyword
Foreign keys require InnoDB engine (MyISAM ignores FK constraints)
Comments via COMMENT clause

All features supported and enforced. SQL Server and Azure SQL are fully compatible.

env:
  SLING_SCHEMA_MIGRATION: all

Attribute

Supported

Enforced

Primary Key

✓

✓ Yes

Foreign Key

✓

✓ Yes

Auto-increment

✓

✓ Yes

Default Value

✓

✓ Yes

NOT NULL

✓

✓ Yes

Indexes

✓

Functional

Description

✓

Metadata

Auto-increment uses IDENTITY(seed, increment) syntax
Foreign keys can be disabled with NOCHECK if needed
Descriptions via Extended Properties (MS_Description)

All features supported and enforced with one limitation.

env:
  SLING_SCHEMA_MIGRATION: all

Attribute

Supported

Enforced

Primary Key

✓

✓ Yes

Foreign Key

✓

✓ Yes (ON DELETE only)

Auto-increment

✓

✓ Yes

Default Value

✓

✓ Yes

NOT NULL

✓

✓ Yes

Indexes

✓

Functional

Description

✓

Metadata

Auto-increment uses GENERATED BY DEFAULT AS IDENTITY
Foreign keys support ON DELETE actions but not ON UPDATE (Oracle limitation)
Comments via COMMENT ON COLUMN / COMMENT ON TABLE

DDL supported but constraints not enforced. Snowflake is an analytical database.

env:
  SLING_SCHEMA_MIGRATION: primary_key,foreign_key,auto_increment,nullable,default_value,description

Attribute

Supported

Enforced

Primary Key

✓

✗ No (metadata only)

Foreign Key

✓

✗ No (metadata only)

Auto-increment

✓

✓ Yes

Default Value

✓

✓ Yes

NOT NULL

✓

✓ Yes

Indexes

—

N/A

Description

✓

Metadata

Auto-increment uses AUTOINCREMENT START ... INCREMENT ...
Primary keys and foreign keys are not enforced — they exist for documentation and query optimization only
Only NOT NULL constraints are enforced in standard tables
Indexes are not applicable (Snowflake uses clustering keys instead)

Limited support. BigQuery constraints are metadata only.

env:
  SLING_SCHEMA_MIGRATION: primary_key,foreign_key,nullable,description

Attribute

Supported

Enforced

Primary Key

✓

✗ No (NOT ENFORCED)

Foreign Key

✓

✗ No (NOT ENFORCED)

Auto-increment

—

N/A

Default Value

—

N/A

NOT NULL

✓

✓ Yes

Indexes

—

N/A

Description

✓

Metadata

Primary keys and foreign keys use NOT ENFORCED syntax (query optimization hints only)
Column descriptions via ALTER TABLE ... SET OPTIONS
No identity columns—use application-generated IDs or GENERATE_UUID()

DDL supported but PK/FK not enforced. Redshift is an analytical data warehouse.

env:
  SLING_SCHEMA_MIGRATION: primary_key,foreign_key,auto_increment,nullable,default_value,description

Attribute

Supported

Enforced

Primary Key

✓

✗ No (informational)

Foreign Key

✓

✗ No (informational)

Auto-increment

✓

✓ Yes

Default Value

✓

✓ Yes

NOT NULL

✓

✓ Yes

Indexes

—

N/A

Description

✓

Metadata

Auto-increment uses IDENTITY(seed, increment)
Primary keys and foreign keys are informational only — used as query optimizer hints, not enforced
Does not support traditional indexes; use SORTKEY and DISTKEY for performance
Comments via COMMENT ON syntax

Most features supported. Foreign keys require explicit enabling.

env:
  SLING_SCHEMA_MIGRATION: primary_key,foreign_key,auto_increment,nullable,default_value,indexes

Attribute

Supported

Enforced

Primary Key

✓

✓ Yes

Foreign Key

✓

✓ Yes (when enabled)

Auto-increment

✓

✓ Yes

Default Value

✓

✓ Yes

NOT NULL

✓

✓ Yes

Indexes

✓

Functional

Description

—

N/A

Auto-increment only works with INTEGER PRIMARY KEY columns
Foreign keys require PRAGMA foreign_keys = ON per connection (disabled by default)
No native column/table comment support

Partial support but constraints are enforced. DuckDB enforces integrity unlike most analytical DBs.

env:
  SLING_SCHEMA_MIGRATION: primary_key,foreign_key,nullable,indexes,description

Attribute

Supported

Enforced

Primary Key

✓

✓ Yes

Foreign Key

✓

✓ Yes

Auto-increment

—

N/A

Default Value

—

N/A

NOT NULL

✓

✓ Yes

Indexes

✓

Functional

Description

✓

Metadata

No native identity columns—use sequences with DEFAULT nextval('seq')
Foreign keys are enforced (unlike Snowflake, BigQuery, Redshift)
Indexes are fully supported via duckdb_indexes() metadata
Column descriptions via COMMENT ON syntax

DDL supported but PK/FK not enforced. Databricks uses Delta Lake.

env:
  SLING_SCHEMA_MIGRATION: primary_key,foreign_key,auto_increment,nullable,default_value,description

Attribute

Supported

Enforced

Primary Key

✓

✗ No (informational)

Foreign Key

✓

✗ No (informational)

Auto-increment

✓

✓ Yes

Default Value

✓

✓ Yes

NOT NULL

✓

✓ Yes

Indexes

—

N/A

Description

✓

Metadata

Auto-increment uses GENERATED BY DEFAULT AS IDENTITY (BIGINT only)
Note: When Databricks is used as a source, identity column detection is limited (Databricks doesn't expose identity metadata via information_schema)
Primary keys and foreign keys are informational only — used for query optimization
Only NOT NULL and CHECK constraints are enforced
Does not support traditional indexes; use Z-ordering for performance

Limited support. ClickHouse is optimized for OLAP, not constraint enforcement.

env:
  SLING_SCHEMA_MIGRATION: primary_key,nullable,default_value,description

Attribute

Supported

Enforced

Primary Key

✓

✗ No (sort order only)

Foreign Key

—

N/A

Auto-increment

—

N/A

Default Value

✓

✓ Yes

NOT NULL

✓

✗ No

Indexes

—

N/A

Description

✓

Metadata

Primary key defines sort order and sparse index — does not enforce uniqueness
No foreign key support
No auto-increment columns (use generateSerialID() in v25.1+)
NOT NULL is not enforced; use Nullable(T) wrapper to allow NULLs
Uses data-skipping indexes (MinMax, Bloom) instead of traditional indexes

Enabling Schema Migration

Set the SLING_SCHEMA_MIGRATION environment variable to enable specific attributes:

env:
  # Enable all schema attributes
  SLING_SCHEMA_MIGRATION: all

  # Or enable specific attributes (comma-separated)
  SLING_SCHEMA_MIGRATION: primary_key,foreign_key,indexes

Available Options

Value

Description

all

Enable all schema migration attributes

primary_key

Migrate primary key constraints

foreign_key

Migrate foreign key relationships

indexes

Migrate indexes (non-PK)

auto_increment

Migrate identity/auto-increment columns

nullable

Migrate NOT NULL constraints

default_value

Migrate column default values

description

Migrate column and table comments

Basic Example

Migrate all schema attributes from SQL Server to PostgreSQL:

source: mssql
target: postgres

defaults:
  mode: full-refresh
  object: public.{stream_table}

streams:
  dbo.customers:
  dbo.orders:
  dbo.products:

env:
  SLING_SCHEMA_MIGRATION: all

Foreign Key Migration

When migrating foreign keys, Sling automatically handles table ordering to ensure parent tables are created before child tables that reference them.

All referenced tables must be included in the replication. If table orders has a foreign key to customers, both tables must be in the streams list.

Automatic Table Ordering

Sling performs topological sorting based on foreign key dependencies. Even if you list tables in the wrong order, they will be processed correctly:

source: mssql
target: postgres

defaults:
  mode: full-refresh

# Tables listed in wrong order - Sling will reorder them automatically
# order_items -> orders -> customers (FK dependencies)
streams:
  dbo.order_items:
    object: public.order_items

  dbo.orders:
    object: public.orders

  dbo.customers:  # Will be processed first due to FK dependencies
    object: public.customers

env:
  SLING_SCHEMA_MIGRATION: foreign_key,primary_key
  SLING_THREADS: 3  # works with threads!

Sling will automatically reorder streams to: customers → orders → order_items

Handling Circular Dependencies

If circular foreign key dependencies are detected, Sling will report an error. Options to resolve:

Remove one table from the replication
Disable foreign key migration
Handle FK creation manually via hooks

Auto-Increment / Identity Columns

Sling migrates auto-increment columns with their seed and increment values:

source: mssql     # IDENTITY(1000, 10)
target: postgres  # GENERATED BY DEFAULT AS IDENTITY (START WITH 1000 INCREMENT BY 10)

streams:
  dbo.invoices:
    object: public.invoices

env:
  SLING_SCHEMA_MIGRATION: auto_increment,primary_key

Database-Specific Behavior

Source

Target

Result

SQL Server IDENTITY(1,1)

PostgreSQL

GENERATED BY DEFAULT AS IDENTITY

PostgreSQL SERIAL

MySQL

AUTO_INCREMENT

Oracle GENERATED AS IDENTITY

SQL Server

IDENTITY(seed, incr)

Default Values

Sling translates default value expressions between databases:

source: mssql
target: postgres

streams:
  dbo.audit_logs:

env:
  SLING_SCHEMA_MIGRATION: default_value

Default Value Translation

Expression

SQL Server

PostgreSQL

MySQL

Oracle

Current timestamp

GETDATE()

CURRENT_TIMESTAMP

SYSDATE

UTC timestamp

GETUTCDATE()

CURRENT_TIMESTAMP AT TIME ZONE 'UTC'

UTC_TIMESTAMP()

SYS_EXTRACT_UTC(SYSTIMESTAMP)

UUID

NEWID()

gen_random_uuid()

UUID()

SYS_GUID()

Boolean true

1

true

1

Boolean false

0

false

0

Column & Table Descriptions

Migrate comments and descriptions to preserve documentation:

source: mssql
target: postgres

streams:
  dbo.products:

env:
  SLING_SCHEMA_MIGRATION: description

This will migrate:

Column comments (e.g., COMMENT ON COLUMN products.price IS 'Product retail price')
Table comments (e.g., COMMENT ON TABLE products IS 'Product catalog')

Indexes

Migrate non-primary key indexes to preserve query performance:

source: mssql
target: postgres

streams:
  dbo.customers:

env:
  SLING_SCHEMA_MIGRATION: indexes

This migrates regular indexes, unique indexes, and composite indexes.

Index names are automatically generated in the target database to avoid conflicts. The format is idx_{table}_{columns}.

Complete E-Commerce Migration Example

A comprehensive example migrating an e-commerce schema with all relationships:

source: mssql
target: postgres

defaults:
  mode: full-refresh
  object: public.{stream_table}

streams:
  # Parent tables (no FK dependencies)
  dbo.categories:
  dbo.customers:

  # Child tables (have FK to parent tables)
  dbo.products:     # FK to categories
  dbo.orders:       # FK to customers

  # Grandchild tables (have FK to child tables)
  dbo.order_items:  # FK to orders and products

env:
  SLING_SCHEMA_MIGRATION: all

Selective Attribute Migration

Enable only specific attributes based on your needs:

Data Integrity Focus

env:
  # Focus on constraints that ensure data integrity
  SLING_SCHEMA_MIGRATION: primary_key,foreign_key,nullable

Performance Focus

env:
  # Focus on attributes that affect query performance
  SLING_SCHEMA_MIGRATION: primary_key,indexes

Documentation Preservation

env:
  # Preserve schema documentation only
  SLING_SCHEMA_MIGRATION: description

Using with Pipelines

Schema migration can also be used within pipelines:

steps:
  # Setup source schema
  - connection: mssql
    query: |
      CREATE TABLE dbo.users (
        id INT IDENTITY(1,1) PRIMARY KEY,
        email NVARCHAR(255) NOT NULL,
        created_at DATETIME DEFAULT GETDATE()
      );

  # Run replication with schema migration
  - replication:
      source: mssql
      target: postgres

      streams:
        dbo.users:
          object: public.users

      env:
        SLING_SCHEMA_MIGRATION: all

  # Verify constraints were created
  - connection: postgres
    query: |
      SELECT constraint_type, constraint_name
      FROM information_schema.table_constraints
      WHERE table_name = 'users'

Incremental Mode Considerations

Schema migration attributes are applied during table creation. When using incremental mode:

First run: Table is created with all schema attributes
Subsequent runs: Only data is upserted; schema is not modified

source: mssql
target: postgres

defaults:
  mode: incremental
  primary_key: id
  update_key: updated_at

streams:
  dbo.orders:
    object: public.orders

env:
  SLING_SCHEMA_MIGRATION: all

Troubleshooting

Missing Foreign Key Dependencies

Error: missing foreign key dependencies: 'orders' depends on 'customers' (not in stream list)

Solution: Add the missing referenced table to your streams:

streams:
  dbo.customers:  # Add the referenced table
  dbo.orders:

Circular Dependency Detected

Error: circular foreign key dependency detected involving: table_a, table_b

Solution: Either disable FK migration or handle FKs manually with hooks:

hooks:
  end:
    - connection: postgres
      query: |
        ALTER TABLE table_a ADD CONSTRAINT fk_a_b
        FOREIGN KEY (b_id) REFERENCES table_b(id);

Unsupported Default Expression

If a default expression cannot be translated, Sling will pass it through as-is with a debug warning. You may need to manually adjust the default in the target database.

Identity Column Conflicts

When using auto_increment migration, Sling uses GENERATED BY DEFAULT AS IDENTITY (PostgreSQL/Oracle) to allow explicit value inserts during data migration while still supporting auto-generation for new inserts.

Best Practices

Test First: Run schema migration on a test environment before production
Start Selective: Begin with primary_key only, then add more attributes
Include All Dependencies: Ensure all FK-referenced tables are in the replication
Use Full-Refresh: Schema attributes are best applied with full-refresh mode initially
Review Generated Schema: After migration, review the target schema for any needed adjustments
Consider Indexes Separately: Large tables may benefit from creating indexes after data load via hooks

Overview

Supported Attributes

Supported Databases

Full Support

Partial Support

Database Compatibility Matrix

Constraint Enforcement Matrix

What You Can Use for Each Database

Enabling Schema Migration

Available Options

Basic Example

Foreign Key Migration

Automatic Table Ordering

Handling Circular Dependencies

Auto-Increment / Identity Columns

Database-Specific Behavior

Default Values

Default Value Translation

Column & Table Descriptions

Indexes

Complete E-Commerce Migration Example

Selective Attribute Migration

Data Integrity Focus

Performance Focus

Documentation Preservation

Using with Pipelines

Incremental Mode Considerations

Troubleshooting

Missing Foreign Key Dependencies

Circular Dependency Detected

Unsupported Default Expression

Identity Column Conflicts

Best Practices

See Also