Gallery
Share
Explore

To print out the records from the PeanutsBookstore.db database using the sqlite package in Node.js

⁠
image.png
⁠
⁠

To print out the records from the PeanutsBookstore.db database using the sqlite package in Node.js, you can update your script as follows. This example will connect to the database, run a query to select all records from a table (assuming there is a table named books), and then print the results to the console.
Here's the complete code for file1.js:

import sqlite3 from 'sqlite3';
import { open } from 'sqlite';

// Function to get and print all records from the books table
(async () => {
// Open the database
const db = await open({
filename: './PeanutsBookstore.db',
driver: sqlite3.Database
});

// Query all records from the books table
const rows = await db.all('SELECT * FROM books');

// Print each record
rows.forEach((row) => {
console.log(row);
});

// Close the database
await db.close();
})();

Explanation:

Import Dependencies:
sqlite3 is the SQLite driver.
open is a method from the sqlite package to open the database.
Async IIFE:
An Immediately Invoked Function Expression (IIFE) with async to use await inside it.
Open Database:
The open function opens the PeanutsBookstore.db database.
Query Records:
db.all('SELECT * FROM books') fetches all records from the books table.
Print Records:
rows.forEach((row) => console.log(row)) iterates through each row and prints it.
Close Database:
db.close() closes the database connection.

Running the Code:

To run this script, open your terminal, navigate to your project directory, and execute:
bash
Copy code
node file1.js

Ensure your PeanutsBookstore.db contains a table named books. If it doesn't, you'll need to adjust the table name in the query accordingly or create the necessary table and insert some data into it. Here’s an example SQL command to create the books table:
sql
Copy code
CREATE TABLE books (
id INTEGER PRIMARY KEY AUTOINCREMENT,
title TEXT NOT NULL,
author TEXT NOT NULL,
genre TEXT NOT NULL,
price REAL NOT NULL
);

You can use an SQLite client like DB Browser for SQLite to execute the above SQL command and insert some sample data into the books table for testing.

megaphone

Seeding the database


JavaScript file to seed the `PeanutsBookstore.db` database with 200 book records.
This script will create the `books` table if it doesn't exist and insert the records.

`seedDatabase.js`:

import sqlite3 from 'sqlite3'; import { open } from 'sqlite';
(async () => { // Open the database const db = await open({ filename: './PeanutsBookstore.db', driver: sqlite3.Database });
// Create the books table if it doesn't exist await db.exec(` CREATE TABLE IF NOT EXISTS books ( id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, author TEXT NOT NULL, genre TEXT NOT NULL, price REAL NOT NULL ) `);
// Prepare to insert 200 books const insertBook = db.prepare(` INSERT INTO books (title, author, genre, price) VALUES (?, ?, ?, ?) `);
// Sample data const books = []; for (let i = 1; i <= 200; i++) { books.push([ `European History Book ${i}`, `Author ${i}`, `European History`, (Math.random() * 50).toFixed(2) // Random price between 0 and 50 ]); }
// Insert books into the database try { await db.exec('BEGIN TRANSACTION'); for (const book of books) { await insertBook.run(book); } await db.exec('COMMIT'); } catch (error) { await db.exec('ROLLBACK'); console.error('Error inserting books:', error); } finally { // Close the database await db.close(); }
console.log('Database seeded successfully with 200 European history books.'); })(); ```

Explanation:
1. **Import Dependencies:** - Import `sqlite3` as the SQLite driver. - Import `open` from the `sqlite` package to open the database.
2. **Open Database:** - The `open` function opens the `PeanutsBookstore.db` database.
3. **Create Table:** - The `db.exec` method is used to create the `books` table if it doesn't already exist.
4. **Prepare Insert Statement:** - Prepare an SQL insert statement to insert records into the `books` table.
5. **Generate Sample Data:** - A loop is used to generate 200 book records with titles, authors, and a random price between 0 and 50.
6. **Insert Records:** - Use a transaction to insert the book records into the database. If there's an error, the transaction is rolled back to maintain database integrity.
7. **Close Database:** - Finally, the database connection is closed.
### Running the Script:
To run this script, open your terminal, navigate to your project directory, and execute:
```bash node seedDatabase.js ```
Ensure that your environment is set up correctly with Node.js and the required packages installed (`sqlite3` and `sqlite`). This script will create the `books` table if it doesn't exist and populate it with 200 records on the theme of European history.

megaphone

Corrected Version

To address the error where `insertBook.run` is not recognized as a function and the `SQLITE_BUSY` error, we need to make sure that we correctly handle prepared statements with the `sqlite` library.
The `sqlite` library uses promises, so we need to ensure that we are awaiting these properly.
Here's an updated version of the `seed_database.js` script that fixes these issues:
### `seed_database.js`:
```javascript import sqlite3 from 'sqlite3'; import { open } from 'sqlite';
(async () => { // Open the database const db = await open({ filename: './PeanutsBookstore.db', driver: sqlite3.Database });
// Create the books table if it doesn't exist await db.exec(` CREATE TABLE IF NOT EXISTS books ( id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, author TEXT NOT NULL, genre TEXT NOT NULL, price REAL NOT NULL ) `);
// Sample data const books = []; for (let i = 1; i <= 200; i++) { books.push([ `European History Book ${i}`, `Author ${i}`, `European History`, (Math.random() * 50).toFixed(2) // Random price between 0 and 50 ]); }
try { // Start a transaction await db.exec('BEGIN TRANSACTION');
// Insert books into the database for (const book of books) { await db.run( `INSERT INTO books (title, author, genre, price) VALUES (?, ?, ?, ?)`, book ); }
// Commit the transaction await db.exec('COMMIT'); } catch (error) { // Rollback the transaction in case of an error await db.exec('ROLLBACK'); console.error('Error inserting books:', error); } finally { // Close the database await db.close(); }
console.log('Database seeded successfully with 200 European history books.'); })(); ```
### Explanation:
1. **Import Dependencies:** - Import `sqlite3` as the SQLite driver. - Import `open` from the `sqlite` package to open the database.
2. **Open Database:** - The `open` function opens the `PeanutsBookstore.db` database.
3. **Create Table:** - The `db.exec` method is used to create the `books` table if it doesn't already exist.
4. **Generate Sample Data:** - A loop is used to generate 200 book records with titles, authors, and a random price between 0 and 50.
5. **Insert Records with Transaction:** - Use a transaction to insert the book records into the database. If there's an error, the transaction is rolled back to maintain database integrity.
6. **Close Database:** - Finally, the database connection is closed.
### Running the Script:
To run this script, open your terminal, navigate to your project directory, and execute:
```bash node seed_database.js ```
Ensure that your environment is set up correctly with Node.js and the required packages installed (`sqlite3` and `sqlite`). This script will create the `books` table if it doesn't exist and populate it with 200 records on the theme of European history.
Want to print your doc?
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.