Relationships & JOINs in D1

Real apps rarely store everything in one giant table. Instead we split data into focused tables and connect them with relationships (a relationship = a rule that says how rows in one table relate to rows in another). In this guide we build a tiny blog: users write posts, and posts are labeled with tags.

Two kinds of relationship cover almost everything: one-to-many (one user writes many posts) and many-to-many (a post has many tags, and a tag is on many posts). Once the data is split, we use a JOIN to glue it back together in one query.

Before any SQL, get a feel for the two patterns. The trick is always asking: 'for one row on this side, how many rows on the other side?'

An ER diagram (entity-relationship diagram) shows tables as boxes and relationships as connectors. The crow's-foot symbol (the little fork) marks the 'many' end. Here users connect to posts as one-to-many, and posts connect to tags as many-to-many — broken into two one-to-many links through post_tags.

A JOIN takes rows from two tables and stitches matching ones into a single wider row. You tell it how to match with an ON condition — usually 'foreign key equals primary key'. That's it: it's a lookup that happens inside the database instead of in your code.

Now wire the whole thing: create the tables (including the join table with foreign keys), seed data, run both kinds of JOIN, write atomically with a batch transaction, then read it from a Worker and a web page.

1. Bind the database

   After 'wrangler d1 create blog-db', add the binding so your Worker can reach it as env.DB.

   jsoncontent_copyCopy

   {
     "d1_databases": [
       {
         "binding": "DB",
         "database_name": "blog-db",
         "database_id": "<paste-your-id-here>"
       }
     ]
   }

2. Create the schema (with FKs)

   post_tags is the join table. It has two foreign keys and a composite primary key. ON DELETE CASCADE means deleting a post auto-removes its link rows.

   sqlcontent_copyCopy

   -- users: one row per author
   CREATE TABLE users (
     id    INTEGER PRIMARY KEY,
     email TEXT NOT NULL UNIQUE,
     name  TEXT NOT NULL
   );
   
   -- posts: each post belongs to exactly ONE user  (one-to-many)
   CREATE TABLE posts (
     id      INTEGER PRIMARY KEY,
     user_id INTEGER NOT NULL,
     title   TEXT NOT NULL,
     body    TEXT,
     FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
   );
   
   -- tags: one row per reusable label
   CREATE TABLE tags (
     id   INTEGER PRIMARY KEY,
     name TEXT NOT NULL UNIQUE
   );
   
   -- post_tags: the JOIN TABLE linking posts and tags  (many-to-many)
   CREATE TABLE post_tags (
     post_id INTEGER NOT NULL,
     tag_id  INTEGER NOT NULL,
     PRIMARY KEY (post_id, tag_id),
     FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE,
     FOREIGN KEY (tag_id)  REFERENCES tags(id)  ON DELETE CASCADE
   );
   
   -- index the foreign-key columns so JOINs stay fast
   CREATE INDEX idx_posts_user_id    ON posts(user_id);
   CREATE INDEX idx_post_tags_tag_id ON post_tags(tag_id);

3. Seed some rows

   Two users, three posts, three tags, and the link rows that connect posts to tags.

   sqlcontent_copyCopy

   INSERT INTO users (id, email, name) VALUES
     (1, 'mei@example.com',  'Mei'),
     (2, 'alex@example.com', 'Alex');
   
   INSERT INTO posts (id, user_id, title) VALUES
     (10, 1, 'Hello D1'),
     (11, 1, 'JOINs explained'),
     (12, 2, 'Edge databases');
   
   INSERT INTO tags (id, name) VALUES
     (100, 'sql'),
     (101, 'cloudflare'),
     (102, 'beginner');
   
   -- the many-to-many link rows
   INSERT INTO post_tags (post_id, tag_id) VALUES
     (10, 100), (10, 101),
     (11, 100), (11, 102),
     (12, 101);

4. Query one-to-many (post + author)

   JOIN posts to users on the foreign key. Each result row now carries the author's name pulled from the users table.

   sqlcontent_copyCopy

   -- One-to-many: every post WITH its author's name.
   SELECT posts.id, posts.title, users.name AS author
   FROM posts
   JOIN users ON posts.user_id = users.id
   WHERE users.id = ?
   ORDER BY posts.id DESC;

5. Query many-to-many (post + tags)

   Hop posts → post_tags → tags. Because a post can match many tags, use GROUP BY + GROUP_CONCAT to fold the tags back into one row per post.

   sqlcontent_copyCopy

   -- Many-to-many: every post with a comma list of its tags.
   SELECT posts.id,
          posts.title,
          GROUP_CONCAT(tags.name) AS tags
   FROM posts
   LEFT JOIN post_tags ON post_tags.post_id = posts.id
   LEFT JOIN tags      ON tags.id = post_tags.tag_id
   GROUP BY posts.id
   ORDER BY posts.id;
   
   -- The other direction: which posts carry the 'sql' tag?
   SELECT posts.title
   FROM posts
   JOIN post_tags ON post_tags.post_id = posts.id
   JOIN tags      ON tags.id = post_tags.tag_id
   WHERE tags.name = ?;

6. Write atomically with batch()

   Creating a post AND its tag links must be all-or-nothing. env.DB.batch() runs every statement in order inside one transaction; last_insert_rowid() returns the id of the post just inserted on the same connection.

   jscontent_copyCopy

   // Create a post AND attach two tags as ONE atomic transaction.
   // If any statement fails, D1 rolls the whole batch back.
   const results = await env.DB.batch([
     env.DB
       .prepare("INSERT INTO posts (user_id, title, body) VALUES (?, ?, ?)")
       .bind(userId, title, body),
     env.DB
       .prepare("INSERT INTO post_tags (post_id, tag_id) VALUES (last_insert_rowid(), ?)")
       .bind(sqlTagId),
     env.DB
       .prepare("INSERT INTO post_tags (post_id, tag_id) VALUES (last_insert_rowid(), ?)")
       .bind(cfTagId),
   ]);

7. Serve joined data from a Worker

   One endpoint, one query: combine author and tags server-side so the browser receives ready-to-render rows.

   jscontent_copyCopy

   export default {
     async fetch(request, env) {
       const url = new URL(request.url);
   
       // GET /api/users/:id/posts -> each post joined with author + tags
       if (request.method === "GET" && url.pathname.startsWith("/api/users/")) {
         const userId = url.pathname.split("/")[3];
   
         const { results } = await env.DB
           .prepare(
             `SELECT posts.id,
                     posts.title,
                     users.name              AS author,
                     GROUP_CONCAT(tags.name) AS tags
              FROM posts
              JOIN users          ON posts.user_id    = users.id
              LEFT JOIN post_tags ON post_tags.post_id = posts.id
              LEFT JOIN tags      ON tags.id           = post_tags.tag_id
              WHERE users.id = ?
              GROUP BY posts.id
              ORDER BY posts.id DESC`
           )
           .bind(userId)
           .all();
   
         return Response.json(results);
       }
   
       return new Response("Not found", { status: 404 });
     },
   };

8. Render it in the browser

   The front-end just fetches JSON. Each row already has author and tags — no extra requests, no client-side joining.

   jscontent_copyCopy

   // Browser: load one author's posts. Each row already carries the
   // author name and tag list, thanks to the JOINs on the server.
   const res = await fetch(`/api/users/${userId}/posts`);
   const posts = await res.json();
   
   posts.forEach((post) => {
     // e.g. 'Hello D1 - by Mei - sql,cloudflare'
     console.log(`${post.title} - by ${post.author} - ${post.tags}`);
   });

• Always index foreign-key columns (user_id, tag_id) so JOINs don't scan whole tables.
• A JOIN can multiply rows (one post × many tags); use GROUP BY + GROUP_CONCAT to fold them back to one row per post.
• Use LEFT JOIN when the left side should appear even with no match (a post with zero tags).
• batch() is atomic and all-or-nothing — ideal for 'create post + its tag links' together.
• D1 bills by rows read and written; a tight JOIN that reads fewer rows is also cheaper.
• Keep your CREATE TABLE statements in schema.sql and apply with wrangler d1 execute --file=./schema.sql.