Explore

BlossomDB - Managing Flower Data with Node.js and SQLite

In this lab, you will create a Node.js application that uses SQLite to manage a database of flowers. You will implement basic CRUD (Create, Read, Update, Delete) operations and run the application using Visual Studio Code.

## Prerequisites: 1. Visual Studio Code installed. 2. Node.js installed from [nodejs.org](https://nodejs.org/). 3. SQLite and DB Browser for SQLite installed from [sqlitebrowser.org](https://sqlitebrowser.org/). 4. Basic understanding of JavaScript and SQL.

## Step-by-Step Instructions:

### Step 1: Setup Project

1. **Create a Project Directory:** Open a terminal and create a new directory for your project. Navigate into the directory. ```bash mkdir blossomdb-nodejs-sqlite cd blossomdb-nodejs-sqlite ```

2. **Initialize Node.js Project:** Initialize a new Node.js project with default settings. ```bash npm init -y ```

3. **Install Required Packages:** Install the `express` and `sqlite3` packages. ```bash npm install express sqlite3 ```

### Step 2: Create SQLite Database

1. **Open DB Browser for SQLite:** Create a new database named `flowers.sqlite`.

2. **Create Table:** Execute the following SQL command to create the necessary table: ```sql CREATE TABLE flowers ( id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, color TEXT NOT NULL, price REAL NOT NULL ); ```

### Step 3: Setup Express Server

1. **Create `app.js`:** Create an `app.js` file in your project directory with the following content: ```javascript const express = require('express'); const sqlite3 = require('sqlite3').verbose(); const bodyParser = require('body-parser'); const app = express();

// Connect to SQLite database const db = new sqlite3.Database('./flowers.sqlite', (err) => { if (err) { console.error('Could not connect to database', err); } else { console.log('Connected to database'); } });

// Middleware app.use(bodyParser.urlencoded({ extended: true })); app.use(express.json());

// Routes app.get('/', (req, res) => { res.send('Welcome to BlossomDB!'); });

// Create a flower (Insert) app.post('/flowers', (req, res) => { const { name, color, price } = req.body; db.run(`INSERT INTO flowers (name, color, price) VALUES (?, ?, ?)`, [name, color, price], function (err) { if (err) { return console.error(err.message); } res.send(`Flower added with ID: ${this.lastID}`); }); });

// Read all flowers app.get('/flowers', (req, res) => { db.all(`SELECT * FROM flowers`, [], (err, rows) => { if (err) { throw err; } res.json(rows); }); });

// Update a flower app.put('/flowers/:id', (req, res) => { const { id } = req.params; const { name, color, price } = req.body; db.run(`UPDATE flowers SET name = ?, color = ?, price = ? WHERE id = ?`, [name, color, price, id], function (err) { if (err) { return console.error(err.message); } res.send(`Flower with ID ${id} updated.`); }); });

// Delete a flower app.delete('/flowers/:id', (req, res) => { const { id } = req.params; db.run(`DELETE FROM flowers WHERE id = ?`, [id], function (err) { if (err) { return console.error(err.message); } res.send(`Flower with ID ${id} deleted.`); }); });

// Start the server const PORT = process.env.PORT || 3000; app.listen(PORT, () => { console.log(`Server is running on port ${PORT}`); }); ```

Step 4: Run and Test the Application

1. **Start the Server:** Start your server by running the following command in the terminal: ```bash node app.js ```

2. **Access the Application:** Open your web browser and navigate to `http://localhost:3000`.

3. **Test CRUD Operations:**

- **Create:** Use a tool like Postman to send a POST request to `http://localhost:3000/flowers` with the following JSON body to add a new flower: ```json { "name": "Rose", "color": "Red", "price": 1.99 } ```

- **Read:** Navigate to `http://localhost:3000/flowers` to see the list of flowers. Alternatively, you can use Postman to send a GET request.

- **Update:** Use Postman to send a PUT request to `http://localhost:3000/flowers/1` with the following JSON body to update the flower with ID 1: ```json { "name": "Tulip", "color": "Yellow", "price": 2.99 } ```

- **Delete:** Use Postman to send a DELETE request to `http://localhost:3000/flowers/1` to delete the flower with ID 1.

### Step 5: Document Your Work

1. **Create a Word Document:** Document all the steps taken, including code snippets, explanations, and screenshots of the application at different stages (form submission, list display, update, delete).

2. **Include Screenshots:** - Project directory structure in Visual Studio Code. - Running application in the browser. - Data in the SQLite database viewed with DB Browser for SQLite.

By completing this lab, you will have a solid understanding of how to set up a Node.js application with SQLite and perform CRUD operations, laying a strong foundation for more advanced web development tasks.

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.