Level Up Your Database: A Practical Guide to Schema Migrations

In the dynamic world of software development, database schemas are constantly evolving to accommodate new features, optimise performance, and adhere to changing data requirements. Database migrations, also known as Schema migrations, transform a schema from its current state to a desired future state. This can involve adding or removing elements, splitting fields, or adjusting data types and constraints.

Manually modifying a production database schema should be avoided and that’s where schema migration tools come in. While many Object-Relational Mappers (ORMs) offer basic migration functionality, complex projects often require more robust solutions. This is when developers reach for dedicated schema migration tools. And this article unveils a simpler approach for schema migrations in Go projects.

GoFr’s approach for migrations

The GoFr framework currently supports data migrations for MySQL, Postgres and Redis. It allows you to maintain a directory for all the migrations with proper versioning control. GoFr also maintains the records in the database itself which helps in tracking which migrations have already been executed and ensures that only migrations that have never been run are executed.

Creating migration files:

You can create a migrations directory which can contain all the existing and future migration files.

❯ go-project
├── migrations
│ └── 1712568232_create_table_orders.go
│ └── all.go

Following is the example to create a table: orders

package migration

import (
    "gofr.dev/pkg/gofr/migration"
)

const createTable = `CREATE TABLE IF NOT EXISTS orders
(
    id          UUID        not null primary key,
    cust_id     UUID        not null,
    products    JSONB       not null,
    status      varchar(10) not null,
    created_at  TIMESTAMP   not null,
    updated_at  TIMESTAMP   not null,
    deleted_at  TIMESTAMP
);`

func createTableOrders() migration.Migrate {
    return migration.Migrate{
       UP: func(d migration.Datasource) error {
          _, err := d.SQL.Exec(createTable)
          if err != nil {
             return err
          }

          return nil
       },
    }
}

You may notice that there is a file named all.go, it maps the migration functions to their versions. For example,

package migration

import (
    "gofr.dev/pkg/gofr/migration"
)

func All() map[int64]migration.Migrate {
    return map[int64]migration.Migrate{
       1712568232: createTableOrders(),
    }
}

Connecting to database:

To connect to the postgreSQL database, you would need to add the following configs in configs/.env file:

DB_HOST=localhost
DB_USER=postgres
DB_PASSWORD=root123
DB_NAME=orders
DB_PORT=2006
DB_DIALECT=postgres

Running the migrations:

Now, when we have created the migration files and map, we are ready to execute the migrations in our database. To execute, you would need to add a command in your main function as shown below:

func main() {
 // Create a new application
 app := gofr.New()

 // Run migrations
 app.Migrate(migration.All())

 // Add required routes
 app.POST("/orders", h.Create)
 app.GET("/orders", h.GetAll)
 app.GET("/orders/{id}", h.GetByID)
 app.PUT("/orders/{id}", h.Update)
 app.DELETE("/orders/{id}", h.Delete)

 // Run the application
 app.Run()
}

Results:

Once you run the application, you would see the following logs, which confirms that the migration is completed and the application has started.

As I mentioned earlier, GoFr also store the records of all migrations, you could access it from the gofr_migrations table in the database.

We’re done!! 🚀

Thank you for reading until the end. Before you go:

• Please consider liking and following! 👏

• Follow me on: LinkedIn | Medium