Auto D1 Endpoints

Chanfana provides a set of specialized endpoint classes designed to seamlessly integrate with Cloudflare D1, Cloudflare's serverless SQL database. These D1 endpoints extend the auto CRUD endpoints and provide built-in functionality for interacting with D1 databases, further simplifying API development for Cloudflare Workers.

Introduction to D1 Endpoints

Chanfana offers the following D1-specific endpoint classes:

• D1CreateEndpoint: Extends CreateEndpoint and provides D1-specific logic for creating resources in a D1 database.
• D1ReadEndpoint: Extends ReadEndpoint and provides D1-specific logic for reading resources from a D1 database.
• D1UpdateEndpoint: Extends UpdateEndpoint and provides D1-specific logic for updating resources in a D1 database.
• D1DeleteEndpoint: Extends DeleteEndpoint and provides D1-specific logic for deleting resources from a D1 database.
• D1ListEndpoint: Extends ListEndpoint and provides D1-specific logic for listing resources from a D1 database, including filtering and pagination optimized for D1.

These endpoints inherit all the benefits of the base CRUD endpoints (schema generation, validation, etc.) and add D1 database interaction capabilities.

Important Note on Path Parameters in Nested Routes:

When using D1 endpoints in modular or nested route structures, it's crucial to correctly define path parameters, especially for ReadEndpoint, UpdateEndpoint, and DeleteEndpoint. A bug was identified where primary key validation in nested routes was failing because the framework was only considering the current route segment for parameter validation, rather than the full merged path.

To address this, Chanfana now allows you to explicitly define pathParameters in your Meta object. This ensures that primary keys are correctly validated even in complex route setups. If you encounter issues with primary key validation in nested routes, especially the error "Model primaryKeys differ from urlParameters", please refer to the Auto Endpoints documentation for details on using the pathParameters Meta property to resolve this.

Setting up D1 Bindings

To use D1 endpoints, you need to have a D1 database bound to your Cloudflare Worker environment. This is typically done in your wrangler.toml file.

Example wrangler.toml configuration:

name = "my-chanfana-d1-api"
main = "src/index.ts"
compatibility_date = "2024-01-01"

[[d1_databases]]
binding = "DB" # <-- Binding name used in your code
database_name = "my-database"
database_id = "your-database-id"

In this configuration, we are binding a D1 database to the variable name DB. This binding name is important as it's used by default in Chanfana's D1 endpoints to access your database. You can customize this binding name if needed.

Using D1 Endpoints

Let's see how to use each D1 endpoint with examples. We'll assume you have a D1 database set up and bound as DB in your Worker environment.

D1CreateEndpoint: Creating Resources in D1

D1CreateEndpoint automatically handles inserting new resources into your D1 database table.

Example: Storing User Data in D1

import { Hono } from 'hono';
import { fromHono, D1CreateEndpoint, contentJson } from 'chanfana';
import { z } from 'zod';

// Define the User Model
const UserModel = z.object({
    id: z.uuid(),
    username: z.string().min(3).max(20),
    email: z.email(),
    fullName: z.string().optional(),
    createdAt: z.iso.datetime(),
});

// Define the Meta object for User
const userMeta = {
    model: {
        schema: UserModel,
        primaryKeys: ['id'],
        tableName: 'users', // Table name in D1 database
    },
};

class CreateUser extends D1CreateEndpoint {
    _meta = userMeta;
    dbName = "DB"
}

const app = new Hono<{ Bindings: { DB: D1Database } }>(); // Define Bindings type for Hono
const openapi = fromHono(app);
openapi.post('/users', CreateUser);

export default app;

In this example:

• CreateUser extends D1CreateEndpoint.
• _meta is set to userMeta, which includes tableName: 'users'. This tells D1CreateEndpoint to insert data into the users table in your D1 database.
• dbName specifies that the D1 database binding is named DB (as defined in wrangler.toml, defaults to DB).
• The create method is not overridden. D1CreateEndpoint provides a default create implementation that automatically inserts the validated data into the D1 table.
• Hono app is typed to include Bindings: { DB: D1Database } to ensure type safety for D1 binding access.

When a POST request is made to /users, D1CreateEndpoint will:

1. Validate the request body against userMeta.model.schema.
2. Get the D1 database binding named DB from the Worker environment.
3. Execute an INSERT SQL query to insert the validated data into the users table.
4. Return a 200 OK response with the created user object.

D1ReadEndpoint: Reading Resources from D1

D1ReadEndpoint handles fetching a single resource from your D1 database based on primary keys provided as path parameters.

Example: Fetching User Details from D1

import { Hono } from 'hono';
import { fromHono, D1ReadEndpoint, contentJson } from 'chanfana';
import { z } from 'zod';

// (UserModel and userMeta are assumed to be defined as in the D1CreateEndpoint example)

class GetUser extends D1ReadEndpoint {
    _meta = userMeta;
}

const app = new Hono<{ Bindings: { DB: D1Database } }>();
const openapi = fromHono(app);
openapi.get('/users/:id', GetUser);

export default app;

In this example:

• GetUser extends D1ReadEndpoint.
• _meta is configured similarly to D1CreateEndpoint.
• The fetch method is not overridden. D1ReadEndpoint provides a default fetch implementation that automatically queries the D1 table based on the path parameters (primary keys).

When a GET request is made to /users/:id, D1ReadEndpoint will:

1. Validate the id path parameter.
2. Get the D1 database binding DB.
3. Execute a SELECT * FROM users WHERE id = ? LIMIT 1 SQL query (assuming 'id' is the primary key and userId is the provided value).
4. Return a 200 OK response with the fetched user object if found, or a 404 Not Found response if not found.

D1UpdateEndpoint: Updating Resources in D1

D1UpdateEndpoint handles updating existing resources in your D1 database.

Example: Updating User Profile in D1

import { Hono } from 'hono';
import { fromHono, D1UpdateEndpoint, contentJson } from 'chanfana';
import { z } from 'zod';

// (UserModel and userMeta are assumed to be defined as in the D1CreateEndpoint example)

class UpdateUser extends D1UpdateEndpoint {
    _meta = userMeta;
}

const app = new Hono<{ Bindings: { DB: D1Database } }>();
const openapi = fromHono(app);
openapi.put('/users/:id', UpdateUser); // Or .patch()

export default app;

In this example:

• UpdateUser extends D1UpdateEndpoint.
• _meta is configured.
• getObject and update methods are not overridden. D1UpdateEndpoint provides default implementations for fetching the existing object and performing the D1 UPDATE query.

When a PUT request is made to /users/:id with a request body, D1UpdateEndpoint will:

1. Validate the id path parameter and the request body.
2. Get the D1 database binding DB.
3. Execute a SELECT query to fetch the existing user based on id.
4. Execute an UPDATE SQL query to update the user in the users table with the data from the request body, using the id to identify the row to update.
5. Return a 200 OK response with the updated user object.

D1DeleteEndpoint: Deleting Resources from D1

D1DeleteEndpoint handles deleting resources from your D1 database.

Example: Removing User Account from D1

import { Hono } from 'hono';
import { fromHono, D1DeleteEndpoint } from 'chanfana';
import { z } from 'zod';

// (UserModel and userMeta are assumed to be defined as in the D1CreateEndpoint example)

class DeleteUser extends D1DeleteEndpoint {
    _meta = userMeta;
}

const app = new Hono<{ Bindings: { DB: D1Database } }>();
const openapi = fromHono(app);
openapi.delete('/users/:id', DeleteUser);

export default app;

In this example:

• DeleteUser extends D1DeleteEndpoint.
• _meta is configured.
• getObject and delete methods are not overridden. D1DeleteEndpoint provides default implementations for fetching the object to be deleted and performing the D1 DELETE query.

When a DELETE request is made to /users/:id, D1DeleteEndpoint will:

1. Validate the id path parameter.
2. Get the D1 database binding DB.
3. Execute a SELECT query to fetch the user to be deleted based on id.
4. Execute a DELETE FROM users WHERE id = ? LIMIT 1 SQL query to delete the user.
5. Return a 200 OK response with the deleted user object.

D1ListEndpoint: Listing Resources from D1 with Advanced Filtering

D1ListEndpoint is designed for efficiently listing resources from D1, including pagination, filtering, and sorting, all optimized for D1 database queries.

Example: Listing Users with Search and Pagination in D1

import { Hono } from 'hono';
import { fromHono, D1ListEndpoint, contentJson } from 'chanfana';
import { z } from 'zod';

// (UserModel and userMeta are assumed to be defined as in the D1CreateEndpoint example)

class ListUsers extends D1ListEndpoint {
    _meta = userMeta;
    filterFields = ['email', 'fullName']; // Fields for exact match filtering
    searchFields = ['username', 'fullName', 'email']; // Fields for search (LIKE query)
    orderByFields = ['username', 'email', 'createdAt']; // Fields for ordering
    defaultOrderBy = 'username';
}

const app = new Hono<{ Bindings: { DB: D1Database } }>();
const openapi = fromHono(app);
openapi.get('/users', ListUsers);

export default app;

In this example:

• ListUsers extends D1ListEndpoint.
• _meta is configured.
• filterFields, searchFields, orderByFields, and defaultOrderBy are set to enable filtering, searching, and sorting.
• The list method is not overridden. D1ListEndpoint provides a default list implementation that automatically generates and executes optimized D1 queries for listing, pagination, filtering, searching, and sorting.

When a GET request is made to /users with query parameters for pagination, filtering, searching, or sorting, D1ListEndpoint will:

1. Validate the query parameters.
2. Get the D1 database binding DB.
3. Construct and execute optimized D1 SQL queries to:
   • Fetch a paginated list of users from the users table, applying filters, search terms, and sorting as specified in the query parameters.
   • Fetch the total count of users matching the filters (for pagination metadata).
4. Return a 200 OK response with the list of users and pagination metadata.

Logging and Error Handling in D1 Endpoints

D1 endpoints provide basic logging capabilities. You can optionally set a logger property in your D1 endpoint classes to receive log messages during database operations. The logger should be an object with log, info, warn, error, debug, and trace methods (similar to a standard logger interface).

Example with Logger:

class CreateUser extends D1CreateEndpoint {
    // ... (rest of the class definition) ...
    logger = console; // Use console as a simple logger
}

D1 endpoints also handle common D1-related errors, such as unique constraint violations. You can customize error messages for specific constraints using the constraintsMessages property.

Constraints and Error Messages in D1 Endpoints

You can customize the error messages for D1 constraint violations (e.g., unique constraints) using the constraintsMessages property in your D1 endpoint classes. This allows you to provide more user-friendly and specific error messages to clients.

Example: Custom Error Message for Unique Username Constraint

import { InputValidationException } from 'chanfana';

class CreateUser extends D1CreateEndpoint {
    // ... (rest of the class definition) ...
    constraintsMessages = {
        'users_username_unique': new InputValidationException('Username already taken', ['body', 'username']),
    };
}

In this example, if a D1 UNIQUE constraint failed error occurs with the constraint name containing users_username_unique, D1CreateEndpoint will throw an InputValidationException with the custom message "Username already taken" and the path ['body', 'username'], providing a more informative error response to the client.

Security: SQL Injection Prevention

D1 endpoints include built-in security utilities to prevent SQL injection attacks. All SQL queries use parameterized statements, and all identifiers (table names, column names) are validated before use.

Utility Functions

The D1 endpoint module provides several utility functions for safe SQL query building:

validateSqlIdentifier(identifier, type)

Validates that a string is a safe SQL identifier. Only allows alphanumeric characters and underscores, must start with a letter or underscore.

import { validateSqlIdentifier } from 'chanfana';

validateSqlIdentifier('users', 'table');     // ✓ Returns 'users'
validateSqlIdentifier('user_name', 'column'); // ✓ Returns 'user_name'
validateSqlIdentifier('DROP TABLE--', 'table'); // ✗ Throws ApiException

validateTableName(tableName) / validateColumnName(columnName, validColumns?)

Convenience wrappers for validateSqlIdentifier:

import { validateTableName, validateColumnName } from 'chanfana';

const table = validateTableName('users');
const column = validateColumnName('email', ['id', 'email', 'name']);

buildSafeFilters(filters, validColumns, startParamIndex?)

Builds SQL filter conditions from an array of filter conditions, validating all column names:

import { buildSafeFilters } from 'chanfana';

const filters = [
  { field: 'status', operator: 'EQ', value: 'active' },
  { field: 'role', operator: 'EQ', value: 'admin' }
];
const validColumns = ['id', 'status', 'role', 'name'];

const { conditions, conditionsParams } = buildSafeFilters(filters, validColumns);
// conditions: ['status = ?1', 'role = ?2']
// conditionsParams: ['active', 'admin']

buildPrimaryKeyFilters(filters, primaryKeys, validColumns, startParamIndex?)

Builds filter conditions for primary key lookups only:

import { buildPrimaryKeyFilters } from 'chanfana';

const { conditions, conditionsParams } = buildPrimaryKeyFilters(
  filters,
  ['id'],           // primary keys
  ['id', 'name'],   // valid columns
);

getD1Binding(getBindings, args, dbName)

Safely retrieves a D1 database binding from the worker environment:

import { getD1Binding } from 'chanfana';

const db = getD1Binding(this.getBindings, args, 'DB');
// Throws ApiException if binding is not defined or not a D1 binding

handleDbError(error, constraintsMessages, logger?, operation?)

Handles database errors and maps UNIQUE constraint violations to custom exceptions:

import { handleDbError } from 'chanfana';

try {
  await db.prepare('INSERT ...').run();
} catch (error) {
  handleDbError(error, this.constraintsMessages, this.logger, 'create');
}

Best Practices

1. Always use parameterized queries: Never interpolate user input directly into SQL strings
2. Validate all identifiers: Use validateTableName and validateColumnName for any dynamic table/column names
3. Use buildSafeFilters: For building WHERE clauses from user-provided filter conditions
4. Define constraintsMessages: Map database constraint violations to user-friendly error messages
5. Use logging: Pass a logger to track database errors for debugging

---

D1 endpoints in Chanfana significantly simplify building APIs backed by Cloudflare D1 databases. They automate database interactions, schema generation, validation, and provide optimized querying capabilities, allowing you to focus on your API logic and business requirements.