Workers D1 -- CRUD Queries and Prepared Statements

In this tutorial, you will learn how to perform Create, Read, Update, and Delete operations using Cloudflare Workers D1 with prepared statements for security and performance. Prepared statements are important because they prevent SQL Injection attacks and enable query plan Caching. A real-world example is a contact management API that stores, retrieves, updates, and deletes customer records all from Workers at the edge.

Why Prepared Statements Matter

When you concatenate user input directly into SQL strings, an attacker can inject malicious SQL commands. Prepared statements separate SQL logic from data by sending the query structure to the database first, then supplying the parameters separately. D1 uses SQLite's prepared statement interface, which also caches the compiled query plan after the first execution, making subsequent calls faster. Every D1 query should use .bind() with positional or named parameters instead of string interpolation.

Real-World Use Case

A customer support dashboard stored user notes in D1. The initial implementation used string interpolation for queries. A penetration test revealed SQL Injection vulnerability in the search endpoint. After migrating all queries to prepared statements with .bind(), the same test payloads were safely treated as literal strings. Query performance also improved by 30 percent due to plan Caching.

CRUD Flow in D1

flowchart LR
    A[HTTP Request] --> B[Worker fetch handler]
    B --> C[Parse Method + Body]
    C --> D{HTTP Method}
    D -->|POST| E[INSERT via prepared stmt]
    D -->|GET| F[SELECT via prepared stmt]
    D -->|PUT| G[UPDATE via prepared stmt]
    D -->|DELETE| H[DELETE via prepared stmt]
    E --> I[JSON Response]
    F --> I
    G --> I
    H --> I

    style E fill:#2ecc71,color:#fff
    style F fill:#3498db,color:#fff
    style G fill:#f39c12,color:#fff
    style H fill:#e74c3c,color:#fff

The Worker inspects the HTTP method and routes to the appropriate D1 query. All queries use prepared statements with env.DB.prepare().bind().

Create -- Inserting Records

// POST /contacts -- Create a new contact
export default {
  async fetch(request, env) {
    if (request.method !== 'POST') {
      return new Response('Method not allowed', { status: 405 });
    }

    const { name, email, phone } = await request.json();

    const result = await env.DB.prepare(
      'INSERT INTO contacts (name, email, phone) VALUES (?1, ?2, ?3) RETURNING id'
    )
      .bind(name, email, phone)
      .first();

    return new Response(JSON.stringify({ id: result.id }), {
      status: 201,
      headers: { 'Content-Type': 'application/json' }
    });
  }
};

// Request body: {"name": "Bob", "email": "bob@example.com", "phone": "555-0123"}
// Expected response:
// {"id": 15}

The RETURNING clause returns values from the inserted row. Positional parameters ?1, ?2, ?3 map to the three values passed to .bind().

Read -- Querying Records

// GET /contacts?email=bob@example.com -- Read contact by email
export default {
  async fetch(request, env) {
    const url = new URL(request.url);
    const email = url.searchParams.get('email');

    if (!email) {
      // Return all contacts if no filter
      const { results } = await env.DB.prepare(
        'SELECT id, name, email, phone, created_at FROM contacts ORDER BY created_at DESC'
      ).all();

      return new Response(JSON.stringify(results), {
        headers: { 'Content-Type': 'application/json' }
      });
    }

    const contact = await env.DB.prepare(
      'SELECT id, name, email, phone, created_at FROM contacts WHERE email = ?1'
    )
      .bind(email)
      .first();

    if (!contact) {
      return new Response('Not found', { status: 404 });
    }

    return new Response(JSON.stringify(contact), {
      headers: { 'Content-Type': 'application/json' }
    });
  }
};

// Request: GET /contacts?email=bob@example.com
// Expected response:
// {"id": 15, "name": "Bob", "email": "bob@example.com", "phone": "555-0123", "created_at": "2026-06-23T10:00:00Z"}

The .all() method returns all matching rows as an array. The .first() method returns a single row or null. Use .all() for list endpoints and .first() for single-record lookups.

Update -- Modifying Records

// PUT /contacts/:id -- Update a contact
export default {
  async fetch(request, env) {
    if (request.method !== 'PUT') {
      return new Response('Method not allowed', { status: 405 });
    }

    const url = new URL(request.url);
    const id = url.pathname.split('/').pop();
    const { name, email, phone } = await request.json();

    const result = await env.DB.prepare(
      'UPDATE contacts SET name = ?1, email = ?2, phone = ?3 WHERE id = ?4 RETURNING *'
    )
      .bind(name, email, phone, Number(id))
      .first();

    if (!result) {
      return new Response('Not found', { status: 404 });
    }

    return new Response(JSON.stringify(result), {
      headers: { 'Content-Type': 'application/json' }
    });
  }
};

// Request: PUT /contacts/15 with body {"name": "Robert", "email": "robert@example.com", "phone": "555-0199"}
// Expected response:
// {"id": 15, "name": "Robert", "email": "robert@example.com", "phone": "555-0199", "created_at": "2026-06-23T10:00:00Z"}

The UPDATE statement with RETURNING * returns the full updated row. If no row matches the WHERE clause, .first() returns null and we respond with a 404.

Delete -- Removing Records

// DELETE /contacts/:id -- Delete a contact
export default {
  async fetch(request, env) {
    if (request.method !== 'DELETE') {
      return new Response('Method not allowed', { status: 405 });
    }

    const url = new URL(request.url);
    const id = url.pathname.split('/').pop();

    const result = await env.DB.prepare(
      'DELETE FROM contacts WHERE id = ?1'
    )
      .bind(Number(id))
      .run();

    if (result.meta.changes === 0) {
      return new Response('Not found', { status: 404 });
    }

    return new Response(null, { status: 204 });
  }
};

// Request: DELETE /contacts/15
// Expected response:
// Status 204 No Content

The .run() method returns a result object with a meta.changes property indicating how many rows were affected. Zero changes means the record did not exist.

Batch Operations

// Insert multiple contacts in a transaction
export default {
  async fetch(request, env) {
    const contacts = await request.json();
    // contacts is an array of {name, email, phone} objects

    const stmt = env.DB.prepare(
      'INSERT INTO contacts (name, email, phone) VALUES (?1, ?2, ?3)'
    );

    const batch = contacts.map(c => stmt.bind(c.name, c.email, c.phone));
    const results = await env.DB.batch(batch);

    return new Response(JSON.stringify({
      inserted: results.length
    }), {
      headers: { 'Content-Type': 'application/json' }
    });
  }
};

// Request body: [{"name": "Alice", "email": "alice"@x".com", "phone": "555-0001"}, {"name": "Bob", "email": "bob"@x".com", "phone": "555-0002"}]
// Expected response:
// {"inserted": 2}

The .batch() method executes multiple statements in a single Transaction. If any statement fails, all changes are rolled back. This is useful for bulk inserts and updates.

Common Errors and Troubleshooting

SQL Injection via String Interpolation

Never use template literals or string concatenation to insert user values into SQL. Always use .bind() with positional or named parameters. Even sanitized input can bypass filters.

Binding Type Mismatch

D1 expects parameter types to match SQL column types. Pass numbers as numbers, strings as strings, and booleans as integers (0 or 1). Type mismatches cause silent type coercion or query failures.

Missing RETURNING Clause

Without RETURNING, INSERT and UPDATE statements do not return the affected row. Use RETURNING * or RETURNING column1, column2 to get data back in a single round trip.

Batch Size Limits

The .batch() method accepts up to 100 statements per call. For larger operations, split into multiple batches. Each batch runs in a separate Transaction.

Query Result Size

D1 limits result sets to approximately 100 MB. Queries returning large datasets should use pagination with LIMIT and OFFSET clauses.

Practice Questions

1. What method replaces ?1 placeholders with actual values in D1?
2. Which D1 method returns a single row or null?
3. How do you execute multiple D1 statements in a single Transaction?

FAQ

Summary

CRUD operations in D1 follow standard SQL patterns with prepared statements for security. Use INSERT with RETURNING for creates, SELECT with .all() or .first() for reads, UPDATE with RETURNING for updates, and DELETE with .run() for deletes. The .batch() method groups multiple statements into a single Transaction. Always use .bind() with positional or named parameters to prevent SQL Injection.

This guide is brought to you by the developers of Cloudflare, SQLite, and Durga Antivirus Pro at DodaTech.