Skip to main content

Quickstart

Preview
This feature is in preview and is subject to change.

This walkthrough goes from an empty terminal to a published Dive: you'll explore data with motherduck query, save a result as a table, build a small React app on top of it, and publish it. The last section shows how to drive the same commands from a script with --output json.

It takes about ten minutes.

Before you begin​

Install the CLI and sign in:

motherduck login

Without a MotherDuck account, motherduck new creates one from the terminal and leaves you signed in to it.

Confirm which account you're working in:

motherduck status

This walkthrough uses sample_data, which is attached to every account, and writes one table into your default database, my_db.

Step 1: Explore the data​

motherduck query runs SQL and writes the result to stdout. Start by looking at what's in the sample taxi table:

motherduck query "DESCRIBE sample_data.nyc.taxi"

Then shape the numbers you want to chart, daily trip counts and average fares for one month:

motherduck query "
  SELECT strftime(tpep_pickup_datetime, '%Y-%m-%d') AS trip_day,
         count(*) AS trips,
         round(avg(fare_amount), 2) AS avg_fare
  FROM sample_data.nyc.taxi
  WHERE tpep_pickup_datetime >= '2022-11-01'
    AND tpep_pickup_datetime < '2022-12-01'
  GROUP BY ALL
  ORDER BY trip_day
  LIMIT 5
"

That prints one row per day, with the trip count and average fare.

Long statements are easier to keep in a file. --file reads one, and --timeout raises the 120-second default when a statement needs it:

motherduck query --file daily_trips.sql --timeout 600

Step 2: Save the result as a table​

A Dive queries MotherDuck live, so give it something to read. Drop the LIMIT and write the result into my_db:

motherduck query "
  CREATE OR REPLACE TABLE my_db.main.taxi_daily AS
  SELECT strftime(tpep_pickup_datetime, '%Y-%m-%d') AS trip_day,
         count(*) AS trips,
         round(avg(fare_amount), 2) AS avg_fare
  FROM sample_data.nyc.taxi
  WHERE tpep_pickup_datetime >= '2022-11-01'
    AND tpep_pickup_datetime < '2022-12-01'
  GROUP BY ALL
"

Step 3: Scaffold the Dive​

motherduck dive init taxi_trips --title "Taxi trips"

That creates taxi_trips/, holding the component and its metadata file. Nothing has reached MotherDuck yet.

Step 4: Write the component​

Replace taxi_trips/index.tsx with a chart over the table you created:

import { useSQLQuery } from '@motherduck/react-sql-query';
import { Bar, BarChart, ResponsiveContainer, Tooltip, XAxis, YAxis } from 'recharts';

export const REQUIRED_DATABASES = [
  { type: 'database', path: 'md:my_db', alias: 'my_db' },
];

const N = (value: unknown): number => (value == null ? 0 : Number(value));

export default function TaxiTrips() {
  const dailyQuery = useSQLQuery(`
    SELECT trip_day, trips, avg_fare
    FROM "my_db"."main"."taxi_daily"
    ORDER BY trip_day
  `);

  const rows = Array.isArray(dailyQuery.data) ? dailyQuery.data : [];
  const chartData = rows.map((row) => ({
    day: String(row.trip_day),
    trips: N(row.trips),
  }));

  return (
    <main>
      <h1>NYC taxi trips, November 2022</h1>
      {dailyQuery.isLoading ? (
        <div>Loading trips...</div>
      ) : (
        <ResponsiveContainer width="100%" height={320}>
          <BarChart data={chartData}>
            <XAxis dataKey="day" />
            <YAxis />
            <Tooltip />
            <Bar dataKey="trips" />
          </BarChart>
        </ResponsiveContainer>
      )}
    </main>
  );
}

REQUIRED_DATABASES is the part push reads. It takes the Dive's dependency list from that export, so there's nothing to keep in step by hand.

The rest — the query API, the numeric conversion, the quoted table name — follows the Dive authoring guide. Run motherduck dive guide before writing or editing a Dive. It ships with the CLI, so it describes the runtime you actually have.

Step 5: Preview it locally​

motherduck dive watch taxi_trips

This serves the Dive at http://127.0.0.1:5173 and re-renders it on every save, against your live MotherDuck data. Edit index.tsx and watch the chart change. --port picks another port, and --no-open leaves the browser alone.

Step 6: Publish it​

motherduck dive push taxi_trips

The first push creates the Dive, records its ID in dive.metadata.json, and prints the URL to open. Every later push adds a version:

motherduck dive push taxi_trips --version-description "add the fare axis"
motherduck dive list-versions taxi_trips

Step 7: Read the output as JSON​

Everything above also works unattended. -o json names the resource a command acted on, so a script can pull one value out with jq:

DIVE_URL=$(motherduck dive push taxi_trips -o json | jq -r '.dive.url')
echo "Published to $DIVE_URL"

query is the exception, returning rows as a bare array. Failures exit non-zero across every command, with an error object in place of the result. See output formats for the shapes.

Because the exit code is meaningful, a query can gate the rest of a script:

if ! motherduck query --file checks.sql -o json > result.json; then
  echo "checks failed" >&2
  exit 1
fi

csv suits results that are naturally tabular:

motherduck query "SELECT * FROM my_db.main.taxi_daily" -o csv > taxi_daily.csv
motherduck dive list -o csv > dives.csv

In CI, skip motherduck login and pass a token instead. See authentication.

Clean up​

motherduck dive delete --dive <id>
motherduck query "DROP TABLE my_db.main.taxi_daily"

dive delete asks you to confirm. Your local taxi_trips/ directory stays where it is.

Next steps​