import Npm from '@mdx/Npm.astro'; import Npx from '@mdx/Npx.astro'; import Callout from '@mdx/Callout.astro'; import Prerequisites from "@mdx/Prerequisites.astro"; import CodeTabs from "@mdx/CodeTabs.astro"; import WhatsNextPostgres from "@mdx/WhatsNextPostgres.astro"; import Breadcrumbs from '@mdx/Breadcrumbs.astro'; import FileStructure from '@mdx/get-started/FileStructure.mdx'; import InstallPackages from '@mdx/get-started/InstallPackages.mdx'; import SetupConfig from '@mdx/get-started/SetupConfig.mdx'; import TransferCode from '@mdx/get-started/TransferCode.mdx'; import QueryTurso from '@mdx/get-started/sqlite/QueryTurso.mdx'; import QueryDatabaseUpdated from '@mdx/get-started/QueryDatabaseUpdated.mdx'; import RunFile from '@mdx/get-started/RunFile.mdx'; import ApplyChanges from '@mdx/get-started/ApplyChanges.mdx'; import SetupEnv from '@mdx/get-started/SetupEnv.mdx'; import CreateTable from '@mdx/get-started/sqlite/CreateTable.mdx'; import ConnectLibsql from '@mdx/get-started/sqlite/ConnectLibsql.mdx'; # Get Started with Drizzle and SQLite Durable Objects - **dotenv** - package for managing environment variables - [read here](https://www.npmjs.com/package/dotenv) - **tsx** - package for running TypeScript files - [read here](https://tsx.is/) - **Cloudflare SQLite Durable Objects** - SQLite database embedded within a Durable Object - [read here](https://developers.cloudflare.com/durable-objects/api/sql-storage/) - **wrangler** - Cloudflare Developer Platform command-line interface - [read here](https://developers.cloudflare.com/workers/wrangler) #### Step 1 - Install required packages drizzle-orm dotenv -D drizzle-kit wrangler @cloudflare/workers-types #### Step 2 - Setup wrangler.toml You would need to have a `wrangler.toml` file for D1 database and will look something like this: ```toml #:schema node_modules/wrangler/config-schema.json name = "sqlite-durable-objects" main = "src/index.ts" compatibility_date = "2024-11-12" compatibility_flags = [ "nodejs_compat" ] # Bind a Durable Object. Durable objects are a scale-to-zero compute primitive based on the actor model. # Durable Objects can live for as long as needed. Use these when you need a long-running "server", such as in realtime apps. # Docs: https://developers.cloudflare.com/workers/wrangler/configuration/#durable-objects [[durable_objects.bindings]] name = "MY_DURABLE_OBJECT" class_name = "MyDurableObject" # Durable Object migrations. # Docs: https://developers.cloudflare.com/workers/wrangler/configuration/#migrations [[migrations]] tag = "v1" new_sqlite_classes = ["MyDurableObject"] # We need rules so we can import migrations in the next steps [[rules]] type = "Text" globs = ["**/*.sql"] fallthrough = true ``` #### Step 3 - Connect Drizzle ORM to the database ```ts /// import { drizzle, type DrizzleSqliteDODatabase } from 'drizzle-orm/durable-sqlite'; import { DurableObject } from 'cloudflare:workers' export class MyDurableObject extends DurableObject { storage: DurableObjectStorage; db: DrizzleSqliteDODatabase; constructor(ctx: DurableObjectState, env: Env) { super(ctx, env); this.storage = ctx.storage; this.db = drizzle(this.storage, { logger: false }); } } ``` #### Step 4 - Generate wrangler types wrangler types The output of this command will be a `worker-configuration.d.ts` file. #### Step 5 - Create a table #### Step 6 - Setup Drizzle config file **Drizzle config** - a configuration file that is used by [Drizzle Kit](/docs/kit-overview) and contains all the information about your database connection, migration folder and schema files. Create a `drizzle.config.ts` file in the root of your project and add the following content: ```typescript copy filename="drizzle.config.ts" import 'dotenv/config'; import { defineConfig } from 'drizzle-kit'; export default defineConfig({ out: './drizzle', schema: './src/db/schema.ts', dialect: 'sqlite', driver: 'durable-sqlite', }); ``` #### Step 7 - Applying changes to the database Generate migrations: ```bash copy npx drizzle-kit generate ``` You can apply migrations only from Cloudflare Workers. To achieve this, let's define the migrate functionality in MyDurableObject: ```ts copy {4-5,17-19} /// import { drizzle, type DrizzleSqliteDODatabase } from 'drizzle-orm/durable-sqlite'; import { DurableObject } from 'cloudflare:workers' import { migrate } from 'drizzle-orm/durable-sqlite/migrator'; import migrations from '../drizzle/migrations'; export class MyDurableObject extends DurableObject { storage: DurableObjectStorage; db: DrizzleSqliteDODatabase; constructor(ctx: DurableObjectState, env: Env) { super(ctx, env); this.storage = ctx.storage; this.db = drizzle(this.storage, { logger: false }); } async migrate() { migrate(this.db, migrations); } } ``` #### Step 8 - Migrate and Query the database ```typescript copy /// import { drizzle, DrizzleSqliteDODatabase } from 'drizzle-orm/durable-sqlite'; import { DurableObject } from 'cloudflare:workers' import { migrate } from 'drizzle-orm/durable-sqlite/migrator'; import migrations from '../drizzle/migrations'; import { usersTable } from './db/schema'; export class MyDurableObject extends DurableObject { storage: DurableObjectStorage; db: DrizzleSqliteDODatabase; constructor(ctx: DurableObjectState, env: Env) { super(ctx, env); this.storage = ctx.storage; this.db = drizzle(this.storage, { logger: false }); // Make sure all migrations complete before accepting queries. // Otherwise you will need to run `this.migrate()` in any function // that accesses the Drizzle database `this.db`. ctx.blockConcurrencyWhile(async () => { await this._migrate(); }); } async insertAndList(user: typeof usersTable.$inferInsert) { await this.insert(user); return this.select(); } async insert(user: typeof usersTable.$inferInsert) { await this.db.insert(usersTable).values(user); } async select() { return this.db.select().from(usersTable); } async _migrate() { migrate(this.db, migrations); } } export default { /** * This is the standard fetch handler for a Cloudflare Worker * * @param request - The request submitted to the Worker from the client * @param env - The interface to reference bindings declared in wrangler.toml * @param ctx - The execution context of the Worker * @returns The response to be sent back to the client */ async fetch(request: Request, env: Env): Promise { const id: DurableObjectId = env.MY_DURABLE_OBJECT.idFromName('durable-object'); const stub = env.MY_DURABLE_OBJECT.get(id); // Option A - Maximum performance. // Prefer to bundle all the database interaction within a single Durable Object call // for maximum performance, since database access is fast within a DO. const usersAll = await stub.insertAndList({ name: 'John', age: 30, email: 'john@example.com', }); console.log('New user created. Getting all users from the database: ', users); // Option B - Slow but maybe useful sometimes for debugging. // You can also directly call individual Drizzle queries if they are exposed // but keep in mind every query is a round-trip to the Durable Object instance. await stub.insert({ name: 'John', age: 30, email: 'john@example.com', }); console.log('New user created!'); const users = await stub.select(); console.log('Getting all users from the database: ', users); return Response.json(users); } } ```

SQLite Durable Objects Setup