# Query MotherDuck from Vercel and Next.js
> Next.js API routes that query MotherDuck over the Postgres wire protocol with the node-postgres driver, no DuckDB binary needed. Use when building a Vercel (or any Node serverless) backend that reads MotherDuck data through pooled, parameterized SQL.
<ExampleHeader
  githubUrl={"https://github.com/motherduckdb/motherduck-examples/tree/main/vercel-nextjs"}
  features={["pg_endpoint"]}
  tags={["vercel","nextjs","node-postgres"]}
/>

This is a Next.js app whose API routes connect to MotherDuck through the Postgres
wire protocol endpoint using the `pg` (node-postgres) driver. It shows the
serverless-friendly MotherDuck pattern: a module-level connection pool reused
across warm function invocations, SSL certificate verification, input validation,
and parameterized queries against the public `sample_data.nyc.taxi` dataset. No
DuckDB binary is bundled, so the deploy stays small and cold starts stay fast.

## Routes

### Code0@@

Returns the 20 most recent taxi trips from `sample_data.nyc.taxi`, ordered by
`tpep_pickup_datetime` descending. No query parameters.

### Code0@@

Returns total passengers and total fare for the given pickup-date range.

Parameters:

- `start`: start date, required, `YYYY-MM-DD` (inclusive lower bound)
- `end`: end date, required, `YYYY-MM-DD` (exclusive upper bound)

Example request and response:

```text
/api/stats?start=2022-11-01&end=2022-12-01
```

```json
{
  "start": "2022-11-01",
  "end": "2022-12-01",
  "total_passengers": 1234567,
  "total_fare": 1234567.89
}
```

Both parameters are required and validated. A missing parameter or a value that
does not match `YYYY-MM-DD` returns HTTP 400 before any query runs.

## Connection details

The app connects to MotherDuck through the
[Postgres wire protocol endpoint](https://motherduck.com/docs/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint),
which speaks the Postgres protocol so any Postgres driver works without a DuckDB
binary. The default host is `pg.us-east-1-aws.motherduck.com` and the endpoint
listens on port `5432`. Set `MOTHERDUCK_HOST=pg.eu-central-1-aws.motherduck.com`
for an EU organization, or `pg.<region>-aws.motherduck.com` for another region.
The connection user is always the literal `user`; the access token is the
password. The `sample_data` database (with the `nyc.taxi` table) ships on every
MotherDuck account, so the example runs against real data with zero setup.

### Connection pooling

`src/lib/motherduck.ts` creates a single module-level `pg.Pool`
(`max: 10`, `idleTimeoutMillis: 5000`) so TCP connections are reused across
requests within one warm function instance instead of dialing MotherDuck on
every call.
[`attachDatabasePool`](https://vercel.com/kb/guide/connection-pooling-with-functions)
from `@vercel/functions` registers the pool so idle connections are drained
before the instance is suspended. The exported `withClient` helper checks out a
client and always releases it in a `finally` block.

### SSL

The connection uses `ssl: { rejectUnauthorized: true }`, equivalent to
PostgreSQL's `sslmode=verify-full`: Node verifies the server certificate against
the system CA bundle and checks hostname matching. MotherDuck's endpoint uses a
publicly trusted certificate, so no custom CA is needed. Do not set
`rejectUnauthorized: false`; it disables verification and exposes the connection
to man-in-the-middle attacks.

## Security

Always sanitize anything that comes from a request before it reaches SQL. This
example does two things.

### 1. Validate inputs

`src/app/api/stats/route.ts` rejects anything that is not a `YYYY-MM-DD` date
before querying:

```js
const datePattern = /^\d{4}-\d{2}-\d{2}$/;
if (!datePattern.test(startDate) || !datePattern.test(endDate)) {
  return NextResponse.json(
    { error: "Invalid date format. Use YYYY-MM-DD." },
    { status: 400 }
  );
}
```

### 2. Use parameterized queries

Never interpolate request values into the SQL string. Pass them as numbered
parameters (`$1`, `$2`) so the driver binds them safely:

```js
const result = await client.query(
  `SELECT
    sum(passenger_count)::INTEGER AS total_passengers,
    round(sum(fare_amount), 2) AS total_fare
  FROM nyc.taxi
  WHERE tpep_pickup_datetime >= $1
    AND tpep_pickup_datetime < $2`,
  [`${startDate} 00:00:00`, `${endDate} 00:00:00`]
);
```

## What you'll adjust

| Setting | Purpose | Options / example |
| --- | --- | --- |
| `MOTHERDUCK_TOKEN` env var | MotherDuck access token used as the connection password | Service-account token from your MotherDuck settings |
| `MOTHERDUCK_HOST` env var | Postgres endpoint host, selects the region | `pg.us-east-1-aws.motherduck.com` (default), `pg.eu-central-1-aws.motherduck.com` for EU |
| `MOTHERDUCK_DB` env var | Database in the connection string | `sample_data` (default), or your own database |
| `connectionString` in `src/lib/motherduck.ts` | How the pool authenticates, fixed at port `5432` | `postgresql://user:${token}@${host}:5432/${db}` |
| Pool options in `src/lib/motherduck.ts` | Pooling behavior for serverless concurrency | `max: 10`, `idleTimeoutMillis: 5000` |
| `ssl` option in `src/lib/motherduck.ts` | TLS verification level | `{ rejectUnauthorized: true }` (equivalent to `sslmode=verify-full`) |
| SQL in `src/app/api/trips/route.ts` | The "recent trips" query and row limit | Change `FROM nyc.taxi`, columns, `LIMIT 20` |
| SQL in `src/app/api/stats/route.ts` | The aggregate query and its `$1`/`$2` date params | Swap the table, columns, and the `datePattern` validation regex |

## Questions to answer

- Which MotherDuck database and schema should the routes read from (default is `sample_data.nyc.taxi`)?
- Which region is the account in, so the right `MOTHERDUCK_HOST` is set (US vs EU)?
- What tables and columns do the API routes need to expose, and what query parameters drive them?
- How will the token be provisioned in production: manual `vercel env add` or the MotherDuck Native Integration on Vercel?
- What concurrency is expected, so the pool `max` and idle timeout can be tuned?

## Run it

Prerequisites: Node.js v18+, a MotherDuck account and access token, and (for deploy) a Vercel account.

```sh
npm install
cp .env.local.example .env.local   # then set MOTHERDUCK_TOKEN
npm run dev                         # http://localhost:3000
```

Build and deploy to Vercel:

```sh
npm run build
npx vercel deploy
npx vercel env add MOTHERDUCK_TOKEN   # if not using the MotherDuck Native Integration
```

If you install the [MotherDuck Native Integration](https://vercel.com/marketplace/motherduck) on Vercel, the access token is injected as an environment variable automatically.

## Files

- [`src/lib/motherduck.ts`](https://github.com/motherduckdb/motherduck-examples/blob/main/vercel-nextjs/src/lib/motherduck.ts) - the shared `pg.Pool` (reads `MOTHERDUCK_TOKEN`/`MOTHERDUCK_HOST`/`MOTHERDUCK_DB`), the `attachDatabasePool` cleanup hook, and the `withClient` checkout/release helper.
- [`src/app/api/trips/route.ts`](https://github.com/motherduckdb/motherduck-examples/blob/main/vercel-nextjs/src/app/api/trips/route.ts) - the `GET /api/trips` handler: queries the 20 most recent `nyc.taxi` trips.
- [`src/app/api/stats/route.ts`](https://github.com/motherduckdb/motherduck-examples/blob/main/vercel-nextjs/src/app/api/stats/route.ts) - the `GET /api/stats` handler: validates `YYYY-MM-DD` dates and runs the parameterized `$1`/`$2` aggregate.
- [`.env.local.example`](https://github.com/motherduckdb/motherduck-examples/blob/main/vercel-nextjs/.env.local.example) - template for the three env vars; copy to `.env.local` and set `MOTHERDUCK_TOKEN`.
- [`package.json`](https://github.com/motherduckdb/motherduck-examples/blob/main/vercel-nextjs/package.json) - dependencies (`next`, `pg`, `@vercel/functions`, React) and the `dev`/`build`/`start` scripts.
- [`next.config.ts`](https://github.com/motherduckdb/motherduck-examples/blob/main/vercel-nextjs/next.config.ts) - Next.js config (empty defaults).
- [`tsconfig.json`](https://github.com/motherduckdb/motherduck-examples/blob/main/vercel-nextjs/tsconfig.json) - TypeScript config, including the `@/*` to `src/*` path alias.

## Caveats

- The token is a credential. Keep it in `.env.local` (gitignored) for local dev
  and in Vercel environment variables for deploy. Do not commit it or expose it
  in client-side code; these queries run only in server-side API routes.
- `src/lib/motherduck.ts` throws at import time if `MOTHERDUCK_TOKEN` is unset.
  Locally that surfaces immediately; on Vercel a missing variable fails the
  function at runtime, so set the env var before relying on the routes.
- The pool is module-level so it can be reused across warm invocations, but
  serverless instances are not shared. Under burst traffic many instances each
  open up to `max: 10` connections; size `max` against your MotherDuck plan's
  connection limits rather than assuming a single global pool.
- Identifiers (table and column names) cannot be parameterized with `$1`. Only
  values can. If a route needs a dynamic table or column name, validate it
  against an allow-list instead of interpolating user input.
- The stats route binds dates as timestamp strings (`YYYY-MM-DD 00:00:00`), and
  the regex enforces that shape. If you loosen the input format, keep the bound
  value a type MotherDuck can compare against `tpep_pickup_datetime`, or the
  query errors or returns nothing.
- The `nyc.taxi` data is historical, so `ORDER BY tpep_pickup_datetime DESC` in
  `/api/trips` returns the latest rows in the dataset, not today's trips. Pick a
  date range that actually exists in the data when testing `/api/stats`.

## How it works / learn more

- `src/lib/motherduck.ts`: the shared `pg.Pool`, the `attachDatabasePool`
  cleanup hook, and the `withClient` checkout/release helper.
- `src/app/api/trips/route.ts` and `src/app/api/stats/route.ts`: the two API
  handlers, including the `YYYY-MM-DD` validation and the parameterized `$1`/`$2`
  aggregate.
- Postgres endpoint reference: [authenticating and connecting using the Postgres endpoint](https://motherduck.com/docs/key-tasks/authenticating-and-connecting-to-motherduck/postgres-endpoint).
- For deeper MotherDuck or DuckDB SQL questions, use the `ask_docs_question` MCP tool or the MotherDuck docs.


---

## Docs feedback

MotherDuck accepts optional user-submitted feedback about this page at `POST https://motherduck.com/docs/api/feedback/agent`.
For agents and automated tools, feedback submission should be user-confirmed before sending.

Payload:

```json
{
  "page_path": "/cookbook/vercel-nextjs/",
  "page_title": "Query MotherDuck from Vercel and Next.js",
  "text": "<the user's feedback, max 2000 characters>",
  "source": "<optional identifier for your interface, for example 'claude.ai' or 'chatgpt'>"
}
```

`page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": "<uuid>"}`, `400` for malformed payloads, and `429` when rate-limited.