- Home
- Skills
- Data & Databases
- Safe SQLite Migrations
Works with the AI Agents you already use
Safe SQLite Migrations
Make irreversible SQLite schema changes safely: a read-only precheck that aborts on collisions, a mandatory backup, table rebuild, and rollback on FK errors.
Free
Safe SQLite Migrations
Example session with this skill installed
I need to drop the 'middle_name' column from the 'users' table in my production SQLite database. Please generate the migration plan and precheck commands.
- Read your context and instructions
- Compiled the safe sqlite migrations
I have prepared a safe migration plan to drop the middle_name column. Since SQLite does not support DROP COLUMN, we will use the table rebuild pattern. I have also included the precheck command to ensure no data loss occurs.
SQLite Migration Plan: Drop 'middle_name'
| Step | Action | Command/Script |
|---|---|---|
| 1 | Precheck | python3 scripts/precheck_safe_merge.py --db production.db --table users |
| 2 | Backup | cp production.db production.db.bak && cp production.db-wal production.db-wal.bak |
| 3 | Create Temp | CREATE TABLE users_dg_tmp (id INTEGER PRIMARY KEY, first_name TEXT, last_name TEXT); |
| 4 | Migrate Data | INSERT INTO users_dg_tmp(id, first_name, last_name) SELECT id, first_name, last_name FROM users; |
| 5 | Swap Tables | DROP TABLE users; ALTER TABLE users_dg_tmp RENAME TO users; |
| 6 | Verify | PRAGMA foreign_key_check; |
| 7 | Version Up | Update schema_version pragma and fresh-install CREATE TABLE scripts. |
-- migration.sql
BEGIN TRANSACTION;
-- 1. Create new table structure
CREATE TABLE users_new (
id INTEGER PRIMARY KEY AUTOINCREMENT,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL
);
-- 2. Transfer data from old table
INSERT INTO users_new (id, first_name, last_name)
SELECT id, first_name, last_name FROM users;
-- 3. Replace old table
DROP TABLE users;
ALTER TABLE users_new RENAME TO users;
-- 4. Integrity check
PRAGMA foreign_key_check;
COMMIT;
Next steps
- Run the
precheck_safe_merge.pyscript provided in the repository to confirm no foreign key constraints will be broken. - Execute the backup command manually to ensure you have a physical copy of the
-waland-shmfiles. - Apply the
migration.sqlscript in a staging environment that mirrors your production data volume.
Connects securely to your tools. The creator never sees your data.
About this skill
SQLite lacks native support for dropping columns or rolling back complex DDL changes, making schema updates risky. One mistake can lead to data loss or a corrupted database state. This skill provides a guarded workflow for destructive changes, ensuring every migration is reversible and validated before execution.
What it does
- Pre-migration collision checking runs read-only scripts to detect data conflicts or empty rows before any changes occur.
- Table rebuild orchestration manages the complex multi-step process required to drop or merge columns in SQLite.
- Automated backup handling ensures main database files and WAL/SHM sidecars are preserved for recovery.
- Integrity verification executes foreign key checks post-migration to prevent shipping inconsistent schemas.
- Version synchronization updates fresh-install DDL to keep migrated databases and new installations identical.
How it works
- Precheck: Run the
precheck_safe_merge.pyscript to identify potential row collisions or data integrity issues. - Backup: Perform a full filesystem copy of the database and its write-ahead log sidecars.
- Execute Rebuild: Perform the table rebuild sequence to drop or merge columns while preserving existing data.
- Verify: Run
PRAGMA foreign_key_checkand schema versioning checks to confirm a successful state.
Frameworks & tools
This skill utilizes standard SQLite DDL, Python-based precheck scripts, and filesystem utilities for atomic backups.
Why this beats prompting it yourself
Writing migrations by hand often overlooks SQLite's specific limitations, like the lack of DROP COLUMN and the behavior of sidecar files. This skill enforces a non-negotiable safety sequence that catches collisions before they happen, preventing silent data corruption that a simple LLM prompt would miss.
Use cases
- Dropping columns from production tables without losing the rest of the row data.
- Merging two columns into one while detecting and resolving value collisions.
- Tightening unique indexes on existing tables by pre-verifying for duplicates.
- Updating legacy schemas to match new application versions safely.
Known limitations
Requires Python to run the precheck scripts. The user must manually resolve any collisions reported by the precheck before the migration can proceed.
Requires Python for precheck scripts. Does not support automatic resolution of data collisions; these must be handled manually before the migration script will proceed.
How to use
- 1
Connect your agent to the Agensi MCP
Copy the prompt and paste it into Claude, ChatGPT, Cursor or any agent that supports MCP. It sets up the connection for you. Manual setup
- 2
Sign in to Agensi
Your agent shows a sign-in link. Sign in and click Allow.
- 3
Ask for what you need
Your agent loads this skill and uses it on your task. It always asks before any payment.
Reviews
No reviews yet
Be one of the first to try it. Every listed skill goes through the trust checks below.
Security scanned
Checked by our 8-point scan before listing
Fresh listing
Recently published to Agensi
Free forever
No account required to browse
Trust & safety
Security scan
Security scan passed 12 days ago
- Free with an Agensi account