Custom Queries and Schemas Tutorial

Version: 0.2.9
Status: Production Ready (v0.2.x feature)

📚 For detailed guides, see:

• Custom Queries Tutorial - Deep dive into custom endpoints
• Custom Schemas Tutorial - Complete schema transformation guide

Introduction

DataBridge now supports custom queries and schema shapes, allowing you to:

• Create complex API endpoints beyond basic CRUD
• Define custom response shapes (DTOs)
• Hide sensitive fields
• Add computed fields
• Transform data before sending to clients

Quick Start

1. Define Custom Queries

Create databridge.queries.ts in your project root:

import { defineQueries } from './databridge.types';

export default defineQueries({
  'getUserOrders': {
    method: 'GET',
    path: '/users/:userId/orders',
    description: 'Get all orders for a user',
    
    params: {
      userId: { type: 'number', required: true },
    },
    
    handler: async (prisma, { params }) => {
      return prisma.order.findMany({
        where: { userId: params.userId },
        include: { items: true },
      });
    },
  },
});

2. Define Custom Schemas

Create databridge.schemas.ts in your project root:

import { defineSchemas } from './databridge.types';

export default defineSchemas({
  UserPublic: {
    from: 'User',
    fields: {
      id: true,
      email: true,
      firstName: true,
      lastName: true,
      password: false,  // Hide sensitive field
      
      fullName: {
        compute: (user) => `${user.firstName} ${user.lastName}`,
        type: 'string',
      },
    },
  },
});

3. Generate Code

databridge generate

This will:

• ✅ Parse your custom configurations
• ✅ Validate against Prisma schema
• ✅ Generate Fastify route files
• ✅ Generate transformer functions
• ✅ Generate TypeScript types
• ✅ Update OpenAPI spec

Custom Queries Guide

Basic Query

'getProductStats': {
  method: 'GET',
  path: '/products/stats',
  
  handler: async (prisma) => {
    return prisma.product.aggregate({
      _count: true,
      _avg: { price: true },
    });
  },
}

Query with Parameters

'searchProducts': {
  method: 'GET',
  path: '/products/search',
  
  query: {
    q: { type: 'string', required: true },
    minPrice: { type: 'number', default: 0 },
    category: { type: 'string' },
  },
  
  handler: async (prisma, { query }) => {
    return prisma.product.findMany({
      where: {
        name: { contains: query.q },
        price: { gte: query.minPrice },
        ...(query.category && { categoryId: query.category }),
      },
    });
  },
}

POST Request with Body

'createOrder': {
  method: 'POST',
  path: '/orders',
  
  body: {
    userId: { type: 'number', required: true },
    items: {
      type: 'array',
      items: {
        productId: { type: 'number', required: true },
        quantity: { type: 'number', required: true },
      },
    },
  },
  
  handler: async (prisma, { body }) => {
    return prisma.order.create({
      data: {
        userId: body.userId,
        items: {
          create: body.items,
        },
      },
    });
  },
}

Path Parameters

'getOrderDetails': {
  method: 'GET',
  path: '/users/:userId/orders/:orderId',
  
  params: {
    userId: { type: 'number', required: true },
    orderId: { type: 'number', required: true },
  },
  
  handler: async (prisma, { params }) => {
    return prisma.order.findFirst({
      where: {
        id: params.orderId,
        userId: params.userId,
      },
      include: {
        items: { include: { product: true } },
      },
    });
  },
}

Custom Schemas Guide

Basic Schema

UserPublic: {
  from: 'User',
  fields: {
    id: true,           // Include
    email: true,
    firstName: true,
    lastName: true,
    password: false,    // Exclude
  },
}

Computed Fields

ProductWithStats: {
  from: 'Product',
  fields: {
    id: true,
    name: true,
    price: true,
    
    // Simple computed field
    priceFormatted: {
      compute: (product) => `$${product.price.toFixed(2)}`,
      type: 'string',
    },
    
    // Async computed field
    averageRating: {
      compute: async (product, prisma) => {
        const result = await prisma.review.aggregate({
          where: { productId: product.id },
          _avg: { rating: true },
        });
        return result._avg.rating || 0;
      },
      type: 'number',
    },
  },
}

Renamed Fields

UserProfile: {
  from: 'User',
  fields: {
    id: true,
    email: true,
    
    // Rename field
    memberSince: {
      from: 'createdAt',
      type: 'date',
    },
    
    // Rename with transformation
    joinDate: {
      from: 'createdAt',
      transform: (date) => date.toISOString().split('T')[0],
      type: 'string',
    },
  },
}

Nested Transformations

OrderWithDetails: {
  from: 'Order',
  fields: {
    id: true,
    total: true,
    
    // Transform nested relations
    user: {
      include: true,
      shape: 'UserPublic',  // Use another custom schema
    },
    
    items: {
      include: true,
      shape: 'OrderItemSummary',
    },
  },
}

Generated Code

Generated Route

From your query definition, DataBridge generates:

// api/routes/custom/getUserOrders.ts
import { FastifyPluginAsync } from 'fastify';
import { z } from 'zod';

const paramsSchema = z.object({
  userId: z.coerce.number(),
});

const route: FastifyPluginAsync = async (fastify) => {
  fastify.get(
    '/users/:userId/orders',
    {
      schema: {
        description: 'Get all orders for a user',
        params: paramsSchema,
      },
    },
    async (request, reply) => {
      const params = paramsSchema.parse(request.params);
      const result = await fastify.prisma.order.findMany({
        where: { userId: params.userId },
        include: { items: true },
      });
      return result;
    }
  );
};

export default route;

Generated Transformer

From your schema definition, DataBridge generates:

// api/transformers/UserPublic.transformer.ts
import { User, PrismaClient } from '@prisma/client';

export interface UserPublic {
  id: number;
  email: string;
  firstName: string;
  lastName: string;
  fullName: string;
}

export async function transformUserPublic(
  user: User,
  prisma?: PrismaClient
): Promise<UserPublic> {
  return {
    id: user.id,
    email: user.email,
    firstName: user.firstName,
    lastName: user.lastName,
    fullName: `${user.firstName} ${user.lastName}`,
  };
}

export async function transformUserPublicArray(
  users: User[],
  prisma?: PrismaClient
): Promise<UserPublic[]> {
  return Promise.all(users.map(u => transformUserPublic(u, prisma)));
}

Best Practices

1. Use Custom Queries For:

• ✅ Complex joins across multiple tables
• ✅ Aggregations and statistics
• ✅ Custom business logic
• ✅ Raw SQL queries for performance

2. Use Custom Schemas For:

• ✅ Hiding sensitive fields (passwords, tokens)
• ✅ Adding computed fields
• ✅ Renaming fields for better API design
• ✅ Transforming nested relations

3. Validation Best Practices:

• Always validate user input with Zod schemas
• Use enum for fixed sets of values
• Set min/max for numbers
• Mark required fields explicitly

4. Performance Tips:

• Use Prisma’s select and include wisely
• Avoid N+1 queries in computed fields
• Use raw SQL for complex queries
• Add pagination to list endpoints

Examples

See full examples in:

• docs/examples/databridge.queries.example.ts
• docs/examples/databridge.schemas.example.ts

Troubleshooting

Validation Errors

If you see validation errors when running databridge generate:

❌ Validation errors:
  • Query "getUserOrders": path parameter ":userId" must be declared in params

Solution: Make sure all path parameters are declared:

params: {
  userId: { type: 'number', required: true },
}

TypeScript Errors

If you get TypeScript errors in config files:

Make sure the databridge.types.ts file exists (generated by databridge init):

import { defineQueries, defineSchemas } from './databridge.types';

Schema Not Found

If transformers can’t find related schemas:

⚠️  Warning: referenced shape "OrderSummary" not found

Solution: Make sure the schema is defined in the same file:

export default defineSchemas({
  OrderSummary: { /* ... */ },
  OrderWithDetails: {
    fields: {
      items: {
        include: true,
        shape: 'OrderSummary',  // ✅ Now it exists
      },
    },
  },
});

What’s Next?

• OpenAPI spec updates for custom endpoints
• Frontend SDK generation for custom queries
• Authentication middleware support
• Rate limiting configuration
• Query caching options

Feedback

This feature is in active development. Please report issues or suggestions on GitHub!