I'm in a pretty common situation, but I'm struggling to figure out exactly how to achieve what I want by looking at Knex's documentation.

I have a set of entities with a one-to-many relationship to another set of entities. In the relational model, this is usually represented by a table containing a composite key, referencing the keys of the two related tables.

I'll provide an example to clarify. Let's say we have books, each of which can belong to many genres. I want to create a function that creates a book by its name, user, and a set of genres (and returns the created book with all properties; not just the id e.g.). If a genre doesn't already exist, we insert it, or if it does exist, we find it and retrieve its ID.

I want all this to happen within a single transaction - so if at any point something fails, and we've already added several genres to the book, we delete both the genres and the book.

How can I model this scenario with Knex?

Here's my attempt:

async create(input: {
    name: string;
    user: User;
    genres: string[];
  }): Promise<Book | undefined> {
    return await knex
      .transaction(async (trx) => {
        const [insertedBook] = await trx<Book>("books").insert(
          {
            name: input.name,
            user_id: input.user.id,
          },
          ["id", "name", "user_id", "created_at", "updated_at"]
        );

        input.genres.forEach(async (g) => {
          const { id } = await this.gs.findOrCreate(g);

          await trx<BookGenre>("book_genres").insert({
            book_id: insertedBook.id,
            genre_id: id,
          });
        });

        return insertedBook;
      })
      .then((ib: Book) => ib)
      .catch((error) => {
        console.error("Creating a book with genres failed", error);
        return undefined;
      });
  }

I'm in a pretty common situation, but I'm struggling to figure out exactly how to achieve what I want by looking at Knex's documentation.

I have a set of entities with a one-to-many relationship to another set of entities. In the relational model, this is usually represented by a table containing a composite key, referencing the keys of the two related tables.

I'll provide an example to clarify. Let's say we have books, each of which can belong to many genres. I want to create a function that creates a book by its name, user, and a set of genres (and returns the created book with all properties; not just the id e.g.). If a genre doesn't already exist, we insert it, or if it does exist, we find it and retrieve its ID.

I want all this to happen within a single transaction - so if at any point something fails, and we've already added several genres to the book, we delete both the genres and the book.

How can I model this scenario with Knex?

Here's my attempt:

async create(input: {
    name: string;
    user: User;
    genres: string[];
  }): Promise<Book | undefined> {
    return await knex
      .transaction(async (trx) => {
        const [insertedBook] = await trx<Book>("books").insert(
          {
            name: input.name,
            user_id: input.user.id,
          },
          ["id", "name", "user_id", "created_at", "updated_at"]
        );

        input.genres.forEach(async (g) => {
          const { id } = await this.gs.findOrCreate(g);

          await trx<BookGenre>("book_genres").insert({
            book_id: insertedBook.id,
            genre_id: id,
          });
        });

        return insertedBook;
      })
      .then((ib: Book) => ib)
      .catch((error) => {
        console.error("Creating a book with genres failed", error);
        return undefined;
      });
  }

• javascript
• database
• knex.js

1 Answer 1

You are almost there! Here is your corrected code where I have added in the .transacting(trx)

async create(input: {
    name: string;
    user: User;
    genres: string[];
  }): Promise<Book | undefined> {
    return await knex
      .transaction(async (trx) => {
        const [insertedBook] = await knex("books").insert({
            name: input.name,
            user_id: input.user.id,
          }) // removed what was here. since these are not raw statements, knex will handle sanitation for you anyways.
        .transacting(trx);

        input.genres.forEach(async (g) => {
          const { id } = await this.gs.findOrCreate(g);

          await knex("book_genres").insert({
            book_id: insertedBook.id,
            genre_id: id,
          })
          .transacting(trx);
        });

        return insertedBook;
      })
      .then((ib: Book) => ib)
      .catch((error) => {
        console.error("Creating a book with genres failed", error);
        return undefined;
      });
  }

Oh, you may have to add back the types again.