Defining Schemas

Learn how to define type-safe database schemas with the append-only log approach

Basic Schema Definition

Define your schema using the schema() function with a builder pattern:

src/schema.ts

import { schema, idColumn, column } from "@fragno-dev/db/schema";

export const commentSchema = schema((s) => {
  return s.addTable("comment", (t) => {
    return t
      .addColumn("id", idColumn())
      .addColumn("content", column("string"))
      .addColumn(
        "createdAt",
        column("timestamp").defaultTo((b) => b.now()),
      )
      .addColumn("userId", column("string"))
      .addColumn("postId", column("string"));
  });
});

Every Table Needs an ID

Every table must have exactly one idColumn(). This is the user-facing primary key (a CUID string). The system also adds a hidden _internalId column for database-level joins.

Columns, Types & Indexes

Column Types

// Basic types
column("string");
column("integer");
column("boolean");
column("timestamp");
column("json");
column("bigint");
column("decimal");
column("date");
column("binary");

// Special types
idColumn(); // Auto-generated CUID primary key
referenceColumn(); // Foreign key reference (bigint)

Modifiers

column("string")
  .nullable() // Allow NULL values
  .defaultTo("active"); // Database-level static default value

Default Values

`defaultTo()` - Database-Level

Database generates the default. Falls back to application code if unsupported.

// Static values
column("string").defaultTo("active");
column("integer").defaultTo(0);
column("boolean").defaultTo(true);

// Special functions
column("timestamp").defaultTo((b) => b.now()); // DEFAULT NOW() or equivalent

`defaultTo$()` - Runtime

Application generates the default.

column("string").defaultTo$((b) => b.cuid()); // Generate CUID
column("timestamp").defaultTo$((b) => b.now()); // Generate timestamp
column("integer").defaultTo$(42); // Static literal
column("integer").defaultTo$((b) => Math.random()); // Custom logic

Example:

.addTable("posts", (t) => {
  return t
    // idColumn() automatically creates a runtime CUID and database-level serial id
    .addColumn("id", idColumn())
    // database-level static default value "draft"
    .addColumn("status", column("string").defaultTo("draft"))
    // database-level timestamp default value
    .addColumn("createdAt", column("timestamp").defaultTo((b) => b.now()))
    // runtime CUID default value
    .addColumn("sessionId", column("string").defaultTo$((b) => b.cuid()));
    // runtime timestamp default value
    .addColumn("updatedAt", column("timestamp").defaultTo$((b) => b.now()));
});

Indexes

In Fragno, indexes are required for most operations such as where and orderBy.

.addTable("comment", (t) => {
  return t
    .addColumn("postId", column("string"))
    .addColumn("userId", column("string"))
    .addColumn("email", column("string"))
    // Single-column index
    .createIndex("idx_post", ["postId"])
    // Multi-column index
    .createIndex("idx_user_post", ["userId", "postId"])
    // Unique index
    .createIndex("idx_email", ["email"], { unique: true });
})

Index Your Query Columns

Create indexes for columns used in where() clauses. Multi-column indexes should list columns in order of selectivity (most selective first).

Relations

Define relationships between tables using addReference():

export const commentSchema = schema((s) => {
  return (
    s
      .addTable("comment", (t) => {
        return t
          .addColumn("id", idColumn())
          .addColumn("content", column("string"))
          .addColumn("parentId", referenceColumn().nullable());
      })
      // Self-referencing: comment -> parent comment
      .addReference("parent", {
        type: "one",
        from: { table: "comment", column: "parentId" },
        to: { table: "comment", column: "id" },
      })
  );
});

Relation Types

// Many-to-one: post -> author
.addReference("author", {
  type: "one",
  from: { table: "posts", column: "userId" },
  to: { table: "users", column: "id" },
})

// One-to-many: user -> posts
.addReference("posts", {
  type: "many",
  from: { table: "users", column: "id" },
  to: { table: "posts", column: "userId" },
})

External References

For references to user data outside your Fragment, use regular string columns:

.addTable("comment", (t) => {
  return t
    .addColumn("id", idColumn())
    .addColumn("content", column("string"))
    // External references - NOT referenceColumn()
    .addColumn("userId", column("string"))
    .addColumn("postId", column("string"));
})

Use referenceColumn() only for internal relations between your Fragment's tables. User data lives in separate tables outside your control, so no foreign key constraint is needed.

You may also chose not to have any external references in your schema. The user can define their own association table in their application's schema.

Schema Evolution

Fragno uses an append-only log approach where every schema change is recorded as an operation. This enables automatic migration generation.

How It Works

const mySchema = schema((s) => {
  return s
    .addTable("users", (t) => t.addColumn("id", idColumn()).addColumn("name", column("string")))
    .addTable("posts", (t) => t.addColumn("id", idColumn()).addColumn("title", column("string")))
    .alterTable("users", (t) => t.addColumn("email", column("string")));
});

// mySchema.version === 3        // Three operations = version 3

Each operation (.addTable(), .alterTable(), .addReference()) increments the version by 1. The CLI uses this to generate migrations for users upgrading from older versions.

Using alterTable()

To evolve your schema over time, always use alterTable() to add columns or indexes:

export const commentSchema = schema((s) => {
  return (
    s
      // Version 1: Initial table
      .addTable("comment", (t) => {
        return t.addColumn("id", idColumn()).addColumn("content", column("string"));
      })
      // Version 2: Add rating
      .alterTable("comment", (t) => {
        return t.addColumn("rating", column("integer").defaultTo(0));
      })
      // Version 3: Add index
      .alterTable("comment", (t) => {
        return t.createIndex("idx_rating", ["rating"]);
      })
  );
});

// commentSchema.version === 3

When users upgrade your Fragment, the CLI generates migrations for only the versions they're missing.

Rules:

Never modify existing addTable() calls
always use alterTable() to add columns
New columns must be nullable

System Columns

Fragno automatically adds hidden system columns to every table:

_internalId (bigint) - Database-native primary key for joins
_version (integer) - Optimistic concurrency control

These are hidden from the user-facing API but used internally for foreign key relationships and optimistic locking.

Next Steps

Learn about Querying with the DB
See Dependencies and Services for using db in context
Check the example-fragments/fragno-db-library for a complete example

On this page