Creating the books-api app and for any other application for that matter, during the initial stages we need to populate the database with either real or dummy data. This process is what we call seeding the database and there are several methods to seed a database and one of which will be shown in this article.
We're basing our entire setup from the books-api repo here. So if you want to follow through with this article you can clone the repo and try it yourself.
First, let's understand the structures for tables authors, publishers, and books.
postgres=# \d authors
Table "public.authors"
Column | Type | Collation | Nullable | Default
--------------+-----------------------------+-----------+----------+-------------------
id | uuid | | not null | gen_random_uuid()
name | text | | not null |
profile_link | text | | |
created_at | timestamp without time zone | | | CURRENT_TIMESTAMP
Indexes:
"authors_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "books" CONSTRAINT "fk_author_id" FOREIGN KEY (author_id) REFERENCES authors(id)
postgres=# \d publishers
Table "public.publishers"
Column | Type | Collation | Nullable | Default
------------+-----------------------------+-----------+----------+-------------------
id | uuid | | not null | gen_random_uuid()
name | text | | not null |
site_link | text | | |
created_at | timestamp without time zone | | | CURRENT_TIMESTAMP
Indexes:
"publishers_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "books" CONSTRAINT "fk_publisher_id" FOREIGN KEY (publisher_id) REFERENCES publishers(id)
postgres=# \d books
Table "public.books"
Column | Type | Collation | Nullable | Default
----------------+---------+-----------+----------+-------------------
id | uuid | | not null | gen_random_uuid()
author_id | uuid | | |
publisher_id | uuid | | |
title | text | | not null |
date_published | date | | not null |
isbn | text | | |
page_count | integer | | |
created_at | date | | | CURRENT_TIMESTAMP
img_link | text | | |
Indexes:
"books_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"fk_author_id" FOREIGN KEY (author_id) REFERENCES authors(id)
"fk_publisher_id" FOREIGN KEY (publisher_id) REFERENCES publishers(id)
postgres=#
And our books data is formatted in JSON as such:
const books = [
{
title: "Iron Flame",
date_published: "2023-11-07",
isbn: "978-1-64937-417-2",
page_count: 623,
publisher: "Red Tower Books (Entangled Publishing)",
site_link: "https://www.entangledpublishing.com/about-us",
author: "Rebecca Yarros",
profile_link: "https://en.wikipedia.org/wiki/Rebecca_Yarros",
img_link: "https://en.wikipedia.org/wiki/File:Iron_Flame_Cover_Art.jpg",
},
{
title: "Spare",
date_published: "2023-01-10",
isbn: "978-0-59359-380-6",
page_count: 416,
publisher: "Penguin Random House",
site_link: "https://en.wikipedia.org/wiki/Penguin_Random_House",
author: "Prince Harry, The Duke of Essex",
profile_link: "https://en.wikipedia.org/wiki/Prince_Harry,_Duke_of_Sussex",
img_link: "https://en.wikipedia.org/wiki/File:Spare_cover.jpg",
},
{
title: "Fourth Wing",
date_published: "2023-04-05",
isbn: "978-1-64937-404-2",
page_count: 512,
publisher: "Red Tower Books (Entangled Publishing)",
site_link: "https://www.entangledpublishing.com/about-us",
author: "Rebecca Yarros",
profile_link: "https://en.wikipedia.org/wiki/Rebecca_Yarros",
img_link: "https://en.wikipedia.org/wiki/File:Fourth_Wing_Cover_Art.jpeg",
},
{
title: "The Woman In Me",
date_published: "2023-10-23",
isbn: "978-1-66800-904-8",
page_count: 288,
publisher: "Gallery Books",
site_link: "https://en.wikipedia.org/wiki/Gallery_Publishing_Group",
author: "Britney Spears",
profile_link: "https://en.wikipedia.org/wiki/Britney_Spears",
img_link:
"https://en.wikipedia.org/wiki/File:Britney_Spears_-_The_Woman_in_Me.png",
},
];
Here we will store the values for author and profile_link to the authors table, the publisher and site_link to the publishers table and the rest of the object's values to the books table.
Dissecting the code
To be able to insert the values to the correct table we will need to iterate through each element in the JSON object.
for (const values of books) {
We then initialize author and publishers as empty JSON objects.
let author = {};
let publisher = {};
Next, we're defining the constant reference to the author_info object that takes the author's name and profile link.
const author_info = {
name: values.author,
profile_link: values.profile_link,
};
To avoid duplicate records, we will execute an entities.author.find() query against the database to fetch any existing author name matching values.author.
const findAuthor = await entities.author.find({
fields: ["id", "name"],
where: { name: { like: values.author } },
limit: 1,
});
If a match is found the next lines of code will be skipped otherwise the data will be stored in the database.
if (Object.entries(findAuthor).length === 0) {
author = await entities.author.save({ input: author_info });
console.log("Create author:", author);
}
Similar process happens when evaluating the publisher's data finding a match and skipping the insert.
Next step is to store the book info to the database taking into consideration the data from authors and publishers tables.
const book_info = {
author_id: author.id != null ? author.id : findAuthor[0]["id"],
publisher_id:
publisher.id != null ? publisher.id : findPublisher[0]["id"],
title: values.title,
date_published: values.date_published,
isbn: values.isbn,
page_count: values.page_count,
img_link: values.img_link,
};
const book = await entities.book.save({ input: book_info });
As I was writing this I noticed we will need to add a function to check for duplicates in the isbn value given that this key should be unique for each table. Adding a UNIQUE constraint in the PostgreSQL table should add consistency to the database as well.