Basic CRUD: Create and Read in Cloudflare Workers and D1

Previously in this course, we covered Schema Design for D1: Managing SQL Migrations with Wrangler, where you defined the structure of your data. Now that your tables exist, it is time to make them useful by building the "Create" and "Read" parts of your CRUD (Create, Read, Update, Delete) operations.

In this lesson, we will use the Cloudflare Workers runtime to interact with your D1 database, injecting data via POST requests and fetching it back with GET requests.

Understanding the D1 Binding

To interact with your database, your Worker uses a "binding." When you configured your wrangler.toml file in the Introduction to D1 SQL Database: Serverless SQLite for Workers lesson, you provided a variable name (like DB). This binding is an object injected into your Worker's context, providing methods like prepare() and bind() to execute SQL.

Writing Data: The Create Operation

To insert data, we parse the incoming JSON body from an HTTP request and use a parameterized SQL query. Parameterized queries are mandatory—they prevent SQL injection by separating the query logic from the user-provided data.

JAVASCRIPT
export default {
  async fetch(request, env) {
    if (request.method === "POST") {
      const { name, email } = await request.json();
      
      // Execute the insert statement
      await env.DB.prepare("INSERT INTO users(name, email) VALUES (?, ?)")
        .bind(name, email)
        .run();
        
      return new Response("User created successfully!", { status: 201 });
    }
  }
};

Reading Data: The Read Operation

Reading data involves selecting rows and returning them as a JSON response. The D1 all() method is the most efficient way to retrieve multiple records.

JAVASCRIPT
export default {
  async fetch(request, env) {
    if (request.method === "GET") {
      // Query all users
      const { results } = await env.DB.prepare("SELECT * FROM users").all();
      
      // Return the result set as JSON
      return new Response(JSON.stringify(results), {
        headers: { "Content-Type": "application/json" }
      });
    }
  }
};

Worked Example: A Combined CRUD Worker

Let's combine these into a single, functional endpoint. We will use a switch statement to handle different request methods.

JAVASCRIPT
export default {
  async fetch(request, env) {
    const { method } = request;

    if (method === "POST") {
      const data = await request.json();
      await env.DB.prepare("INSERT INTO items(name) VALUES (?)")
        .bind(data.name)
        .run();
      return new Response(JSON.stringify({ status: "created" }), { status: 201 });
    }

    if (method === "GET") {
      const { results } = await env.DB.prepare("SELECT * FROM items").all();
      return Response.json(results); // Helper for JSON responses
    }

    return new Response("Method not allowed", { status: 405 });
  }
};

Hands-on Exercise

1. Open your existing project directory.
2. Update your index.js file to include the code above, targeting a table you created in your previous migration.
3. Run npx wrangler dev --local to start your development server.
4. Use curl to test your new endpoints:
   • Create: curl -X POST http://localhost:8787 -d '{"name": "Test Item"}' -H "Content-Type: application/json"
   • Read: curl http://localhost:8787
5. Verify the data appears in your terminal.

Common Pitfalls

• Forgetting await: Database operations are asynchronous. If you forget to await the run() or all() methods, the Worker may exit before the database responds.
• JSON Parsing Errors: Always wrap request.json() in a try/catch block. If the client sends malformed JSON, the Worker will throw an error and return a 500 response by default.
• Binding Mismatch: Ensure the variable name in wrangler.toml matches the property used on the env object (e.g., env.DB vs env.MY_DATABASE).

FAQ

Q: Can I use SELECT * in production? A: It is generally safer to select specific columns (e.g., SELECT id, name) to reduce payload size and prevent accidental exposure of sensitive fields.

Q: How do I handle large datasets? A: For large result sets, implement pagination using LIMIT and OFFSET in your SQL queries.

Q: Is Response.json() supported everywhere? A: It is standard in the modern Workers runtime. If you are on an older environment, you may need to use new Response(JSON.stringify(data), { headers: { "Content-Type": "application/json" } }).

Recap

In this lesson, you moved from schema definition to actual data interaction. You learned that:

1. D1 Bindings are the gateway to your database within a Worker.
2. Parameterized Queries are non-negotiable for security.
3. JSON Handling is essential for modern API development.

Up next: Basic CRUD: Update and Delete, where we will complete the lifecycle of a database record.