CODEWITHSUNDEEP
nestjstypeorm
killjoy
killjoy

Reputation: 1052

Nestjs TypeOrm postgresql using custom schema

In my Nestjs TypeOrm app, I'd like tu encapsulate all my db artifacts in a schema named globe. So, I indicate the schema in my ormconfig.ts

const DatabaseConfigForMigrations = {
  name: 'countrydb',
  type: 'postgres',
  host: process.env.DB_HOST,
  port: process.env.DB_PORT,
  schema: 'globe',
  username: process.env.DB_USERNAME,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_NAME,
  synchronize: false,
  logging: true,
  entities: ['dist/src/**/*entity.js'],
  migrations: ['dist/src/migrations/**/*.js'],
  cli: {
    migrationsDir: 'src/migrations',
  },
  options: { trustServerCertificate: true },
};

also in my models. Such as;

@Entity({ schema: 'globe' })
@ObjectType()
export class City {
  @PrimaryGeneratedColumn()
  @Field(() => Int)
  id: number;

  @Column()
  @Field()
  name: string;

  @Column({ nullable: true })
  @Field({ nullable: true })
  touristic: boolean;

  @Column({ nullable: true })
  @Field(() => Int, { nullable: true })
  population: number;

  @ManyToOne(() => Country, (country) => country.cities)
  @JoinColumn({ name: 'country_id' })
  country: Country;

  @Column({ type: 'int', name: 'country_id' })
  countryId: number;
}

However, when I run yarn migration:run unfortunately, error: error: schema "globe" does not exist happens.

How can I have TypeOrm create my tables in my globe schema?

Upvotes: 0

Views: 4111

Answers (1)

loser
loser

Reputation: 51

It's a bit tricky and strange having to use explicitly the typeorm's url connection option and then specify the schema inside its search_path option to get it to work for me.

e.g. ormconfig.js

    module.exports = {
      type: 'postgres',
      url: `postgresql://${process.env.DB_USERNAME}:${process.env.DB_PASSWORD}@${process.env.DB_HOST}:${process.env.DB_PORT}/${process.env.DB_NAME}?options=-c%20search_path=globe`,
      ...

Upvotes: -1

Related Questions