search

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.16k stars 6.3k forks source link

Generated migrations for PostgreSQL includes hardcoded schema in some cases, migrations then fail with different schema #10538

Open SmallhillCZ opened 10 months ago

SmallhillCZ commented 10 months ago

Issue description

Generated migrations for PostgreSQL includes hardcoded schema in some cases, migrations then fail with different schema.

Expected Behavior

Creating table test with column type, which is ENUM should create following migration, i.e. without schema name:

await queryRunner.query(`CREATE TYPE "test_type_enum" AS ENUM('type_one', 'type_two')`);
await queryRunner.query(`CREATE TABLE "test" ("type" "test_type_enum" NOT NULL);

Actual Behavior

Migration including schema name is created instead:

await queryRunner.query(`CREATE TYPE "public"."test_type_enum" AS ENUM('type_one', 'type_two')`);
await queryRunner.query(`CREATE TABLE "test" ("type" "public"."test_type_enum" NOT NULL);

and running migrations with a different schema set in Data source will fail:

query failed: CREATE TYPE "public"."test_type_enum" AS ENUM('type_one', 'type_two')
error: error: permission denied for schema public
[

Steps to reproduce


enum TypeEnum {
  "type_one" = "type_one",
  "type_two" = "type_two"
}

@Entity("test")
export class TestEntity {
  @Column({ enum: TypeEnum }) type!: TypeEnum;
}
typeorm migration:generate -d data-source.js test-migration

My Environment

Dependency Version
Operating System Debian GNU/Linux 12 (bookworm)
Node.js version 20.6.1
Typescript version 5.1.3
TypeORM version 0.3.17

Additional Context

No response

Relevant Database Driver(s)

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

Yes, I have the time, but I don't know how to start. I would need guidance.

benjaminhera commented 8 months ago

Same problem I encountered. At least give the option to override the schema for an enum, maybe have an option like enumSchema which has a type of string? and by default it gets created without a schema.

brunorafaeI commented 1 month ago

Hi there !

I don't think it would be a bug, because PostgreSQL allows you to use schemas different from the default one (public). Therefore, mentioning the schema is important for making a correct reference. If you don't specify anything, the database engine will interpret that you are using the default schema (public), but the correct approach is to have the proper reference in the SQL script. For example, I have a table that uses enum types.

# booking_notes (app_widget)

@Index("booking_notes_pkey", ["id"], { unique: true })
@Entity("booking_notes", { schema: "app_widgets" })
export class BookingNotes {
  @Column("uuid", {
    primary: true,
    name: "id",
    default: () => "gen_random_uuid()",
  })
  id: string

  @Column("enum", {
    name: "status",
    enum: ["OPEN", "CLOSED"],
    default: "OPEN",
  })
  status: "OPEN" | "CLOSED"

  ...
# migrations (typeorm)

 await queryRunner.query(
      `CREATE TYPE "app_widgets"."booking_notes_status_enum" AS ENUM('OPEN', 'CLOSED')`
    )
SmallhillCZ commented 1 month ago

If you don't specify anything, the database engine will interpret that you are using the default schema (public)

TypeORM sets search path to whatever schema is in data-source.js. So public is then not used by postgres, unless also the schema in data-source.js is empty.

The problem is that if you don't specify anything in the table definition, but do specify schema in data-source.js that value is hardcoded in the migration and used when the migration is run. I believe if there is nothing in the code, the value from the current data-source.js should not be hardcoded to the migration.

i.e. CREATE TYPE should do the same what is done for CREATE TABLE - the schema, when not provided, is omitted and the search path is set to whatever is in data-source.js (or public if empty).

SmallhillCZ commented 1 month ago

Maybe better example:

Let's assume I have two environments, production and local (development).

In local environment I create a table which contains an ENUM type column like this:

enum TypeEnum {
  "type_one" = "type_one",
  "type_two" = "type_two"
}

@Entity("test")
export class TestEntity {
  @Column({ enum: TypeEnum }) type!: TypeEnum;
}

In local environment I run

Everything goes well, my table and type are created. BUT the migration contains hardcoded public schema. This is now okay, because I use public schema on local environment.

Now let's commit the migration to git to my server to the production environment and run the migrations there:

But I get an error. Because now everything is created correctly in the app schema (note the { schema: "app" } above), but the ENUM type is trying to be created in the hardcoded public schema from my local environment, which my app does not have permissions to in the production environment on server.

brunorafaeI commented 1 month ago

Talvez um exemplo melhor:

Vamos supor que eu tenha dois ambientes, produção e local (desenvolvimento).

  • Na produção tenho { schema: "app" }em meudata-source.json
  • No local eu tenho { schema: "public" }no meudata-source.json

No ambiente local , crio uma tabela que contém uma coluna do tipo ENUM como esta:

enum TypeEnum {
  "type_one" = "type_one",
  "type_two" = "type_two"
}

@Entity("test")
export class TestEntity {
  @Column({ enum: TypeEnum }) type!: TypeEnum;
}

No ambiente local eu corro

  • typeorm migration:generate -d data-source.js test-migration
  • typeorm migration:run -d data-source.js

Tudo vai bem, minha tabela e tipo são criados. MAS a migração contém esquema codificado public. Agora está tudo bem, porque eu uso esquema público no ambiente local .

Agora vamos confirmar a migração para o git no meu servidor para o ambiente de produção e executar as migrações lá:

  • typeorm migration:run -d data-source.js

Mas eu recebo um erro. Porque agora tudo é criado corretamente no appesquema (note o { schema: "app" }acima), mas o tipo ENUM está tentando ser criado no publicesquema codificado do meu ambiente local , para o qual meu aplicativo não tem permissões no ambiente de produção no servidor.

Let me see if I understood correctly, you are confirming to me that you have two different structures and want TypeORM to be able to deduce which structure to use? First point, your local structure should be the same for production (if we are talking about the same project).

Literally your explanation makes no sense at all, generating a migration with a local structure and wanting that migration to run on a different structure!

ertl commented 1 month ago

I fully understand the issue. In our situation, we provide web applications to our customers, who then host them in their own environments using their own databases. Not all customers want to use the schema name we've predefined. Since the schema is configurable, I believe it should be dynamically adjustable as well. We use several different databases, and with Oracle and MSSQL, TypeORM doesn’t seem to adapt the schema in the scripts as expected. From a standardization perspective, it would be ideal if the schema were not included for PostgreSQL either.

SmallhillCZ commented 4 weeks ago

As @ertl says.

In real world it's sometimes not developer's decision what is the schema name going to be. In that case I would, as a developer, not fill the schema name in table/type/whatever definition and expect TypeORM to use the schema from DataSource definition (which might be, together with username and password dynamically set, e.g. by ENV variable). And for this to work I need TypeORM to not hardcode the dynamic schema name to the migration code and instead use what is currently in DataSource