Checkout the previous post on How I deployed my Astro + Turso + Drizzle project to Cloudflare Pages if you want to know more about the project.

Background

I wanted to migrate my Turso DB to Cloudflare D1. As such this post will go into the details of the challenges I faced during the migration process.

You can find the project and the repository below:

Challenges

Making D1 work with drizzle and Astro

D1 as well as drizzle-kit requires the project to have a wrangler.toml file. Here is mine:

#:schema node_modules/wrangler/config-schema.json
name = "map"
compatibility_date = "2024-10-11"
pages_build_output_dir = "./dist"

[placement]
mode = "smart"

[vars]
NODE_VERSION = "20.17.0"
PNPM_VERSION = "9.9.0"

[[d1_databases]]
binding = "MAP"
database_name = "map"
database_id = "10eb6d23-6724-46e8-9bf2-c4091739f69f"
migrations_dir = "migrations"
migrations_table = "__drizzle_migrations"

Ensure the corresponding D1 binding is also set in Cloudflare dashboard. Based on their docs it seemed like drizzle expects the argument to be of type D1Database. Since env.d.ts file is being used throughout the application for environment related stuff, I updated it like so:

/// <reference path="../.astro/types.d.ts" />

type Runtime = import("@astrojs/cloudflare").Runtime<Env>;

declare namespace App {
  interface Locals extends Runtime {
    env: {
      CLOUDFLARE_ACCOUNT_ID: string;
      CLOUDFLARE_DATABASE_ID: string;
      CLOUDFLARE_D1_TOKEN: string;
      CLOUDFLARE_D1_BINDING: string;
      MAP: D1Database;
    };
  }
}

interface ImportMeta {
  readonly env: {
    readonly CLOUDFLARE_ACCOUNT_ID: string;
    readonly CLOUDFLARE_DATABASE_ID: string;
    readonly CLOUDFLARE_D1_TOKEN: string;
    readonly CLOUDFLARE_D1_BINDING: string;
  };
}

If you observe, MAP in the env.d.ts file matches the binding in the wrangler.toml file.

Turso dump was not working

I wanted to take a dump of my data. After searching, I found out about the dump command. I ran turso db shell map-viz ".dump" > map-viz.sql. map-viz being the name of my database. However, every single time, the map-viz.sql was being created with empty content. I’m not sure what was wrong since I was correctly executing the command and the data was also present. So I finally resorted to manually exporting the data in the CSV format.

D1 does not support importing data in the CSV format

Feeling good about myself after clearing the Turso hurdle, I was disappointed to find out that D1 does not support CSV import (at least as of this writing). So how to solve this new issue then?

I updated my env to point to the hosted D1 and ran pnpm db:migrate to apply the migrations to my database. Good thing was D1 allowed running SQL queries directly. Since I had the CSV data, I wanted to create SQL statements from it. What good way than to use Claude for this. I asked it to write SQL queries and provided it a couple of CSV records. After modifying the output a bit, here is the final JS file which I used:

const fs = require("fs");
const csv = require("csv-parse/sync");

const fileName = "location.csv";
const tableName = "location";

// Function to escape single quotes in string values
function escapeQuotes(value) {
  return value.replace(/'/g, "''");
}

// Function to format value for SQL
function formatValue(value) {
  if (value === "") return "NULL";
  if (isNaN(value)) return `'${escapeQuotes(value)}'`;
  return value;
}

// Read the CSV file
const fileContent = fs.readFileSync(fileName, "utf8");

// Parse the CSV content
const records = csv.parse(fileContent, {
  columns: true,
  skip_empty_lines: true,
});

// Generate INSERT statements
const insertStatements = records.map((record) => {
  const columns = Object.keys(record).join(", ");
  const values = Object.values(record).map(formatValue).join(", ");
  return `INSERT INTO ${tableName} (${columns}) VALUES (${values});`;
});

// Write INSERT statements to a file
fs.writeFileSync(`${fileName}.sql`, insertStatements.join("\n"));

console.log(`INSERT statements have been written to ${fileName}.sql`);

After running the script it generated the SQL statements like so:

INSERT INTO location (id, created_at, updated_at, latitude, longitude, city, region, region_code, country, hash, count, last_visit) VALUES (54, '2024-10-12 11:21:03', 1728732060, -34.0866, 18.8144, 'Somerset West', 'Western Cape', 'WC', 'ZA', '-34.08660-18.81440-Somerset West-Western Cape-WC-ZA-45.222.74.86', 1, 1728732060);
INSERT INTO location (id, created_at, updated_at, latitude, longitude, city, region, region_code, country, hash, count, last_visit) VALUES (64, '2024-10-12 17:19:12', 1728753549, 46.3153, 4.8344, 'Mâcon', 'Bourgogne-Franche-Comté', 'BFC', 'FR', '46.31530-4.83440-Mâcon-Bourgogne-Franche-Comté-BFC-FR-2a01:cb10:82ae:c600:259e:a34c:1e8e:a4b5', 1, 1728753549);

SQL commands now being generated, the last step was to import the data. For which I ran wrangler d1 execute map --file=./location.csv.sql --remote and voila! The data was there in my D1 database.

Conclusion

Even though I faced some challenges during integration of Cloudflare services and migrating data, moving to D1 was a good decision since the initial load times have pretty much been cut in half.