Connecting Cloudflare D1 SQLite Database with Metabase for Powerful Analytics

Are you looking to unlock deeper insights from your Cloudflare D1 database? This step-by-step guide will show you how to export your Cloudflare D1 SQLite database and connect it to Metabase for powerful analytics and visualization capabilities.

Cloudflare D1 offers a serverless SQL database that's globally distributed and built on SQLite, making it perfect for applications that need low-latency data access. However, when it comes to analytics and reporting, you might want more robust visualization tools. That's where Metabase comes in - an open-source business intelligence tool that makes it easy to create dashboards and explore your data.

This guide will walk you through the entire process of exporting your D1 database and setting it up with Metabase on a remote VM. The operation takes approximately 15 minutes to complete, after which you'll have a fully configured Metabase instance running with Docker and using your exported Cloudflare D1 database.

Prerequisites

Before we begin, make sure you have the following:

• An active Cloudflare account
• A Cloudflare D1 database with your data
• SSH access to a remote VM with:
  • Docker installed and running
  • Node.js installed (with npm)
  • SQLite installed
  • Open ports: 3000 (for Metabase) and 8976 (for Wrangler authentication)
• 15 minutes of free time

---

Step 1: Install Wrangler CLI

First, we need to install Wrangler, Cloudflare's command-line tool for managing D1 databases and other Cloudflare resources.

npm install wrangler --save-dev
npx wrangler login

Step 2: Authenticate Wrangler on a Remote VM

This is where things get interesting. The Wrangler login process uses a web browser for authentication with Cloudflare, but we're working on a remote VM accessed via SSH!

When you execute npx wrangler login, you'll be prompted to open a link in your browser:

 ⛅️ wrangler 4.7.1
------------------

Attempting to login via OAuth...
Opening a link in your default browser: https://dash.cloudflare.com/oauth2/auth?response_type=code&client_id=*redacted*&redirect_uri=http%3A%2F%2Flocalhost%3A8976%2Foauth%2Fcallback&scope=account%3Aread%20user%3Aread%20workers%3Awrite%20workers_kv%3Awrite%20workers_routes%3Awrite%20workers_scripts%3Awrite%20workers_tail%3Aread%20d1%3Awrite%20pages%3Awrite%20zone%3Aread%20ssl_certs%3Awrite%20ai%3Awrite%20queues%3Awrite%20pipelines%3Awrite%20secrets_store%3Awrite%20offline_access&state=*redacted*&code_challenge=*redacted*&code_challenge_method=S256

Clicking that link will open it in your local web browser. After authenticating with Cloudflare, the browser will attempt to redirect to http://localhost:8976/.... However, this port is on your remote VM, not your local machine!

Setting Up a Reverse SSH Tunnel

To solve this problem, we need to set up a reverse SSH tunnel that forwards port 8976 from the remote machine to your local computer:

ssh -L 8976:0.0.0.0:8976 -N -A root@your-vm-ip-address

With this tunnel in place, when your browser tries to access http://localhost:8976/..., the request will be forwarded to the remote VM, allowing the authentication process to complete successfully.

After successful authentication, verify that Wrangler can access your D1 databases:

wrangler d1 list

You should see output similar to:

┌──────────────────────────────────────┬───────┬──────────────────────────┬────────────┬────────────┬───────────┐
│ uuid                                 │ name  │ created_at               │ version    │ num_tables │ file_size │
├──────────────────────────────────────┼───────┼──────────────────────────┼────────────┼────────────┼───────────┤
│ e8907acf-6e61-466b-575b-0e8a7344a78e │ my-db │ 2024-03-28T21:49:35.780Z │ production │ 6          │ 53624832  │
└──────────────────────────────────────┴───────┴──────────────────────────┴────────────┴────────────┴───────────┘

Step 3: Create a Wrangler Configuration File

Next, create a wrangler.toml file in your root directory to define your D1 database binding:

[[d1_databases]]
binding = "DB"
database_name = "<your-database-name>"
database_id = "<your-database-id>"

Replace <your-database-name> and <your-database-id> with the values from the wrangler d1 list output.

Step 4: Export Your Cloudflare D1 Database

Now that everything is set up, we can export the database using Wrangler. The following command will export your D1 database to a SQL file:

WARNING

Be aware that exporting is a blocking operation and all write and reads to a database will be blocked.

npx wrangler d1 export <your-database-name> --output=d1export.sql --remote

This command connects to Cloudflare's servers, retrieves your database, and saves it as SQL statements in the d1export.sql file. For more details on Wrangler commands, check the official Cloudflare D1 documentation.

Step 5: Import the SQL Export to SQLite

The exported file contains SQL statements that need to be imported into a local SQLite database. While you could use the standard approach:

sqlite3 my-database.sqlite < d1export.sql
# or
sqlite> .read d1export.sql

These methods can be very slow for larger databases because SQLite synchronizes changes to disk and writes to a journal by default.

Optimizing SQLite Import Performance

To dramatically speed up the import process (up to 100x faster), we can temporarily disable synchronization and journaling:

sqlite3 my-database.sqlite
SQLite> PRAGMA synchronous = OFF;
SQLite> PRAGMA journal_mode = MEMORY;
SQLite> .read d1export.sql

These PRAGMA settings tell SQLite to:

• Skip synchronizing changes to disk after each transaction
• Keep the journal in memory instead of writing it to disk

After the import completes, verify that all tables were imported correctly:

SQLite> .tables

This command will list all the tables in your imported database.

Step 6: Set Up Metabase with Docker

Now it's time to set up Metabase using Docker. The following command pulls the Metabase image and starts a container with the necessary configurations:

docker run -d -p 3000:3000 \
  -v /path/to/your/local/metabase-data:/metabase-data \
  -v /path/to/your/sqlite/database:/sqlite-db \
  -e "MB_DB_FILE=/metabase-data/metabase.db" \
  -e "MB_DB_TYPE=h2" \
  --name metabase \
  metabase/metabase

This command:

• Maps port 3000 on your VM to port 3000 in the container
• Creates volume mounts for Metabase's internal database and your SQLite database
• Sets environment variables for Metabase's configuration
• Names the container "metabase" for easy reference

Metabase takes a few minutes to start up. You can monitor the startup process by checking the container logs:

# First, get the container ID
docker ps

CONTAINER ID   IMAGE               COMMAND                  CREATED        STATUS        PORTS                                       NAMES
a584101b1a0f   metabase/metabase   "/app/run_metabase.sh"   18 hours ago   Up 18 hours   0.0.0.0:3000->3000/tcp, :::3000->3000/tcp   metabase

# Then, view the logs
docker logs -f a584101b1a0f

Step 7: Access and Configure Metabase

After a few minutes, Metabase should be up and running. Access it by opening http://<your-vm-ip-address>:3000 in your web browser.

Follow the setup wizard to create an admin account and configure your Metabase instance. When you reach the step to set up your first database connection:

1. Select "SQLite" as the database type
2. For the database path, enter: /sqlite-db/my-database.sqlite (this is the path inside the Docker container where your SQLite database is mounted)
3. Give your database a name (e.g., "Cloudflare D1 Data")
4. Complete the setup process

Once connected, Metabase will analyze your database schema and prepare it for exploration and visualization.

Step 8: Automate Database Updates with a Cron Job

To keep your Metabase instance updated with the latest data from your Cloudflare D1 database, you can set up a nightly cron job that repeats the export and import process automatically.

Create a shell script named update_d1_database.sh:

#!/bin/bash

# Set variables
DB_NAME="your-database-name"
EXPORT_FILE="d1export.sql"
SQLITE_DB="/path/to/your/sqlite/database/my-database.sqlite"

# Export D1 database
npx wrangler d1 export $DB_NAME --output=$EXPORT_FILE --remote

# Create a temporary SQLite database
sqlite3 $SQLITE_DB.new <<EOF
PRAGMA synchronous = OFF;
PRAGMA journal_mode = MEMORY;
.read $EXPORT_FILE
EOF

# Replace the old database with the new one
mv $SQLITE_DB.new $SQLITE_DB

# Clean up
rm $EXPORT_FILE

echo "Database update completed at $(date)"

Make the script executable:

chmod +x update_d1_database.sh

Add it to your crontab to run nightly:

crontab -e

Add the following line to run the script at 2 AM every day:

0 2 * * * /path/to/update_d1_database.sh >> /path/to/update_log.txt 2>&1

Benefits of Using Metabase with Cloudflare D1

Connecting your Cloudflare D1 database to Metabase offers several advantages:

1. Powerful Visualization: Create charts, dashboards, and reports that help you understand your data at a glance
2. SQL Editor: Write custom SQL queries to extract specific insights from your data
3. Scheduled Reports: Set up automated reports to be delivered via email
4. Team Collaboration: Share dashboards and insights with your team
5. Data Exploration: Easily explore your data with Metabase's intuitive interface
6. Cross-Database Queries: Compare data across different databases if you're using multiple data sources

Conclusion

By following this guide, you've successfully connected your Cloudflare D1 SQLite database to Metabase, unlocking powerful analytics and visualization capabilities. This setup gives you the best of both worlds: the performance and global distribution of Cloudflare D1 for your application data, and the robust analytics features of Metabase for business intelligence.

The automated update process ensures that your analytics are always based on the most current data, making this solution suitable for production environments where data-driven decisions are critical.

Whether you're tracking user behavior, monitoring application performance, or analyzing business metrics, this Cloudflare D1 and Metabase integration provides a solid foundation for your data analytics needs.