Inserting into many-to-many relationship table with a returned id

Question

Take the following tables

CREATE TABLE album(
  id SERIAL PRIMARY KEY NOT NULL,
  duration INTEGER NOT NULL
);

CREATE TABLE genre(
  id SERIAL PRIMARY KEY NOT NULL,
  name VARCHAR(32) NOT NULL
);

CREATE TABLE album_genre(
  album_id REFERENCES album(id),
  genre_id REFERENCES genre(id)
);

I have the following data to be inserted

const album = {
  duration: 12345
  genre1Id: 1,
  genre2Id: 2,
  genre3Id: 3
}

How could I construct a SQL query that inserts the album data and then inserts into album_genre using the genre id's and the inserted album's id

Answer 1

In Postgres, you could construct the query using inserts in CTEs. The following works for the situation you describe:

with data(duration, genre1Id, genre2Id, genre3Id) as (
      values(12345, 1, 2, 3)
     ),
     i as (
      insert into albums(duration)
          select duration
          from data
          returning *
     )
insert into album_genre (album_id, genre_id)
    select i.id, v.genre
    from i join
         data d
         on i.duration = d.duration, lateral
         (values (d.genre1Id), (d.genre2Id), (d.genre3Id)) v(genre);

For multiple rows, the same construct works, but assumes that duration is unique. You need a unique identifier to match the inserted rows with the original data.