将数据导入到 Cloudflare D1

背景

最近在实现 Mass Block Twitter 插件的 Spam 账户共享黑名单时，使用了 Cloudflare D1 作为服务端的存储数据库。而之前在本地 indexedDB 中已经存储了一些数据，所以需要导入现有的数据到 Cloudflare D1 中。使用 IndexedDB Exporter 插件将 indexedDB 数据库导出为 JSON 数据，但如何导入仍然是个问题。

最初参考了 Cloudfalre D1 的官方文档，但它并没有如何导入 JSON 数据的指南，而且它也只能在 Cloudflare Worker 中使用，但它确实提供通过 wrangler 执行 SQL 的功能，所以重要的是如何从 JSON 得到 SQL。

尝试

直接从 JSON 生成 Insert SQL

最初看起来这很简单，既然已经有了 JSON 数据，从 JSON 数据生成 SQL 字符串似乎并不难，只需要一些简单的字符串拼接即可。但实际上比想象中的麻烦，主要是 SQL 中有许多转义很烦人，边界情况似乎很多，JSON.stringify 生成的字符串在 SQL 中有错误的语法。

例如

• " 双引号
• ' 单引号
• \n 换行
• 可能的 SQL 注入
• 其他。。。

虽然也调研过一些现有的 npm 包，例如 json-sql-builder2，但它似乎并不支持生成在单个 SQL 中插入多行的功能，因此一开始并为考虑。而且它生成的结果如下，看起来是给代码用的，而非直接可以执行的 SQL 文件。

{
  "sql": "INSERT INTO people (first_name, last_name, age) VALUES (?, ?, ?)",
  "values": ["John", "Doe", 40]
}

调用 Rest API

在生成的 SQL 一直出现语法错误之后，一度尝试过直接在 Worker 中实现一个 API 用来做这件事，直到在 wrangler dev 在本地测试发现效率很低后放弃了，实现 API 本身反而并不复杂。

导入到 sqlite 然后 dump 出 Insert SQL

最后还是回归到生成 SQL 的方案上，基本流程如下

1. 从 JSON 生成 CSV
2. 使用 sqlite3 导入 CSV 到本地 .sqlite 文件
3. 使用 sqlite3 dump 数据到 .sql 文件
4. 使用 wrangler cli 执行 .sql 文件导入数据

sqlite3 db.sqlite < migrations/0001_init.sql # 初始化一个 sqlite 数据库

vite-node 0002_import.ts # 生成需要的 csv 文件

sqlite3 db.sqlite <<EOF
.mode csv
.import users.csv Tweet
EOF # 导入 csv 到 sqlite 中

sqlite3 db.sqlite .dump > db.sql # 导出数据到 sql 文件
# ⚠️ 注意，导出数据后需要进行一些必要的编辑，删除创建 table 和 index 的语句，还有 csv 导入时可能错误导入的标题行，仅保留正确的 insert into 语句
npx wrangler d1 execute mass-block-twitter --local --file=migrations/db.sql # 执行 sql 文件导入数据，修改 --local 为 --remote 在远端执行操作

0002_import.ts 的具体实现

import _data from './assets/mass-db_exported_data.json'
import { writeFile } from 'node:fs/promises'
import path from 'node:path'
import { Parser } from '@json2csv/plainjs'
import { tweetSchema, userSchema } from '../lib/request'
import { Tweet, User } from '@prisma/client'

const data = _data as {
  tweets: (typeof tweetSchema._type & {
    user_id: string
    updated_at: string
  })[]
  users: (typeof userSchema._type & {
    updated_at: string
  })[]
}

const list: User[] = data.users.map(
  (it) =>
    ({
      id: it.id,
      screenName: it.screen_name,
      name: it.name,
      description: it.description ?? null,
      profileImageUrl: it.profile_image_url ?? null,
      accountCreatedAt: it.created_at ? new Date(it.created_at) : null,
      spamReportCount: 0,
      createdAt: new Date(it.updated_at!),
      updatedAt: new Date(it.updated_at!),
    } satisfies User),
)
const parser = new Parser({
  fields: [
    'id',
    'screenName',
    'name',
    'description',
    'profileImageUrl',
    'accountCreatedAt',
    'spamReportCount',
    'createdAt',
    'updatedAt',
  ],
})
const csv = parser.parse(list)
await writeFile(path.resolve(__dirname, 'users.csv'), csv)

总结

Cloudflare D1 是一个不错的数据库，基本在 Web 场景中是完全可用的，而且与 Worker 一起使用时也无需关注 Worker 执行的区域与数据库所在的区域可能不一致的问题。而关于这次的数据导入的麻烦，如果已经熟悉 D1 或数据库的话，可能 10 分钟就搞定了，还是太久不碰数据库和后端生疏了。