Adding column and running an update query on the table to populate data in that column does not run in a single migration

[sumit-anantwar]

Describe the Bug
We need to add two columns to a table and then populate the new columns with some data.
Doing this in a single migration fails with error Invalid column name

Steps to Reproduce
Steps to reproduce the behavior:

1. My migrations look like

IF NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'pass' AND COLUMN_NAME = 'date_created')
BEGIN
    ALTER TABLE [pass]
        ADD date_created datetime NOT NULL DEFAULT (GETDATE());
END

IF NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'pass' AND COLUMN_NAME = 'date_updated')
BEGIN
    ALTER TABLE [pass]
        ADD date_updated datetime NOT NULL DEFAULT (GETDATE());
END

UPDATE [pass]
    SET date_created = start_date, date_updated = start_date;

3. I ran migrate with the following options

driver, err := sqlserver.WithInstance(conn.DB, &sqlserver.Config{})
if err != nil {
	log.Fatal("Failed to create migration driver: ", err.Error())
}

migrationsPath := fmt.Sprintf("file:///db_migrations"

m, err := migrate.NewWithDatabaseInstance(
	migrationsPath,
	"sqlserver",
	driver,
)
if err != nil {
	log.Fatal("Failed to create migrate instance: ", err.Error())
}
err = m.Migrate(CurrentDBVersion)
if err != nil {
	log.Fatal("Failed to execute migration: ", err.Error())
}

5. See error

Failed to execute migration: migration failed: Invalid column name 'date_updated'. in line 14: IF NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'pass' AND COLUMN_NAME = 'date_created')
BEGIN
    ALTER TABLE [pass]
        ADD date_created datetime NOT NULL DEFAULT (GETDATE());
END

IF NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'pass' AND COLUMN_NAME = 'date_updated')
BEGIN
    ALTER TABLE [pass]
        ADD date_updated datetime NOT NULL DEFAULT (GETDATE());
END

UPDATE [pass]
    SET date_created = start_date, date_updated = start_date;

 (details: mssql: Invalid column name 'date_updated'.)

Expected Behavior
The three queries should run in a sequence, and the update query should execute successfully.
Note that, these queries run without errors in Azure Data Studio.
Also, if we add the update query in a separate migration, it runs without errors.

Migrate Version
v4.17.1-0.20240102204802-0d4158977486

Loaded Source Drivers
sqlserver

Loaded Database Drivers
sqlserver

Go Version
go version go1.22.3 linux/amd64

Stacktrace

• NA -

Additional context

• NA -

[sumit-anantwar]

After some investigation, we found that the migrations are being executed inside an ExecContext

migrate/database/sqlserver/sqlserver.go

Line 236 in 2477f63

func (ss *SQLServer) Run(migration io.Reader) error {

And it seems that the ALTER TABLE changes aren't applied until the ExecContext block exits.
In which case, the new columns don't yet exist when the update is executed.

So, we tried to change the context for the update statement
Putting the update statement inside an SQL EXEC block did the trick.

EXEC('UPDATE pass SET date_created = start_date, date_updated = start_date')

Iit would be great if someone could confirm if this is the correct approach.