Basic CRUD: Update and Delete in Cloudflare Workers and D1

Previously in this course, we covered Basic CRUD: Create and Read in Cloudflare Workers and D1, where we established how to persist and retrieve data. This lesson adds the final pieces of the CRUD puzzle: Updates and Deletions.

Mastering these operations is critical because real-world applications are rarely static. Whether you are letting a user edit their profile or removing an expired item from your database, you need reliable, secure SQL commands.

Writing Secure SQL Updates and Deletions

When you perform Updates or Deletions in a Database, you are modifying or removing state. Unlike simple reads, these operations carry the risk of unintended data loss.

The UPDATE Query

The UPDATE statement modifies existing records based on a condition. You must always include a WHERE clause; if you omit it, SQL will update every single row in your table, which is almost certainly not what you intended.

SQL
UPDATE items SET name = 'New Name', status = 'active' WHERE id = 1;

The DELETE Query

The DELETE statement removes records. Just like with updates, the WHERE clause is your safety net. Without it, you will wipe your entire table.

SQL
DELETE FROM items WHERE id = 1;

Worked Example: Updating and Deleting in Workers

In our project, we are managing a list of tasks. Let's build a Worker endpoint that handles both PUT (for updates) and DELETE requests.

JAVASCRIPT
export default {
  async fetch(request, env) {
    const { method } = request;
    const url = new URL(request.url);
    const id = url.pathname.split(CE9178">'/').pop(); // Extract ID from URL

    try {
      if (method === CE9178">'PUT') {
        const { name } = await request.json();
        const result = await env.DB.prepare(
          "UPDATE tasks SET name = ? WHERE id = ?"
        ).bind(name, id).run();
        
        return new Response(JSON.stringify({ success: result.success }));
      }

      if (method === CE9178">'DELETE') {
        const result = await env.DB.prepare(
          "DELETE FROM tasks WHERE id = ?"
        ).bind(id).run();
        
        return new Response(JSON.stringify({ success: result.success }));
      }
    } catch (e) {
      return new Response(JSON.stringify({ error: e.message }), { status: 500 });
    }
  }
};

Handling Database Errors Gracefully

Notice the try...catch block. Database operations can fail for many reasons: a lost connection, a schema mismatch, or a constraint violation (like trying to delete a row that another table depends on).

Always catch these errors and return a meaningful HTTP status code (like 500 Internal Server Error). Never leak raw SQL error messages to the end user in production; log them to the console instead for your own debugging.

Hands-on Exercise

1. Modify your schema: Ensure your tasks table has an id column defined as PRIMARY KEY.
2. Add the logic: Update your existing index.js Worker file to include the PUT and DELETE logic provided above.
3. Test: Use curl to test the operations:
   • curl -X PUT -d '{"name": "Updated Task"}' http://localhost:8787/tasks/1
   • curl -X DELETE http://localhost:8787/tasks/1

Common Pitfalls

• Forgetting the WHERE clause: Always double-check that your UPDATE or DELETE has a filter. If you're unsure, run the SELECT query with the same WHERE clause first to verify exactly which rows will be affected.
• Assuming rows exist: Just because you send a DELETE request for id=999 doesn't mean that ID exists. D1 won't throw an error for a non-existent row, it will simply report zero rows affected. Check the meta.changes property in the result object if you need to confirm the row was actually found.
• SQL Injection: Never concatenate strings directly into your SQL. Always use bind() (as shown in our example) to pass variables safely.

FAQ

Q: Does DELETE remove the data permanently? A: Yes, it is removed from the database storage. If you need to keep data for auditing, consider a "soft delete" strategy where you add an is_deleted boolean column instead.

Q: What is the difference between run() and first() in D1? A: first() retrieves a single record, while run() executes a query and returns metadata about the operation (like how many rows were affected). Use run() for UPDATE and DELETE.

Recap

In this lesson, we covered the critical destructive and transformative aspects of CRUD. By mastering SQL Updates and Deletions, you have completed the core functionality required for any data-driven application. We also emphasized the importance of error handling, ensuring your Database interactions remain robust.

Up next: We will discuss how to optimize these operations by learning about Managing Database Connections.