how do I get the insertId for my test to work

Question

I'm doing a backend application without frameworks or ORM for study, and I'm using the mysql2 package for Node and when I'm going to add a book to the table, I can't because the error column author_id cannot be null despite it being filled. Researching I understood that I need to get the insertion id of author and for bookDAO but I couldn't do it. can you help me?

AnthorDAO

import Author from "../models/Author.js"

export default class AuthorDAO {
  constructor(conn) {
    this._conn = conn
  }

  async add(author) {
    if (!(author instanceof Author)) {
      throw new Error(`The object is not an author type`)
    }
    try {
      const authors = await this._conn.query(`SELECT * FROM author`)

      if (authors.some(a => a.email === author.email)) {
        throw new Error(`The author already exists`)
      }
      await this._conn.query(`INSERT INTO author (name, email) VALUES (?, ?)`, [author.name, author.email])
    } catch (error) {
      throw new Error(error)
    }
  }

class Author

export default class Author {
  constructor(name, email) {
    this.name = name
    this.email = email
    this.date = new Date()
  }


  set name(name) {
    if (isEmpty(name) || isNull(name)) throw new Error(`The name field needs to be filled`)
    this._name = name
  }

  set email(email) {
    if (isEmpty(email) || !email.match(emailFormat))
      throw new Error(`The email field must be filled in with a valid format`)
    this._email = email
  }

  get name() {
    return this._name
  }
  get email() {
    return this._email
  }
}

bookDAO

import Book from "../models/Book.js"

export default class BookDAO {
  constructor(conn) {
    this._conn = conn
  }

 async add(book) {
    if (!(book instanceof Book)) {
      throw new Error(`The Object is not of the Book type`)
    }
    try {
      const books = await this._conn.query(`SELECT * FROM book`)

      if(books.some(b => b.title === book.title)){
        throw new Error(`There is already a book registration with that title!`)
      }
      await this._conn.query(`INSERT INTO book (
        author_id, category_id, title, resume, summary, number_of_pages,
        isbn, edition, price) VALUES 
        (?, ?, ?, ?, ?, ?, ?, ?, ?)`, [book.author.id, book.category.id, book.title, book.title, book.summary, book.number_of_pages,
          book.isbn, book.edition, book.price])
    } catch(error){
      throw new Error(error)
    }
}

test with jest

 it("Successfully adding a book", async () => {
    const categoryDAO = new CategoryDAO(conn)
    const authorDAO = new AuthorDAO(conn)
    const bookDAO = new BookDAO(conn)
    const author = new Author("Ana", "Ana@gmail.com")
    const category = new Category("Design")
    await categoryDAO.add(category)
    await authorDAO.add(author)
    const book = new Book(
      author,
      category,
      "Design UX/UI",
      "This book is about Design",
      "Summary",
      40,
      "978-85-13196-08-9",
      50,
      "12"
    )
    await bookDAO.add(book)
    expect(book).toBeDefined()
  })

tables

const table_author = `CREATE TABLE IF NOT EXISTS author(
                id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(50) NOT NULL,
                email VARCHAR(50) NOT NULL UNIQUE KEY,
                date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
              )`

const table_book = `CREATE TABLE IF NOT EXISTS book (
                id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
                title VARCHAR(50) NOT NULL UNIQUE KEY,
                resume VARCHAR(500) NOT NULL,
                summary VARCHAR(500) NOT NULL,
                number_of_pages INTEGER UNSIGNED NOT NULL,
                isbn VARCHAR(17) NOT NULL UNIQUE KEY,
                author_id INTEGER NOT NULL,
                category_id INTEGER NOT NULL,
                edition INTEGER UNSIGNED NOT NULL,
                price FLOAT UNSIGNED NOT NULL,
                time_registration TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
                FOREIGN KEY (author_id) REFERENCES author(id),
                FOREIGN KEY (category_id) REFERENCES category(id)
                ON DELETE CASCADE
              )`

log

   Error: Column 'author_id' cannot be null

      24 |           book.isbn, book.edition, book.price])
      25 |     } catch(error){

class Author has no id, traversing though.. the query is expecting `book.author.id`, which if you debug only has `book.author.name` and `book.author.email` — Lawrence Cherone, Dec 05 '20 at 21:04
After inserting the author, do you need to retrieve the [last inserted ID](https://stackoverflow.com/questions/31371079/retrieve-last-inserted-id-with-mysql) and populate that into the `author` variable, so it is accessible when you attempt to insert the book record? — kmoser, Dec 05 '20 at 21:08
you need to return insertId from `await this._conn.query(\`INSERT INTO author\`` (or the whole author object), i.e `const author = await authorDAO.add(new Author("Ana", "Ana@gmail.com"))` — Lawrence Cherone, Dec 05 '20 at 21:08
@LawrenceCherone thanks! But I still haven't fully understood it. Do you have a practical example? — Laura, Dec 05 '20 at 22:00
I believe so @kmoser . But I wanted to do it as a method of my `Author` class — Laura, Dec 05 '20 at 22:02
@Laura Yes, that would be the right place to do it, since that's where the ID is being generated. The link in my previous comment shows an example of how to do that. — kmoser, Dec 05 '20 at 22:17
@kmoser I don't know which method to use here, but would that be the way? `const result = await this._conn.query("SELECT * FROM authors ORDER BY id desc LIMIT 1"; user.notThatWhichMethodHere(result.insertId)` — Laura, Dec 05 '20 at 22:38
@Laura No, you have to do this as part of the `INSERT`, not a `SELECT`. Try `await this._conn.query('INSERT INTO author (name, email) VALUES (?, ?)', [author.name, author.email], function(err, result, fields) { author.id = result.insertId } )`. — kmoser, Dec 05 '20 at 23:04

score 0 · Accepted Answer · answered Dec 10 '20 at 01:49

With the help above, I was able to solve it. As my query method only received two parameters, the callbak function does not work, so I did it as follows:

 async add(author) {
    if (!(author instanceof Author)) {
      throw new Error(`The object is not an author type`)
    }
    try {
      const authors = await this._conn.query(`SELECT * FROM author`)

      if (authors.some(a => a.email === author.email)) {
        throw new Error(`The author already exists`)
        
      }
      const result = await this._conn.query(
        "INSERT INTO author (name, email) VALUES (?, ?)",
        [author.name, author.email])
            author.id = result.insertId
            console.log(result.insertId)
           
    } catch (error) {
      throw new Error(error)
    }
  }

how do I get the insertId for my test to work

1 Answers1