skills.rest
saintgo7
saintgo7COMMUNITY
Explore all skills by this author →
GitHub

postgres-migration-from-sqlite

Migrate FastAPI/SQLAlchemy/Alembic services from SQLite to PostgreSQL.

Updated Apr 4, 2026
Search Tags:#postgresql#sqlite#data migration#sqlalchemy#alembic#asyncpg#serial sequence
One-click install
npx skills add https://github.com/saintgo7/claude-skills --skill postgres-migration-from-sqlite
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: postgres-migration-from-sqlite
Source: https://github.com/saintgo7/claude-skills/tree/main/postgres-migration-from-sqlite
Command: npx skills add https://github.com/saintgo7/claude-skills --skill postgres-migration-from-sqlite

SYSTEM DOCUMENTATION & REQUIREMENTS

💡 This Skill requires sqlite3, psql, pg_dump, and includes scripts (resource) components.

What problem does it solve?

This skill resolves production database write-lock issues and scalability limits caused by running a FastAPI/SQLAlchemy/Alembic app on SQLite by guiding a safe migration to PostgreSQL.

Core Features & Use Cases

  • DSN migration for SQLAlchemy (async + Alembic): Switches from a SQLite file-backed connection to a PostgreSQL host/port/database/user DSN while aligning asyncpg/psycopg2 expectations.
  • Alembic schema alignment: Helps you stamp or re-initialize PostgreSQL schema so migrations continue to work correctly after the backend switch.
  • Data transfer + SERIAL sequence recovery: Moves data via table-by-table CSV extraction and PostgreSQL COPY, then repairs SERIAL/IDENTITY sequences to prevent duplicate key failures.
  • Verification and rollback planning: Provides row-count and sample-query checks and a rollback approach by reverting the application to SQLite and handling potential new data loss.

Quick Start

Instruct the AI to produce a step-by-step SQLite-to-PostgreSQL migration plan for a FastAPI/SQLAlchemy/Alembic service experiencing "database is locked" under high concurrency, including Alembic strategy, data transfer method, SERIAL sequence repair, and verification commands.

Frequently Asked Questions about postgres-migration-from-sqlite

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I migrate from SQLite to PostgreSQL for a FastAPI app?▼

Migrating from SQLite to PostgreSQL requires switching the SQLAlchemy DSN to a PostgreSQL host, aligning Alembic schema via stamp or re-init, transferring data table-by-table, and repairing SERIAL sequences to prevent duplicate key failures.

Why does my SQLite database get locked under high concurrency?▼

SQLite encounters database is locked errors under high concurrency due to file-level write locks. Migrating to PostgreSQL eliminates these write-lock failures and enables scaling for uvicorn multi-worker deployments using asyncpg connectivity.

How do I fix duplicate key errors after migrating data to PostgreSQL?▼

Fix duplicate key errors after data migration to PostgreSQL by repairing SERIAL or IDENTITY sequences. This sequence recovery ensures auto-incrementing primary keys continue from the correct offset following table-by-table data restoration.

Can I use Alembic to align schema after switching from SQLite to PostgreSQL?▼

Yes, Alembic can align the PostgreSQL schema by stamping the database or re-initializing migrations. This ensures SQLAlchemy migration scripts continue to function correctly after switching to the asyncpg backend.

What is the best way to verify data after a SQLite to PostgreSQL migration?▼

The best way to verify data after a SQLite to PostgreSQL migration is running row-count comparisons and sample-query checks. This post-migration validation confirms data integrity before finalizing the backend switch.

How do I plan a rollback when migrating from SQLite to PostgreSQL?▼

Plan a rollback when migrating from SQLite to PostgreSQL by reverting the application configuration to the SQLite connection. Be aware that any new data written to PostgreSQL during the migration period may face potential data loss during this rollback.

Related Skills

Explore More Software Engineering Skills→
pgplexpgplex
994

Validate with Database

Validate PostgreSQL schema drift by comparing pg_dump with pgschema outputs.

Official
Intermediate
WaterplanAIWaterplanAI
31

git-rewrite-history

Rewrites git history with mandatory dry-run planning and user confirmation.

Official
Advanced
NelkitNelkit
1

alembic-migration

Automate PostgreSQL schema changes with SQLAlchemy ORM and Alembic migrations.

Community
Intermediate
gpt-cmdrgpt-cmdr
73

executing-remote-plans

Execute HEC-RAS plans across remote machines via PsExec, Docker, or SSH.

Community
Advanced