Overview

The @fragno-dev/db package provides an optional database layer for Fragments that need persistent storage. This allows Fragment authors to define a type-safe schema while users provide their existing database connection.

Key Features

• Schema Definition: Define tables, columns, indexes, and relations
• Type-Safe ORM: Full TypeScript type inference for queries
• User-Owned Database: Fragment users provide their database adapter
• ORM Agnostic: Works with Kysely or Drizzle
• Transactional: All database operations support ACID transactions
• Automatic Migrations: Schema versioning and migration generation
• Durable Hooks (outbox pattern): Trigger durable hooks when database operations are performed, allowing the user to take action after a successful commit.
• Namespaced Tables: Prevents conflicts with user tables

Installation

npm install @fragno-dev/db

Creating a Database Fragment

Use defineFragment() from the @fragno-dev/core package and extend it with withDatabase(schema) from @fragno-dev/db.

import { defineFragment, instantiate } from "@fragno-dev/core";
import { withDatabase, type FragnoPublicConfigWithDatabase } from "@fragno-dev/db";
import { commentSchema } from "./schema";

export interface CommentFragmentConfig {
  maxCommentsPerPost?: number;
}

const commentFragmentDef = defineFragment<CommentFragmentConfig>("comment-fragment")
  .extend(withDatabase(commentSchema))
  .providesBaseService(({ defineService }) => {
    return defineService({
      createComment: function (data) {
        return this.serviceTx(commentSchema)
          .mutate(({ uow }) => {
            const id = uow.create("comment", data);
            return { id: id.toJSON(), ...data };
          })
          .build();
      },
      getComments: function (postId: string) {
        return this.serviceTx(commentSchema)
          .retrieve((uow) =>
            uow.find("comment", (b) => b.whereIndex("idx_post", (eb) => eb("postId", "=", postId))),
          )
          .transformRetrieve(([comments]) => comments)
          .build();
      },
    });
  })
  .build();

// Make sure to allow your users to provide their database adapter
export function createCommentFragment(
  config: CommentFragmentConfig = {},
  options: FragnoPublicConfigWithDatabase,
) {
  return instantiate(commentFragmentDef)
    .withConfig(config)
    .withRoutes([])
    .withOptions(options)
    .build();
}

Querying using transactions

In Fragno DB, database operations are defined using a builder pattern and executed as atomic transactions. Services define operations using serviceTx, and route handlers execute them using handlerTx.

This design is important because it:

• Makes composition easy: a handler can combine multiple service calls (even across multiple Fragments) and commit them together
• Amortises database round-trips: work can be batched into fewer DB interactions for significant speed-ups for most applications
• Doesn't block the database: interactive transactions can block the database with locks, in the builder pattern this cannot happen.
• Enables Durable Hooks: hooks are recorded in the same transaction and run after commit (Durable Hooks)

this context in services vs handlers

• Services get this.serviceTx(schema): a builder to define retrieval and mutation operations, returning a TxResult via .build().
• Handlers get this.handlerTx(): executes service calls as transactions with automatic retries on optimistic conflicts via .execute().

See Transactions for the full pattern and examples.

Next Steps

• Learn about Defining Schemas with the append-only log approach
• Explore the Querying API for CRUD operations
• Learn how to structure Transactions and side effects with Durable Hooks
• See the example-fragments/fragno-db-library for a complete example