Execute migrations on postgres using .sql files via golang API

[kvskrishna]

Hello,

I searched but couldn't find any information on if its possible to execute sql migrations using golang API.

For example:

CREATE TABLE users(
    id int NOT NULL,
    name varchar(100) NULL,
    PRIMARY KEY(id)
);

Can this statement be executed directly using the golang api similar to how its done via the CLI.

[a8m]

Hey @kvskrishna 👋

Although some of the migration execution logic is available in the sql/migrate package, most of the atlas migrate apply functionality is implemented as a CLI and cannot be used from the Go API. Can you elaborate on what blocks you from using the CLI? How do you deploy your apps, etc?

[krishna-st]

Hello @a8m,

We currently have our local testing/development depending on running an integrated postgres and then running migrations. Switching to altas requires a big change on our end since we cannot run the migrations directly from the .sql files unless its run via a cli. So wondering if there's any alternative way to run the migrations using .sql files.

[qulaz]

@kvskrishna @krishna-startree

I've using this code to apply migrations from .hcl file. I think you can change it a bit to make it work for sqls migrations.

Install atlas in your project

go get ariga.io/atlas@latest

import (
	"context"
	"database/sql"
	"testing"

	"ariga.io/atlas/sql/postgres"
	"ariga.io/atlas/sql/schema"
	"github.com/hashicorp/hcl/v2/hclparse"
	"github.com/stretchr/testify/require"
)

func migrate(t *testing.T, db *sql.DB) {
	driver, err := postgres.Open(db)
	require.NoError(t, err)

	p := hclparse.NewParser()
	_, diag := p.ParseHCLFile("/path/to/your/migrations/schema.hcl")
	require.False(t, diag.HasErrors())

	fromRealm, err := driver.InspectRealm(context.Background(), nil)
	require.NoError(t, err)

	toRealm := &schema.Realm{}
	err = postgres.EvalHCL.Eval(p, toRealm, nil)
	require.NoError(t, err)

	changes, err := driver.RealmDiff(fromRealm, toRealm)
	require.NoError(t, err)

	// WARNING: i use test database in this example!
	// For real databases, you will most likely want to review changes first!
	err = driver.ApplyChanges(context.Background(), changes)
	require.NoError(t, err)
}

Other option that i used before is run CLI in testcontainers, but it slower than call Go API directly

func migrate(t *testing.T, postgresDSN string) {
	atlasContainer, err := testcontainers.GenericContainer(context.Background(), testcontainers.GenericContainerRequest{
		ContainerRequest: testcontainers.ContainerRequest{
			Image: "arigaio/atlas:latest",
			Mounts: testcontainers.ContainerMounts{
				testcontainers.BindMount("/path/to/host/migrations, "/migrations"),
			},
			Cmd: []string{
				"schema",
				"apply",
				"-u",
				postgresDSN,
				"--to",
				"file:///migrations/schema.hcl",
				"--auto-approve",
			},
			WaitingFor: wait.ForExit().WithExitTimeout(time.Second * 10),
		},
		Started: true,
	})

	// Use this to see container logs for debug
	// l, err := atlasContainer.Logs(context.Background())
	// require.NoError(t, err)
	//
	// logs, err := io.ReadAll(l)
	// require.NoError(t, err)
	// fmt.Println(string(logs))
}

One more useful helper for tests - it's clearing database

// cleanDB removes all from database.
// WARNING: DO NOT USE IT WITH PRODUCTION DATABASE, YOU WILL LOSE ALL YOUR DATA!!!
func clearDB(t *testing.T, db *sql.DB) {
	t.Helper()

	c, err := postgres.Open(db)
	require.NoError(t, err)

	var drop []schema.Change

	r, err := c.InspectRealm(context.Background(), nil)
	require.NoError(t, err)

	drop, err = c.RealmDiff(r, schema.NewRealm())
	require.NoError(t, err)

	if len(drop) == 0 {
		return
	}

	err = c.ApplyChanges(context.Background(), drop)
	require.NoError(t, err)
}

[peterldowns]

Thank you very much for the example @qulaz.

@a8m I know that the maintainers have mentioned before that executing the CLI is the preferred API, but is there any chance you'd revisit that decision? The official website has a documentation stub for the golang API that is not actually very helpful, since it only goes over schema inspection. Why not expose the CLI commands as a programmatic interface?

[cwacek]

Seconding this. It actually appears that much of the functionality is in the API, but there are a few critical things (like the RevisionReadWriter that actually reads the revision table is in the internal API).

[peterldowns]

If anyone is looking for alternative migrations tool which works equally well as a cli and as a library, I ended up writing https://github.com/peterldowns/pgmigrate

[erlendp]

Also seconding this. Issue we're having with switching is that our database credentials are acquired via an AWS IAM access token, which we get via our application stack. I'm planning to continue with that approach, and just spawn the atlas command as a child process, passing it the required database connection string. Looking forward to tidying that up at some future date once the API is fleshed out :)

[rotemtam]

Hey @erlendp

Funny you mention this because I'm just wrapping up work on an aws_rds_token data source which will allow you to pull in IAM credentials into your atlas project file.

If you write code that invokes Atlas as a CLI, I suggest using https://github.com/ariga/atlas-go-sdk/tree/master/atlasexec which we use internally at Ariga for many use cases.