In a Nutshell ! -"SQL MIGRATIONS" (with GO)

26 Oct, 2024

Hello there,

This is a the beginning of a series of blogs that i'm planning to write on small topics which i find interesting.(The title is totally not stolen from kurzgesagt).
Today we'll be (delving) (lol) diving into SQL Migrations.

What are SQL Migrations?
Setting Up the Environment
Creating Your First Migration
Running Migrations
Best Practices
Conclusion

What are SQL Migrations?

Database migrations are a crucial part of modern application development. They help us manage and version control our database schema changes just like we version control our application code.

SQL migrations are essentially version control for your database structure. Each migration represents a specific change to your database schema, such as:

Creating new tables
Modifying existing tables
Adding or removing columns
Creating indexes
Adding constraints

Setting Up the Environment

We'll be using Windows for this tutorial. To get started, we need to install the migrate tool. We'll use Scoop, a command-line installer for Windows, to make this process smooth.

First, install Scoop if you haven't already:

Set-ExecutionPolicy RemoteSignedProcess -Scope CurrentUser
irm get.scoop.sh | iex

Then install the migrate tool:

scoop install migrate


## Creating Your First Migration

Migrations in the `migrate` tool follow a specific pattern. Each migration consists of two files:

* An "up" migration (for applying changes)
* A "down" migration (for reverting changes)

Let's create a migration for a `users` table:

```bash
migrate create -ext sql -dir db/migrations -seq create_users_table

This command creates two files:

db/migrations/000001_create_users_table.up.sql
db/migrations/000001_create_users_table.down.sql

Let's look at the contents of these files:

000001_create_users_table.up.sql:

CREATE TABLE IF NOT EXISTS users(
   user_id serial PRIMARY KEY,
   username VARCHAR (50) UNIQUE NOT NULL,
   password VARCHAR (50) NOT NULL,
   email VARCHAR (300) UNIQUE NOT NULL
);

000001_create_users_table.down.sql:

DROP TABLE IF EXISTS users;

Understanding the Migration Files

Up Migration: The .up.sql file contains the changes you want to apply. In our case, it creates a new users table with specific columns and constraints.
Down Migration: The .down.sql file contains the opposite operation that reverts the changes made by the up migration. Here, it drops the users table.

Making Migrations Idempotent

Notice the IF NOT EXISTS and IF EXISTS clauses in our SQL statements. These make our migrations idempotent, meaning they can be run multiple times without causing errors or changing the result beyond the initial application. This is crucial for:

Preventing errors when running migrations multiple times
Ensuring consistency across different environments
Making migrations more robust and reliable

Running Migrations

To apply your migrations, use the following command:

migrate -database ${POSTGRESQL_URL} -path db/migrations up

Replace ${POSTGRESQL_URL} with your actual PostgreSQL connection URL, which typically looks like:

postgresql://username:password@localhost:5432/database_name?sslmode=disable

Rolling Back Migrations

If you need to undo changes, you can run the down migrations:

migrate -database ${POSTGRESQL_URL} -path db/migrations down

You can also roll back a specific number of migrations:

migrate -database ${POSTGRESQL_URL} -path db/migrations down 1

Remember that good migration practices are crucial for maintaining a healthy database over time. Always plan your migrations carefully, test them thoroughly, and maintain clear documentation of your schema changes.

Happy migrating! 🚀

Note: This blog post covers SQL migrations using the migrate tool with PostgreSQL on Windows. The principles apply to other databases and operating systems as well.

#golang #migrations #sql

DJ's DevBlog 👾