Todo (PostgreSQL)

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

The PostgreSQL implementation leverages strong types and relational integrity to provide a secure and efficient task management foundation.

This schema relies on the users table from the Auth Domain. The userId field is configured with a foreign key constraint and onDelete: 'cascade'.

Installation Guide

To add this schema to your project, run:

npx servercn-cli add schema todo

During initialization, ensure you select PostgreSQL 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 one by one (such as auth/user and auth/session), 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 {
  pgTable,
  serial,
  varchar,
  boolean,
  timestamp,
  integer,
  json,
  uniqueIndex,
  index,
  pgEnum
} from "drizzle-orm/pg-core";
import { timestamps } from "./schema.helper";
import { relations } from "drizzle-orm";
import { todos } from "./todo.schema";
 
export interface IAvatar {
  public_id?: string;
  url: string;
  size?: number;
}
 
export const roleEnum = pgEnum("role", ["user", "admin"]);
export const providerEnum = pgEnum("provider", ["local", "google", "github"]);
 
export const users = pgTable(
  "users",
  {
    id: serial("id").primaryKey(),
    name: varchar("name", { length: 100 }).notNull(),
    email: varchar("email", { length: 255 }).notNull().unique(),
    password: varchar("password", { length: 255 }),
    role: roleEnum("role").default("user").notNull(),
 
    provider: providerEnum("provider").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: integer("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:
//? 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 PostgreSQL.

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

import {
  boolean,
  text,
  varchar,
  char,
  index,
  serial,
  pgTable
} from "drizzle-orm/pg-core";
import { users } from "./user.schema";
import { relations } from "drizzle-orm";
import { timestamps } from "./schema.helper";
 
export const todos = pgTable(
  "todos",
  {
    id: serial().primaryKey(),
    userId: char("user_id", { length: 36 })
      .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/pg-core";
 
export const timestamps = {
  createdAt: timestamp("created_at").defaultNow().notNull(),
  updatedAt: timestamp("updated_at")
    .defaultNow()
    .$onUpdate(() => new Date())
    .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: "postgresql",
  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 one by one (such as auth/user and auth/session), the relationships between them won’t be automatic—you’ll need to implement them manually.

File & Folder Structure

ServerCN

Select a file to view its contents

Installation

npx servercn-cli add schema todo