How use D1 database with Cloudflare Pages
What will we do in this position and why?
I make this post because I like some cloudflare products like D1 and Pages but feel that it still lacks a UX improvement, but in the meantime you can follow this blog to connect your D1 Database 😁.
We create a Remix application and connect it to the D1 database. So we cover how to do this and what is D1 and cloudflare pages and also what you have to have installed to use both.
What is D1 database?
According to cloudflare:
📎 D1 is Cloudflare’s native serverless database. D1 allows you to build applications that handle large amounts of users at no extra cost. With D1, you can restore your database to any minute within the last 30 days.
Basically D1 is another alternative for databases such as Supabe, PlanetScale, FaunaDB, etc. But D1 works with Cloudflare Workers or Cloudflare Pages. This allows us to create a full-stack application and hosting in a single reliable provider like Cloudflare.
In addition Cloudflare is working on D1 being able to have read-only replicas distributed around the world, this reduces the latency of our users when reading information is incredible! So I think D1 is a great option for its features and ecosystem.
What is Cloudflare Pages?
Cloudflare pages is a service where we can deploy our frontend or full-stack apps as easy as linking to our repository provide the necessary envs and hit the deploy button!
Cloudflare supports and has several examples using different frameworks like React. Remix, NextJS, Vue, Nuxt and many more. In our case we will use Remix.
Let's start
First of all you obviously have to have a cloudflare account already created. Then once logged in, in the dashboard go to Workers & Pages > D1 and create a database. In my case, my database will be called blog-proof and will have a user table with the columns id, first_name and last_name(we will do this later).
Excellent you have your database, now we will create your remix project using the cloudflare pages remix template
npx create-remix@latest --template remix-run/remix/templates/cloudflare
Once our Remix project is created, we will add the file wrangler.toml in its root and we will add the necessary data such as the project name, the compatibility date and the D1 database bindings.
name = "how-connect-d1-with-remix"
compatibility_date = "2024-05-23"
[[d1_databases]]
binding = "DB"
preview_database_id = "XXXXXX-XXXX-XXXX-XXXXX-XXXXX"
database_name = "blog-proof"
database_id = "XXXXXX-XXXX-XXXX-XXXXX-XXXXX"
Using Wrangler
To communicate with the database and create a local one we will use Wrangler which is a command-line tool to install it follow this guide.
Once installed in our project we will use the following command to create our table:
npx wrangler d1 execute DB --local --command “CREATE TABLE user (id integer, first_name text, last_name text)”
This command will create the table in our local db and to create it remotely just change the --local flag to --remote.
npx wrangler d1 execute DB --remote --command “CREATE TABLE user (id integer, first_name text, last_name text)”
Using D1 in Remix
To interact with the database we will use Kysely as query builder we will install kysely and its dialect for D1.
pnpm i kysely kysely-d1
After installing these libraries we will add the following files:
// db/index.ts
import { Kysely } from "kysely";
import { D1Dialect } from "kysely-d1";
import { User } from "./tables-interfaces/user";
interface Database {
user: User;
}
export const db = (DB: D1Database) => {
const db = new Kysely<Database>({ dialect: new D1Dialect({ database: DB }) });
return db;
};
// db/tables-interfaces/user.ts
export interface User {
id: number;
first_name: string;
last_name: string;
}
We will also add the type D1Database in our load-context.ts file.
interface Env {
DB: D1Database;
}
Creating a query for get users
Perfect, now in our _index.ts path we will add the loader function to get our env DB from our context and use it to bring the records in our table user.
// app/routes/_index.ts
export const loader = async ({ context }: LoaderFunctionArgs) => {
const { DB } = context.cloudflare.env;
const result = await db(DB).selectFrom("user").selectAll().execute();
return json(result);
};
export default function Index() {
const data = useLoaderData<typeof loader>();
return (
<div style={{ fontFamily: "system-ui, sans-serif", lineHeight: "1.8" }}>
<h1>Welcome to Remix Demo app with D1 Database</h1>
<ul>
{data.map((user) => (
<li key={user.id}>
{user.first_name}, {user.last_name}
</li>
))}
</ul>
</div>
);
}
Then our db is already querying our database, obviously it doesn't get anything because it is empty. Let's solve this!
Creating a query for create users
Create our action function and create user query
// app/routes/_index.ts
export const action = async ({ request, context }: ActionFunctionArgs) => {
const { DB } = context.cloudflare.env;
console.log("dasd");
const body = await request.formData();
await db(DB)
.insertInto("user")
.values({
id: Number(Math.floor(Math.random() * 1000) + 1),
first_name: String(body.get("firstName")),
last_name: String(body.get("lastName")),
})
.executeTakeFirst();
return null;
};
Then update our Index function adding a form:
export default function Index() {
const data = useLoaderData<typeof loader>();
const fetcher = useFetcher();
let optimisticAddUser = {
firstName: "",
lastName: "",
};
if (fetcher.formData?.has("firstName") && fetcher.formData?.has("lastName")) {
optimisticAddUser = {
firstName: String(fetcher.formData?.get("firstName")),
lastName: String(fetcher.formData?.get("lastName")),
};
}
return (
<div style={{ fontFamily: "system-ui, sans-serif", lineHeight: "1.8" }}>
<h1>Welcome to Remix Demo app with D1 Database</h1>
<fetcher.Form method="post">
<input placeholder="First Name" name="firstName" type="text" />
<input placeholder="Last Name" name="lastName" type="text" />
<button type="submit">Create</button>
</fetcher.Form>
<div>
<h3>Users List</h3>
<ul>
{!!optimisticAddUser.firstName && !!optimisticAddUser.lastName && (
<li>
{optimisticAddUser.firstName}, {optimisticAddUser.lastName}
</li>
)}
{data.map((user) => (
<li key={user.id}>
{user.first_name}, {user.last_name}
</li>
))}
</ul>
</div>
</div>
);
}
Perfect now we have a list of users and a form to add more. This is de index route final code:
// app/routes/_index.ts
import {
ActionFunctionArgs,
json,
type LoaderFunctionArgs,
type MetaFunction,
} from "@remix-run/cloudflare";
import { useFetcher, useLoaderData } from "@remix-run/react";
import { db } from "db";
export const meta: MetaFunction = () => {
return [
{ title: "New Remix App" },
{
name: "description",
content: "Welcome to Remix! Using Vite and Cloudflare!",
},
];
};
export const loader = async ({ context }: LoaderFunctionArgs) => {
const { DB } = context.cloudflare.env;
const result = await db(DB).selectFrom("user").selectAll().execute();
return json(result);
};
export const action = async ({ request, context }: ActionFunctionArgs) => {
const { DB } = context.cloudflare.env;
console.log("dasd");
const body = await request.formData();
await db(DB)
.insertInto("user")
.values({
id: Number(Math.floor(Math.random() * 1000) + 1),
first_name: String(body.get("firstName")),
last_name: String(body.get("lastName")),
})
.executeTakeFirst();
return null;
};
export default function Index() {
const data = useLoaderData<typeof loader>();
const fetcher = useFetcher();
let optimisticAddUser = {
firstName: "",
lastName: "",
};
if (fetcher.formData?.has("firstName") && fetcher.formData?.has("lastName")) {
optimisticAddUser = {
firstName: String(fetcher.formData?.get("firstName")),
lastName: String(fetcher.formData?.get("lastName")),
};
}
return (
<div style={{ fontFamily: "system-ui, sans-serif", lineHeight: "1.8" }}>
<h1>Welcome to Remix Demo app with D1 Database</h1>
<fetcher.Form method="post">
<input placeholder="First Name" name="firstName" type="text" />
<input placeholder="Last Name" name="lastName" type="text" />
<button type="submit">Create</button>
</fetcher.Form>
<div>
<h3>Users List</h3>
<ul>
{!!optimisticAddUser.firstName && !!optimisticAddUser.lastName && (
<li>
{optimisticAddUser.firstName}, {optimisticAddUser.lastName}
</li>
)}
{data.map((user) => (
<li key={user.id}>
{user.first_name}, {user.last_name}
</li>
))}
</ul>
</div>
</div>
);
}
Final words
The last thing left to do is to upload and connect your github or gitlab repo to Cloudflare Pages and deploy. Note that Cloudflare Pages does not read wrangler.toml files, so once your project is deployed go to configuration > functions > bindings add your database and deploy again or you can follow this guide. And this is the code of the demo app.
Thanks for reading and I hope it helped