Language-Agnostic Database Change Management with Sqitch
Introduction
Managing database schema changes effectively is crucial in modern software development, especially when working with multiple teams or languages. Sqitch provides a powerful, language-agnostic approach to database change management that prioritizes flexibility and traceability. This article will guide you through setting up and using Sqitch for database migrations.
Getting Started
Sqitch is an open-source tool for managing database changes. Unlike many tools tied to specific programming languages or frameworks, Sqitch focuses purely on migrations and version control, making it suitable for projects in any tech stack. It integrates well with various databases, including PostgreSQL, MySQL, SQLite, and more. Key features of Sqitch include:
- Language independence
- Deployment planning and verification
- Robust dependency management
- Integration with Git for version control
Why Sqitch?
Sqitch stands out for its:
- Language Agnosticism: Unlike ORM-based tools, Sqitch doesn't tie migrations to a specific language or framework.
- Fine-grained Control: You can define dependencies between migrations, ensuring a logical order of execution.
- Reversible Changes: Sqitch supports revert scripts to roll back changes, aiding in error recovery.
Prerequisites
Before diving into Sqitch, ensure the following:
- A version control system like Git is installed.
- A database of your choice is set up (e.g., PostgreSQL, MySQL, or SQLite).
- Sqitch is installed. You can follow the official installation guide.
In this article, we will be using PostgreSQL as our database. You can maintain your database in any database management system. For a convenient deployment option, consider cloud-based solutions like Rapidapp, which offers managed PostgreSQL databases, simplifying setup and maintenance.
Create a free database in Rapidapp in seconds here
Setting Up Sqitch for Your Project
Initialize Sqitch
sqitch init my_project --target db:pg://localhost/my_database
This creates a sqitch.conf file and a migrations directory for your project.
If you are using Rapidapp, you can add a target as follows
sqitch target add prod "db:pg://<user>:<pw>@pg.rapidapp.io:5433/<db>?ssl=true&application_name=sqitch"
followed by;
sqitch deploy prod
Define Your First Change
sqitch add add_users_table -n "Add users table"
This creates three scripts:
deploy/add_users_table.sqlfor applying changesrevert/add_users_table.sqlfor rolling back changesverify/add_users_table.sqlfor verifying the applied changes
Writing and Executing Migrations
Writing Deployment Scripts
Edit deploy/add_users_table.sql to include SQL for creating a table:
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(150) UNIQUE NOT NULL
);
Defining Revert Scripts
Edit revert/add_users_table.sql to include SQL for dropping the table.
DROP TABLE IF EXISTS users;
Verification Scripts
Edit verify/add_users_table.sql to ensure the table exists.
SELECT 1 FROM information_schema.tables WHERE table_name = 'users';
Deploying Changes
sqitch deploy
Sqitch ensures changes are applied in order, respecting dependencies.
Version Control and Collaboration
With Sqitch, migration scripts are stored in the repository, making it easy to track changes and collaborate. The sqitch.plan
file acts as a roadmap for your migrations.
Example Git workflow
- Add your changes:
git add deploy/ revert/ verify/ sqitch.plan
- Commit the changes:
git commit -m "Add users table migration"
Advanced Features
Dependency Management
Specify dependencies between migrations in the sqitch.plan file:
add_users_table
add_roles_table [add_users_table]
This ensures the add_users_table migration runs before add_roles_table.
Database Targets
Sqitch supports multiple deployment targets. Update the sqitch.conf file to define targets for staging, production, etc.
Rolling Back Changes
sqitch revert to:add_users_table
Understanding Sqitch’s Internal Tracking Table
When you deploy migrations with Sqitch, it creates and uses an internal tracking table in your database to manage the state of migrations. This table ensures migrations are executed in the correct order, tracks their deployment status, and prevents reapplying or skipping steps.
projects Table
This table tracks information about different projects managed by Sqitch. Example:
| project | uri | created_at | creator_name | creator_email |
|---|---|---|---|---|
| my_project | https://github.com/my_project | 2024-12-12 09:00:00 | Alice Developer | [email protected] |
| another_proj | https://gitlab.com/another_proj | 2024-11-30 14:45:00 | Bob Engineer | [email protected] |
| test_project | https://bitbucket.org/test_proj | 2024-10-01 08:20:00 | Charlie Tester | [email protected] |
changes Table
The changes table is where Sqitch records every migration or database change applied to the project. Each row represents
a specific change, with associated metadata to ensure traceability, accountability, and reproducibility.
Example:
| change_id | script_hash | change | project | note | committed_at | committer_name | committer_email | planned_at | planner_name | planner_email |
|---|---|---|---|---|---|---|---|---|---|---|
| e29b82fa-4d65-11e3-86e0-425861b86ab6 | 4c1a7e5379f2430c22f2d4e5b65f92dd9edc800b | add_users_table | my_project | Adds a users table to the schema | 2024-12-12 10:15:00 | Alice Developer | [email protected] | 2024-12-11 08:00:00 | Bob Planner | [email protected] |
| fbcf9d76-4d65-11e3-86e0-425861b86ab6 | a3f9c6e5379f2430c22f2d4e5b65f92dd9edc900a | add_orders_table | my_project | Adds an orders table to the schema | 2024-12-12 11:30:00 | Charlie Engineer | [email protected] | 2024-12-10 09:30:00 | Eve Designer | [email protected] |
| d86e02fa-4d65-11e3-86e0-425861b86ab6 | 5d6f8e5379f2430c22f2d4e5b65f92dd9edc700c | update_users | another_proj | Updates users with a new column | 2024-12-12 14:00:00 | Dave Maintainer | [email protected] | 2024-12-09 12:45:00 | Fiona Strategist | [email protected] |
dependencies Table
The dependencies table in Sqitch tracks the relationships between changes, indicating which changes must be applied before others.
This ensures that database migrations are executed in the correct order and that all prerequisites are satisfied before applying a change.
Example:
| change_id | type | dependency | dependency_id |
|---|---|---|---|
| e29b82fa-4d65-11e3-86e0-425861b86ab6 | require | add_users_table | d86e02fa-4d65-11e3-86e0-425861b86ab6 |
| e29b82fa-4d65-11e3-86e0-425861b86ab6 | require | add_roles_table | fbcf9d76-4d65-11e3-86e0-425861b86ab6 |
| fbcf9d76-4d65-11e3-86e0-425861b86ab6 | conflict | drop_roles_table | g12f03fa-4d65-11e3-86e0-425861b86ab6 |
| d86e02fa-4d65-11e3-86e0-425861b86ab6 | require | initialize_schema | h34f84fa-4d65-11e3-86e0-425861b86ab6 |
events Table
The events table records significant actions or milestones in the Sqitch migration process. This includes when a change
is planned, executed, or reverted, providing a detailed audit trail of all operations.
Example:
| event | change_id | change | project | note | requires | conflicts | tags | committed_at | committer_name | committer_email | planned_at | planner_name | planner_email |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| deploy | e29b82fa-4d65-11e3-86e0-425861b86ab6 | add_users_table | my_project | Initial deployment of the users... | initialize_schema | v1.0.0 | 2024-12-12 10:30:00 | Alice Developer | [email protected] | 2024-12-10 09:00:00 | Bob Planner | [email protected] | |
| revert | fbcf9d76-4d65-11e3-86e0-425861b86ab6 | drop_roles_table | my_project | Reverted the roles table changes. | add_roles_table | v1.0.1 | 2024-12-12 11:45:00 | Dave Maintainer | [email protected] | 2024-12-10 10:00:00 | Eve Planner | [email protected] | |
| fail | d86e02fa-4d65-11e3-86e0-425861b86ab6 | add_orders_table | my_project | Deployment failed due to conflict. | add_users_table | drop_orders_table | v1.0.2 | 2024-12-12 12:15:00 | Carol Engineer | [email protected] | 2024-12-10 11:00:00 | Frank Planner | [email protected] |
releases Table
The releases table tracks major version releases of a project, recording metadata about when a release was installed and who performed the installation.
This provides a clear audit trail for version management.
Example:
| version | installed_at | installer_name | installer_email |
|---|---|---|---|
| 1.0 | 2024-12-12 14:00:00 | Alice Developer | [email protected] |
| 1.1 | 2024-12-15 10:30:00 | Bob Engineer | [email protected] |
| 2.0 | 2025-01-10 09:45:00 | Carol Maintainer | [email protected] |
tags Table
The tags table records version tags applied to a project, along with metadata about their creation, application, and
associated changes. Tags act as meaningful labels (e.g., versions or milestones) for database states.
Example:
| tag_id | tag | project | change_id | note | committed_at | committer_name | committer_email | planned_at | planner_name | planner_email |
|---|---|---|---|---|---|---|---|---|---|---|
| abc123xyz | v1.0.0 | my_project | def456uvw | Initial release | 2024-12-12 15:00:00.123456 | Alice Developer | [email protected] | 2024-12-10 10:00:00.123456 | Bob Planner | [email protected] |
| ghi789lmn | v1.1.0 | my_project | xyz987qrs | Feature update | 2024-12-15 12:00:00.654321 | Carol Engineer | [email protected] | 2024-12-13 14:00:00.654321 | Dave Planner | [email protected] |
| opq246stu | v2.0.0 | my_project | klm123nop | Major upgrade | 2025-01-10 11:30:00.321456 | Frank Maintainer | [email protected] | 2025-01-08 16:00:00.321456 | Eve Planner | [email protected] |
Conclusion
Sqitch is a robust, language-agnostic tool that simplifies database change management, making it ideal for diverse tech stacks. Its emphasis on dependencies, reversibility, and version control ensures seamless collaboration and reliable deployments. Whether you’re a seasoned developer or new to database migrations, Sqitch is worth exploring.