CSV export in Next.js App Router -- downloadable reports from Drizzle ORM queries

Every SaaS user eventually asks for a data export. Whether it is their transaction history, user list, or analytics summary, a CSV download button is one of those features users notice when it is missing.

This post shows you the full pattern: a protected download endpoint, a Drizzle ORM query, proper response headers, and a fetch-based UI button that handles the auth header.

The endpoint

Create app/api/reports/orders/route.ts:

import { NextRequest } from 'next/server';
import { getUserFromRequest } from '@/lib/auth';
import { handleError } from '@/lib/errors/handleError';
import { orderService } from '@/modules/order';
 
export async function GET(req: NextRequest) {
  try {
    const user = await getUserFromRequest(req);
    const csv = await orderService.exportCsv(user.id);
 
    return new Response(csv, {
      status: 200,
      headers: {
        'Content-Type': 'text/csv; charset=utf-8',
        'Content-Disposition': 'attachment; filename="orders.csv"',
      },
    });
  } catch (error: unknown) {
    return handleError(error);
  }
}

Thin route: validate auth, delegate to the service, return the CSV with the right headers.

The service method

In modules/order/order.service.ts:

export async function exportCsv(userId: string): Promise<string> {
  const orders = await orderRepo.findAllByUser(userId);
 
  const header = ['id', 'amount', 'status', 'created_at'].join(',');
  const rows = orders.map((o) =>
    [o.id, o.amount, o.status, o.createdAt.toISOString()].join(',')
  );
 
  return [header, ...rows].join('\n');
}

For most SaaS exports this is fine. The query runs on Neon, the CSV string fits in memory, and the response is a few kilobytes. If you are exporting millions of rows, switch to a ReadableStream.

The repository query

In modules/order/order.repo.ts:

export async function findAllByUser(userId: string) {
  return db
    .select({
      id: orderTable.id,
      amount: orderTable.amount,
      status: orderTable.status,
      createdAt: orderTable.createdAt,
    })
    .from(orderTable)
    .where(eq(orderTable.userId, userId))
    .orderBy(desc(orderTable.createdAt));
}

Select only the columns you export -- do not pull full rows.

Handling special characters

If any field can contain commas, quotes, or newlines, escape it:

function csvEscape(value: string | number | null): string {
  if (value === null || value === undefined) return '';
  const s = String(value);
  if (s.includes(',') || s.includes('"') || s.includes('\n')) {
    return '"' + s.replace(/"/g, '""') + '"';
  }
  return s;
}

Use csvEscape(o.description) in your row mapping for any free-text field.

The download button

A plain anchor tag with the download attribute does not work for protected routes -- it cannot send an auth header. Use a fetch plus Blob approach instead:

'use client';
 
import { Button } from '@/components/ui/button';
 
export function ExportButton() {
  async function handleExport() {
    const token = localStorage.getItem('token');
    const res = await fetch('/api/reports/orders', {
      headers: { Authorization: `Bearer ${token}` },
    });
    if (!res.ok) return;
 
    const blob = await res.blob();
    const url = URL.createObjectURL(blob);
    const a = document.createElement('a');
    a.href = url;
    a.download = 'orders.csv';
    a.click();
    URL.revokeObjectURL(url);
  }
 
  return (
    <Button variant="outline" onClick={handleExport}>
      Export CSV
    </Button>
  );
}

This pattern works for any protected download: swap the URL and filename for a different report.

Adding a date range filter

Extend the route to read query params:

const { searchParams } = new URL(req.url);
const from = searchParams.get('from');
const to = searchParams.get('to');
 
const csv = await orderService.exportCsv(user.id, { from, to });

In the repo, add conditional where clauses:

.where(
  and(
    eq(orderTable.userId, userId),
    from ? gte(orderTable.createdAt, new Date(from)) : undefined,
    to ? lte(orderTable.createdAt, new Date(to)) : undefined,
  )
)

Pass the params from the button:

const res = await fetch(`/api/reports/orders?from=${from}&to=${to}`, {
  headers: { Authorization: `Bearer ${token}` },
});

Next step

Add an export button to your dashboard table. The pattern above handles auth, query, and download in three files and takes about 30 minutes on a running codebase. If you are starting from scratch, the boilerplate at boilerplate.iteam-company.com already has JWT auth, Drizzle ORM, and the module structure wired up -- you start at the service layer, not zero.