Skip to main content

Vercel

Vercel is a cloud platform for static sites and serverless functions. It supports two ways to connect your Next.js application to MotherDuck:

  • Postgres endpoint — connects from server-side API routes using the standard pg npm package. This lets you connect to MotherDuck databases like a regular Postgres database, but use MotherDuck as the fast analytics query backend.
  • Wasm SDK — runs DuckDB directly in the browser using WebAssembly. Best for building analytics dashboards that are highly interactive, allowing queries to execute on the user's device.

Both approaches work with Vercel's Native Integration for automatic token management.

Vercel typically provides two ways to integrate with 3rd party services:

  • Native integration: create a new account on the 3rd party service and connect it to Vercel. Billing and setup is managed by Vercel.
  • Non-native integration (connectable accounts): connect existing 3rd party accounts to Vercel.
info

Vercel supports Native Integration with MotherDuck. Support for non-native integration (connectable accounts) is not yet available. But to use your existing MotherDuck account, populate the Vercel app environment variables with a MotherDuck token for your existing account.

Native integration​

To kickstart the integration, you can either start from:

How to install​

  1. To install the MotherDuck Native Integration from the Vercel Marketplace: 2 Navigate to the Vercel Marketplace or to the Integrations Console on your Vercel Dashboard.
  2. Locate the MotherDuck integration.
  3. Click Install.
  4. On the Install MotherDuck modal, you are presented with two plans options. modal1
  5. On the next modal, you would be prompt to give your database a name. Note that a new installation will create a new account and database within a new MotherDuck organization. modal2
  6. You are all set! You have now a new account and database within a new organization. Plus, tokens (access token, and read scaling token) are automatically generated and stored in Vercel's environment variables. model3

You can head to Getting Started section on the integration page to have more information on how to use the integration. model4

Connect using the Postgres endpoint​

Next.js API routes can connect to MotherDuck through the Postgres endpoint using the pg npm package. This gives you a thin-client path to query MotherDuck from serverless functions without any DuckDB dependencies.

This guide walks through building a Next.js app that queries NYC taxi data from MotherDuck's built-in sample_data database.

Prerequisites​

Project setup​

Create a new Next.js project and install the Postgres client:

npx create-next-app@latest motherduck-nextjs --typescript --app
cd motherduck-nextjs
npm install pg @vercel/functions
npm install --save-dev @types/pg

Store your token​

For local development, create a .env.local file (this is gitignored by default in Next.js):

MOTHERDUCK_TOKEN="your_token_here"
MOTHERDUCK_HOST="pg.us-east-1-aws.motherduck.com"
MOTHERDUCK_DB="sample_data"

For production, add the environment variable through the Vercel dashboard or CLI:

vercel env add MOTHERDUCK_TOKEN

If you installed the Native Integration, your access token is already available as an environment variable.

Create the connection pool​

Create src/lib/motherduck.ts. The pool is initialized at module scope so it persists across requests within the same function instance — this is the recommended pattern for connection pooling on Vercel.

import { Pool, PoolClient } from "pg";
import { attachDatabasePool } from "@vercel/functions";

const token = process.env.MOTHERDUCK_TOKEN;
const host = process.env.MOTHERDUCK_HOST ?? "pg.us-east-1-aws.motherduck.com";
const db = process.env.MOTHERDUCK_DB ?? "sample_data";

if (!token) {
  throw new Error("MOTHERDUCK_TOKEN environment variable is required");
}

const pool = new Pool({
  connectionString: `postgresql://user:${token}@${host}:5432/${db}`,
  ssl: { rejectUnauthorized: true },
  max: 10,
  idleTimeoutMillis: 5000,
});

attachDatabasePool(pool);

export async function withClient<T>(
  fn: (client: PoolClient) => Promise<T>
): Promise<T> {
  const client = await pool.connect();
  try {
    return await fn(client);
  } finally {
    client.release();
  }
}

A few things to note:

  • attachDatabasePool(pool) from @vercel/functions ensures idle connections are cleaned up before a function instance is suspended, preventing connection leaks.
  • idleTimeoutMillis: 5000 closes unused connections after 5 seconds, balancing reuse during traffic bursts with prompt cleanup during quiet periods.
  • ssl: { rejectUnauthorized: true } enables full certificate verification (verify-full). Node.js verifies the server certificate against the system CA bundle and checks that the hostname matches the certificate. MotherDuck's Postgres endpoint uses a publicly trusted certificate, so no custom CA configuration is needed.

Write the API routes​

Create two route handlers. The first returns a sample of recent taxi trips.

src/app/api/trips/route.ts

import { NextResponse } from "next/server";
import { withClient } from "@/lib/motherduck";

export async function GET() {
  try {
    const rows = await withClient(async (client) => {
      const result = await client.query(
        `SELECT
          tpep_pickup_datetime AS pickup,
          tpep_dropoff_datetime AS dropoff,
          passenger_count,
          trip_distance,
          fare_amount,
          tip_amount,
          total_amount
        FROM nyc.taxi
        ORDER BY tpep_pickup_datetime DESC
        LIMIT 20`
      );
      return result.rows;
    });

    return NextResponse.json(rows);
  } catch (error) {
    console.error("Failed to fetch trips:", error);
    return NextResponse.json(
      { error: "Failed to fetch trips" },
      { status: 500 }
    );
  }
}

The second accepts date range parameters and returns aggregated fare data. It validates inputs before querying and uses parameterized queries ($1, $2) to prevent SQL injection — never interpolate user input directly into SQL strings.

src/app/api/stats/route.ts

import { NextRequest, NextResponse } from "next/server";
import { withClient } from "@/lib/motherduck";

export async function GET(request: NextRequest) {
  const startDate = request.nextUrl.searchParams.get("start");
  const endDate = request.nextUrl.searchParams.get("end");

  if (!startDate || !endDate) {
    return NextResponse.json(
      {
        error:
          "Both 'start' and 'end' query parameters are required. Use YYYY-MM-DD format.",
      },
      { status: 400 }
    );
  }

  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 }
    );
  }

  try {
    const data = await withClient(async (client) => {
      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`]
      );
      return result.rows[0];
    });

    return NextResponse.json({
      start: startDate,
      end: endDate,
      ...data,
    });
  } catch (error) {
    console.error("Failed to fetch stats:", error);
    return NextResponse.json(
      { error: "Failed to fetch stats" },
      { status: 500 }
    );
  }
}

Test locally​

npm run dev

Then open http://localhost:3000/api/trips or try the stats endpoint with a date range:

http://localhost:3000/api/stats?start=2022-11-01&end=2022-12-01

Deploy​

vercel deploy

Or push to a connected Git repository — Vercel deploys automatically on every push.

Connect using the Wasm SDK​

The Wasm SDK runs DuckDB in the browser, making it ideal for highly interactive analytics dashboards.

Project templates​

Learn more about how to setup your projects by using the following templates:

  • MotherDuck's Vercel template : A fully-fledged template that includes a Next.js project and a MotherDuck WASM setup with sample data integration and an interactive data visualization example.
  • MotherDuck's Vercel template minimal : a minimal template which includes a Next.js project and MotherDuck Wasm setup with some sample data integration.