Todo (MySQL with Drizzle)

This page documents the Todo schema for projects using MySQL with Drizzle ORM.

The MySQL implementation focuses on compatibility and performance, using standard types and efficient indexes.

Installation Guide

To add this schema to your project, run:

npx servercn-cli add schema todo

During initialization, ensure you select MySQL when prompted for the database.

Database Design

To see the complete database design, including the User schema from the Auth Domain, please click here visualization.

Database Design

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 is the core component for storing user identity, credentials, and profile information.

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

import {
  mysqlTable,
  varchar,
  boolean,
  timestamp,
  int,
  json,
  uniqueIndex,
  index,
  mysqlEnum
} from "drizzle-orm/mysql-core";
import { relations } from "drizzle-orm";
import { todos } from "./todo.schema";
import { timestamps } from "./schema.helper";
 
export 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)
  ]
);
 
//? Relations between
//? ii. user and todos.
//? (One-to-Many)
export const usersRelations = relations(users, ({ many }) => ({
  todos: many(todos)
}));
 
//? User type
export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;

2. Todo Schema

The Todo Schema defines the structure of your tasks in MySQL.

File Path: src/drizzle/schemas/todo.schema.ts

import {
  boolean,
  mysqlTable,
  text,
  varchar,
  index,
  int
} from "drizzle-orm/mysql-core";
import { users } from "./user.schema";
import { relations } from "drizzle-orm";
import { timestamps } from "./schema.helper";
 
export const todos = mysqlTable(
  "todos",
  {
    id: int("id").primaryKey().autoincrement(),
    userId: int("user_id")
      .notNull()
      .references(() => users.id, { onDelete: "cascade" }),
 
    title: varchar("title", { length: 255 }).notNull(),
    description: text("description"),
    completed: boolean("completed").notNull().default(false),
 
    ...timestamps
  },
  table => [
    index("todo_user_id_idx").on(table.userId),
    index("todo_completed_idx").on(table.completed)
  ]
);
 
//? Relations between
//? ii. todo and users.
//? (many-to-one)
export const todosRelations = relations(todos, ({ one }) => ({
  user: one(users, {
    fields: [todos.userId],
    references: [users.id]
  })
}));
 
export type Todo = typeof todos.$inferSelect;
export type NewTodo = typeof todos.$inferInsert;

Installation:

npx servercn-cli add schema todo/todo

3. schema.helper.ts

src/drizzle/schemas/schema.helper.ts 
import { timestamp } from "drizzle-orm/mysql-core";
 
export const timestamps = {
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at").defaultNow().onUpdateNow().notNull()
};

4. Export all schemas

ssrc/drizzle/index.ts 
export * from "./schemas/user.schema";
export * from "./schemas/todo.schema";

5. drizzle.config.ts

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;

Relational Design

Explicit relations make data fetching much simpler and more expressive:

src/drizzle/schemas/user.schema.ts 
import { relations } from "drizzle-orm";
import { todos } from "./todo.schema";
 
//? Relations between
//? ii. user and todos.
//? (One-to-Many)
export const usersRelations = relations(users, ({ many }) => ({
  todos: many(todos)
}));

src/drizzle/schemas/todo.schema.ts 
import { relations } from "drizzle-orm";
import { users } from "./user.schema";
 
//? Relations between
//? ii. todo and users.
//? (many-to-one)
export const todosRelations = relations(todos, ({ one }) => ({
  user: one(users, {
    fields: [todos.userId],
    references: [users.id]
  })
}));

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

File & Folder Structure

Loading files...

Installation

npx servercn-cli add schema todo