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 token or 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:

1. MySQL/MariaDB: Foreign keys require InnoDB engine; MyISAM ignores FK constraints

2. Oracle: Supports ON DELETE actions but not ON UPDATE actions for foreign keys

3. Redshift: Primary keys and foreign keys are informational only (not enforced)

4. Redshift: Does not support traditional indexes; use SORTKEY/DISTKEY instead

5. Databricks: Auto-increment only supports BIGINT column type

6. Databricks: Primary keys and foreign keys are informational only (not enforced); only NOT NULL is enforced

7. Databricks: Does not support traditional indexes; use Z-ordering instead

8. Snowflake: Primary keys and foreign keys are not enforced in standard tables (metadata only); only NOT NULL is enforced

9. BigQuery: Primary keys and foreign keys use NOT ENFORCED syntax (metadata only for query optimization)

10. SQLite: Auto-increment only works with INTEGER PRIMARY KEY columns

11. SQLite: Foreign keys require PRAGMA foreign_keys = ON per connection (disabled by default)

12. 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	✓ Yes	✓ Yes	✓ Yes
MySQL / MariaDB	✓ Yes	✓ Yes (InnoDB)	✓ Yes	✓ Yes
SQL Server	✓ Yes	✓ Yes	✓ Yes	✓ Yes
Oracle	✓ Yes	✓ Yes	✓ Yes	✓ Yes
Redshift	✗ No	✗ No	✓ Yes	✓ Yes
Databricks	✗ No	✗ No	✓ Yes	✓ Yes
Snowflake	✗ No	✗ No	✓ Yes	✓ Yes
BigQuery	✗ No	✗ No	✓ Yes	Partial
SQLite	✓ Yes	✓ Yes*	✓ Yes	✓ Yes
DuckDB	✓ Yes	✓ Yes	✓ Yes	✓ 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

PostgreSQL

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

MySQL / MariaDB

SQL Server

Oracle

Snowflake

BigQuery

Redshift

SQLite

DuckDB

Databricks

ClickHouse

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:

1. Remove one table from the replication

2. Disable foreign key migration

3. 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	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	1
Boolean false	0	false	0	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

1. Test First: Run schema migration on a test environment before production

2. Start Selective: Begin with primary_key only, then add more attributes

3. Include All Dependencies: Ensure all FK-referenced tables are in the replication

4. Use Full-Refresh: Schema attributes are best applied with full-refresh mode initially

5. Review Generated Schema: After migration, review the target schema for any needed adjustments

6. Consider Indexes Separately: Large tables may benefit from creating indexes after data load via hooks

See Also

• Replication Modes

• Hooks

• Target Options