iTranslated by AI
Setting up a D1 + Prisma + Kysely-Prisma environment
Note: The d1 and miniflare v3 discussed in this article are not yet stable, so there is a high possibility that the code in this article will stop working in the future.
I am largely referring to the following article:
However, since various things worked or didn't work at the time of writing, I have made significant adjustments. It is expected that the local DB path for .wrangler/state/v3 will likely change frequently.
What is this article
- I want to perform d1 migrations with prisma
- For d1, I will run kysely-d1 using prisma's type definitions
- I want to retrieve d1 bindings from workerd for testing purposes, even from environments other than wrangler dev/pages
Ultimately, this will work:
import type { D1Database } from "@cloudflare/workers-types";
import type { DB } from "./db/types"; // generated by prisma
import { D1Dialect } from "kysely-d1";
import { Kysely } from "kysely";
export default {
async fetch(req: Request, env: {DB: D1Database}) {
const db = new Kysely<DB>({
dialect: new D1Dialect({ database: env.DB }),
});
const users = await db.selectFrom("User").selectAll().execute();
return Response.json(users);
}
}
Why
@prisma/client cannot be used in the Cloudflare Workers/Pages environment. @prisma/client/edge is intended for communicating with an external server via Prisma Data Proxy and is not meant to run standalone.
That being said, I feel it will eventually work, so I'll have Prisma manage only migrations and type definitions, and execute d1 from Kysely. While I prefer Drizzle's API, it lacks this kind of integration.
d1 Setup
Install the necessary packages (add missing ones as needed):
$ npm install kysely kysely-prisma kysely-d1 prisma wrangler -D
First, create a d1 database:
# Wrangler setup is omitted
npx wrangler d1 create mydb
If you don't have a wrangler.toml file, create one and add the following:
[[d1_databases]]
binding = "DB"
database_name = "mydb"
database_id = "<database_id>"
migrations_dir = ".wrangler/migrations"
Note that the database_id varies depending on the environment. migrations_dir will be explained later.
Next, set up Prisma:
$ npm install prisma --save-dev
$ npx prisma init --datasource-provider sqlite
This generates prisma/schema.prisma for SQLite.
In this case, we won't use it as-is. We'll set it up for the Kysely adapter.
prisma-kysely setup
Define the model for Kysely output:
datasource db {
provider = "sqlite"
url = env("DATABASE_URL")
}
generator kysely {
provider = "prisma-kysely"
output = "../src/db"
fileName = "types.ts"
enumFileName = "enums.ts"
}
model User {
id String @id @default(dbgenerated("(uuid())"))
name String
posts Post[]
createdAt Int @default(dbgenerated("(unixepoch())"))
}
model Post {
id String @id @default(dbgenerated("(uuid())"))
content String
userId String?
user User? @relation(fields: [userId], references: [id])
createdAt Int @default(dbgenerated("(unixepoch())"))
}
One point to note is that to define values that you want kysely-prisma to generate in the database, I had to use dbgenerated(). If you use Prisma's uuid() etc. directly in default, they will not be Optional at the Kysely type level.
Next, write the Prisma connection string in .env:
DATABASE_URL="file:../.wrangler/state/v3/d1/<database_id>/db.sqlite"
This is the path where wrangler d1 generates SQLite locally, ensuring that Prisma and Wrangler look at the same database.
Run the migration against the local database:
$ npx prisma migrate dev --create-only
# Check prisma/migrations/*/migration.sql
$ npx prisma migrate deploy # Apply
$ npx prisma generate # Generate type definition files
I'm using --create-only to check the SQL before executing it. This is because Prisma generates some SQL that cannot be executed in d1, so I had to manually correct it. Specifically, PRAGMA foreign_keys_check; often needs to be removed.
Applying prisma/migrations/*/migration.sql to d1
I want to apply the contents under prisma/migrations to d1, but since Wrangler's migrations_dir expects files to be expanded flatly, the Prisma directory structure—where files are organized in the prisma/migrations/*/migration.sql pattern—cannot be used as-is.
So, I wrote a zx script to relocate them to .wrangler/migrations/*.sql. While I was at it, I also added logic to pull parameters from the DB definitions in wrangler.toml.
// npm install zx glob @iarra/toml -D
import fs from "node:fs";
import path from "node:path";
import { parseArgs } from "node:util";
import { globSync } from "glob";
import { parse } from "@iarna/toml";
const opts = parseArgs({
options: {
wrangler: {
type: "string",
short: 'w',
default: "./wrangler.toml",
},
"database-name": {
type: "string",
short: 'n'
},
prisma: {
type: "string",
default: "./prisma/migrations",
},
production: {
type: "boolean",
short: 'p'
},
},
allowPositionals: true,
});
const cwd = process.cwd();
const wrangler = loadWranglerConfig(opts.values.wrangler);
const database = wrangler.d1_databases.find((d1_database) => d1_database.database_name === opts.values["database-name"])
?? wrangler.d1_databases[0];
if (!database) {
console.error("No database found");
process.exit(1);
} else {
console.log("[d1 database detected]", database);
}
const prismaDir = opts.values.prisma ?? './prisma/migrations';
const migrationDir = database.migrations_dir ?? '.wrangler/migrations';
await $`rm -rf ${migrationDir} && mkdir -p ${migrationDir}`;
for (const prismaPath of globSync(`${prismaDir}/*/migration.sql`, { cwd })) {
const migrationName = prismaPath.split("/")[2]; // prisma/migrations/<here>/migration.sql
const migrationPath = `${migrationDir}/${migrationName}.sql`
await $`cp ${prismaPath} ${migrationPath}`
}
if (opts.values.production) {
await $`pnpm wrangler d1 migrations apply ${database.database_name}`;
} else {
await $`pnpm wrangler d1 migrations apply ${database.database_name} --local`;
}
/**
* @param wranglerPath {string | undefined}
* @return {{
* d1_databases: Array<{
* database_name: string,
* binding: string,
* database_id: string,
* migrations_dir?: string,
* }>
* }}
*/
function loadWranglerConfig(wranglerPath) {
const wranglerTomlPath = path.join(cwd, wranglerPath);
const raw = fs.readFileSync(wranglerTomlPath, "utf-8");
return parse(raw);
}
This script expands the files into the expected .wrangler/migrations directory and then executes the Wrangler migration.
$ npx zx scripts/migration.mjs --production
[d1 database detected] {
binding: 'DB',
database_name: 'mydb2',
database_id: '837018aa-cde8-49ce-ac2a-68225f45dea8',
migrations_dir: '.wrangler/migrations'
}
$ rm -rf .wrangler/migrations && mkdir -p .wrangler/migrations
$ cp prisma/migrations/20230904073804_rename/migration.sql .wrangler/migrations/20230904073804_rename.sql
$ cp prisma/migrations/20230904072431_user_at/migration.sql .wrangler/migrations/20230904072431_user_at.sql
$ cp prisma/migrations/20230904072218_created/migration.sql .wrangler/migrations/20230904072218_created.sql
$ cp prisma/migrations/20230904070604_fix/migration.sql .wrangler/migrations/20230904070604_fix.sql
$ cp prisma/migrations/20230904063309_post/migration.sql .wrangler/migrations/20230904063309_post.sql
$ cp prisma/migrations/20230904055000_init/migration.sql .wrangler/migrations/20230904055000_init.sql
$ pnpm wrangler d1 migrations apply mydb2 --local
Although I designed this script to be runnable against local environments, it's probably better to stick with prisma migrate deploy for local use and only use this script for production. Both Prisma and d1 write intermediate migration states into their own dedicated tables, and I want to avoid inconsistencies between them.
If it runs successfully, you're all set. If an error occurs, check if you're using any unsupported commands. Personally, I verified things by running commands line-by-line against the production database using something like wrangler d1 execute db-name --command='...'.
For migrations after the initial setup, change prisma/schema.prisma and then run migrate dev.
Note: Behavior differences between local and production d1
When changing table definitions with Prisma, it generates SQL containing PRAGMA foreign_keys_check;. Although this is unsupported in the production d1 environment, it passes during local prisma migrate, so it needs to be removed.
Refer here for the PRAGMAs supported by d1.
Additionally, there is a flaw in the SQL comment parsing logic where comments ending with ; are identified as empty statements, leading to execution failure.
-- comment;
select 1;
Note that casual commenting will often cause things to stop working.
Also, while prisma-sqlite shouldn't generate it, ALTER TABLE RENAME COLUMN is also unsupported.
By the way, you can see which migrations have been applied in the d1_migrations table.
$ npx wrangler d1 execute mydb2 --command='select * from d1_migrations'
┌────┬────────────────────────────┬─────────────────────┐
│ id │ name │ applied_at │
├────┼────────────────────────────┼─────────────────────┤
│ 1 │ 20230904055000_init.sql │ 2023-09-04 06:28:34 │
├────┼────────────────────────────┼─────────────────────┤
│ 2 │ 20230904063309_post.sql │ 2023-09-04 06:35:40 │
├────┼────────────────────────────┼─────────────────────┤
│ 3 │ 20230904070604_fix.sql │ 2023-09-04 07:07:41 │
├────┼────────────────────────────┼─────────────────────┤
│ 4 │ 20230904072218_created.sql │ 2023-09-04 07:23:49 │
├────┼────────────────────────────┼─────────────────────┤
│ 5 │ 20230904072431_user at.sql │ 2023-09-04 07:25:36 │
├────┼────────────────────────────┼─────────────────────┤
│ 6 │ 20230904073804_rename.sql │ 2023-09-04 07:40:46 │
├────┼────────────────────────────┼─────────────────────┤
│ 7 │ 20230904072431_user_at.sql │ 2023-09-04 08:43:14 │
└────┴────────────────────────────┴─────────────────────┘
I previously had a bad experience where I deleted this table as a test, and wrangler d1 migrations apply stopped working entirely.
Calling kysely-d1 from Cloudflare Workers/Pages
Finally, we'll start executing d1 inside the server.
Running npx prisma generate generates a type definition file like the following:
import type { ColumnType } from "kysely";
export type Generated<T> = T extends ColumnType<infer S, infer I, infer U>
? ColumnType<S, I | undefined, U>
: ColumnType<T, T | undefined, T>;
export type Timestamp = ColumnType<Date, Date | string, Date | string>;
export type Post = {
id: Generated<string>;
content: string;
userId: string | null;
createdAt: Generated<number>;
};
export type User = {
id: Generated<string>;
name: string;
createdAt: Generated<number>;
};
export type DB = {
Post: Post;
User: User;
};
Initialize Kysely by passing this type information. At that time, wrap the d1 instance with kysely-d1.
Here is an example of calling it within a simple worker:
import type { D1Database } from "@cloudflare/workers-types";
import type { DB } from "./db/types";
import { D1Dialect } from "kysely-d1";
import { Kysely } from "kysely";
type Env = {
DB: D1Database;
}
export default {
async fetch(req: Request, env: Env) {
const db = new Kysely<DB>({
dialect: new D1Dialect({ database: env.DB }),
});
const users = await db.selectFrom("User").selectAll().execute();
return Response.json(users);
}
}
Now you can use type definitions generated from Prisma with Kysely.
If Prisma becomes fully supported in the future, it should be easy to migrate directly to @prisma/client.
Bonus: Testing d1 in a local Node environment
I want to initialize a d1 instance from outside the wrangler (workerd) runtime. In short, I want to do something equivalent to jest-environment-miniflare. However, miniflare v2 is currently deprecated, and the same applies to jest-environment-miniflare.
I looked for a way to create a d1 instance using miniflare v3, which has almost no documentation, and eventually ended up with something like this:
// npm install miniflare -D # Check that version 3 is installed
import { Miniflare } from "miniflare";
const D1_PERSIST_ENDPOINT = ".wrangler/state/v3/d1";
const database_id = "<database_id>";
const mf = new Miniflare({
workers: [
{
name: "main",
modules: true,
script: `export default {
async fetch(req, env, ctx) {
return new Response("ok");
}
}
`,
d1Databases: { DB: database_id },
},
],
d1Persist: D1_PERSIST_ENDPOINT,
d1Databases: [database_id]
});
// const response = await mf.dispatchFetch("http://localhost:8787/");
// console.log(await response.text()); // ok
const bindings = await mf.getBindings("main");
const db = bindings.DB;
await db.exec("CREATE TABLE IF NOT EXISTS requests (url TEXT)");
await db.exec("INSERT INTO requests (url) VALUES ('aaa')");
const prepared = await db.prepare(`select * from "requests"`);
const res = await prepared.all();
console.log(res);
await mf.dispose();
Internally, miniflare v3 acts as a bridge to workerd. First, you create an appropriate ModuleWorker and retrieve its bindings. You specify the database_id from the worker side for the top-level d1Databases. At this point, you match the path of the actual DB to the path expected by wrangler d1.
In other words, I'm making sure it points to .wrangler/state/v3/d1/<database_id>/db.sqlite.
In this case it's d1, but you can also create bindings for r2 and others.
I believe this will eventually be an officially provided feature from Cloudflare, but for now, it requires various manual steps.
Thoughts
- I can now use Prisma migrations and type information with Kysely, but I honestly want to use Prisma directly...
- The error messages from d1 are not very mature, making it exhausting to investigate why an error occurred.
- While I was building this, the production d1 went down, and I couldn't figure out the cause.
Discussion