How to INSERT with SELECT values in TypeORM

Question

Lets say I have main table Users and a reference table UserRoles such that each User has one Role saved in the column roleId, and every role has an id and a description.

If I want to insert a new User with the role Administrator, the following SQL query would work:

INSERT INTO `users` (`name`, `roleId`) 
VALUES ('John Doe', (SELECT `id` FROM `roles` WHERE `roles`.`description` = 'admin'));

How would I replicate the same in TypeORM having two entities User and Role?

I know I can do the following:

const adminRole = await Role.findOne({description: 'admin'});
const newUser = User.create({name: 'John Doe'});
newUser.role = adminRole;
await newUser.save();

But this would rather be equivalent to SELECT the role into a variable and then using that variable during the INSERT. is there a way to condense this into one query so that the database is hit only once?

I know that I can create an user with the relation like this

User.create({name: 'John Doe', role: {id: 1}});

But I need to know the id for this. If I put the name like the following

User.create({name: 'John Doe', role: {name: 'admin'}});

I get the following error Error: Cannot find alias for relation at type because I have not loaded the relation.

I've found information on how to make an INSERT INTO SELECT statement here, but this is where the whole insert comes from a select, not just a particular column.

Is there a way to emulate this query for insertions? or I'm forced to either do it in two steps (or as many as reference columns as I have) or use the query builder?

Thanks you in advance

Answer 1

TypeOrm doesn't allow that out of box, however you can achive what that in two ways

1. Using Query Builder

const role = await getConnection()
        .createQueryBuilder()
        .select('role')
        .from(Role, 'role')
        .where('role.description = :description', { description: 'admin' })
        .getOne();

    if (!role) {
        console.error('Role not found');
        return;
    }

    const newUser = User.create({ name: 'John Doe', role });

2. Using Transaction

This approach will keep the atomicity intact, and the complete operation is performed in one go (It might actually look like we are hitting the database multiple times, but underneath the typeorm compiles them to a single query to be executed)

import { getManager, getRepository } from 'typeorm';
import { User } from './entity/User';
import { Role } from './entity/Role';

async function createUserWithRole() {
    const entityManager = getManager();
    const roleRepository = getRepository(Role);

    try {
        await entityManager.transaction(async transactionalEntityManager => {
            const adminRole = await roleRepository.findOneOrFail({ description: 'admin' });
            const newUser = transactionalEntityManager.create(User, { name: 'John Doe', role: adminRole });
            await transactionalEntityManager.save(newUser);
        });
        console.log('User created successfully with role.');
    } catch (error) {
        console.error('Error creating user with role:', error);
    }
}

createUserWithRole();