Cloudflare D1をWranguler CLI/Web APIから操作してみる

Cloudflare D1とは

• Cloudflare Workersから利用可能なサーバレスなデータベース
• 内部的にはSQLiteを使用している
• Cloudflareの保有するエッジネットワーク上に構築されている
• マスタとなるDBが1つと、そこからレプリケーションされた読み取り専用のDBがエッジネットワークに展開される
• 内部的にDurable Objectsを使用している

D1の構築

Wrangler CLIの設定

• Wrangler CLIはCloudflare Workers向けのCLI
• Cloudflareの様々なサービスの設定や操作をコマンドラインで実現するためのツール
• 詳細: Install/Update Wrangler

# wranglerのインストール
$ npm i -g wrangler

# Cloudflareにログイン
$ wrangler login

# アルファ版であることを表記したワーニングが毎回出力されると邪魔なのでオフにしておく
# 内容: D1 Bindings are currently in alpha to allow the API to evolve before general availability. Please report any issues to https://github.com/cloudflare/workers-sdk/issues/new/choose
$ export NO_D1_WARNING=true

DBの作成

# DBの作成
# [[d1_databases]] 以下の項目は後々使用するので内容を控えておく
$ wrangler d1 create my-first-d1
--------------------
🚧 D1 is currently in open alpha and is not recommended for production data and traffic
🚧 Please report any bugs to https://github.com/cloudflare/workers-sdk/issues/new/choose
🚧 To request features, visit https://community.cloudflare.com/c/developers/d1
🚧 To give feedback, visit https://discord.gg/cloudflaredev
--------------------

✅ Successfully created DB 'my-first-d1'!

Add the following to your wrangler.toml to connect to it from a Worker:

[[ d1_databases ]]
binding = "DB" # i.e. available in your Worker on env.DB
database_name = "my-first-d1"
database_id = "XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX"

# DBが作成されたことを確認
$ wrangler d1 list
--------------------
🚧 D1 is currently in open alpha and is not recommended for production data and traffic
🚧 Please report any bugs to https://github.com/cloudflare/workers-sdk/issues/new/choose
🚧 To request features, visit https://community.cloudflare.com/c/developers/d1
🚧 To give feedback, visit https://discord.gg/cloudflaredev
--------------------

┌──────────────────────────────────────┬─────────────┬─────────────────────────────┐
│ uuid                                 │ name        │ created_at                  │
├──────────────────────────────────────┼─────────────┼─────────────────────────────┤
│ XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX │ my-first-d1 │ 2023-03-20T01:16:25.883384Z │
└──────────────────────────────────────┴─────────────┴─────────────────────────────┘

• ダッシュボードのWorkers > D1 からも作成されていることが確認できる

テーブルの作成とレコードの追加

• usersテーブルを作成し、レコードを1つ追加するSQLを用意する

users.sql

DROP TABLE IF EXISTS users;

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER NOT NULL
);

INSERT INTO Users (name, age) VALUES ("Takashi", 25);

• SQLの実行

# テーブルの作成とレコードの追加を行うSQLを実行
# --fileオプションでSQLファイルを指定することができる
$ wrangler d1 execute my-first-d1 --file=./users.sql
--------------------
🚧 D1 is currently in open alpha and is not recommended for production data and traffic
🚧 Please report any bugs to https://github.com/cloudflare/workers-sdk/issues/new/choose
🚧 To request features, visit https://community.cloudflare.com/c/developers/d1
🚧 To give feedback, visit https://discord.gg/cloudflaredev
--------------------

🌀 Mapping SQL input into an array of statements
🌀 Parsing 3 statements
🌀 Executing on my-first-d1 (XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX):
🚣 Executed 3 commands in 16.656564995646477ms
value of stdout.lastframe() is undefined

# テーブルが作成されていることを確認
$ wrangler d1 execute my-first-d1 --command="SELECT name FROM sqlite_master WHERE type='table';"
Delegating to locally-installed wrangler@2.12.3 over global wrangler@2.12.3...
Run `npx wrangler d1 execute my-first-d1 --command=SELECT name FROM sqlite_master WHERE type='table';` to use the local version directly.


🌀 Mapping SQL input into an array of statements
🌀 Parsing 1 statements
🌀 Executing on my-first-d1 (XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX):
🚣 Executed 1 command in 0.15126200020313263ms
┌─────────────────┐
│ name            │
├─────────────────┤
│ d1_kv           │
├─────────────────┤
│ sqlite_sequence │
├─────────────────┤
│ users           │
└─────────────────┘

# レコードが追加されていることを確認
# --commandオプションでSQLを直接引数に渡すことができる
$ wrangler d1 execute my-first-d1 --command="SELECT * FROM users;"
--------------------
🚧 D1 is currently in open alpha and is not recommended for production data and traffic
🚧 Please report any bugs to https://github.com/cloudflare/workers-sdk/issues/new/choose
🚧 To request features, visit https://community.cloudflare.com/c/developers/d1
🚧 To give feedback, visit https://discord.gg/cloudflaredev
--------------------

🌀 Mapping SQL input into an array of statements
🌀 Parsing 1 statements
🌀 Executing on my-first-d1 (XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX):
🚣 Executed 1 command in 5.40939000248909ms
┌────┬─────────┬─────┐
│ id │ name    │ age │
├────┼─────────┼─────┤
│ 1  │ Takashi │ 25  │
└────┴─────────┴─────┘

• ダッシュボードのD1 > <データベース名> からもテーブルが作成されていることが確認できる

• ダッシュボードのD1 > <データベース名> > <テーブル名> からもレコードが追加されていることが確認できる

Workersのプロジェクトを作成

• Workersを作成することで、R2のバケットがHTTPS経由でWeb APIとして操作できるようになる
• WorkersはJavaScriptのコードをエッジネットワークに配置し、実行できる仕組み

# プロジェクトの初期化
# 今回はTypeScriptを有効化し、Workerの方式はFetch Handlerとしている
$ wrangler init my-first-d1     
 ⛅️ wrangler 2.12.3 
--------------------
Using npm as package manager.
✨ Created my-first-d1/wrangler.toml
✔ Would you like to use git to manage this Worker? … yes
✨ Initialized git repository at my-first-d1
✔ No package.json found. Would you like to create one? … yes
✨ Created my-first-d1/package.json
✔ Would you like to use TypeScript? … yes
✨ Created my-first-d1/tsconfig.json
✔ Would you like to create a Worker at my-first-d1/src/index.ts? › Fetch handler
✨ Created my-first-d1/src/index.ts
✔ Would you like us to write your first test with Vitest? … no
npm WARN deprecated rollup-plugin-inject@3.0.2: This package has been deprecated and is no longer maintained. Please use @rollup/plugin-inject.
npm WARN deprecated sourcemap-codec@1.4.8: Please use @jridgewell/sourcemap-codec instead

added 104 packages, and audited 105 packages in 22s

11 packages are looking for funding
  run `npm fund` for details

found 0 vulnerabilities
✨ Installed @cloudflare/workers-types and typescript into devDependencies

To start developing your Worker, run `cd my-first-d1 && npm start`
To publish your Worker to the Internet, run `npm run deploy`

• 以下のようなディレクトリ構造でファイルが生成される

.
├── node_modules
├── package-lock.json
├── package.json
├── src
│   └── index.ts
├── tsconfig.json
└── wrangler.toml

Workersの設定と実装

• 生成されたファイルをベースに設定と実装を追加していく

wrangler.toml

name = "my-first-d1"
main = "src/index.ts"
compatibility_date = "2023-03-20"

[[ d1_databases ]]
binding = "DB" # i.e. available in your Worker on env.DB
database_name = "my-first-d1"
database_id = "XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX"

• 20代以上のユーザを取得するクエリを実行する
• 実装はGet started #Write queries within your Workerを参考にした

src/index.ts

/**
 * Welcome to Cloudflare Workers! This is your first worker.
 *
 * - Run `wrangler dev src/index.ts` in your terminal to start a development server
 * - Open a browser tab at http://localhost:8787/ to see your worker in action
 * - Run `wrangler publish src/index.ts --name my-worker` to publish your worker
 *
 * Learn more at https://developers.cloudflare.com/workers/
 */

export interface Env {
	// Example binding to KV. Learn more at https://developers.cloudflare.com/workers/runtime-apis/kv/
	// MY_KV_NAMESPACE: KVNamespace;
	//
	// Example binding to Durable Object. Learn more at https://developers.cloudflare.com/workers/runtime-apis/durable-objects/
	// MY_DURABLE_OBJECT: DurableObjectNamespace;
	//
	// Example binding to R2. Learn more at https://developers.cloudflare.com/workers/runtime-apis/r2/
	// MY_BUCKET: R2Bucket;
	//
	// Example binding to a Service. Learn more at https://developers.cloudflare.com/workers/runtime-apis/service-bindings/
	// MY_SERVICE: Fetcher;

	MY_FIRST_D1: D1Database;
}

export default {
	async fetch(
		request: Request,
		env: Env,
		ctx: ExecutionContext
	): Promise<Response> {
		const { pathname } = new URL(request.url);

		if (pathname === "/api/users") {
			const { results } = await env.MY_FIRST_D1.prepare(
				"SELECT * FROM users WHERE age > ?"
			)
			.bind(19)
			.all();
			return Response.json(results);
		}

		return new Response(
			"Call /api/users to see users over teenager"
		);
	},
};

Workersのデプロイ

$ wrangler publish
Delegating to locally-installed wrangler@2.12.3 over global wrangler@2.12.3...
Run `npx wrangler publish` to use the local version directly.

 ⛅️ wrangler 2.12.3 
--------------------
Your worker has access to the following bindings:
- D1 Databases:
  - MY_FIRST_D1: my-first-d1 (XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX)
Total Upload: 6.80 KiB / gzip: 2.18 KiB
Uploaded my-first-d1 (1.51 sec)
Published my-first-d1 (4.53 sec)
  https://my-first-d1.foo.workers.dev
Current Deployment ID: XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX

• ダッシュボードのWorkersからWorkersが作成されたことが確認できる

curlから動作確認

$ curl my-first-d1.foo.workers.dev/api/users
[{"id":1,"name":"Takashi","age":25}]