mssql: Column is dropped and recreated in every migration

[ertl]

Issue Description

@column({ type: 'nvarchar', length: 'max' })
value: string;

this code leads to a recreation of the column for every migration.

Expected Behavior

no recreation of the column

Actual Behavior

• This Code produces the wrong migration output. (Column is dropped an added)

  @column({ type: 'nvarchar', length: 'max' })
  value: string;

• This Code produces the correct output

  @column({ type: 'nvarchar', length: 'MAX' })
  value: string;

My Environment

Dependency	Version
Operating System	windows
Node.js version	16.16.0
Typescript version	4.7.2
TypeORM version	0.3.10

Additional Context

Relevant Database Driver(s)

DB Type	Reproducible
aurora-mysql	no
aurora-postgres	no
better-sqlite3	no
cockroachdb	no
cordova	no
expo	no
mongodb	no
mysql	no
nativescript	no
oracle	no
postgres	no
react-native	no
sap	no
spanner	no
sqlite	no
sqlite-abstract	no
sqljs	no
sqlserver	yes

Are you willing to resolve this issue by submitting a Pull Request?

• ✖️ Yes, I have the time, and I know how to start.
• ✖️ Yes, I have the time, but I don't know how to start. I would need guidance.
• ✖️ No, I don’t have the time, but I can support (using donations) development.
• ✅ No, I don’t have the time and I’m okay to wait for the community / maintainers to resolve this issue.

[AgusNeira]

@ertl Could you provide the extra migration lines you mention? Both for up and down

[Lewiscowles1986]

I'm getting this and it is really frustrating.

I'm running postgres with:

docker run --rm -e 'POSTGRES_PASSWORD=password' -p 5432:5432 postgres

My migrations for a database with type: text which were varchar(255) columns; are now, all dropping columns to rename to character varying.

model

'use strict';

const { EntitySchema } = require('typeorm');

module.exports = new EntitySchema({
  name: 'Person',
  tableName: 'person',
  schema: 'person',
  columns: {
    personId: {
      primary: true,
      type: 'uuid',
    },
    firstName: {
      type: 'text',
      nullable: false,
    },
  },
});

orm.js

'use strict';

require('dotenv').config();
const { DataSource } = require('typeorm');
const models = require('../models');
const dbConfig = require('../config');

const orm = new DataSource({
  type: 'postgres',
  schema: 'public',
  host: dbConfig.databaseHost,
  port: dbConfig.databasePort,
  username: dbConfig.databaseUsername,
  password: dbConfig.databasePassword,
  database: dbConfig.databaseName,
  entities: Object.values(models),
  migrations: ['migrations/*.js'],
  migrationTableName: 'migrations',
  cli: {
    migrationsDir: 'migrations',
  },
});

module.exports = { orm };

What is slightly more madenning is that the table was originally made using typeORM a few versions back. I keep seeing these breaking changes when updating typeORM; and this one seems to just manifest even if I leave typeORM alone.

CREATE TABLE public.person (
	"personId" uuid NOT NULL,
	"firstName" varchar(255) NOT NULL,
	CONSTRAINT person_pkey PRIMARY KEY ("personId")
);

nodejs v16.18.1 (I've also used 18.12.1 and 14.21.1, neither fixed)

I Have renamed the schema and removed superflous fields here for a minimum reproducible context. TypeORM version(s) used

• 0.3.4
• 0.3.6
• 0.3.10

Migration generated:

const { MigrationInterface, QueryRunner } = require("typeorm");

module.exports = class migration1669570480791 {
    name = 'migration1669570480791'

    async up(queryRunner) {
        await queryRunner.query(`
            ALTER TABLE "public"."person" DROP COLUMN "firstName"
        `);
        await queryRunner.query(`
            ALTER TABLE "public"."person"
            ADD "firstName" text NOT NULL
        `);
    }

    async down(queryRunner) {
        await queryRunner.query(`
            ALTER TABLE "public"."person"
            ADD "firstName" character varying(255) NOT NULL
        `);
    }
};

[Lewiscowles1986]

There is a chance I'm pulling a different postgres version to the one we used to generate the original migration.

but I checked the typeorm source code. IT seems to have a TS definition that "varchar" | "text" | "character varying" is the same damn thing!

We had this before with NULL columns, where I had to go adjust all the models to invert their logic to avoid a migration. We're not doing anything weird per-se. It feels like the most vanilla setup on earth.

[Lewiscowles1986]

Using the following I've been able to make it play along without dropping columns

[
  {
    oldColumn: {
      "@instanceof": Symbol(TableColumn),
      isNullable: false,
      isGenerated: false,
      isPrimary: false,
      isUnique: false,
      isArray: false,
      length: "255",
      zerofill: false,
      unsigned: false,
      name: "firstName",
      type: "character varying",
      comment: undefined,
    },
    newColumn: {
      "@instanceof": Symbol(TableColumn),
      isNullable: false,
      isGenerated: false,
      isPrimary: false,
      isUnique: false,
      isArray: false,
      length: "",
      zerofill: false,
      unsigned: false,
      name: "firstName",
      type: "text",
      width: undefined,
      charset: undefined,
      collation: undefined,
      precision: undefined,
      scale: undefined,
      default: undefined,
      onUpdate: undefined,
      generationStrategy: undefined,
      generatedIdentity: undefined,
      comment: undefined,
      enum: undefined,
      enumName: undefined,
      primaryKeyConstraintName: undefined,
      asExpression: undefined,
      generatedType: undefined,
      spatialFeatureType: undefined,
      srid: undefined,
    },
  },
]

now using the following more strict definition (unsure how it ever worked with type: 'text' anyway

    firstName: {
      type: 'varchar',
      length: 255,
      nullable: false,
    },

It's likely in need of further tuning (who has > 100 character name parts?)