GO LANG DB Migration && migration errors && Error fix to dirty dabatse version 1. fix and force version

BASIC

* go lang migration will create a table scheme_migration to track migrations 

How It Works in Detail

1. Versioning
   Each migration has a numeric version number (e.g., 000001, 000002, etc.). This version number is derived from the prefix in the migration file name.

2. schema_migrations Table (or Equivalent)

   • When you run migrations for the first time, golang-migrate creates a table in your database to track the “current version.”
   • By default, this table might be named schema_migrations (the name can vary depending on the driver or your configuration).

3. Tracking Migrations

   • Each time a migration is applied, golang-migrate updates the version in schema_migrations.
   • For example, if your highest version is currently 1, and you have a new migration file with version 2, golang-migrate will run that migration and update the “current version” to 2.

4. Skipping Already-Applied Migrations

   • When you call m.Up(), golang-migrate compares the latest recorded version in schema_migrations to the versions of all available .up.sql files.
   • Any migration with a version equal to or lower than the recorded version is considered already applied.
   • It only runs migrations whose version is higher than what’s recorded in schema_migrations.

5. Result

• If there are no new migrations (i.e., the current version in the database is already equal to the highest version in your migration files), m.Up() returns migrate.ErrNoChange.
• If there are pending migrations, m.Up() runs them in ascending order and updates the version in the DB after each successful migration.

-----------------------------------------------------------------------------------------

1. Installation

First, install the golang-migrate tool by running the following command:

bash

Copy code

go install github.com/golang-migrate/migrate/v4/cmd/migrate@latest

This will install the migrate command-line tool.

To install the Go module itself, run this in your Go project directory:

bash

Copy code

go get -u github.com/golang-migrate/migrate/v4

2. Creating Migrations

To create a new migration, use the migrate command. Here’s how to create a new migration file:

bash

Copy code

migrate create -ext sql -dir db/migrations create_users_table

Example SQL Migration Files

000001_create_users_table.up.sql

This file contains the SQL commands to apply the migration, such as creating a table.

sql

Copy code

CREATE TABLE users (

    id SERIAL PRIMARY KEY,

    name VARCHAR(100),

    email VARCHAR(100) UNIQUE NOT NULL,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

);

000001_create_users_table.down.sql

This file contains the SQL commands to revert the migration, typically by dropping the created table.

sql

Copy code

DROP TABLE users;

USING CODE, so migration is ran checked in build

// If in a different package can use return fmt.Errorf("failed to open file source: %w", err) to return error 

// the github packages need to be added go.mod using go get 

package main

import (

    "fmt"

    "log"

    "github.com/golang-migrate/migrate/v4"

    _ "github.com/golang-migrate/migrate/v4/database/postgres"

    _ "github.com/golang-migrate/migrate/v4/source/file"

)

func main() {

    // The source includes 'file://'

    // The database URL includes your driver scheme (e.g., 'postgres://')

    m, err := migrate.New(

        "file://db/migrations",

        "mysql://user:password@tcp(localhost:3306)/mydb?multiStatements=true",

    )

    if err != nil {

        log.Fatalf("migrate.New failed: %v", err)

    }

    err = m.Up()

    if err != nil && err != migrate.ErrNoChange {

        log.Fatalf("m.Up failed: %v", err)

    }

    fmt.Println("Migrations applied successfully!")

}

------------------

Fix to dirty databse

Cause - migration applied but encountered error so version in correct

error such as there is an error in ur schema etc

Fix : remove table in scheme_migrations, and dete migrated tables

other fixes:

1. Why the Database Becomes Dirty

• A migration may fail due to a syntax error, connection interruption, or invalid SQL.
• Once a failure occurs, golang-migrate sets the dirty flag on that migration version to indicate the database might be partially migrated.

2. Identify the Dirty Version

Often, you’ll see an error or logs indicating something like:

go
error: Dirty database version 1. Fix and force version.

That means the migration version 1 is marked dirty.

You can also run the status command (if using the CLI) to confirm:

bash
migrate -path ./migrations -database "postgres://user:pass@localhost/dbname?sslmode=disable" status

The status output will show a “dirty” flag next to the problematic version.

---

3. Fix/Repair the Migration

If you know what caused the migration to fail (for example, a bad SQL statement), you should fix that migration. However, once the database is already marked dirty, you have two main approaches:

1. Roll back the partial changes: Manually revert any partial changes the failed migration might have made (e.g., drop a half-created table, or fix data inconsistencies). Then force the version to the previous clean state.
2. Mark the current version as successfully applied: If the partial changes are actually okay (or you manually fixed them), you can “force” the migration to mark the database as at a certain version—and not dirty—so future migrations can continue.

---

4. Force the Version with the CLI

Using the golang-migrate CLI, you can do something like:

bash
migrate -path ./migrations \
  -database "postgres://user:pass@localhost:5432/dbname?sslmode=disable" \
  force 1

Where 1 is the version you want to set the database to, and simultaneously clear the dirty flag. This tells golang-migrate:

“Treat the database as if version 1 has been successfully applied (not dirty anymore).”

After Forcing

• If you forced version 1, you can now try running migrate up again. It will proceed from version 2 onward.
• Make sure that your actual database state matches what your forced version suggests (e.g., if you forced version 1 as done, ensure the changes in 000001_*.up.sql are truly in place, or revert them as needed).

---

5. Programmatic Forcing (Optional)

If you’re using golang-migrate in your Go code (not just the CLI), you can also do something like:

go
import (
    "github.com/golang-migrate/migrate/v4"
    // ...
)

// Assuming `m` is a *migrate.Migrate instance
if err := m.Force(1); err != nil {
    // handle error
}

This will set the migration version to 1 and clear the dirty state.

---

6. Retest Your Migrations

After forcing the version and making sure your DB is consistent:

1. Run your migration command again:

   bash
   migrate -path ./migrations \
     -database "postgres://user:pass@localhost:5432/dbname?sslmode=disable" \
     up
   

2. Ensure there are no remaining errors and that your database schema is now up to date.

---

Summary

• A “dirty” database version means a migration got stuck/failed.
• Manually fix/undo or confirm the partial changes in your DB.
• Use migrate force <version> (CLI) or m.Force(<version>) (Go code) to mark the version clean and ready for further migrations.
• Finally, re-run migrate up to continue with new migrations.