Optimize createMany with m2m connect

[IlyaSemenov]

Problem

createMany creating new m2m relations runs a separate SQL query for each related object.

Steps to reproduce

class AuthorTable extends BaseTable {
  readonly table = "author"

  columns = this.setColumns((t) => ({
    id: t.identity().primaryKey(),
    name: t.string(1, 100)
  }))

  relations = {
    books: this.hasAndBelongsToMany(() => BookTable, {
      primaryKey: "id",
      foreignKey: "author_id",
      associationPrimaryKey: "id",
      associationForeignKey: "book_id",
      joinTable: "author_book_rel",
    }),
  }
}

class BookTable extends BaseTable {
  readonly table = "book"

  columns = this.setColumns((t) => ({
    id: t.identity().primaryKey(),
    title: t.text(1, 200),
  }))

  relations = {
    authors: this.hasAndBelongsToMany(() => AuthorTable, {
      primaryKey: "id",
      foreignKey: "book_id",
      associationPrimaryKey: "id",
      associationForeignKey: "author_id",
      joinTable: "author_book_rel",
    }),
  }
}

const db = orchidORM(
  {
    databaseURL: process.env.DATABASE_URL,
    log: true,
  },
  tables: {
    author: AuthorTable,
    book: BookTable,
  }
)

await db.$adapter.query(`
  create table "author" (
    id serial primary key,
    name varchar(100) not null
  );
  create table book (
    id serial primary key,
    title varchar(200) not null
  );
  create table author_book_rel (
    author_id integer not null,
    book_id integer not null
  );
`)

await db.author.createMany([
  { id: 1, name: "George Orwell" },
  { id: 2, name: "Mark Twain" },
])
await db.book.createMany([
  { id: 1, title: "1984", authors: { connect: [{ id: 1 }] } },
  { id: 2, title: "Tom Sawyer", authors: { connect: [{ id: 2 }] } },
  {
    id: 3,
    title: "Imaginary Book",
    authors: { connect: [{ id: 1 }, { id: 2 }] },
  },
])

Actual result

Each m2m pair of ids is pulled with a standalone query:

(1.4ms) INSERT INTO "author"("id", "name") VALUES ($1, $2), ($3, $4) RETURNING * [1, 'George Orwell', 2, 'Mark Twain']
(0.2ms) BEGIN
(0.7ms) INSERT INTO "book"("id", "title") VALUES ($1, $2), ($3, $4), ($5, $6) RETURNING * [1, '1984', 2, 'Tom Sawyer', 3, 'Imaginary Book']
(0.6ms) SELECT "authors"."id" FROM "author" AS "authors" WHERE "authors"."id" = $1 LIMIT $2 [1, 1]
(0.6ms) SELECT "authors"."id" FROM "author" AS "authors" WHERE "authors"."id" = $1 LIMIT $2 [2, 1]
(0.8ms) SELECT "authors"."id" FROM "author" AS "authors" WHERE "authors"."id" = $1 LIMIT $2 [1, 1]
(1.0ms) SELECT "authors"."id" FROM "author" AS "authors" WHERE "authors"."id" = $1 LIMIT $2 [2, 1]
(0.4ms) INSERT INTO "author_book_rel"("book_id", "author_id") VALUES ($1, $2), ($3, $4), ($5, $6), ($7, $8) [1, 1, 2, 2, 3, 1, 3, 2]
(3.9ms) COMMIT

Expected

I expect m2m connections to be retrieved and inserted with a single query (I removed placeholders and aliases for simplicity):

INSERT INTO "author_book_rel"("book_id", "author_id") VALUES
  (1, (SELECT id FROM "author" WHERE id=1 LIMIT 1)),
  (2, (SELECT id FROM "author" WHERE id=2 LIMIT 1)),
  (3, (SELECT id FROM "author" WHERE id=1 LIMIT 1)),
  (3, (SELECT id FROM "author" WHERE id=2 LIMIT 1))

Additional suggestion: single filter for multiple related rows

I believe in most cases there is no need to provide multiple queries for pulling related rows. What I mean is, typically the related rows could be referred with a single query/filter (per each new row):

await db.book.createMany([
  { id: 1, title: "1984", authors: { connect: { id: 1 } },
  { id: 2, title: "Tom Sawyer", authors: { connect: { id: 2 } },
  {
    id: 3,
    title: "Imaginary Book",
    authors: { connect: { id: [1, 2] } },
  },
])

and then:

INSERT INTO "author_book_rel"("book_id", "author_id")
  select 1, author.id from author where id=1
  union select 2, author.id from author where id=2
  union select 3, author.id from author where id in (1,2)

The ORM could detect that by checking connect to be either object or array (alternatively, it could be a new keyword like connectMany). If there are mixed types, it could run two queries at most (or simply use union for all cases altogether).