import Tab from '@mdx/Tab.astro'; import Tabs from '@mdx/Tabs.astro'; import Npm from "@mdx/Npm.astro"; import Callout from '@mdx/Callout.astro'; import Steps from '@mdx/Steps.astro'; import AnchorCards from '@mdx/AnchorCards.astro'; import Breadcrumbs from '@mdx/Breadcrumbs.astro'; import CodeTabs from "@mdx/CodeTabs.astro"; import Prerequisites from "@mdx/Prerequisites.astro"; import IntrospectSQLite from '@mdx/get-started/sqlite/IntrospectSqlite.mdx'; 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 SetupEnv from '@mdx/get-started/SetupEnv.mdx'; import TransferCode from '@mdx/get-started/TransferCode.mdx'; import ApplyChanges from '@mdx/get-started/ApplyChanges.mdx'; import RunFile from '@mdx/get-started/RunFile.mdx'; import ConnectSQLiteCloud from '@mdx/get-started/sqlite/ConnectSQLiteCloud.mdx'; import QueryDatabase from '@mdx/get-started/QueryDatabase.mdx'; import QueryDatabaseUpdated from '@mdx/get-started/QueryDatabaseUpdated.mdx'; import UpdateSchema from '@mdx/get-started/sqlite/UpdateSchema.mdx';

Get Started with Drizzle and SQLite Durable Objects in existing project

<Prerequisites> - **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) </Prerequisites> <FileStructure/>

Step 1 - Install required package

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 - Setup Drizzle config file

Drizzle config - a configuration file that is used by Drizzle Kit 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

import 'dotenv/config';
import { defineConfig } from 'drizzle-kit';

export default defineConfig({
  out: './drizzle',
  schema: './src/db/schema.ts',
  dialect: 'sqlite',
  driver: 'durable-sqlite',
});

<Callout title='tips'> You can check [our tutorial](/docs/guides/d1-http-with-drizzle-kit) on how to get env variables from CloudFlare </Callout>

Step 4 - Introspect your database

Step 5 - Transfer code to your actual schema file

Step 6 - Connect Drizzle ORM to the database

typescript

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 7 - Query the database

typescript

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<any>;

  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<Response> {
    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: '[email protected]',
    });
    console.log('New user created. Getting all users from the database: ', users);
    /*
    const users: {
      id: number;
      name: string;
      age: number;
      email: string;
      phone: string | null;
    }[]
    */

    // 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: '[email protected]',
    });
    console.log('New user created!');

    const users = await stub.select();
    console.log('Getting all users from the database: ', users);
    /*
    const users: {
      id: number;
      name: string;
      age: number;
      email: string;
      phone: string | null;
    }[]
    */

    return Response.json(users);
  }
}

Step 8 - Run index.ts file

Step 9 - Update your table schema (optional)

Step 10 - Applying changes to the database (optional)

Step 11 - Query the database with a new field (optional)

typescript

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<any>;

  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<Response> {
    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: '[email protected]',
      phone: '123-456-7890',
    });
    console.log('New user created. Getting all users from the database: ', users);
    /*
    const users: {
      id: number;
      name: string;
      age: number;
      email: string;
      phone: string | null;
    }[]
    */

    // 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: '[email protected]',
      phone: '123-456-7890',
    });
    console.log('New user created!');

    const users = await stub.select();
    console.log('Getting all users from the database: ', users);
    /*
    const users: {
      id: number;
      name: string;
      age: number;
      email: string;
      phone: string | null;
    }[]
    */

    return Response.json(users);
  }
}