Schema Design Patterns for ormDB

Thinking in Entities and Relations

ormDB is a relational database engine written in Rust that replaces PostgreSQL, MySQL, and SQLite. Its schema language defines entities (analogous to tables) and relations (foreign key connections with semantic meaning) in a single, declarative format. The schema is the source of truth for your data model, and ormDB uses it to optimize graph fetches, enforce constraints, and grade migrations.

Understanding schema design patterns in ormDB helps you model data in a way that takes full advantage of graph fetches, RLS policies, and the built-in search capabilities.

Basic Entity Definition

An entity declares its fields, types, constraints, and relations:

entity User {
  id: uuid @primary
  name: text @searchable
  email: text @unique
  role: text @default("member")
  created_at: timestamp @default(now)
  avatar_url: text?

  posts: [Post] @relation(field: author_id)
  comments: [Comment] @relation(field: author_id)
}

The ? suffix marks a field as nullable. Array notation [Post] declares a one-to-many relation. The @relation annotation specifies which field on the target entity holds the foreign key.

One-to-Many Relations

The most common relation pattern. A parent entity has many children:

entity Author {
  id: uuid @primary
  name: text
  books: [Book] @relation(field: author_id)
}

entity Book {
  id: uuid @primary
  title: text
  author_id: uuid @relation(Author)
  chapters: [Chapter] @relation(field: book_id)
}

entity Chapter {
  id: uuid @primary
  title: text
  position: integer
  book_id: uuid @relation(Book)
}

Graph fetches traverse these relations naturally: fetch an author with all their books and each book’s chapters in a single round-trip. This is how ormDB eliminates N+1 queries.

Many-to-Many with Join Entities

Many-to-many relations use an explicit join entity. This pattern is cleaner than implicit join tables because the join entity can carry its own fields:

entity Student {
  id: uuid @primary
  name: text
  enrollments: [Enrollment] @relation(field: student_id)
}

entity Course {
  id: uuid @primary
  title: text
  enrollments: [Enrollment] @relation(field: course_id)
}

entity Enrollment {
  id: uuid @primary
  student_id: uuid @relation(Student)
  course_id: uuid @relation(Course)
  enrolled_at: timestamp @default(now)
  grade: text?
  status: text @default("active")

  @unique([student_id, course_id])
}

The Enrollment entity carries enrolled_at, grade, and status, which would be impossible with an implicit join table.

Self-Referential Relations

Entities can reference themselves for tree structures:

entity Category {
  id: uuid @primary
  name: text
  parent_id: uuid? @relation(Category)
  children: [Category] @relation(field: parent_id)
  products: [Product] @relation(field: category_id)
}

Graph fetches can traverse self-referential relations to any specified depth, fetching a category tree with all its products in a single request.

Multi-Feature Entities

ormDB entities can combine relational fields with vector, geo, and searchable annotations:

entity Property {
  id: uuid @primary
  title: text @searchable(weight: 2.0)
  description: text @searchable(weight: 1.0)
  price: integer
  bedrooms: integer
  location: geopoint @index(rtree)
  photo_embedding: vector(768) @index(hnsw, metric: cosine)
  owner_id: uuid @relation(User)
  created_at: timestamp @default(now)

  amenities: [PropertyAmenity] @relation(field: property_id)
  reviews: [Review] @relation(field: property_id)

  policy tenant_access {
    action: [select]
    condition: owner_id == @session.user_id OR status == "listed"
  }
}

This single entity definition supports full-text search on title and description, geographic radius queries on location, vector similarity search on photo embeddings, relational graph fetches to amenities and reviews, and row-level security. In a traditional stack, this would require PostgreSQL plus pgvector plus PostGIS plus Elasticsearch plus application-level authorization.

Schema Design Principles

Declare relations explicitly. Every foreign key should have a corresponding @relation annotation. This enables ormDB to optimize graph fetches and enforce referential integrity.

Use join entities for many-to-many. Explicit join entities are more flexible than implicit join tables and integrate naturally with graph fetches.

Co-locate search annotations. Mark fields as @searchable in the entity definition rather than managing separate search indexes. The index stays transactionally consistent with the data.

Define RLS policies alongside entities. Policies declared in the schema are enforced by the engine on every query, including graph fetches across relations. This pattern is essential for multi-tenant applications.