Skip to content
Skills Library
Programming · Planning

data-migration

/data-migration

Plans and scripts data migrations between SQL databases: schema mapping, type conversion, batched and resumable loading, validation and cutover. Use when the user mentions "data migration," "database migration," "migrate from MySQL to PostgreSQL," "schema migration," "ETL pipeline," "data transfer," "database consolidation," "legacy migration," or "move data between databases." Covers schema analysis, mapping, transformation, batch processing, validation, and cutover planning. For query optimization during migration, see sql-optimizer.

Repository
skills ↗
Licence
Apache-2.0
Last repository change
25.10.2026

Assessment and security

100/ 100Class: good
Format25 / 25
Frontmatter with name and description, valid name, description up to 1,024 characters, name matching the folder.
Discoverability25 / 25
Description explains when to use the skill, has a reasonable length and uses third person.
Content35 / 35
At least 150 words and at most 500 lines, headings, step lists, examples or code.
Hygiene15 / 15
No placeholders such as TODO, no Windows paths, with a licence.
Riskno signals detected in the scan
Files in this package
2
Scripts
0
HTML comments
0
URLs in SKILL.md
0
Deprecation

No signals detected. Repository changed on 25.10.2026, without references to retired models or APIs.

Licence

Apache-2.0, otwarta: można używać i zmieniać, z zachowaniem licencji i informacji o zmianach.

What it does and when to use it

Plans and scripts data migrations between SQL databases: schema mapping, type conversion, batched and resumable loading, validation and cutover. Use when the user mentions "data migration," "database migration," "migrate from MySQL to PostgreSQL," "schema migration," "ETL pipeline," "data transfer," "database consolidation," "legacy migration," or "move data between databases." Covers schema analysis, mapping, transformation, batch processing, validation, and cutover planning. For query optimization during migration, see sql-optimizer.

Triggers from the description

data migration,database migration,migrate from MySQL to PostgreSQL,schema migration,ETL pipeline,data transfer,database consolidation,legacy migration,
Original author description

Plans and scripts data migrations between SQL databases: schema mapping, type conversion, batched and resumable loading, validation and cutover. Use when the user mentions "data migration," "database migration," "migrate from MySQL to PostgreSQL," "schema migration," "ETL pipeline," "data transfer," "database consolidation," "legacy migration," or "move data between databases." Covers schema analysis, mapping, transformation, batch processing, validation, and cutover planning. For query optimization during migration, see sql-optimizer.

How it works

  1. Overview
  2. Instructions
  3. Examples
  4. Guidelines
Original SKILL.md headings · 127 lines, 776 words
SKILL.md preview
---
name: data-migration
description: >-
  Plans and scripts data migrations between SQL databases: schema mapping, type
  conversion, batched and resumable loading, validation and cutover. Use when the
  user mentions "data migration,"
  "database migration," "migrate from MySQL to PostgreSQL," "schema migration,"
  "ETL pipeline," "data transfer," "database consolidation," "legacy migration,"
  or "move data between databases." Covers schema analysis, mapping, transformation,
  batch processing, validation, and cutover planning. For query optimization during
  migration, see sql-optimizer.
license: Apache-2.0
compatibility: "Any SQL database (PostgreSQL, MySQL, SQLite, MSSQL)"
metadata:
  author: terminal-skills
  version: "1.1.0"
  category: development
  tags: "migration, database, etl, data-pipeline, schema"
---

# Data Migration

## Overview

Builds automated data migration pipelines between databases. Handles schema analysis and mapping, type conversions, data transformations, dependency-ordered table loading, batch processing for large datasets, checkpoint/resume for reliability, post-migration validation, and cutover planning. Produces repeatable scripts that can be dry-run against staging before production.

## Instructions

### 1. Schema Analysis

Start every migration by analyzing source and target:

```
For each table in source:
  - Column names, types, nullability, defaults
  - Primary keys and auto-increment sequences
  - Foreign key relationships (build dependency graph)
  - Indexes and unique constraints
  - Row count estimate (for batch sizing)
  - Encoding/collation (especially for MySQL → PostgreSQL)
```

Generate a schema map document listing every column with its source type, target type, and any transformation needed.

### 2. Type Mapping

Common cross-database type conversions:

| MySQL | PostgreSQL | Notes |
|-------|-----------|-------|
| TINYINT(1) | BOOLEAN | Map 0/1 to false/true |
| ENUM('a','b') | VARCHAR + CHECK | Or create custom TYPE |
| DATETIME | TIMESTAMPTZ | DATETIME stores no time zone: decide which zone the values were written in, then convert. MySQL converts TIMESTAMP itself, not DATETIME. DATETIME ranges 1000-9999; TIMESTAMP only 1970-2038 |
| INT AUTO_INCREMENT | SERIAL | Reset sequence after migration |
| DOUBLE | DOUBLE PRECISION | Direct mapping |
| BLOB | BYTEA | Binary data |
| TEXT (latin1) | TEXT (UTF-8) | Re-encode characters |
| JSON | JSONB | Use binary JSON in PG |

### 3. Dependency Resolution

Build a directed acyclic graph from foreign keys:

```
1. Parse all FK constraints → build adjacency list
2. Topological sort → migration order
3. Circular dependencies: temporarily drop FK, migrate, re-add FK
4. Self-referencing tables: migrate in two passes (data, then self-FK updates)
```

### 4. Batch Processing

For tables with more than 10,000 rows:

```
function migrateLargeTable(table, batchSize = 5000):
  lastId = loadCheckpoint(table) or 0
  while true:
    rows = SELECT * FROM source.table WHERE id > lastId ORDER BY id LIMIT batchSize
    if rows.empty: break
    transformed = rows.map(row => transform(row, table.mapping))
    INSERT INTO target.table VALUES transformed
    lastId = rows.last.id
    saveCheckpoint(table, lastId, totalMigrated)
```

Performance targets:
- 5,000 rows/batch for most tables
- 1,000 rows/batch for tables with BLOB/TEXT columns
- Disable target indexes during bulk load, rebuild after

### 5. Validation

Post-migration validation checklist:

```
1. Row counts: source vs target for every table
2. Random sampling: 100 random rows per table, field-by-field comparison
3. Aggregate checks: SUM, COUNT, MIN, MAX on numeric columns
4. Referential integrity: all FKs resolve (no orphans)
5. Encoding: sample text fields for valid UTF-8
6. Sequences: verify auto-increment/serial values set above max ID
7. Nullability: no unexpected NULLs in NOT NULL target columns
```

### 6. Cutover Planning

Three strategies by downtime tolerance:

**Full downtime** (simplest): Stop app → migrate → validate → start app. For small datasets (< 1M rows, < 1 hour).

**Minimal downtime** (recommended): Pre-migrate bulk data → set up change capture → maintenance mode → apply delta → switch → validate. Downtime: 2-10 minutes.

**Zero downtime** (complex): Dual-write to both databases → background migration → gradual read traffic shift → drop old writes. Requires application changes.

## Examples

### Example 1: MySQL to PostgreSQL

**Prompt**: "Migrate our MySQL 5.7 database to PostgreSQL 16. 30 tables, biggest is 5M rows."

**Output**: Schema mapping JSON, type conversion DDL, migration script with dependency ordering, batch processing for large tables, checkpoint file, validation suite, and cutover runbook.

### Example 2: Database Consolidation

**Prompt**: "Merge two SQLite databases into one PostgreSQL. Some tables overlap with different schemas."

**Output**: Schema diff report, merge strategy document (which columns win conflicts), deduplication logic using configurable match keys, migration script, and conflict resolution log.

## Guidelines

- **Always dry-run on staging first** — never run migration directly against production
- **Keep source untouched** — migration should be read-only on source until cutover
- **Checkpoint everything** — large migrations will fail; resumability is required
- **Validate before cutover** — automated validation catches what manual spot-checks miss
- **Plan rollback** — if target validation fails, have a documented path back to source
- **Log extensively** — rows processed, rows skipped, transformation errors, timing
- **Reset sequences** — after migration, set serial/auto-increment above max migrated ID. In PostgreSQL, for each table with a `serial` column (tested on PostgreSQL 16):

  ```sql
  SELECT setval(pg_get_serial_sequence('orders', 'id'), coalesce(max(id), 0) + 1, false) FROM orders;
  ```

  The `false` makes the next `nextval` return exactly that value, so an empty table also resets to 1. Passing `true` together with `max(id) + 1` would skip one number.
- **Test with production volume** — a script that works on 1000 rows may OOM on 5M
Files in this package (2)
  • SKILL.md 6 KB
  • _scores.json 3 KB

Versions

RepositoryScoreLicence
This page · TerminalSkills/skills100Apache-2.0
muon-m2/magento2-tools100MIT
uw-ssec/rse-plugins95BSD-3-Clause
cbrock84/headcount90MIT
arjunprabhulal/devops-skills90MIT

Database snapshot: 13.11.2026 · package version d4bcd5eccfe9 (SHA-256 digest of the entire package)

Downloads in the last 30 days: 0 · ranking

Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.

Similar skills

  • 95database
    Guide database design, migration, and query optimization. Use when: schema design, migration planning, query optimization, index strategy. Not for: application code migration or framework upgrades — use /migrate; the API contract over the data — use /api-design. Keywords:…
  • 100database-design
    Database schema design, optimization, and migration patterns for PostgreSQL, MySQL, and NoSQL databases. Use for designing schemas, writing migrations, or optimizing queries.
  • 100database-schema-designer
    Use this skill when designing or reviewing database schemas for SQL and NoSQL databases. Covers normalization, indexing strategy, migration management with rollback safety, query optimization, and multi-tenant patterns. Supports PostgreSQL, MySQL, SQLite, MongoDB, and Vitess.
  • 100database-testing
    Validate database integrity, test migrations forward and backward, verify schema constraints, manage seed data, detect migration drift, and identify query performance issues. Covers PostgreSQL, MySQL, MongoDB with Prisma, TypeORM, Drizzle, and SQLAlchemy, plus Testcontainers…
  • 100database-migration-patterns
    Manage database schema changes safely with migration tools, zero-downtime strategies, and rollback procedures. Covers Alembic, SQL migrations, data migrations, and testing strategies. Triggers on database migration, schema changes, or Alembic configuration requests.
  • 100db-migrator
    Database schema migration, versioning, and rollback management for SQL and NoSQL databases. Use when: (1) creating database migrations, (2) modifying table schemas, (3) adding/removing columns, (4) creating indexes, (5) data migrations, (6) rollback operations, (7)…

Feedback and reports

Your message goes to Bartosz Sokoliński. An email address is needed only for a reply. See the privacy policy.