Database Experimental

The SDK includes a built-in database solution using SQLite Durable Objects and Kysely for SQL queries. Create isolated databases at runtime with minimal setup.

---

Motivation

We believe a lightweight, SQL-based query builder is the best fit as the out of the box solution. With just SQL, you either already know it (so you can be immediately productive) or learning it is transferrable knowledge. This doesn’t replace your existing ORMs - you’re always free to use your preferred database solution where it makes sense.

For applications with modular components or add-ons, there’s an additional benefit: natural isolation. Each database instance is completely separate, giving you explicit control over how components communicate with each other’s data.

rwsdk/db delivers both simplicity and isolation in one package: Write your migrations, call createDb(), and start querying with full type safety. Types are inferred directly from your migrations.

---

How It Works

Under the hood, rwsdk/db combines:

1. SQLite Durable Objects - Each database instance runs in its own isolated Durable Object
2. Kysely - A lightweight, type-safe SQL query builder with the same API naming and semantics as SQL

Type Inference

Instead of code generation or handwritten types, we infer your database schema directly from your migrations:

import { type Migrations } from "rwsdk/db";

export const migrations = {
  "001_initial_schema": {
    async up(db) {
      return [
        await db.schema
          .createTable("users")
          .addColumn("id", "text", (col) => col.primaryKey())
          .addColumn("username", "text", (col) => col.notNull().unique())
          .execute(),
      ];
    },
  },
} satisfies Migrations;

// TypeScript automatically knows about your 'users' table and its columns
const user = await db.selectFrom("users").selectAll().executeTakeFirst();

When Migrations Run

Migrations run when createDb() is called. If that happens at the module level (shown in the examples), then:

Development:. Runs when you start your development server.

Production: When you deploy with npm run release, the deployment process includes an initial request to your application, which triggers migration updates.

Migration Failures and Rollback

If a migration’s up() function fails, rwsdk/db automatically calls the corresponding down() function to undo any partial changes. This rollback is per-migration - previously successful ones are not affected.

Because SQLite doesn’t support transactional DDL (Data Definition Language) statements, a failed migration can leave the database in a partially modified state. It is therefore important to write down() functions that are idempotent and can run safely even if up() only partially succeeded.

// Example of a defensive down() function
async down(db) {
  // Defensively drop tables that might not exist if `up()` failed
  await db.schema.dropTable("posts").ifExists().execute();
  await db.schema.dropTable("users").ifExists().execute();
}

---

Setup

You’ll need to create three files and update your Wrangler configuration:

1. Define Your Migrations

src/db/migrations.ts

import { type Migrations } from "rwsdk/db";

export const migrations = {
  "001_initial_schema": {
    async up(db) {
      await db.schema
        .createTable("todos")
        .addColumn("id", "text", (col) => col.primaryKey())
        .addColumn("text", "text", (col) => col.notNull())
        .addColumn("completed", "integer", (col) => col.notNull().defaultTo(0))
        .addColumn("createdAt", "text", (col) => col.notNull())
        .execute();
    },

    async down(db) {
      await db.schema.dropTable("todos").ifExists().execute();
    },
  },
} satisfies Migrations;

2. Create Your Database Instance

src/db/db.ts

import { env } from "cloudflare:workers";
import { type Database, createDb } from "rwsdk/db";
import { type migrations } from "@/db/migrations";

export type AppDatabase = Database<typeof migrations>;
export type Todo = AppDatabase["todos"];

export const db = createDb<AppDatabase>(
  env.APP_DURABLE_OBJECT,
  "todo-database" // unique key for this database instance
);

3. Create Your Durable Object Class

src/db/durableObject.ts

import { SqliteDurableObject } from "rwsdk/db";
import { migrations } from "@/db/migrations";

export class AppDurableObject extends SqliteDurableObject {
  migrations = migrations;
}

4. Export from Worker

src/worker.tsx

export { AppDurableObject } from "@/db/durableObject";

// ... rest of your worker code

5. Configure Wrangler

wrangler.jsonc

{
  "durable_objects": {
    "bindings": [
      {
        "name": "APP_DURABLE_OBJECT",
        "class_name": "AppDurableObject"
      }
    ]
  },
  "migrations": [
    {
      "tag": "v1",
      "new_sqlite_classes": ["AppDurableObject"]
    }
  ]
}

---

Usage Examples

Basic CRUD Operations

import { db } from "@/db";

// Create a todo
const todo = {
  id: crypto.randomUUID(),
  text: "Finish the documentation",
  completed: 0,
  createdAt: new Date().toISOString(),
};
await db.insertInto("todos").values(todo).execute();

// Find a todo
const foundTodo = await db
  .selectFrom("todos")
  .selectAll()
  .where("id", "=", todo.id)
  .executeTakeFirst();

// Update a todo
await db
  .updateTable("todos")
  .set({ completed: 1 })
  .where("id", "=", todo.id)
  .execute();

// Delete a todo
await db.deleteFrom("todos").where("id", "=", todo.id).execute();

Complex Queries with Joins

While the guestbook example is simple, you can still perform joins. For a more detailed example, see the Patterns section below.

Real-World Example: Passkey Authentication

Here’s how the passkey addon uses rwsdk/db:

// Create a new credential
export async function createCredential(
  credential: Omit<Credential, "id" | "createdAt">
): Promise<Credential> {
  const newCredential: Credential = {
    id: crypto.randomUUID(),
    createdAt: new Date().toISOString(),
    ...credential,
  };

  await db.insertInto("credentials").values(newCredential).execute();
  return newCredential;
}

// Find credentials for a user
export async function getUserCredentials(
  userId: string
): Promise<Credential[]> {
  return await db
    .selectFrom("credentials")
    .selectAll()
    .where("userId", "=", userId)
    .execute();
}

---

Patterns

Nesting Relational Data (ORM-like Behavior)

While rwsdk/db uses a query builder instead of a full ORM, you can still structure your query results to include nested relational data. Kysely provides helper functions like jsonObjectFrom and jsonArrayFrom that make this easy.

For this example, we’ll switch to a more complex schema involving blog posts and users to better demonstrate joins.

1. The Schema

First, let’s assume a schema with users and posts.

src/db/migrations.ts

// Abridged for clarity
await db.schema
  .createTable("users")
  .addColumn("id", "text", (col) => col.primaryKey())
  .addColumn("username", "text", (col) => col.notNull().unique())
  .execute();

await db.schema
  .createTable("posts")
  .addColumn("id", "text", (col) => col.primaryKey())
  .addColumn("title", "text", (col) => col.notNull())
  .addColumn("userId", "text", (col) =>
    col.notNull().references("users.id")
  )
  .execute();

2. The Query

With the schema in place, you can write a query to fetch posts and embed the author’s information.

src/db/queries.ts

import { db } from "@/db";
import { jsonObjectFrom } from "kysely/helpers/sqlite";

export async function getAllPostsWithAuthors() {
  return await db
    .selectFrom("posts")
    .selectAll("posts")
    .select((eb) => [
      jsonObjectFrom(
        eb
          .selectFrom("users")
          .select(["id", "username"])
          .whereRef("users.id", "=", "posts.userId")
      ).as("author"),
    ])
    .execute();
}

3. The Result

The getAllPostsWithAuthors function will return an array of post objects, each with a nested author object:

[
  {
    "id": "post-123",
    "title": "My First Post",
    "author": { "id": "user-abc", "username": "Alice" }
  }
]

This pattern allows you to fetch complex, nested data structures in a single, efficient query.

---

Seeding Your Database

For development and testing, you’ll often need a consistent set of data. You can create a seed script to populate your database with default values.

1. Create a Seed Script

Create a script that exports an async function as the default export. This script will have access to your application’s environment, including your Durable Object bindings, when run via rwsdk worker-run.

src/scripts/seed.ts

import { db } from "@/db/db";

export default async () => {
  console.log("… Seeding todos");
  await db.deleteFrom("todos").execute();

  await db
    .insertInto("todos")
    .values([
      {
        id: crypto.randomUUID(),
        text: "Write the seed script",
        completed: 1,
        createdAt: new Date().toISOString(),
      },
      {
        id: crypto.randomUUID(),
        text: "Update the documentation",
        completed: 0,
        createdAt: new Date().toISOString(),
      },
    ])
    .execute();

  console.log("✔ Finished seeding todos 🌱");
};

2. Add a seed script to package.json

Add a script to your package.json to run your seed file using the rwsdk worker-run command.

package.json

{
  "scripts": {
    "seed": "rwsdk worker-run ./src/scripts/seed.ts"
  }
}

3. Run the Seed Script

Now you can seed your database from the command line:

npm run seed

---

API Reference

createDb()

Creates a database instance connected to a Durable Object.

createDb<T>(durableObjectNamespace: DurableObjectNamespace, key: string): Database<T>

• durableObjectNamespace: Your Durable Object binding from the environment
• key: Unique identifier for this database instance
• Returns: Kysely database instance with your inferred types

Database<T> Type

The main database type that provides access to your tables and their schemas.

type AppDatabase = Database<typeof migrations>;
type Todo = AppDatabase["todos"]; // Inferred table type

Migrations Type

Use to define the structure for your database migrations.

export const migrations = {
  "001_create_todos": {
    async up(db) {
      await db.schema
        .createTable("todos")
        .addColumn("id", "text", (col) => col.primaryKey())
        .addColumn("text", "text", (col) => col.notNull())
        .addColumn("completed", "integer", (col) => col.notNull().defaultTo(0))
        .execute();
    },
    async down(db) {
      await db.schema.dropTable("todos").execute();
    },
  },
} satisfies Migrations;

SqliteDurableObject

Base class for your Durable Object that handles SQLite operations.

class YourDurableObject extends SqliteDurableObject {
  migrations = yourMigrations;
}

For complete query builder documentation, see the Kysely documentation. Everything you can do with Kysely, you can do with rwsdk/db.

---

FAQ

Q: Why use SQL instead of an ORM?

A: We’re not replacing ORMs - rwsdk/db works alongside your existing tools. We believe a lightweight, SQL-based query builder is a better fit as the out of the box solution, but you’re always free to use your preferred ORM or database solution where it makes sense for your application.

Q: What about latency and performance?

A: Durable Objects run in a single location, so there’s a latency consideration compared to globally distributed databases. However, they excel at simplicity and isolation. For many use cases, the ease of setup benefit outweighs the latency trade-off. You can also create multiple database instances with different keys to distribute load geographically if needed.

Q: Is this suitable for production use?

A: This is currently a preview feature, which means the API may evolve based on feedback. The underlying technologies (SQLite, Durable Objects, Kysely) are all production-ready, but we recommend testing thoroughly and having migration strategies ready as the API stabilizes.

Q: How do I handle database backups?

A: Durable Objects automatically persist data, but like D1, there aren’t built-in backup features. For critical applications, implement additional backup strategies. You can export data periodically or replicate to external systems as needed.

Q: Why does rwsdk/db auto-rollback failed migrations instead of leaving recovery to the developer?

A: We recognize that in many scenarios, particularly in production, a developer is best equipped to handle a failed migration with full context. Manual recovery can offer more granular control than a one-size-fits-all automated approach.

However, rwsdk/db opts for automated rollbacks by default to ensure database integrity. The primary reason is that SQLite does not support transactions for schema changes (DDL). A failed up() migration could otherwise leave the database in an inconsistent, half-migrated state. By automatically running the down() function, we return the database to a known-good state. This is critical for the zero-setup, runtime-isolated environments rwsdk/db is designed for, where direct manual intervention may not be feasible.

To work effectively with this automated system, it’s best to plan migrations carefully. Each down() function should be written to cleanly undo only what its corresponding up() function does. This practice makes it much easier and safer to reason about the database state, fix the migration, and redeploy.