How to INSERT with SELECT values in TypeORM

When executing an INSERT operation in TypeORM, you can use a SELECT statement to provide the values to be inserted. You need to use a QueryBuilder to construct the INSERT statement based on certain conditions or dynamic data. The following is a simple example demonstrating how to combine INSERT and SELECT in TypeORM:

Suppose we have two entities, User and Profile, and we want to insert selected user information (such as id) as a foreign key into the Profile table.

First, ensure that your User and Profile entities are properly defined and associated.

typescript
import {
  Entity,
  PrimaryGeneratedColumn,
  Column,
  OneToOne,
  JoinColumn,
} from 'typeorm';

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

  @Column()
  name: string;

  @OneToOne(() => Profile)
  @JoinColumn()
  profile: Profile;
}

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

  @Column()
  userId: number;
}

Suppose we now want to insert the IDs of all users named 'Alice' into the Profile table.

Here's how to execute the operation using QueryBuilder:

typescript
import { getRepository } from 'typeorm';

async function insertProfileForUser() {
  const userRepository = getRepository(User);
  const profileRepository = getRepository(Profile);

  await profileRepository
    .createQueryBuilder()
    .insert()
    .into(Profile)
    .values(
      userRepository
        .createQueryBuilder()
        .select("user.id", "userId")
        .from(User, "user")
        .where("user.name = :name", { name: 'Alice' })
    )
    .execute();
}

In this example, we first retrieve the repositories for User and Profile, then create a new QueryBuilder for the INSERT operation. The values method takes another QueryBuilder instance that selects the id of users named 'Alice' from the User table. Note that by using .select("user.id", "userId"), we alias the result as userId to ensure it matches the userId column in the Profile table.

When constructing such queries, ensure that the columns returned by your SELECT statement match the columns you are inserting into the target table. In practical applications, you should also consider transaction management, error handling, and performance optimization. If you have specific requirements in your application scenario, you can adjust the basic example above to meet your needs.