How do I keep the "many" side of a one-to-many relationship unique in a TypeORM Model?

Question

I am writing a program which scrapes a web API and gathers a list of cryptocurrency symbols, and their daily price history.

I am running into an issue where, due to the construction of my database tables, if I encounter the same symbol more than once, the program adds a new row to the database which duplicates fully the data from the first time around.

Below are the two tables, as Typeorm entities:

First, the symbol:

@Entity()
export class CryptoSymbol {
  @PrimaryColumn({ type: "int", unique: true })
  id!: number;

  @Column("varchar")
  currency_code!: string;

  @Column("varchar")
  currency_name!: string;

  @Column({ type: "boolean", nullable: true })
  active!: boolean;

  @OneToMany(
    (type) => CryptoDailyData,
    (cryptoDailyData) => cryptoDailyData.cryptoSymbol
  )
  cryptoDailyData!: CryptoDailyData[];
}

Then the DailyData:

@Entity()
export class CryptoDailyData {
  @PrimaryGeneratedColumn()
  id!: number;

  @Column({ type: "datetime" })
  date!: Date;

  @Column({ type: "varchar" })
  foreign_currency_type!: string;

  @Column("float")
  open_USD!: number;

  @Column("float")
  open_foreign_currency!: number;

  @Column("float")
  high_USD!: number;

  @Column("float")
  high_foreign_currency!: number;

  @Column("float")
  low_USD!: number;

  @Column("float")
  low_foreign_currency!: number;

  @Column("float")
  close_USD!: number;

  @Column("float")
  close_foreign_currency!: number;

  @Column("float")
  volume!: number;

  @Column("float")
  market_cap!: number;

  @ManyToOne(
    (type) => CryptoSymbol,
    (cryptoSymbol) => cryptoSymbol.cryptoDailyData
  )
  cryptoSymbol!: CryptoSymbol;
}

For example: if I scrape "BTC" for BitCoin, and I store all of its daily history in the DailyData table, if I run the program again and encounter "BTC" again, it will reproduce the entire history of BTC for a second time.

What I think I'm running into is the fact that the "real" primary key of the DailyData table should be a combination of "Date" and "cryptoSymbolID," since dates can only repeat for different symbols. But I'm not sure you can define that kind of columnal relationship in Typeorm?

Answer 1

I believe you can achieve this by defining a composite unique key.

Since you didn't mention the underline database you're using, I'm going to give you the answer for PostgreSQL. (You may or may not have to change the following code based on your database)

1. Change your date column to only consist of date type, not datetime type like you've defined now.

   @Column({ type: "date" })
   date!: string;
   

2. Specify the join column for your @ManyToOne relationship. (There is already a foreign key column created when you use @ManyToOne but let's remove it and use a one defined by ourselves)

   // This column will keep the reference to your 'CryptoSymbol' entity
   @Column({ type: "bigint" })
   crypto_symbol_id!: int;
   
   @ManyToOne(
     (type) => CryptoSymbol,
     (cryptoSymbol) => cryptoSymbol.cryptoDailyData
   )
   @JoinColumn({ name: "crypto_symbol_id" })
   cryptoSymbol!: CryptoSymbol;
   

3. Create a composite unique key for your entity.

   @Entity()
   @Unique(['crypto_symbol_id', 'date'])
   export class CryptoDailyData {
   

Now if your program tries to insert data for the same crypto symbol on the same day, this will throw you an error.

The updated CryptoDailyData entity will look like below:

@Entity()
@Unique(['crypto_symbol_id', 'date'])
export class CryptoDailyData {
  @PrimaryGeneratedColumn()
  id!: number;

  @Column({ type: "date" })
  date!: string;

  @Column({ type: "varchar" })
  foreign_currency_type!: string;

  @Column("float")
  open_USD!: number;

  @Column("float")
  open_foreign_currency!: number;

  @Column("float")
  high_USD!: number;

  @Column("float")
  high_foreign_currency!: number;

  @Column("float")
  low_USD!: number;

  @Column("float")
  low_foreign_currency!: number;

  @Column("float")
  close_USD!: number;

  @Column("float")
  close_foreign_currency!: number;

  @Column("float")
  volume!: number;

  @Column("float")
  market_cap!: number;

  @Column({ type: "bigint" })
  crypto_symbol_id!: int;

  @ManyToOne(
    (type) => CryptoSymbol,
    (cryptoSymbol) => cryptoSymbol.cryptoDailyData
  )
  @JoinColumn({ name: "crypto_symbol_id" })
  cryptoSymbol!: CryptoSymbol;
}

I also want to point out that this is a database level safeguard. You should also try this on your application. You should check whether there exists a CryptoDailyData record for a particular cryptocurrency for the day, and then determine whether to insert a new record for the day or to do nothing.

Hope this helps you. Cheers 🍻 !!!