如何获取insertId以使我的测试工作

Question

我正在做一个没有框架或ORM的后端应用程序来进行研究，我正在为Node使用mysql2包，当我要向表中添加一本书时，我不能，因为错误column author_id cannot be null尽管已经填充。研究中我知道我需要获得author和bookDAO的插入id，但我做不到。你能帮我吗？

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)
}
}

类作者

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)
}
}

使用jost 进行测试

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", "[e-mail]")
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()
})

表格

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
)`

日志

Error: Column 'author_id' cannot be null
24 |           book.isbn, book.edition, book.price])
25 |     } catch(error){

Answer 1

在上面的帮助下，我解决了这个问题。由于我的query方法只收到了两个参数，callbak函数不起作用，所以我按如下方式做了：

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)
}
}