One post tagged 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.

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.sql for applying changes
• revert/add_users_table.sql for rolling back changes
• verify/add_users_table.sql for 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:

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:

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:

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:

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:

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:

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.