Cloudflare D1

This guide provides specific information about using workers-qb with Cloudflare D1, Cloudflare's serverless SQL database.

Cloudflare D1 Database

Cloudflare D1 is a globally distributed SQL database designed for serverless environments. It's directly integrated with Cloudflare Workers, providing low-latency access to data from the edge. workers-qb is particularly well-suited for working with D1 due to its lightweight nature and focus on performance in edge contexts.

D1QB Class

To interact with Cloudflare D1 databases using workers-qb, you will use the D1QB class. This class extends the base QueryBuilder and is specifically tailored for D1's API.

Connecting to D1

To connect to your D1 database, you need to obtain a D1 database binding from your Cloudflare Worker environment. This binding is then passed to the D1QB constructor.

Example: Connecting to D1 in a Cloudflare Worker

import { D1QB } from 'workers-qb';

// Define your database schema for type-safe queries
type Schema = {
  users: {
    id: number;
    name: string;
    email: string;
    created_at: string;
  };
  products: {
    id: number;
    name: string;
    price: number;
  };
};

export interface Env {
  DB: D1Database; // 'DB' is the name of your D1 database binding in wrangler.toml
}

export default {
  async fetch(request: Request, env: Env, ctx: ExecutionContext): Promise<Response> {
    // Initialize D1QB with schema for type-safe queries
    const qb = new D1QB<Schema>(env.DB);

    // ... your queries using qb ...

    return new Response("D1 queries executed");
  },
};

Make sure you have configured your D1 database binding correctly in your wrangler.toml file. For example:

# wrangler.toml
name = "my-worker-app"
main = "src/index.ts"
compatibility_date = "2023-08-01"

[[d1_databases]]
binding = "DB" # This 'DB' binding name should match the 'Env' interface
database_name = "my-database"
database_id = "your-database-uuid"

D1 Specific Examples

Here are examples demonstrating common operations using D1QB.

Basic Queries with D1QB

All basic and advanced query operations described in the Basic Queries and Advanced Queries sections are fully applicable to D1QB. You can use createTable, dropTable, insert, select, update, delete, joins, modular select builder, where clauses, etc., just as demonstrated in those sections, replacing PGQB or DOQB with D1QB.

Example: Inserting and Fetching Data in D1

import { D1QB } from 'workers-qb';

// Define your database schema
type Schema = {
  products: {
    id: number;
    name: string;
    price: number;
  };
};

export interface Env {
  DB: D1Database;
}

export default {
  async fetch(request: Request, env: Env, ctx: ExecutionContext): Promise<Response> {
    const qb = new D1QB<Schema>(env.DB);

    // Create table (if not exists) - you'd typically do this in migrations
    await qb.createTable({
        tableName: 'products',
        ifNotExists: true,
        schema: `
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            price REAL NOT NULL
        `
    }).execute();

    // Insert a product - table name and columns are autocompleted
    const insertedProduct = await qb.insert({
      tableName: 'products',  // ✓ Autocomplete: 'products'
      data: {
        name: 'Example Product',  // ✓ Only valid columns allowed
        price: 19.99,
      },
      returning: ['id', 'name', 'price'],  // ✓ Autocomplete for columns
    }).execute();

    console.log('Inserted product:', insertedProduct.results);

    // Fetch all products - result type is automatically inferred
    const allProducts = await qb.fetchAll({
      tableName: 'products',
    }).execute();

    // allProducts.results is typed as Schema['products'][]
    console.log('All products:', allProducts.results);

    return Response.json({
      insertedProduct: insertedProduct.results,
      allProducts: allProducts.results,
    });
  },
};

Batch Operations in D1

D1 supports batch operations, which can significantly improve performance when executing multiple queries at once. workers-qb provides the batchExecute method on D1QB to leverage this feature.

Example: Batch Inserting Multiple Products

import { D1QB } from 'workers-qb';

// Define your database schema
type Schema = {
  products: {
    id: number;
    name: string;
    price: number;
  };
};

export interface Env {
  DB: D1Database;
}

export default {
  async fetch(request: Request, env: Env, ctx: ExecutionContext): Promise<Response> {
    const qb = new D1QB<Schema>(env.DB);

    const productsToInsert = [
      { name: 'Product A', price: 9.99 },
      { name: 'Product B', price: 24.50 },
      { name: 'Product C', price: 15.75 },
    ];

    // Create an array of Query objects for batch execution
    const insertQueries = productsToInsert.map(productData =>
      qb.insert({
        tableName: 'products',
        data: productData,
        returning: ['id', 'name', 'price'],
      }) // insert() returns a Query object directly
    );

    const batchResults = await qb.batchExecute(insertQueries);

    const insertedProducts = batchResults.map(result => result.results);

    console.log('Batch inserted products:', insertedProducts);

    return Response.json({
      batchInsertedProducts: insertedProducts,
    });
  },
};

In this example, we create an array of Query objects from individual insert operations and then execute them all in a single batch using qb.batchExecute(). The insert(), update(), and delete() methods return Query objects directly, which can be passed to batchExecute(). This is more efficient than executing each query separately.

Note: Batch operations in D1 have limitations. Refer to the Cloudflare D1 documentation for details on batch operation constraints.

Execution Metrics

When you execute a query with D1QB, the returned result object contains metrics about the database operation. This includes rowsRead and rowsWritten, which provide insight into the impact of your query.

• rowsRead: The number of rows read from the database to execute the query.
• rowsWritten: The number of rows written (inserted, updated, or deleted) to the database.

These metrics can be useful for monitoring and optimizing your database queries.

Example: Accessing Execution Metrics

import { D1QB } from 'workers-qb';

// Define your database schema
type Schema = {
  users: {
    id: number;
    name: string;
  };
};

export interface Env {
  DB: D1Database;
}

export default {
  async fetch(request: Request, env: Env, ctx: ExecutionContext): Promise<Response> {
    const qb = new D1QB<Schema>(env.DB);

    // Example of an insert operation
    const insertResult = await qb.insert({
      tableName: 'users',
      data: { name: 'John Doe' },
      returning: ['id', 'name'],
    }).execute();

    console.log(`Rows written: ${insertResult.rowsWritten}`); // e.g., "Rows written: 1"

    // Example of a select operation
    const selectResult = await qb.fetchAll({
      tableName: 'users',
    }).execute();

    console.log(`Rows read: ${selectResult.rowsRead}`); // e.g., "Rows read: 5"

    return Response.json({
      insertedUser: insertResult.results,
      allUsers: selectResult.results,
      metrics: {
        insert: {
          rowsWritten: insertResult.rowsWritten,
          rowsRead: insertResult.rowsRead,
        },
        select: {
          rowsWritten: selectResult.rowsWritten,
          rowsRead: selectResult.rowsRead,
        },
      },
    });
  },
};