How to insert values into a junction table without knowing the id of the reference table?

Question

I have three tables: releases, dependencies and release_dependencies. Each release can have multiple dependencies which are thus store in a junction table called release_dependencies.

When inserting the dependencies for a release I know the release_id but I don't know the id of a dependency. The dependency_id could already exist in the database or I may have just found that dependency. A dependency has a unique index on the type, name and version and the to be inserted release_dependencies knows this. But I need that index to be turned into an id. Here's an example in Javascript to maybe make my question a bit more clear:

import knex from "knex";
import {randomUUID} from 'crypto';

const db = knex({
    client: 'mysql2',
    debug: true,
    connection: {
        host: 'localhost',
        user: 'root',
        password: 'root',
        database: 'app',
        port: 33061,
        timezone: '+00:00',
    }
});

const releaseId = randomUUID();

const dependencies = [
    {
        id: randomUUID(),
        name: 'cors',
        version: '3.0.5',
        type: 'npm',
    },
    {
        id: randomUUID(),
        name: 'express',
        version: '5.0.4',
        type: 'npm',
    }
];

const releaseDependencies = [
    {
        id: randomUUID(),
        release_id: releaseId,
        dependency_id: 'npm-cors-3.0.5',
        parent_dependency_id: 'npm-express-5.0.4',
        is_dev: false
    },
    {
        id: randomUUID(),
        release_id: releaseId,
        dependency_id: 'npm-express-5.0.4',
        parent_dependency_id: '',
        is_dev: false
    }
];

await db
    .insert(dependencies)
    .into('dependencies')
    .onConflict()
    .ignore();

await db.destroy();

As you can see I'm inserting dependencies which will be ignored if it already exists. But now I want to insert my release_dependencies but it does not know the id of the dependency its referring to. Whats the best way to solve this?

My current solution is making a dependencyLookup from the dependencies table, but that means I have to load all dependencies into memory. I have thought of another solution which is the following:

INSERT INTO `release_dependencies` (`id`, `release_id`, `dependency_id`, `parent_dependency_id`, `is_dev`)
VALUES ('581b4262-b25f-477a-a151-2fa07c37e5a7',
        'a30bc721-6323-4d28-a4b3-a90400e4231a',
        (SELECT `id` FROM `dependencies` WHERE Concat_ws('-', type, name, version) = ?),
        (SELECT `id` FROM `dependencies` WHERE Concat_ws('-', type, name, version) = ?),
        false);

What is the best way to handle my problem?

Answer 1

I'd do it this way:

INSERT INTO `release_dependencies` (`id`, `release_id`, `dependency_id`, `parent_dependency_id`, `is_dev`)
SELECT '581b4262-b25f-477a-a151-2fa07c37e5a7',
       'a30bc721-6323-4d28-a4b3-a90400e4231a',
       id,
       id,
       false
FROM `dependencies`
WHERE (type, name, version) = (?, ?, ?);

That uses a syntax called tuple comparison, where all three columns must match the respective parameter.

If you need to match multiple dependencies, you can use tuple comparison with IN(). Just like any other IN() predicate, you must have as many parameters as you have values.

...
WHERE (type, name, version) IN ( (?, ?, ?), (?, ?, ?), (?, ?, ?) );