10 - Build a Blog API

This is a hands-on exercise that ties together everything from the course. You'll build a blog backend from scratch.

What you'll do:

Define a schema with 4 tables: users, posts, tags, postTags (join table)
Define relations for one-to-many (user → posts) and many-to-many (posts ↔ tags)
Push the schema and seed test data
Query: posts with authors, posts by tag
Create, update, and delete posts with tags

Make sure your db/index.ts passes the schema to drizzle() (as shown in the Relations lesson) so db.query works.

Try each step yourself first, then check the solution below.

The Schema

A blog needs four tables: users, posts, tags, and a join table linking posts to tags. This covers one-to-many (user has many posts) and many-to-many (posts have tags, tags have posts).

// src/db/schema.ts
import { pgTable, serial, text, integer, boolean, timestamp } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  email: text('email').notNull().unique(),
  createdAt: timestamp('created_at').defaultNow()
});

export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: text('title').notNull(),
  content: text('content').notNull(),
  published: boolean('published').default(false),
  authorId: integer('author_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
  createdAt: timestamp('created_at').defaultNow(),
  updatedAt: timestamp('updated_at').defaultNow()
});

export const tags = pgTable('tags', {
  id: serial('id').primaryKey(),
  name: text('name').notNull().unique()
});

export const postTags = pgTable('post_tags', {
  postId: integer('post_id').notNull().references(() => posts.id, { onDelete: 'cascade' }),
  tagId: integer('tag_id').notNull().references(() => tags.id, { onDelete: 'cascade' })
});

{ onDelete: 'cascade' } means deleting a user removes their posts, and deleting a post removes its tag links. The database handles cleanup automatically.

Defining Relations

Relations tell Drizzle how tables connect. Define them for every table that participates in a relationship. For small projects, keep relations in the same file as your schema. You can split them into a separate relations.ts file when your schema grows beyond 10+ tables.

export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts)
}));

export const postsRelations = relations(posts, ({ one, many }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id]
  }),
  postTags: many(postTags)
}));

export const tagsRelations = relations(tags, ({ many }) => ({
  postTags: many(postTags)
}));

export const postTagsRelations = relations(postTags, ({ one }) => ({
  post: one(posts, {
    fields: [postTags.postId],
    references: [posts.id]
  }),
  tag: one(tags, {
    fields: [postTags.tagId],
    references: [tags.id]
  })
}));

Four tables, four relation definitions. The join table (postTags) has two one() relations pointing to each side.

Seeding Data

Push the schema and insert some test data.

npx drizzle-kit push

To insert some data use the following script:

// src/seed.ts
import { db, client } from './db';
import { users, posts, tags, postTags } from './db/schema';

// Clean up from previous runs (order matters due to foreign keys)
await db.delete(postTags);
await db.delete(posts);
await db.delete(tags);
await db.delete(users);

const [alice, bob] = await db.insert(users).values([
  { name: 'Alice', email: 'alice@example.com' },
  { name: 'Bob', email: 'bob@example.com' }
]).returning();

const [typescript, webdev, tutorial] = await db.insert(tags).values([
  { name: 'typescript' },
  { name: 'webdev' },
  { name: 'tutorial' }
]).returning();

const [post1, post2, post3] = await db.insert(posts).values([
  { title: 'Getting Started with Drizzle', content: 'Drizzle is a TypeScript ORM...', published: true, authorId: alice.id },
  { title: 'Advanced TypeScript Tips', content: 'Generics are powerful...', published: true, authorId: alice.id },
  { title: 'My First Post', content: 'Hello world...', published: false, authorId: bob.id }
]).returning();

await db.insert(postTags).values([
  { postId: post1.id, tagId: typescript.id },
  { postId: post1.id, tagId: tutorial.id },
  { postId: post2.id, tagId: typescript.id },
  { postId: post2.id, tagId: webdev.id },
  { postId: post3.id, tagId: webdev.id }
]);

await client.end();

Run it with npx tsx --env-file=.env src/seed.ts.

For the following examples, put each one in src/app.ts and run with npx tsx --env-file=.env src/app.ts. Remember to import what you need and call await client.end() at the end.

Querying Posts with Authors

Fetch all published posts with their author info and tags.

const publishedPosts = await db.query.posts.findMany({
  where: eq(posts.published, true),
  columns: { id: true, title: true, createdAt: true },
  with: {
    author: {
      columns: { id: true, name: true }
    },
    postTags: {
      with: { tag: true }
    }
  }
});
console.log(publishedPosts);
// [
//   {
//     id: 1, title: 'Getting Started with Drizzle', createdAt: '2024-01-15T...',
//     author: { id: 1, name: 'Alice' },
//     postTags: [
//       { postId: 1, tagId: 1, tag: { id: 1, name: 'typescript' } },
//       { postId: 1, tagId: 3, tag: { id: 3, name: 'tutorial' } }
//     ]
//   },
//   ...
// ]

One query. No N+1. The relational API handles the joins internally.

Querying Posts by Tag

Find all posts tagged with "typescript".

import { eq } from 'drizzle-orm';

const typescriptPosts = await db.select({
  postId: posts.id,
  postTitle: posts.title,
  authorName: users.name
}).from(postTags)
  .innerJoin(posts, eq(postTags.postId, posts.id))
  .innerJoin(users, eq(posts.authorId, users.id))
  .innerJoin(tags, eq(postTags.tagId, tags.id))
  .where(eq(tags.name, 'typescript'));
console.log(typescriptPosts);
// [
//   { postId: 1, postTitle: 'Getting Started with Drizzle', authorName: 'Alice' },
//   { postId: 2, postTitle: 'Advanced TypeScript Tips', authorName: 'Alice' }
// ]

Start from the join table and work outward. This is a case where SQL joins are cleaner than the relational API because you're filtering by a child condition.

Creating a Post with Tags

Insert the post first, then link the tags. Use .returning() to get the new post's ID.

// First, get the IDs we need
const [alice] = await db.select().from(users).where(eq(users.name, 'Alice'));
const [typescript] = await db.select().from(tags).where(eq(tags.name, 'typescript'));
const [tutorial] = await db.select().from(tags).where(eq(tags.name, 'tutorial'));

async function createPost(
  title: string,
  content: string,
  authorId: number,
  tagIds: number[]
) {
  const [post] = await db.insert(posts).values({
    title,
    content,
    authorId
  }).returning();

  if (tagIds.length > 0) {
    await db.insert(postTags).values(
      tagIds.map(tagId => ({ postId: post.id, tagId }))
    );
  }

  return post;
}

const newPost = await createPost(
  'Drizzle Relations Deep Dive',
  'Relations in Drizzle...',
  alice.id,
  [typescript.id, tutorial.id]
);
console.log(newPost);
// { id: 4, title: 'Drizzle Relations Deep Dive', ... }

Two queries: one insert for the post, one batch insert for the tags. This is fine. Trying to do it in one query adds complexity with no real benefit.

Updating a Post

Update the post fields with .set(). To change tags, delete the old links and insert new ones.

// Update title and content
const [updated] = await db.update(posts)
  .set({ title: 'Updated Title', updatedAt: new Date() })
  .where(eq(posts.id, 4))
  .returning();
console.log(updated);
// { id: 4, title: 'Updated Title', updatedAt: '2024-01-16T...' }

// Replace tags: delete old, insert new
const [webdev] = await db.select().from(tags).where(eq(tags.name, 'webdev'));
const [tutorialTag] = await db.select().from(tags).where(eq(tags.name, 'tutorial'));

await db.delete(postTags).where(eq(postTags.postId, 4));
await db.insert(postTags).values([
  { postId: 4, tagId: webdev.id },
  { postId: 4, tagId: tutorialTag.id }
]);

Delete-then-insert is the simplest way to replace many-to-many links. For large datasets, you could diff and only insert/delete the changes, but for a blog this is plenty efficient.

Deleting a Post

Because we set onDelete: 'cascade' on postTags.postId, deleting a post automatically removes its tag links.

await db.delete(posts).where(eq(posts.id, 4));
// Post deleted, post_tags rows for post 4 also deleted automatically

Without cascade, you'd need to delete the join table rows first:

// ❌ Without cascade: manual cleanup required
await db.delete(postTags).where(eq(postTags.postId, 4));
await db.delete(posts).where(eq(posts.id, 4));

// ✅ With cascade: one query, database handles cleanup
await db.delete(posts).where(eq(posts.id, 4));

Deleting a user cascades to their posts, which cascades to post_tags. The whole chain cleans up automatically.

Key Takeaways

A blog schema needs four tables: users, posts, tags, and a post_tags join table for many-to-many
{ onDelete: 'cascade' } on foreign keys lets the database handle cleanup when parent rows are deleted
Define relations() on every table that participates in a relationship, including join tables
Use db.query with with to fetch posts with authors and tags in one call
Use SQL joins when filtering by related data (e.g., posts by tag name)
Create many-to-many links with a batch insert: db.insert(postTags).values(tagIds.map(...))
Replace many-to-many links by deleting old rows and inserting new ones
.returning() gives back the inserted/updated row so you can use its ID immediately