typeorm / typeorm

ORM for TypeScript and JavaScript. Supports MySQL, PostgreSQL, MariaDB, SQLite, MS SQL Server, Oracle, SAP Hana, WebSQL databases. Works in NodeJS, Browser, Ionic, Cordova and Electron platforms.
http://typeorm.io
MIT License
34.02k stars 6.27k forks source link

PosgreSQL: precision in column with type 'geography' generates duplicates migration #9077

Open MrRedBobr opened 2 years ago

MrRedBobr commented 2 years ago

Issue Description

"Precision" for the geography column duplicates the migration.

Steps to Reproduce

  1. Create geography column.

    @Column({
      type: 'geography',
      nullable: true,
      spatialFeatureType: 'Point',
      srid: 4326,
      transformer: {
        from(point: GeographyPoint): LocationDto {
          if (point) {
            const [lat, lng] = point.coordinates;
            return { lat, lng };
          } else {
            return null;
          }
        },
        to(location: LocationDto): GeographyPoint {
          return location
            ? {
                type: 'Point',
                coordinates: [location.lat, location.lng],
              }
            : null;
        },
      },
    })
    location?: LocationInput;
  2. Generate migration and run it.

  3. Change precision for this column.

    @Column({
      type: 'geography',
      nullable: true,
      spatialFeatureType: 'Point',
      srid: 4326,
      precision: 20, // add
      transformer: {
        from(point: GeographyPoint): LocationDto {
          if (point) {
            const [lat, lng] = point.coordinates;
            return { lat, lng };
          } else {
            return null;
          }
        },
        to(location: LocationDto): GeographyPoint {
          return location
            ? {
                type: 'Point',
                coordinates: [location.lat, location.lng],
              }
            : null;
        },
      },
    })
    location?: LocationInput;
export class dev1654518273416 implements MigrationInterface {
    name = 'dev1654518273416'

    public async up(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`ALTER TABLE "any_table_name" ALTER COLUMN "location" TYPE geography(Point,4326)`);
    }

    public async down(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`ALTER TABLE "any_table_name" ALTER COLUMN "location" TYPE geography(Point,4326)`);
    }

}
  1. Run this migration
  2. Run migration:generate one more time
  3. In the new migration we can see duplicate

    
    export class dev1654518876605 implements MigrationInterface {
    name = 'dev1654518876605'
    
    public async up(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`ALTER TABLE "any_table_name" ALTER COLUMN "location" TYPE geography(Point,4326)`);
    }
    
    public async down(queryRunner: QueryRunner): Promise<void> {
        await queryRunner.query(`ALTER TABLE "any_table_name" ALTER COLUMN "location" TYPE geography(Point,4326)`);
    }

}



### My Environment

<!--
  Please add any other relevant dependencies to this table at the end.
  For example: Electron, React Native, or NestJS.
-->

| Dependency          | Version  |
| ---                 | ---      |
| Operating System    |  windows        |
| Node.js version     | v16.13.2  | <!-- run `node -v` to obtain this -->
| Typescript version  | 4.4.4  | <!-- run `npm list typescript` to obtain this -->
| TypeORM version     | 0.2.41  | <!-- run `npm list typeorm` to obtain this -->
| NestJS     | 8.1.2  | 

### Additional Context

Precision added in https://github.com/typeorm/typeorm/pull/7166

### Relevant Database Driver(s)

<!-- Put "yes" instead of "no" to your issue's relevant databases -->

| 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`        | yes            |
| `react-native`    | no            |
| `sap`             | no            |
| `spanner`         | no            |
| `sqlite`          | no            |
| `sqlite-abstract` | no            |
| `sqljs`           | no            |
| `sqlserver`       | no            |

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

<!-- Put "✅" (:white_check_mark:) to one of these options, left "✖️" (:heavy_multiplication_x:) others: -->

 - ✖️ 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.

<!--
  Remember that first-time contributors are welcome! 🙌
  👋 Have a great day and thank you for the bug report!
-->
MrRedBobr commented 2 years ago

For now it can be fixed with process variable:

@Column({
      type: 'geography',
      nullable: true,
      spatialFeatureType: 'Point',
      srid: 4326,
     ...(!process?.env?.TYPEORM_GENERATE && { precision: 15 }), //pay attantion
      transformer: {
        from(point: GeographyPoint): LocationDto {
          if (point) {
            const [lat, lng] = point.coordinates;
            return { lat, lng };
          } else {
            return null;
          }
        },
        to(location: LocationDto): GeographyPoint {
          return location
            ? {
                type: 'Point',
                coordinates: [location.lat, location.lng],
              }
            : null;
        },
      },
    })
  location?: LocationInput;

Paist this string in ormconfig.js:

process.env.TYPEORM_GENERATE = true;