Cloudflare D1

This page discusses the concepts behind using Prisma ORM and Cloudflare D1, explains the commonalities and differences between Cloudflare D1 and other database providers, and leads you through the process for configuring your application to integrate with Cloudflare D1.

Prisma ORM support for Cloudflare D1 is currently in Preview. We would appreciate your feedback on GitHub.

If you want to deploy a Cloudflare Worker with D1 and Prisma ORM, follow this tutorial.

What is Cloudflare D1?

D1 is Cloudflare's native serverless database and was initially launched in 2022. It's based on SQLite and can be used when deploying applications with Cloudflare Workers.

Following Cloudflare's principles of geographic distribution and bringing compute and data closer to application users, D1 supports automatic read-replication. It dynamically manages the number of database instances and locations of read-only replicas based on how many queries a database is getting, and from where.

For write-operations, queries travel to a single primary instance in order to propagate the changes to all read-replicas and ensure data consistency.

Commonalities with other database providers

D1 is based on SQLite.

Many aspects of using Prisma ORM with D1 are just like using Prisma ORM with any other relational database. You can still:

model your database with the Prisma Schema Language
use Prisma ORM's existing sqlite database connector in your schema
use Prisma Client in your application to talk to the database server at D1

Differences to consider

There are a number of differences between D1 and SQLite to consider. You should be aware of the following when deciding to use D1 and Prisma ORM:

Making schema changes. With Prisma ORM 7, you need to use D1's migration system via the Wrangler CLI combined with the prisma migrate diff command for your migration workflows. See the Schema migrations with Prisma ORM on D1 section below for more information.
Local and remote D1 (SQLite) databases. Cloudflare provides local and remote versions of D1. The local version is managed using the --local option of the wrangler d1 CLI and is located in .wrangler/state. The remote version is managed by Cloudflare and is accessed via HTTP.

How to connect to D1 in Cloudflare Workers or Cloudflare Pages

When using Prisma ORM with D1, you need to use the sqlite database provider and the @prisma/adapter-d1 driver adapter.

If you want to deploy a Cloudflare Worker with D1 and Prisma ORM, follow these step-by-step instructions.

Schema migrations with Prisma ORM on D1

With Prisma ORM 7, the recommended approach for managing database schema migrations with Cloudflare D1 is to use the Wrangler CLI combined with prisma migrate diff.

Using the Wrangler CLI with `prisma migrate diff`

Cloudflare D1 comes with its own migration system that works via the wrangler d1 CLI commands.

The Wrangler CLI provides the structure for migrations, but you need to generate the SQL statements from your Prisma schema. This is where prisma migrate diff comes in - it generates SQL statements based on your Prisma schema that you can then apply using Wrangler.

Creating an initial migration

The workflow for creating an initial migration looks as follows. Assume you have a fresh D1 instance without any tables.

1. Update your Prisma data model

This is your initial version of the Prisma schema that you want to map to your D1 instance:

model User {
  id    Int     @id @default(autoincrement())
  email String  @unique
  name  String?
}

2. Create migration file using `wrangler` CLI

Next, you need to create the migration file using the wrangler d1 migrations create command:

npx wrangler d1 migrations create __YOUR_DATABASE_NAME__ create_user_table

Since this is the very first migration, this command will prompt you to also create a migrations folder. Note that if you want your migration files to be stored in a different location, you can customize it using Wrangler.

Once the command has executed and assuming you have chosen the default migrations name for the location of your migration files, the command has created the following folder and file for you:

migrations/
└── 0001_create_user_table.sql

However, before you can apply the migration to your D1 instance, you actually need to put a SQL statement into the currently empty 0001_create_user_table.sql file.

3. Generate SQL statements using `prisma migrate diff`

To generate the initial SQL statement, you can use the prisma migrate diff command which compares to schemas (via its --to-X and --from-X options) and generates the steps that are needed to "evolve" from one to the other. These schemas can be either Prisma or SQL schemas.

For the initial migration, you can use the special --from-empty option though:

npx prisma migrate diff \
  --from-empty \
  --to-schema ./prisma/schema.prisma \
  --script \
  --output migrations/0001_create_user_table.sql

The command above uses the following options:

--from-empty: The source for the SQL statement is an empty schema.
--to-schema ./prisma/schema.prisma: The target for the SQL statement is the schema in ./prisma/schema.prisma.
--script: Output the result as SQL. If you omit this option, the "migration steps" will be generated in plain English.
--output migrations/0001_create_user_table.sql: Store the result in migrations/0001_create_user_table.sql.

After running this command, migrations/0001_create_user_table.sql will have the following contents:

migrations/0001_create_user_table.sql

-- CreateTable
CREATE TABLE "User" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "email" TEXT NOT NULL,
    "name" TEXT
);

-- CreateIndex
CREATE UNIQUE INDEX "User_email_key" ON "User"("email");

4. Execute the migration using `wrangler d1 migrations apply`

Finally, you can apply the migration against your D1 instances.

For the local instance, run:

npx wrangler d1 migrations apply __YOUR_DATABASE_NAME__ --local

For the remote instance, run:

npx wrangler d1 migrations apply __YOUR_DATABASE_NAME__ --remote

Evolve your schema with further migrations

For any further migrations, you can use the same workflow but instead of using --from-empty, you'll need to use --from-local-d1 because your source schema for the prisma migrate diff command now is the current schema of that local D1 instance, while the target remains your (then updated) Prisma schema.

1. Update your Prisma data model

Assume you have updated your Prisma schema with another model:

model User {
  id    Int     @id @default(autoincrement())
  email String  @unique
  name  String?
  posts Post[]
}

model Post {
  id       Int    @id @default(autoincrement())
  title    String
  author   User   @relation(fields: [authorId], references: [id])
  authorId Int
}

2. Create migration file using `wrangler` CLI

Like before, you first need to create the migration file:

npx wrangler d1 migrations create __YOUR_DATABASE_NAME__ create_post_table

Once the command has executed (again assuming you have chosen the default migrations name for the location of your migration files), the command has created a new file inside of the migrations folder:

migrations/
├── 0001_create_user_table.sql
└── 0002_create_post_table.sql

As before, you now need to put a SQL statement into the currently empty 0002_create_post_table.sql file.

3. Generate SQL statements using `prisma migrate diff`

As explained above, you now need to use --from-local-d1 instead of --from-empty to specify a source schema:

npx prisma migrate diff \
  --from-local-d1 \
  --to-schema ./prisma/schema.prisma \
  --script \
  --output migrations/0002_create_post_table.sql

The command above uses the following options:

--from-local-d1: The source for the SQL statement is the local D1 database file.
--to-schema ./prisma/schema.prisma: The target for the SQL statement is the schema in ./prisma/schema.prisma.
--script: Output the result as SQL. If you omit this option, the "migration steps" will be generated in plain English.
--output migrations/0002_create_post_table.sql: Store the result in migrations/0002_create_post_table.sql.

After running this command, migrations/0002_create_post_table.sql will have the following contents:

migrations/0002_create_post_table.sql

-- CreateTable
CREATE TABLE "Post" (
    "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    "title" TEXT NOT NULL,
    "authorId" INTEGER NOT NULL,
    CONSTRAINT "Post_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User" ("id") ON DELETE RESTRICT ON UPDATE CASCADE
);

4. Execute the migration using `wrangler d1 migrations apply`

Finally, you can apply the migration against your D1 instances.

For the local instance, run:

npx wrangler d1 migrations apply __YOUR_DATABASE_NAME__ --local

For the remote instance, run:

npx wrangler d1 migrations apply __YOUR_DATABASE_NAME__ --remote

Limitations

Transactions not supported

Cloudflare D1 currently does not support transactions (see the open feature request). As a result, Prisma ORM does not support transactions for Cloudflare D1. When using Prisma's D1 adapter, implicit & explicit transactions will be ignored and run as individual queries, which breaks the guarantees of the ACID properties of transactions.

Cloudflare D1

On this page