orm.getSchemaGenerator().refreshDatabase() fails if Postgres extensions required

[corbt]

Describe the bug
I'm using orm.getSchemaGenerator().refreshDatabase() against a test database within my test suite to ensure I have an up-to-date version of the database available for end-to-end tests.

Unfortunately, since some of my models rely on Postgres extensions and the schema generator doesn't appear to detect or re-apply the required extensions, this call fails. An example failure is below.

Stack trace

DriverException: create table "user" ("id" uuid not null default uuid_generate_v4()); - function uuid_generate_v4() does not exist

To Reproduce

1. Create an entity that relies on a Postgres extension. For example, this User model relies on the popular "uuid-ossp" extension:

@Entity()
export class User {
  @PrimaryKey({ type: 'uuid', defaultRaw: 'uuid_generate_v4()' })
  id!: string;
}

2. Generate a migration with yarn run mikro-orm migration:create --initial
3. Add the extension to the top of the migration (eg. add the line this.addSql('CREATE EXTENSION "uuid-ossp";')).
4. Run the migration with yarn run mikro-orm migration:up
5. Within a test suite, add the line orm.getSchemaGenerator().refreshDatabase() to a beforeAll hook.
6. Notice

Expected behavior
refreshDatabase() should track the enabled extensions in a database and re-enable them once it has been refreshed and before applying the rest of the schema.

Versions

Dependency	Version
node	16
typescript	4.3.5
mikro-orm	5.1.3
postgres	14

[AzariasB]

I see that you're using postgres 14
I think they recently added the function gen_random_uuid which does not require an extension https://www.postgresql.org/docs/current/functions-uuid.html

[corbt]

Thanks, that's actually good to know! Unfortunately doesn't solve the problem for me entirely though, since I also use some PostGIS geometry columns in my schema, so I still need that extension enabled.

[B4nan]

I don't agree this should be somehow automatically handled by the ORM. I am open for adding some hooks to schema generator so you can do it yourself.

Note that you enabled the extension inside a migration - that is a different level, above schema generator. We can't track what migrations are doing from a context of schema generator, the dependency is in opposite direction.

[corbt]

We can't track what migrations are doing from a context of schema generator, the dependency is in opposite direction.

This may differ between databases, but at least in the Postgres world enabled extensions are conceptually part of the database's schema. If you run pg_dump --schema-only the resulting SQL will include the CREATE EXTENSION clauses for any extensions enabled in your database. Rails also tracks enabled extensions as part of the database schema. The schema.rb file that Rails auto-generates also tracks and re-applies enabled extensions using the enable_extension "<extension_name>" syntax.

[B4nan]

My point is you can't expect things you set inside migrations to have any effect on schema generator.

[corbt]

Oh yes, agreed! In general migrations can include any arbitrary code that could transform data, etc. I don't expect those changes to be persisted in the general case without re-running migrations.

For Postgres extensions specifically though, it would be really nice if MicroORM gave me a way to either declaratively map them, or ensure they get re-added when I spin up a new dev or test environment.

The easiest solution might be to add a config string to mikro-orm.config.ts. Like setupDatabaseSQL or something? The ORM could then run that SQL before creating the schema, and would give me an escape hatch where I could put the CREATE EXTENSION [x] commands I need (as well as set other database values like encodings, timezone, etc if necessary).

[B4nan]

Yes, that's what I meant when I said I can imagine some hooks for SG.

[Jcalcaldev]

Any updates on this? Any way to add the CREATE EXTENSION line on the initial migration programatically through mikroOrm?