INSERT Using Sequelize Queries

Understanding the Problem

We need to create a POST route that will insert new tree data into our database. Think of this like adding a new card to a deck - we need to make sure all the required information is there and properly formatted before we add it.

Expected Input Example:

{
    "name": "My Big Tree",
    "location": "My Backyard",
    "height": 123.4,
    "size": 57.9
}
    

Expected Output Example:

{
    "status": "success",
    "message": "Successfully created new tree",
    "data": {
        "id": 7,
        "tree": "My Big Tree",
        "location": "My Backyard",
        "heightFt": 123.4,
        "groundCircumferenceFt": 57.9,
        "updatedAt": "2025-02-17T...",
        "createdAt": "2025-02-17T..."
    }
}
    

Planning the Solution

1. Extract tree data from request body
2. Map request fields to database columns
3. Create new tree record using Sequelize
4. Handle potential errors (duplicates, missing fields)
5. Return appropriate response

Solution

File Location: server/routes/trees.js

// POST route to create a new tree
router.post('/', async (req, res, next) => {
    // Extract data from request body
    const { name, location, height, size } = req.body;

    try {
        // Create new tree record
        // Note the mapping between request fields and database columns
        const tree = await Tree.create({
            tree: name,               // 'name' in request becomes 'tree' in DB
            location: location,
            heightFt: height,         // 'height' becomes 'heightFt'
            groundCircumferenceFt: size  // 'size' becomes 'groundCircumferenceFt'
        });

        // Return success response with new tree data
        res.json({
            status: "success",
            message: "Successfully created new tree",
            data: tree
        });
    } catch (err) {
        // Handle potential errors
        next({
            status: "error",
            message: "Could not create new tree",
            details: err.errors ? err.errors.map(item => item.message) : err.message
        });
    }
});
    

Real World Application

This pattern is used in many real-world scenarios:

• Creating new user accounts in a registration system
• Adding new products to an e-commerce platform
• Submitting form data in a contact management system
• Creating new posts in a social media application

Field Mapping Explanation

Think of field mapping like translation between two languages. In our case:

Testing the Implementation

You can test this implementation in several ways:

1. Run the test suite: npm test test/phase-02-spec.js
2. Use Postman to send POST requests with various data
3. Try creating duplicate trees to test error handling
4. Attempt to create trees with missing fields

Common Pitfalls

Watch out for these common issues:

• Forgetting to handle asynchronous operations with async/await
• Not properly mapping between request fields and database columns
• Forgetting to handle potential database errors
• Not validating input data before attempting to create records

Advanced Implementation

A more sophisticated version might include:

router.post('/', async (req, res, next) => {
    // Input validation middleware
    const validateTree = (data) => {
        const errors = [];
        if (!data.name) errors.push("Name is required");
        if (!data.height || data.height <= 0) errors.push("Valid height is required");
        if (!data.size || data.size <= 0) errors.push("Valid size is required");
        return errors;
    };

    try {
        // Validate input
        const validationErrors = validateTree(req.body);
        if (validationErrors.length > 0) {
            throw new Error(validationErrors.join(', '));
        }

        // Normalize data
        const treeData = {
            tree: req.body.name.trim(),
            location: (req.body.location || 'Unknown').trim(),
            heightFt: parseFloat(req.body.height),
            groundCircumferenceFt: parseFloat(req.body.size)
        };

        // Create with transaction
        const tree = await sequelize.transaction(async (t) => {
            return await Tree.create(treeData, { transaction: t });
        });

        // Return success response
        res.status(201).json({
            status: "success",
            message: "Successfully created new tree",
            data: tree
        });
    } catch (err) {
        next(err);
    }
});
    

Practice Exercises

To better understand this concept, try these exercises:

1. Add validation to ensure tree names are unique
2. Implement a bulk create route that can add multiple trees at once
3. Add field transformation (e.g., converting meters to feet before saving)
4. Create a more detailed error reporting system