# Database and CRUD Operations

**Quick Summary:** Build a complete database with CRUD operations (Create, Read, Update, Delete) to manage a book list using Luna Park.

---

## 1. Project Architecture

1. Create two separate folders:
   - **Backend**: for database and routes
   - **Frontend**: for user interface

*In this tutorial, we start with the backend.*

## 2. Creating the Database

### Initialize the database
1. Create a new database named **db-books**
2. The database contains by default:
   - **ID**: unique identifier
   - **created_at**: automatic creation date

### Add custom columns
1. Add a **title** column (type: string)
2. Add an **author** column (type: string)
3. Add a few **rows** with test data

> In 15 seconds, your database is ready to use!

## 3. API Routes - General Concept

**Routes** are URLs that allow you to:
- **Retrieve** data (GET)
- **Send** new data (POST)
- **Modify** existing data (PUT)
- **Delete** data (DELETE)

We'll create 4 routes corresponding to CRUD operations.

## 4. GET Route - Retrieve All Books

### Create the get-books route
1. Create a new route named **get-books**
2. Set the **method**: GET

### Simple method: dbfind
1. Create a **dbfind** node
2. Select the **db-books** table
3. The result returns an array of **rows**
4. Connect the result to the **response**
5. Connect the execution thread between dbfind and the route
6. Test with **Execute** and Ctrl+Space to visualize

### Advanced method: dbfrom with query
1. Create a **dbfrom** node
2. Select the **db-books** table
3. Add a **select** to choose fields:
   - `id`
   - `title`
   - `author`
4. Add a **dbexecute**
5. Test with Ctrl+Space
6. Connect the result to the **response**

### Type the response (best practice)
1. Define the expected response type:
   - **Array of objects**
   - Each object contains: `id`, `title`, `author`
2. Advantage: automatic type checking
3. If a field is missing or extra → **missingProperty** error

## 5. POST Route - Create a New Book

### Create the create-books route
1. Create a new route named **create-books**
2. Verify the **method** is POST

### Define the body (incoming data)
In **Route Input**, add a **body** containing:
- **title** (string): book title
- **author** (string): book author

> This data will come from frontend inputs

### Insert into database
1. Pull a wire from the body
2. Create a **dbinsert** node
3. Select the **db-books** table
4. Connect the **body** to the **data** field

### Return updated state (optional but recommended)
1. Add a **dbfind** after insertion
2. Select the **db-books** table
3. Connect the result to the **response**
4. Copy the response type from **get-books** (right-click → copy)
5. Paste into **create-books** response

## 6. DELETE Route - Delete a Book

### Create the delete-books route
1. Create a new route named **delete-books**
2. Change the **method** to DELETE
3. Paste the response type (same as get-books)

### Define the ID to delete
In **Route Input**, add a **body** containing:
- **id** (string): identifier of the book to delete

### Delete by ID
1. Pull a wire from delete-books
2. Create a **delete by ID** node
3. Select the **db-books** table
4. Connect the ID:
   - Pull a wire from the **body**
   - Create a **get property**
   - Select the **id** property
   - Connect to the ID field of delete by ID

### Return updated state
1. Add a **dbfind** after deletion
2. Select the **db-books** table
3. Connect the result to the **response**

## 7. PUT Route - Update an Existing Book

### Create the update-books route
1. Create a new route named **update-books**
2. Change the **method** to PUT
3. Paste the response type

### Define the body
In **Route Input**, add a **body** containing:
- **id** (string): book to modify
- **title** (string): new title
- **author** (string): new author

### Update by ID
1. Pull a wire from update-books
2. Create a **DBUpdateByID** node
3. Select the **db-books** table

### Handle update data
1. For the **ID**:
   - Create a **spread** from the body
   - Select **id**
   - Connect to the ID field

2. For the **data to modify**:
   - Create a **create object**
   - Add **title** and **author** properties
   - Create a **spread** from the body
   - Connect **title** and **author** to the object
   - Connect the object to the **update** field

### Return updated state
1. Add a **dbfind** after the update
2. Select the **db-books** table
3. Connect the result to the **response**

## Summary of Nodes Used

| Node | Usage |
|------|-------|
| **dbfind** | Retrieve all data from a table |
| **dbfrom + select** | Flexible query with field selection |
| **dbexecute** | Execute a dbfrom query |
| **dbinsert** | Insert a new entry |
| **delete by ID** | Delete a specific entry |
| **DBUpdateByID** | Modify an existing entry |
| **get property** | Extract a property from an object |
| **spread** | Destructure an object |
| **create object** | Create a new object |

## HTTP Methods

- **GET**: retrieve data
- **POST**: create new data
- **PUT**: modify existing data
- **DELETE**: delete data

## Practical Tips

- **Always type responses** to avoid structure errors
- **Return the current state** of the database after each modification
- Use **Ctrl+Space** to visualize results during development
- The **ID** is the key to precisely identify each element
- The **body** transmits data from frontend to backend
- Always verify the **HTTP method** is correct for each route

---