This was the initial migration I'm trying to edit that Prisma generated when I "renamed" the field in the model

/*
  Warnings:

  - You are about to drop the column `testName` on the `Account` table. All the data in the column will be lost.
  - Added the required column `personName` to the `Account` table without a default value. This is not possible if the table is not empty.

*/
BEGIN TRY

BEGIN TRAN;

-- Remove testName column
ALTER TABLE [dbo].[Account] DROP COLUMN [testName];

-- Add personName column
ALTER TABLE [dbo].[Account] ADD [personName] NVARCHAR(1000) NOT NULL;

COMMIT TRAN;

END TRY
BEGIN CATCH

IF @@TRANCOUNT > 0
BEGIN
    ROLLBACK TRAN;
END;
THROW

END CATCH

And then my edit looks like this:

/*
  Warnings:

  - You are about to drop the column `testName` on the `Account` table. All the data in the column will be lost.
  - Added the required column `personName` to the `Account` table without a default value. This is not possible if the table is not empty.

*/
BEGIN TRY

BEGIN TRAN;

-- Add personName column
ALTER TABLE [dbo].[Account] ADD [personName] NVARCHAR(1000) NOT NULL;

-- Move data from testName to personName
UPDATE [dbo].[Account] SET [personName] = [testName];

-- Remove testName column
ALTER TABLE [dbo].[Account] DROP COLUMN [testName];

COMMIT TRAN;

END TRY
BEGIN CATCH

IF @@TRANCOUNT > 0
BEGIN
    ROLLBACK TRAN;
END;
THROW

END CATCH

But I get this error when trying to run the migration:

Error: P3006

Migration `20211027171057_account_testname_to_personname` failed to apply cleanly to the shadow database. 
Error:
Invalid column name 'personName'.
   0: sql_migration_connector::validate_migrations
             at migration-engine/connectors/sql-migration-connector/src/lib.rs:342
   1: migration_core::api::DevDiagnostic
             at migration-engine/core/src/api.rs:108

This is in SQL Server and the only way people are saying to fix is to use

GO

to split the script into batches (since the new column is not yet accessible in the current batch of statements). But I don't have access to

GO

in the

.sql

migrations (

Incorrect syntax near 'GO'

) since:

The GO command is frequently used inside T-SQL code to use batches. It is not a T-SQL statement, but it is a command recognized by native SQL Server tools like the SSMS, the SQLCMD, and OSQL (SQLCMD is the command-line to handle SQL Server and osql is an old version of SQLCMD that may be removed someday).

So I'm thinking I would need to do something else here? Do I need to do two migrations similar to the expand/contract pattern described in the Prisma docs? 1. First migration: Add the column, make it optional/nullable 2. Second migration: Copy data over from old column to new, drop the old column

Okay solutions I've found: • Always use the expand/contract pattern (which is what would be better in prod anyways) then I avoid this problem • Use SQL Server's sp_rename stored procedure to rename the column for me, haven't tested it but will try next time If anybody has anything to add or correct me on, would love to hear