Anyone with some general SQL knowledge able to hel...
# orm-help
d
Anyone with some general SQL knowledge able to help me do a prisma migration to rename a column that already has data? I've created a migration file with
--create-only
so I can edit it to copy the data to the new column, but I'm getting an error when trying to execute the file during the actual migration itself
This was the initial migration I'm trying to edit that Prisma generated when I "renamed" the field in the model
Copy code
/*
  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:
Copy code
/*
  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:
Copy code
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
r
@Danny 👋 If you just want to rename a column, then what you need is the
alter table
command. 1. Create a migration renaming the field in
schema.prisma
using
prisma migrate dev --create-only
2. Replace the contents of the
.sql
file with this:
alter table tale_name rename column column_name to new_column_name
3. Run
prisma migrate dev
d
Since I'm using SQL Server I think I would need the
sp_rename
stored proc right? https://docs.microsoft.com/en-us/sql/relational-databases/tables/rename-columns-database-engine?view=sql-server-ver15