Generating migrations for gorm with Atlas

For those unfamiliar with gorm, it is the most popular orm for Go, which lets you define Go structs and work with SQL databases(sqlite, PG, MySQL etc.).

Here is an example,

type User struct {
    gorm.Model
    Name string
}

db.First(&user, "10")
// Is equivalent to 
// SELECT * FROM users WHERE id = 10;

Migrations with gorm

Gorm provides an auto migration feature that automatically finds changes in the schema and applies them to your database.

db.AutoMigrate(&User{})

This is a convenient method as you don't have to migrate manually. Ideally, it would be best if you kept them separate as it would let you push migrations, and when it's successful, you can deploy the code changes for the migration.

Using Atlas to generate SQL migrations

Atlas lets you manage your database schema as code. It has a form integration that lets you generate the sql for your Gorm schema changes. Let's see it in action by testing a sqlite db.

Setup a new project and install gorm and atlas gorm provider

mkdir gorm-atlas && cd gorm-atlas
go mod init gorm-atlas

// Install gorm and sqlite drivers
go get -u gorm.io/gorm
go get -u gorm.io/driver/sqlite

// Install atlas packages
go get -u ariga.io/atlas-provider-gorm
go get ariga.io/atlas-provider-gorm/gormschema@v0.1.0

Now in your main.go file, create two gorm models

type User struct {
    gorm.Model
    Name string
}

type Session struct {
    gorm.Model
    Id     string
    UserId uint
    User   User
}

And in the main function, we load the two models into gormschema and write the SQL statements to stdout

func main() {
    stmts, err := gormschema.New("sqlite").Load(&User{}, &Session{})
    if err != nil {
        fmt.Fprintf(os.Stderr, "failed to load gorm schema: %v\n", err)
        os.Exit(1)
    }
    io.WriteString(os.Stdout, stmts)
}

Atlas will read the stdout to generate SQL.

Now, create an atlas.hcl\ file. This will have the configuration for the migration

data "external_schema" "gorm" {
  program = [
    "go",
    "run",
    ".",
  ]
}

env "gorm" {
  src = data.external_schema.gorm.url
  dev = "sqlite://data.db"
  migration {
    dir = "file://migrations"
  }
  format {
    migrate {
      diff = "{{ sql . \"  \" }}"
    }
  }
}

• program defines what program to run to generate the schema

• migration is where the generated SQL will be

• dev is the path to the database

Now, to generate the initial migration, run

atlas migrate diff --env gorm

This will create the data.db (if it does not exist) and the migrations under the migrations folder. It generated,

-- Create "users" table
CREATE TABLE `users` (
  `id` integer NULL,
  `created_at` datetime NULL,
  `updated_at` datetime NULL,
  `deleted_at` datetime NULL,
  `name` text NULL,
  PRIMARY KEY (`id`)
);
-- Create index "idx_users_deleted_at" to table: "users"
CREATE INDEX `idx_users_deleted_at` ON `users` (`deleted_at`);
-- Create "sessions" table
CREATE TABLE `sessions` (
  `id` text NULL,
  `created_at` datetime NULL,
  `updated_at` datetime NULL,
  `deleted_at` datetime NULL,
  `user_id` integer NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `fk_sessions_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
);
-- Create index "idx_sessions_deleted_at" to table: "sessions"
CREATE INDEX `idx_sessions_deleted_at` ON `sessions` (`deleted_at`);

Update the user struct to add a new field

type User struct {
    gorm.Model
    Name string
    Age  uint64
}

Running the atlas command generates a new file

-- Add column "age" to table: "users"
ALTER TABLE `users` ADD COLUMN `age` integer NULL;

Now you can use any migrations tool to migrate these SQL files.

References

• https://gorm.io/docs/migration.html
• https://atlasgo.io/guides/orms/gorm

Comments

[anoop-calicut]

How to generate the migration schema with the "if not exists" clause?

[Amal Shaji]

Why do you need if not exists?

[anoop-calicut]

to keep the schema idempotent and that can be used in a typical way.