Blog App (MySQL with Drizzle ORM)

This document outlines the MySQL database schema for a sample blog application. The schema is designed to be efficient, scalable, and easy to understand. We use Drizzle ORM for defining our data models with TypeScript.

The core of our application revolves around six main tables: User, Post, Category, Comment, PostLike, and CommentLike.

Installation Guide

To add these schemas to your project, run:

npx servercn-cli add schema blog-app

If you install schema individually, the relationships between them won't be automatic—you'll need to implement them manually.

1. User Schema

The User schema stores essential information about blog authors and administrators, including authentication details and profile information.

Path: src/drizzle/schemas/user.schema.ts

import {
  mysqlTable,
  varchar,
  boolean,
  timestamp,
  int,
  json,
  uniqueIndex,
  index,
  mysqlEnum
} from "drizzle-orm/mysql-core";
import { timestamps } from "./schema.helper";
 
interface IAvatar {
  public_id?: string;
  url: string;
  size?: number;
}
 
export const users = mysqlTable(
  "users",
  {
    id: int("id").primaryKey().autoincrement(),
    name: varchar("name", { length: 100 }).notNull(),
    email: varchar("email", { length: 255 }).notNull().unique(),
    password: varchar("password", { length: 255 }),
    role: mysqlEnum("role", ["user", "admin"]).default("user").notNull(),
 
    provider: mysqlEnum("provider", ["local", "google", "github"])
      .default("local")
      .notNull(),
    providerId: varchar("provider_id", { length: 255 }),
 
    avatar: json("avatar").$type<IAvatar>(),
 
    isEmailVerified: boolean("is_email_verified").default(false).notNull(),
    lastLoginAt: timestamp("last_login_at"),
    failedLoginAttempts: int("failed_login_attempts").default(0).notNull(),
    lockUntil: timestamp("lock_until"),
 
    isDeleted: boolean("is_deleted").default(false).notNull(),
    deletedAt: timestamp("deleted_at"),
    reActivateAvailableAt: timestamp("re_activate_available_at"),
 
    ...timestamps
  },
  (table) => [
    uniqueIndex("email_idx").on(table.email),
    index("role_idx").on(table.role),
    index("is_deleted_idx").on(table.isDeleted)
  ]
);
 
export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;

Installation

npx servercn-cli add schema blog-app/user

2. Post Schema

The Post schema stores blog posts with support for titles, content, excerpts, featured images, and publication status.

Path: src/drizzle/schemas/post.schema.ts

import {
  index,
  int,
  json,
  mysqlEnum,
  mysqlTable,
  timestamp,
  uniqueIndex,
  varchar
} from "drizzle-orm/mysql-core";
import { timestamps } from "./schema.helper";
import { users } from "./user.schema";
import { categories } from "./category.schema";
 
const POST_STATUSES = ["draft", "published", "archived"] as const;
 
interface IFile {
  public_id: string;
  url: string;
  size: number;
}
 
export const posts = mysqlTable(
  "posts",
  {
    id: int().primaryKey().autoincrement(),
    title: varchar("title", { length: 100 }).notNull(),
    slug: varchar("slug").notNull().unique(),
    content: varchar("description"),
    excerpt: varchar("excerpt"),
    authorId: int("author_id")
      .references(() => users.id, { onDelete: "cascade" })
      .notNull(),
    categoryId: int("category_id")
      .references(() => users.id, { onDelete: "cascade" })
      .notNull(),
    tags: json("tags").$type<string[]>(),
    featuredImage: json("featured_image").$type<IFile>(),
    views: int("views").default(0).notNull(),
    status: mysqlEnum("status", POST_STATUSES).default("draft").notNull(),
    publishedAt: timestamp("published_at"),
    ...timestamps
  },
  (table) => [
    uniqueIndex("author_id_slug_unique").on(table.authorId, table.slug),
    index("category_idx").on(table.categoryId),
    index("status_idx").on(table.status)
  ]
);
 
export type NewPost = typeof posts.$inferInsert;
export type Post = typeof posts.$inferSelect;

Installation

npx servercn-cli add schema blog-app/post

3. Category Schema

The Category schema organizes blog posts into hierarchical categories with names, slugs, and descriptions.

Path: src/drizzle/schemas/category.schema.ts

import { int, mysqlTable, uniqueIndex, varchar } from "drizzle-orm/mysql-core";
import { timestamps } from "./schema.helper";
 
export const categories = mysqlTable(
  "categories",
  {
    id: int().primaryKey().autoincrement(),
    name: varchar("name", { length: 100 }).notNull(),
    slug: varchar("slug").notNull().unique(),
    description: varchar("description"),
    ...timestamps
  },
  (table) => [
    uniqueIndex("slug_idx").on(table.slug),
    uniqueIndex("name_idx").on(table.name)
  ]
);
 
export type NewCategory = typeof categories.$inferInsert;
export type Category = typeof categories.$inferSelect;

Installation

npx servercn-cli add schema blog-app/category

4. Comment Schema

The Comment schema enables user engagement through comments on blog posts, with support for nested replies.

Path: src/drizzle/schemas/comment.schema.ts

import { mysqlTable, int, text } from "drizzle-orm/mysql-core";
import { posts } from "./post.schema";
import { users } from "./user.schema";
import { timestamps } from "./schema.helper";
 
export const comments = mysqlTable("comments", {
  id: int("id").primaryKey().autoincrement(),
  postId: int("post_id")
    .notNull()
    .references(() => posts.id),
  authorId: int("author_id")
    .notNull()
    .references(() => users.id),
  content: text("content").notNull(),
  parentCommentId: int("parent_comment_id"),
  ...timestamps
});

Installation

npx servercn-cli add schema blog-app/comment

5. Post Like Schema

The PostLike schema tracks user likes on blog posts with a composite unique index.

Path: src/drizzle/schemas/post-like.schema.ts

import { mysqlTable, int, uniqueIndex } from "drizzle-orm/mysql-core";
import { users } from "./user.schema";
import { posts } from "./post.schema";
import { timestamps } from "./schema.helper";
 
export const postLikes = mysqlTable(
  "post_likes",
  {
    userId: int("user_id")
      .references(() => users.id, { onDelete: "cascade" })
      .notNull(),
    postId: int("post_id")
      .references(() => posts.id, { onDelete: "cascade" })
      .notNull(),
    ...timestamps
  },
  (table) => [uniqueIndex("unique_like").on(table.userId, table.postId)]
);

Installation

npx servercn-cli add schema blog-app/post-like

6. Comment Like Schema

The CommentLike schema tracks user likes on comments with a composite unique index.

Path: src/drizzle/schemas/comment-like.schema.ts

import { mysqlTable, int, uniqueIndex } from "drizzle-orm/mysql-core";
import { comments } from "./comment.schema";
import { users } from "./user.schema";
import { timestamps } from "./schema.helper";
 
export const commentLikes = mysqlTable(
  "comment_likes",
  {
    userId: int("user_id")
      .notNull()
      .references(() => users.id),
    commentId: int("comment_id")
      .notNull()
      .references(() => comments.id),
    ...timestamps
  },
  (table) => [
    uniqueIndex("unique_comment_like").on(table.userId, table.commentId)
  ]
);

Installation

npx servercn-cli add schema blog-app/comment-like

7. Export all schemas

All database schemas must be re-exported from a single entry point. This allows Drizzle to load every table definition from one file during migration generation.

File Path: src/drizzle/index.ts

export * from "./schemas/user.schema";
export * from "./schemas/post.schema";
export * from "./schemas/category.schema";
export * from "./schemas/comment.schema";
export * from "./schemas/post-like.schema";
export * from "./schemas/comment-like.schema";

8. drizzle.config.ts

Create a drizzle.config.ts file at the project root. This file defines how Drizzle connects to the database and where it should read schemas and write migrations.

File Path: drizzle.config.ts

import { Config, defineConfig } from "drizzle-kit";
 
export default defineConfig({
  out: "./src/drizzle/migrations",
  schema: "./src/drizzle/index.ts",
  dialect: "mysql",
  dbCredentials: {
    url: process.env.DATABASE_URL!
  },
  verbose: true,
  strict: true
}) satisfies Config;

File & Folder Structure

ServerCN

Select a file to view its contents

Installation

npx servercn-cli add schema blog-app