ORM-25: Add relationship helpers to the schema builder #25

Open
opened 2026-10-13 12:49:40 +01:00 by NoctisWolfshadow · 0 comments
Owner

Depends on: #18, #24

Context

1-1, 1-n and n-m relations all come down to a foreign key column or a pivot table. The helpers generate the right shape. Column names are explicit; there is no singularisation magic (categories to category_id is not guessable).

Tasks

  • t.foreign_key("user_id", "users") emits user_id INTEGER NOT NULL REFERENCES users(id). Options: .nullable(), .unique() (for 1-1) and .on_delete(Cascade | SetNull | Restrict). It also creates an index on the FK column unless .unique() is set.
  • s.create_pivot("post_tags", ("posts", "post_id"), ("tags", "tag_id")) emits a composite PK, both FKs ON DELETE CASCADE, and an index on the second column.
  • Rewrite the #18 migrations with the helpers.

Example

// 1-n: a post belongs to a user
Migration::schema(2, "create_posts")
    .up(|s| s.create_table("posts", |t| {
        t.id();
        t.foreign_key("user_id", "users").on_delete(Cascade);
        t.text("title").not_null();
        t.boolean("published").not_null().default(false);
    }))
    .down(|s| s.drop_table("posts"));

// 1-1: at most one profile per user
t.foreign_key("user_id", "users").unique().on_delete(Cascade);

// n-m
Migration::schema(4, "create_post_tags")
    .up(|s| s.create_pivot("post_tags", ("posts", "post_id"), ("tags", "tag_id")))
    .down(|s| s.drop_table("post_tags"));

Generated SQL for the 1-n example and the pivot:

CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title TEXT NOT NULL,
    published INTEGER NOT NULL DEFAULT 0
);
CREATE INDEX posts_user_id_index ON posts (user_id);

CREATE TABLE post_tags (
    post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    tag_id  INTEGER NOT NULL REFERENCES tags(id)  ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);
CREATE INDEX post_tags_tag_id_index ON post_tags (tag_id);

Acceptance criteria

  • Helper output matches the hand-written #18 schema (same PRAGMA table_info and PRAGMA foreign_key_list).
  • #18's tests still pass with the rewritten migrations.
**Depends on:** #18, #24 ## Context 1-1, 1-n and n-m relations all come down to a foreign key column or a pivot table. The helpers generate the right shape. Column names are explicit; there is no singularisation magic (`categories` to `category_id` is not guessable). ## Tasks - [ ] `t.foreign_key("user_id", "users")` emits `user_id INTEGER NOT NULL REFERENCES users(id)`. Options: `.nullable()`, `.unique()` (for 1-1) and `.on_delete(Cascade | SetNull | Restrict)`. It also creates an index on the FK column unless `.unique()` is set. - [ ] `s.create_pivot("post_tags", ("posts", "post_id"), ("tags", "tag_id"))` emits a composite PK, both FKs `ON DELETE CASCADE`, and an index on the second column. - [ ] Rewrite the #18 migrations with the helpers. ## Example ```rust // 1-n: a post belongs to a user Migration::schema(2, "create_posts") .up(|s| s.create_table("posts", |t| { t.id(); t.foreign_key("user_id", "users").on_delete(Cascade); t.text("title").not_null(); t.boolean("published").not_null().default(false); })) .down(|s| s.drop_table("posts")); // 1-1: at most one profile per user t.foreign_key("user_id", "users").unique().on_delete(Cascade); // n-m Migration::schema(4, "create_post_tags") .up(|s| s.create_pivot("post_tags", ("posts", "post_id"), ("tags", "tag_id"))) .down(|s| s.drop_table("post_tags")); ``` Generated SQL for the 1-n example and the pivot: ```sql CREATE TABLE posts ( id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE, title TEXT NOT NULL, published INTEGER NOT NULL DEFAULT 0 ); CREATE INDEX posts_user_id_index ON posts (user_id); CREATE TABLE post_tags ( post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE, tag_id INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE, PRIMARY KEY (post_id, tag_id) ); CREATE INDEX post_tags_tag_id_index ON post_tags (tag_id); ``` ## Acceptance criteria - [ ] Helper output matches the hand-written #18 schema (same `PRAGMA table_info` and `PRAGMA foreign_key_list`). - [ ] #18's tests still pass with the rewritten migrations.
Sign in to join this conversation.
No description provided.