Updating PSQL Tables with Relationships between Entities Using NestJS, TypeORM, GraphQL

Question

I've been struggling for a week now on creating new tables and updating the TypeORM entities on the backend. We're using NestJS, GraphQL, and TypeORM with a PSQL database. We have a production server/database setup with clients' information saved already. I'm trying to add a new table to the database using a code-first approach to generate schema. On the master branch of the repo, I start it up in my local environment, and connect to a clean database. Once I create accounts, and save information to the tables, I then switch to a new branch that contains the code for implementing the new table, including the module, service, entity, and resolver. If I try to run this branch and connect to the same database I was using on master, it fails to compile, fails to generate a schema.gql file, and stops at "GraphQLModule dependencies initialized." This new table that I created has a ManyToOne relationship with the Teams table, that already has values contained in it. For some reason, I think TypeORM is failing to update the database properly, and I don't know why. If I create a new database, and connect to the new database on the branch with the new table code, it works just fine, and no errors are thrown. Problem is if I connect to the original database, no error is thrown, but the code fails to compile, and I don't know how to debug it.

Has anyone had any issue adding new tables to their PSQL database using TypeORM, Nest, and GraphQL?

Here are some code snippets showing what I mean:

Entity for Waiver Table (exists on the old database already)

@Entity({ name: 'waivers' })
@ObjectType()
export class WaiverEntity extends BaseEntity {
  @Field(() => ID)
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Field(() => AccountEntity)
  @ManyToOne(
    () => AccountEntity,
    creator => creator.waivers,
    { onDelete: 'SET NULL' },
  )
  @JoinColumn()
  creator: Promise<AccountEntity>;

  @Field(() => TeamEntity)
  @ManyToOne(
    () => TeamEntity,
    team => team.waivers,
    { onDelete: 'CASCADE' },
  )
  @JoinColumn()
  team: Promise<TeamEntity>;

  @Field(() => ID)
  @Column({ nullable: true })
  creatorId: string;

  @Field(() => ID)
  @Index()
  @Column({ nullable: true })
  teamId: string;

  @Field()
  @Column('json')
  organizer: Organizer;

  @Field()
  @Column('json')
  event: Event;

  @Field()
  @Column('json', { nullable: true })
  eventDate: EventDate;

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

  @Field({ nullable: true })
  @Column({ nullable: true })
  customerLabel: string;

  @Field(() => CustomEntity, { nullable: true, defaultValue: [] })
  @Column('jsonb', { nullable: true })
  intensity: CustomEntity;

  @Field(() => [CustomEntity], { nullable: true, defaultValue: [] })
  @Column('jsonb', { nullable: true })
  activities: CustomEntity[];

  @Field({ defaultValue: waiverStatus.DRAFT, nullable: false })
  @Column({ default: waiverStatus.DRAFT, nullable: false })
  status: string;

  @Field({ nullable: true })
  @Column({ type: 'varchar', nullable: true })
  title: string;

  @Field({ nullable: true })
  @Column({ nullable: true })
  body: string;

  @Field({ nullable: true })
  @Column({ nullable: true, default: signatureDefaultContent })
  signatureContent: string;

  @Field(() => [String], { nullable: true })
  @Column('simple-array', { nullable: true })
  ageGroup: string[];

  @Field(() => [AdditionalFields], { nullable: false, defaultValue: [] })
  @Column('jsonb', { nullable: true })
  additionalFields: AdditionalFields[];

  @Field({ nullable: false })
  @Column({ nullable: false })
  step: number;

  @Exclude()
  @Field({ nullable: true })
  @Column({ nullable: true, unique: true })
  pdfURL: string;

  @BeforeInsert()
  cleanUpBeforeUpdate(): void {
    // add Prefix on retrieval
    if (this.organizer && this.organizer.photoURL) {
      try {
        const photoUrls = this.organizer.photoURL.split(
          `${AWS_BUCKETS.ORGANIZATION_BUCKET_IMAGE}/`,
        );

        this.organizer.photoURL =
          photoUrls.length > 1 ? photoUrls[1] : this.organizer.photoURL;
      } catch (e) {}
    }
  }

  @AfterLoad()
  updateURLs(): void {
    // add Prefix on retrieval
    this.pdfURL = this.pdfURL
      ? `${getBucketPrefix(
          AWS_BUCKETS_TYPES.WAIVER_BUCKET_FILES,
          'https://',
        )}/${this.pdfURL}`
      : null;

    if (this.organizer) {
      this.organizer.photoURL = this.organizer.photoURL
        ? `${getBucketPrefix(
            AWS_BUCKETS_TYPES.ORGANIZATION_BUCKET_IMAGE,
            'https://',
          )}/${this.organizer.photoURL}`
        : null;
    }
  }

  @Field({ nullable: true })
  @Column({ type: 'timestamp', nullable: true })
  @IsDate()
  publishDate: Date;

  @Field({ nullable: true })
  @Column({ nullable: true, unique: true })
  slug: string;

  @Field(() => [DownloadEntity], { nullable: true })
  @OneToMany(
    () => DownloadEntity,
    downloadEntity => downloadEntity.waiver,
  )
  @JoinColumn()
  waiverDownloads: Promise<DownloadEntity[]>;

  @Field({ defaultValue: 0 })
  downloadCount: number;

  @Field(() => [WaiverMembersEntity])
  @OneToMany(
    () => WaiverMembersEntity,
    waiverMember => waiverMember.account,
  )
  accountConnection: Promise<WaiverMembersEntity[]>;

  @Field(() => [WaiverConsentsEntity])
  @OneToMany(
    () => WaiverConsentsEntity,
    waiverMember => waiverMember.waiver,
  )
  consent: Promise<WaiverConsentsEntity[]>;

  @Field(() => [AccountEntity])
  waiverMember: AccountEntity[];

  @Field(() => [ParticipantsEntity])
  @OneToMany(
    () => ParticipantsEntity,
    participant => participant.waiver,
  )
  participants: ParticipantsEntity[];

  @Field({ defaultValue: 0 })
  totalResponses: number;

  @Field()
  eventName: string;

  @Field({ nullable: true })
  @Column({ type: 'varchar', nullable: true })
  smsContent: string;

  @Field({ nullable: true })
  @Column({ nullable: true })
  smsCode: string;

  @Field()
  @Column({ type: 'timestamp', default: () => timeStamp })
  @IsDate()
  createdAt: Date;

  @Field()
  @Column({
    type: 'timestamp',
    default: () => timeStamp,
    onUpdate: timeStamp,
  })
  @IsDate()
  lastUpdatedAt: Date;
}

And here is the new entity waiver templates, which has a ManyToOne relationship to the teams table, and exists on the new branch

@Entity({ name: 'waiverTemplates' })
@ObjectType()
export class WaiverTemplateEntity extends BaseEntity {
  @Field(() => ID)
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Field(() => TeamEntity)
  @ManyToOne(
    () => TeamEntity,
    team => team.waiverTemplates,
    { onDelete: 'CASCADE', eager: true },
  )
  @JoinColumn()
  team: Promise<TeamEntity>;

  @Field(() => ID)
  @Index()
  @Column({ nullable: true })
  teamId: string;

  @Field()
  @Column('json')
  event: Event;

  @Field()
  @Column('json')
  eventDate: EventDate;

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

  @Field({ nullable: true })
  @Column({ nullable: true })
  customerLabel: string;

  @Field(() => CustomEntity, { nullable: true, defaultValue: [] })
  @Column('jsonb', { nullable: true })
  intensity: CustomEntity;

  @Field(() => [CustomEntity], { nullable: true, defaultValue: [] })
  @Column('jsonb', { nullable: true })
  activities: CustomEntity[];

  @Field({ defaultValue: waiverStatus.DRAFT, nullable: false })
  @Column({ default: waiverStatus.DRAFT, nullable: false })
  status: string;

  @Field({ nullable: true })
  @Column({ type: 'varchar', nullable: true })
  title: string;

  @Field({ nullable: true })
  @Column({ nullable: true })
  body: string;

  @Field({ nullable: true })
  @Column({ nullable: true, default: signatureDefaultContent })
  signatureContent: string;

  @Field(() => [String], { nullable: true })
  @Column('simple-array', { nullable: true })
  ageGroup: string[];

  @Field(() => [AdditionalFields], { nullable: false, defaultValue: [] })
  @Column('jsonb', { nullable: true })
  additionalFields: AdditionalFields[];

  @Field()
  eventName: string;
}

And finally, here is the teams table, which also exists on the old branch. This is the code from the new branch, which contains a new OneToMany relationship to the WaiverTemplateEntity.

@Entity({ name: 'teams' })
@ObjectType()
export class TeamEntity extends BaseEntity {
  @Field(() => ID)
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Field()
  @Column('varchar')
  title: string;

  @Field({ nullable: true })
  @Column('varchar', { nullable: true })
  taxID?: string;

  @Field({ nullable: true })
  @Column(simpleJSON, { nullable: true })
  type: CustomEntity;

  @Field({ nullable: true })
  @Column('varchar', { nullable: true })
  description?: string;

  @Field(() => AccountEntity, { nullable: false })
  @OneToOne(
    () => AccountEntity,
    accountEntity => accountEntity.organization,
    { nullable: true, onDelete: 'SET NULL' },
  )
  creator: AccountEntity;

  @Field({ nullable: true })
  @Column({ nullable: true })
  creatorId: string;

  @Field(() => BillingEntity, { nullable: true })
  @OneToOne(
    () => BillingEntity,
    billingEntity => billingEntity.team,
    { cascade: true },
  )
  billingInformation: Promise<BillingEntity>;

  @Field({ nullable: true })
  @Column('varchar', { nullable: true })
  photoURL?: string;

  @Field({ defaultValue: false })
  @Column({ default: false })
  nonProfitFreemium: boolean;

  @AfterLoad()
  updateURLs(): void {
    // add Prefix on retrieval
    this.photoURL = this.photoURL
      ? `${getBucketPrefix(
          AWS_BUCKETS_TYPES.ORGANIZATION_BUCKET_IMAGE,
          'https://',
        )}/${this.photoURL}`
      : null;
  }

  @Field(() => [CardEntity], { nullable: true })
  @OneToMany(
    () => CardEntity,
    cardEntity => cardEntity.holder,
    { cascade: true },
  )
  cards: Promise<CardEntity[]>;

  @Field({ nullable: true, defaultValue: {} })
  @Column(simpleJSON, { nullable: true })
  location?: LocationEntity;

  @Field({ nullable: true, defaultValue: {} })
  @Column(simpleJSON, { nullable: true })
  contact?: ContactEntity;

  @Field({ nullable: true })
  @Column({ nullable: true })
  numberOfEmployees?: string;

  @Field({ nullable: true })
  @Column({ nullable: true })
  stripeId?: string;

  @Field()
  @Column({ type: 'timestamp', default: () => 'CURRENT_TIMESTAMP(6)' })
  @IsDate()
  createdAt: Date;

  @Field()
  @Column({
    type: 'timestamp',
    default: () => 'CURRENT_TIMESTAMP(6)',
    onUpdate: 'CURRENT_TIMESTAMP(6)',
  })
  @IsDate()
  lastUpdatedAt: Date;

  @Field(() => [InvitationEntity])
  @OneToMany(
    () => InvitationEntity,
    invitationEntity => invitationEntity.team,
  )
  invitations: Promise<InvitationEntity[]>;

  @Field(() => [WaiverEntity])
  @OneToMany(
    () => WaiverEntity,
    waiver => waiver.team,
  )
  waivers: Promise<WaiverEntity[]>;

  @Field({ nullable: true })
  @Column({ default: () => 0 })
  credits: number;

  @Field({ nullable: true })
  @Column({ default: () => false })
  autoReload: boolean;

  @Field({ nullable: true })
  @Column({ default: () => 0 })
  autoReloadAmount: number;

  @Field({ nullable: true })
  @Column({ default: () => 0 })
  autoReloadMinAmount: number;

  @Field({ nullable: true })
  @Column({ type: 'float', default: 0.0 })
  fixedWaiverPrice: number;

  @Field(() => [TransactionEntity])
  @OneToMany(
    () => TransactionEntity,
    transaction => transaction.team,
  )
  transactions: Promise<TransactionEntity[]>;

  @Field(() => [WaiverTemplateEntity])
  @OneToMany(
    () => WaiverTemplateEntity,
    waiverTemplate => waiverTemplate.team,
  )
  waiverTemplates: Promise<WaiverTemplateEntity[]>;
}

I know there's a lot of columns in the tables, but the ones to pay attention to are the relationships between the Teams table and the WaiverTemplates table. This is the only thing I changed in the entities, and what I think may be responsible for me being unable to connect to the previous database on this new branch. If you want to see my service, resolver, or modules, please ask. I don't believe they are causing any issues, because if I connect to a new database, everything compiles and works as intended, no errors are thrown. I'm really just looking for any insight on how to debug this problem.

Srujan Srujan · Accepted Answer · 2021-03-04T06:25:24

If anyone is interested in this issue, I resolved the error finally today, at least in respects to the tables above.

When changing the PSQL database with TypeORM, it's better to either create or generate your own migration files with typeorm migration:generate -n [name of migration file] and then typeorm migration:run. The generate command will auto generate an up and down SQL migration to run. You can use npx before this command or access the cli from node_modules, because just running the typeorm command gave me a command not found error.

I then looked inside the generated migration file, and lo and behold, the columns I was adding to the table were not set to be NULL, and thus I had the error of the values for those columns in the previous table being null. I had to manually add NULL to each of those columns for the code to compile. It's weird though, because I updated the entities to have {nullable: true} in the @Column decorators for those fields.

If anyone knows how to work better with changing relations in already existing tables with TypeORM and Nest, please reach out to me. I'm still working on writing the SQL manually for the migration file so that I can change the relationships in three other tables. I'm working with legacy code done poorly, so the relationships were wrong from the beginning.

Updating PSQL Tables with Relationships between Entities Using NestJS, TypeORM, GraphQL

1 Answers