Cloudflare Pages + Remix(v2.8) + Drizzle ORM + D1 お試し

npm create cloudflare@latest my-remix-app -- --framework=remix

import { sql } from "drizzle-orm";
import { sqliteTable, text } from 'drizzle-orm/sqlite-core';

export const users = sqliteTable('users', {
  id: text('id').primaryKey(),
  email: text('email').unique(),
  name: text('name').notNull(),
  nickname: text('nickname'),
  customerId: text('customerId').unique(),
  createdAt: text("created_at").default(sql`CURRENT_TIMESTAMP`).notNull(),
  updatedAt: text("updated_at").default(sql`CURRENT_TIMESTAMP`).notNull(),
});

npm add -D drizzle-kit drizzle-orm

export default {
  schema: "./app/db/schema.ts",
  out: "./db/migrations",
};

npx drizzle-kit generate:sqlite --breakpoints

CREATE TABLE `users` (
	`id` text PRIMARY KEY NOT NULL,
	`email` text,
	`name` text NOT NULL,
	`customerId` text,
	`created_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL
        `updated_at` text DEFAULT CURRENT_TIMESTAMP NOT NULL
);
--> statement-breakpoint
CREATE UNIQUE INDEX `users_email_unique` ON `users` (`email`);--> statement-breakpoint
CREATE UNIQUE INDEX `users_customerId_unique` ON `users` (`customerId`);

export const users = sqliteTable('users', {
  id: text('id').primaryKey(),
  email: text('email').unique(),
  name: text('name').notNull(),
+  nickname: text('nickname'),
  customerId: text('customerId').unique(),
  createdAt: text("created_at").default(sql`CURRENT_TIMESTAMP`).notNull(),
  updatedAt: text("updated_at").default(sql`CURRENT_TIMESTAMP`).notNull(),
});

$ npx drizzle-kit generate:sqlite --breakpoints

drizzle-kit: v0.20.14
drizzle-orm: v0.29.4

No config path provided, using default 'drizzle.config.ts'
Reading config file '/home/kazuph/src/github.com/kazuph/cf-samples/cf-remix-drizzle-d1/drizzle.config.ts'
1 tables
users 6 columns 2 indexes 0 fks

[✓] Your SQL migration file ➜ db/migrations/0001_wild_magik.sql 🚀

ALTER TABLE users ADD `nickname` text;

npx wrangler d1 create mydb
...
[[ d1_databases ]]
binding = "DB"
database_name = "mydb"
database_id = "<YOUR_D1_ID>"

name = "cf-remix-drizzle-d1"
compatibility_date = "2024-03-04"

[[ d1_databases ]]
binding = "DB"
database_name = "mydb"
database_id = "<YOUR_D1_ID>"
migrations_dir = "db/migrations"

$ npx wrangler d1 migrations apply mydb --local

 ⛅️ wrangler 3.31.0
-------------------
Migrations to be applied:
┌────────────────────────┐
│ name                   │
├────────────────────────┤
│ 0000_high_red_hulk.sql │
├────────────────────────┤
│ 0001_wild_magik.sql    │
└────────────────────────┘
✔ About to apply 2 migration(s)
Your database may not be available to serve requests during the migration, continue? … yes
🌀 Mapping SQL input into an array of statements
🌀 Executing on local database mydb () from .wrangler/state/v3/d1:
┌────────────────────────┬────────┐
│ name                   │ status │
├────────────────────────┼────────┤
│ 0000_high_red_hulk.sql │ ✅       │
├────────────────────────┼────────┤
│ 0001_wild_magik.sql    │ 🕒️     │
└────────────────────────┴────────┘
🌀 Mapping SQL input into an array of statements
🌀 Executing on local database mydb () from .wrangler/state/v3/d1:
┌────────────────────────┬────────┐
│ name                   │ status │
├────────────────────────┼────────┤
│ 0000_high_red_hulk.sql │ ✅       │
├────────────────────────┼────────┤
│ 0001_wild_magik.sql    │ ✅       │
└────────────────────────┴────────┘

$ npx wrangler d1 execute mydb --command="select * from d1_migrations;"

 ⛅️ wrangler 3.31.0
-------------------
🌀 Mapping SQL input into an array of statements
🌀 Parsing 1 statements
🌀 Executing on remote database mydb ():
🌀 To execute on your local development database, pass the --local flag to 'wrangler d1 execute'
🚣 Executed 1 commands in 0.3032ms
┌────┬─────────────────────────────┬─────────────────────┐
│ id │ name                        │ applied_at          │
├────┼─────────────────────────────┼─────────────────────┤
│ 1  │ 0000_conscious_ironclad.sql │ 2024-03-02 19:29:23 │
└────┴─────────────────────────────┴─────────────────────┘

npx wrangler d1 execute mydb --command="INSERT INTO users (id, email, name, nickname, customerId, created_at, updated_at) VALUES 
('1', 'user@example.com', 'ユーザー太郎', 'taro', 'cust_1234', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);" --local

$ npx wrangler d1 execute mydb --command="select * from users;" --local                                                  
 ⛅️ wrangler 3.31.0
-------------------
🌀 Mapping SQL input into an array of statements
🌀 Executing on local database mydb () from .wrangler/state/v3/d1:
┌────┬──────────────────┬────────┬────────────┬─────────────────────┬──────────┬─────────────────────┐
│ id │ email            │ name   │ customerId │ created_at          │ nickname │ updated_at          │
├────┼──────────────────┼────────┼────────────┼─────────────────────┼──────────┼─────────────────────┤
│ 1  │ user@example.com │ ユーザー太郎 │ cust_1234  │ 2024-03-06 08:23:36 │ taro     │ 2024-03-06 08:23:36 │
└────┴──────────────────┴────────┴────────────┴─────────────────────┴──────────┴─────────────────────┘

$ npm run typegen

> typegen
> wrangler types

 ⛅️ wrangler 3.31.0
-------------------
interface Env {
        DB: D1Database;
}

+ import type { LoaderFunctionArgs, LoaderFunction } from "@remix-run/cloudflare";
+ import { useLoaderData } from "@remix-run/react";

+ import { drizzle } from 'drizzle-orm/d1';
+ import { users } from '../db/schema';

...

+ export const loader: LoaderFunction = async ({ context }: LoaderFunctionArgs) => {
+   const db = drizzle(context.cloudflare.env.DB);
+   const allUsers = await db.select().from(users).all();
+ 
+   return {
+     allUsers
+   };
+ };

...

export default function Index() {
+  const { allUsers } = useLoaderData<typeof loader>();

return (
    <div style={{ fontFamily: "system-ui, sans-serif", lineHeight: "1.8" }}>
      <h1>Welcome to Remix (with Vite and Cloudflare)</h1>
      <ul>
+         {allUsers.map((user) => (
+           <li key={user.id}>{user.name}</li>
+         ))}
        <li>
...

npm run dev