Skip to main content

Cloudflare D1

This guide 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.

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:

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:

  • 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.
  • Making schema changes. Since D1 uses HTTP to connect to the remote database, this makes it incompatible with some commands of Prisma Migrate, like prisma migrate dev. However, you can use D1's migration system and the prisma migrate diff command for your migration workflows. See the Migration workflows below for more information.

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.

Migration workflows​

Cloudflare D1 comes with its own migration system. We recommend that you use this migration system via the wrangler d1 migrations command to create and manage migration files on your file system.

This command doesn't help you in figuring out the SQL statements for creating your database schema that need to be put inside of these migration files though. If you want to query your database using Prisma Client, it's important that your database schema maps to your Prisma schema, this is why it's recommended to generate the SQL statements from your Prisma schema.

When using D1, you can use the prisma migrate diff command for that purpose.

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-datamodel ./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-datamodel ./prisma/schema.prisma: The target for the SQL statement is the data model 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-datamodel ./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-datamodel ./prisma/schema.prisma: The target for the SQL statement is the data model 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