Elysia Tools

在一个清晰的工作台里浏览工具、案例、可视化和主题指南。

热门分类

专题

语言
ZHDERUPTENFRES

Elysia Tools

导航

数据库 ORM

Drizzle ORM 示例

Drizzle ORM轻量级TypeScript ORM示例,包括模式定义、查询、迁移、关系和数据库模式

案例

这个案例集合中的条目

代码typescript简单

Drizzle ORM 基础设置

基本Drizzle ORM设置,包括数据库配置、模式定义和基本CRUD操作

难度

4/10

预计时间

30 min

标签

drizzle, orm, typescript, database

前置条件

TypeScript, SQL basics, Node.js, Database concepts

typescript
// Drizzle ORM Basic Setup Example

import { drizzle } from 'drizzle-orm/postgres-js';
import { pgTable, serial, text, integer, timestamp, boolean, varchar, pgEnum } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';
import { sql } from 'drizzle-orm';

// 1. Database Configuration
// Environment variables
const DATABASE_URL = process.env.DATABASE_URL || 'postgresql://localhost:5432/myapp';

// Database instance
export const db = drizzle(DATABASE_URL);

// 2. Enums
export const userRoleEnum = pgEnum('user_role', ['admin', 'user', 'moderator']);
export const postStatusEnum = pgEnum('post_status', ['draft', 'published', 'archived']);
export const orderStatusEnum = pgEnum('order_status', ['pending', 'confirmed', 'shipped', 'delivered', 'cancelled']);

// 3. Table Definitions

// Users table
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  username: varchar('username', { length: 50 }).unique().notNull(),
  email: varchar('email', { length: 255 }).unique().notNull(),
  passwordHash: varchar('password_hash', { length: 255 }).notNull(),
  firstName: varchar('first_name', { length: 100 }).notNull(),
  lastName: varchar('last_name', { length: 100 }).notNull(),
  role: userRoleEnum('role').default('user'),
  isActive: boolean('is_active').default(true),
  emailVerified: boolean('email_verified').default(false),
  avatarUrl: varchar('avatar_url', { length: 500 }).optional(),
  bio: text('bio').optional(),
  createdAt: timestamp('created_at', { mode: 'default' }).defaultNow(),
  updatedAt: timestamp('updated_at', { mode: 'default' }).defaultNow(),
  lastLoginAt: timestamp('last_login_at').optional()
});

// Categories table
export const categories = pgTable('categories', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 100 }).notNull(),
  slug: varchar('slug', { length: 100 }).unique().notNull(),
  description: text('description').optional(),
  parentId: integer('parent_id').references(() => categories.id).optional(),
  isActive: boolean('is_active').default(true),
  sortOrder: integer('sort_order').default(0),
  createdAt: timestamp('created_at', { mode: 'default' }).defaultNow()
});

// Posts table
export const posts = pgTable('posts', {
  id: serial('id').primaryKey(),
  title: varchar('title', { length: 255 }).notNull(),
  slug: varchar('slug', { length: 255 }).unique().notNull(),
  content: text('content').notNull(),
  excerpt: text('excerpt').optional(),
  authorId: integer('author_id').references(() => users.id).notNull(),
  status: postStatusEnum('status').default('draft'),
  featured: boolean('featured').default(false),
  viewCount: integer('view_count').default(0),
  publishedAt: timestamp('published_at').optional(),
  createdAt: timestamp('created_at', { mode: 'default' }).defaultNow(),
  updatedAt: timestamp('updated_at', { mode: 'default' }).defaultNow()
});

// Post categories join table
export const postCategories = pgTable('post_categories', {
  postId: integer('post_id').references(() => posts.id).notNull(),
  categoryId: integer('category_id').references(() => categories.id).notNull(),
  createdAt: timestamp('created_at', { mode: 'default' }).defaultNow()
});

// Comments table
export const comments = pgTable('comments', {
  id: serial('id').primaryKey(),
  postId: integer('post_id').references(() => posts.id).notNull(),
  authorId: integer('author_id').references(() => users.id).notNull(),
  content: text('content').notNull(),
  parentId: integer('parent_id').references(() => comments.id).optional(),
  isApproved: boolean('is_approved').default(false),
  createdAt: timestamp('created_at', { mode: 'default' }).defaultNow(),
  updatedAt: timestamp('updated_at', { mode: 'default' }).defaultNow()
});

// Tags table
export const tags = pgTable('tags', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 50 }).unique().notNull(),
  slug: varchar('slug', { length: 50 }).unique().notNull(),
  color: varchar('color', { length: 7 }).default('#000000'), // Hex color
  createdAt: timestamp('created_at', { mode: 'default' }).defaultNow()
});

// Post tags join table
export const postTags = pgTable('post_tags', {
  postId: integer('post_id').references(() => posts.id).notNull(),
  tagId: integer('tag_id').references(() => tags.id).notNull(),
  createdAt: timestamp('created_at', { mode: 'default' }).defaultNow()
});

// 4. Type Definitions (TypeScript types extracted from schemas)
export type User = typeof users.$inferSelect;
export type NewUser = typeof users.$inferInsert;
export type Category = typeof categories.$inferSelect;
export type NewCategory = typeof categories.$inferInsert;
export type Post = typeof posts.$inferSelect;
export type NewPost = typeof posts.$inferInsert;
export type Comment = typeof comments.$inferSelect;
export type NewComment = typeof comments.$inferInsert;
export type Tag = typeof tags.$inferSelect;
export type NewTag = typeof tags.$inferInsert;

// 5. Relations Definition
export const usersRelations = relations(users, ({ many }) => ({
  posts: many(posts, { relationName: 'author' }),
  comments: many(comments, { relationName: 'author' })
}));

export const categoriesRelations = relations(categories, ({ many, one }) => ({
  parent: one(categories, {
    fields: [categories.parentId],
    references: [categories.id],
    relationName: 'children'
  }),
  children: many(categories, {
    fields: [categories.parentId],
    references: [categories.id],
    relationName: 'parent'
  }),
  posts: many(posts, { relationName: 'categories' })
}));

export const postsRelations = relations(posts, ({ one, many }) => ({
  author: one(users, {
    fields: [posts.authorId],
    references: [users.id],
    relationName: 'posts'
  }),
  comments: many(comments, { relationName: 'post' }),
  categories: many(categories, { relationName: 'posts' }),
  tags: many(tags, { relationName: 'posts' }),
  parentComments: many(comments, {
    fields: [comments.parentId],
    references: [comments.id],
    relationName: 'childComments'
  })
}));

export const commentsRelations = relations(comments, ({ one, many }) => ({
  post: one(posts, {
    fields: [comments.postId],
    references: [posts.id],
    relationName: 'comments'
  }),
  author: one(users, {
    fields: [comments.authorId],
    references: [users.id],
    relationName: 'comments'
  }),
  parent: one(comments, {
    fields: [comments.parentId],
    references: [comments.id],
    relationName: 'childComments'
  }),
  childComments: many(comments, {
    fields: [comments.parentId],
    references: [comments.id],
    relationName: 'parent'
  })
}));

export const tagsRelations = relations(tags, ({ many }) => ({
  posts: many(posts, { relationName: 'tags' })
}));

// 6. Basic CRUD Operations

// Create operations
export async function createUser(userData: NewUser): Promise<User> {
  const [user] = await db.insert(users).values(userData).returning();
  return user;
}

export async function createCategory(categoryData: NewCategory): Promise<Category> {
  const [category] = await db.insert(categories).values(categoryData).returning();
  return category;
}

export async function createPost(postData: NewPost, categoryIds?: number[], tagIds?: number[]): Promise<Post> {
  const [post] = await db.insert(posts).values(postData).returning();

  // Add categories if provided
  if (categoryIds && categoryIds.length > 0) {
    await db.insert(postCategories).values(
      categoryIds.map(categoryId => ({
        postId: post.id,
        categoryId
      }))
    );
  }

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

  return post;
}

export async function createComment(commentData: NewComment): Promise<Comment> {
  const [comment] = await db.insert(comments).values(commentData).returning();
  return comment;
}

export async function createTag(tagData: NewTag): Promise<Tag> {
  const [tag] = await db.insert(tags).values(tagData).returning();
  return tag;
}

// Read operations
export async function getUserById(id: number): Promise<User | undefined> {
  const [user] = await db.select().from(users).where(eq(users.id, id)).limit(1);
  return user;
}

export async function getUserByEmail(email: string): Promise<User | undefined> {
  const [user] = await db.select().from(users).where(eq(users.email, email)).limit(1);
  return user;
}

export function getAllUsers(): Promise<User[]> {
  return db.select().from(users);
}

export function getActiveUsers(): Promise<User[]> {
  return db.select().from(users).where(eq(users.isActive, true));
}

export async function getPostById(id: number): Promise<Post | undefined> {
  const [post] = await db.select().from(posts).where(eq(posts.id, id)).limit(1);
  return post;
}

export function getPostsByAuthor(authorId: number): Promise<Post[]> {
  return db.select().from(posts).where(eq(posts.authorId, authorId));
}

export function getPublishedPosts(): Promise<Post[]> {
  return db.select().from(posts).where(eq(posts.status, 'published'));
}

export function getFeaturedPosts(): Promise<Post[]> {
  return db.select().from(posts).where(eq(posts.featured, true));
}

export function searchPosts(searchTerm: string): Promise<Post[]> {
  return db.select().from(posts).where(
    or(
      ilike(posts.title, `%${searchTerm}%`),
      ilike(posts.content, `%${searchTerm}%`)
    )
  );
}

export function getPostsWithCategories(postId: number): Promise<Post & { categories: Category[] }> {
  return db
    .select({
      id: posts.id,
      title: posts.title,
      slug: posts.slug,
      content: posts.content,
      excerpt: posts.excerpt,
      authorId: posts.authorId,
      status: posts.status,
      featured: posts.featured,
      viewCount: posts.viewCount,
      publishedAt: posts.publishedAt,
      createdAt: posts.createdAt,
      updatedAt: posts.updatedAt
    })
    .from(posts)
    .leftJoin(postCategories, eq(posts.id, postCategories.postId))
    .leftJoin(categories, eq(postCategories.categoryId, categories.id))
    .where(eq(posts.id, postId))
    .groupBy(posts.id)
    .then(posts => {
      // This is a simplified version - in real code, you'd use more sophisticated SQL
      return posts.map(post => ({
        ...post,
        categories: [] // Would need to fetch categories separately or use proper SQL
      }));
    });
}

// Update operations
export async function updateUser(id: number, updateData: Partial<NewUser>): Promise<User> {
  const [user] = await db
    .update(users)
    .set({ ...updateData, updatedAt: new Date() })
    .where(eq(users.id, id))
    .returning();
  return user;
}

export async function updatePost(id: number, updateData: Partial<NewPost>): Promise<Post> {
  const [post] = await db
    .update(posts)
    .set({ ...updateData, updatedAt: new Date() })
    .where(eq(posts.id, id))
    .returning();
  return post;
}

export async function incrementPostViewCount(id: number): Promise<Post> {
  const [post] = await db
    .update(posts)
    .set({
      viewCount: sql`${posts.viewCount} + 1`,
      updatedAt: new Date()
    })
    .where(eq(posts.id, id))
    .returning();
  return post;
}

// Delete operations
export async function deleteUser(id: number): Promise<void> {
  await db.delete(users).where(eq(users.id, id));
}

export async function deletePost(id: number): Promise<void> {
  await db.delete(posts).where(eq(posts.id, id));
}

export async function deleteComment(id: number): Promise<void> {
  await db.delete(comments).where(eq(comments.id, id));
}

// 7. Advanced Queries
export function getUsersWithPostCount() {
  return db
    .select({
      id: users.id,
      username: users.username,
      email: users.email,
      firstName: users.firstName,
      lastName: users.lastName,
      role: users.role,
      isActive: users.isActive,
      postCount: count(posts.id)
    })
    .from(users)
    .leftJoin(posts, eq(users.id, posts.authorId))
    .groupBy(users.id)
    .orderBy(desc(sql`count(${posts.id})`));
}

export function getRecentPosts(limit: number = 10) {
  return db
    .select()
    .from(posts)
    .where(eq(posts.status, 'published'))
    .orderBy(desc(posts.createdAt))
    .limit(limit);
}

export function getPopularPosts(days: number = 30, limit: number = 5) {
  return db
    .select()
    .from(posts)
    .where(
      and(
        eq(posts.status, 'published'),
        gte(posts.createdAt, new Date(Date.now() - days * 24 * 60 * 60 * 1000))
      )
    )
    .orderBy(desc(posts.viewCount))
    .limit(limit);
}

export function getPostsWithComments(postId: number) {
  return db.query.posts
    .findFirst({
      where: eq(posts.id, postId),
      with: {
        author: {
          columns: {
            id: true,
            username: true,
            firstName: true,
            lastName: true
          }
        },
        comments: {
          orderBy: desc(comments.createdAt),
          with: {
            author: {
              columns: {
                id: true,
                username: true,
                firstName: true,
                lastName: true
              }
            }
          }
        }
      }
    });
}

// 8. Transaction Examples
export async function createPostWithTags(
  postData: NewPost,
  tagNames: string[]
): Promise<Post> {
  return db.transaction(async (tx) => {
    // Create the post
    const [post] = await tx.insert(posts).values(postData).returning();

    // Create or find tags
    const createdTags = await Promise.all(
      tagNames.map(async (tagName) => {
        // Check if tag already exists
        const [existingTag] = await tx
          .select()
          .from(tags)
          .where(eq(tags.name, tagName))
          .limit(1);

        if (existingTag) {
          return existingTag;
        }

        // Create new tag
        const [newTag] = await tx
          .insert(tags)
          .values({
            name: tagName,
            slug: tagName.toLowerCase().replace(/\s+/g, '-')
          })
          .returning();

        return newTag;
      })
    );

    // Create post-tag relationships
    await tx.insert(postTags).values(
      createdTags.map(tag => ({
        postId: post.id,
        tagId: tag.id
      }))
    );

    return post;
  });
}

export async function transferPostOwnership(
  postId: number,
  newAuthorId: number
): Promise<Post> {
  return db.transaction(async (tx) => {
    // Update the post
    const [post] = await tx
      .update(posts)
      .set({ authorId: newAuthorId, updatedAt: new Date() })
      .where(eq(posts.id, postId))
      .returning();

    return post;
  });
}

// 9. Migration Helper Functions
export async function createDatabaseSchema() {
  // This would typically be handled by Drizzle migrations
  // but here's an example of programmatic schema creation

  console.log('Creating database schema...');

  // Tables would be created here if not using migration files
  console.log('Database schema created successfully!');
}

// 10. Database Health Check
export async function checkDatabaseHealth(): Promise<boolean> {
  try {
    // Test basic connection
    await db.select().from(users).limit(1);

    // Test all tables exist
    const tables = [
      'users',
      'categories',
      'posts',
      'comments',
      'tags',
      'post_categories',
      'post_tags'
    ];

    for (const table of tables) {
      await db.select().from(sql.raw(`SELECT 1 FROM ${table} LIMIT 1`));
    }

    console.log('Database health check passed');
    return true;
  } catch (error) {
    console.error('Database health check failed:', error);
    return false;
  }
}

// 11. Utility Functions

// Type-safe query builder helper
function buildQuery<T>(
  table: any,
  options: {
    where?: any;
    orderBy?: any;
    limit?: number;
    offset?: number;
    select?: any[];
  } = {}
) {
  let query = db.select(options.select || []).from(table);

  if (options.where) {
    query = query.where(options.where);
  }

  if (options.orderBy) {
    query = query.orderBy(options.orderBy);
  }

  if (options.limit) {
    query = query.limit(options.limit);
  }

  if (options.offset) {
    query = query.offset(options.offset);
  }

  return query as any; // Return as T
}

// Pagination helper
export interface PaginationOptions {
  page?: number;
  limit?: number;
  sortBy?: string;
  sortOrder?: 'asc' | 'desc';
}

export interface PaginatedResult<T> {
  data: T[];
  pagination: {
    page: number;
    limit: number;
    total: number;
    totalPages: number;
    hasNext: boolean;
    hasPrev: boolean;
  };
}

export async function getPaginatedResults<T>(
  query: any,
  options: PaginationOptions = {}
): Promise<PaginatedResult<T>> {
  const page = options.page || 1;
  const limit = options.limit || 20;
  const offset = (page - 1) * limit;

  // Get total count
  const [{ count }] = await db
    .select({ count: sql`count(*)` })
    .from(query as any);

  const total = Number(count);

  // Get paginated data
  const data = await query
    .limit(limit)
    .offset(offset)
    .orderBy(
      options.sortBy && options.sortOrder
        ? options.sortOrder === 'asc'
          ? asc(sql.raw(`${options.sortBy}`))
          : desc(sql.raw(`${options.sortBy}`))
        : sql`id`
    );

  return {
    data,
    pagination: {
      page,
      limit,
      total,
      totalPages: Math.ceil(total / limit),
      hasNext: page * limit < total,
      hasPrev: page > 1
    }
  };
}

// Import required operators and functions
import { eq, and, or, gte, lte, ilike, count, asc, desc } from 'drizzle-orm';

export {
  users,
  categories,
  posts,
  comments,
  tags,
  postCategories,
  postTags,
  db,
  type User,
  type NewUser,
  type Category,
  type NewCategory,
  type Post,
  type NewPost,
  type Comment,
  type NewComment,
  type Tag,
  type NewTag
};
代码typescript中等

Drizzle 迁移和关系

Drizzle ORM高级特性,包括迁移、复杂关系、预加载和数据库模式

难度

7/10

预计时间

45 min

标签

drizzle, migrations, relations, typescript

前置条件

Drizzle basics, TypeScript, SQL, Database design, PostgreSQL

typescript
// Drizzle Migrations and Relations Advanced Example

import { drizzle } from 'drizzle-orm/postgres-js';
import { pgTable, serial, text, integer, timestamp, boolean, varchar, pgEnum, json, real } from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';
import { sql } from 'drizzle-orm';
import { migrate } from 'drizzle-orm/postgres-js/migrator';
import { eq, and, or, inArray, isNull, not, asc, desc } from 'drizzle-orm';

// Database configuration
const DATABASE_URL = process.env.DATABASE_URL || 'postgresql://localhost:5432/myapp';
export const db = drizzle(DATABASE_URL);

// 1. Enhanced Table Definitions with Advanced Features

// Payment status enum
export const paymentStatusEnum = pgEnum('payment_status', [
  'pending',
  'processing',
  'completed',
  'failed',
  'refunded'
]);

// Notification type enum
export const notificationTypeEnum = pgEnum('notification_type', [
  'info',
  'success',
  'warning',
  'error'
]);

// Users table with additional fields
export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  username: varchar('username', { length: 50 }).unique().notNull(),
  email: varchar('email', { length: 255 }).unique().notNull(),
  passwordHash: varchar('password_hash', { length: 255 }).notNull(),
  firstName: varchar('first_name', { length: 100 }).notNull(),
  lastName: varchar('last_name', { length: 100 }).notNull(),
  avatarUrl: varchar('avatar_url', { length: 500 }).optional(),
  bio: text('bio').optional(),
  website: varchar('website', { length: 255 }).optional(),
  location: json('location').optional(), // Store as JSON
  preferences: json('preferences').default({}), // User preferences
  isActive: boolean('is_active').default(true),
  isVerified: boolean('is_verified').default(false),
  lastLoginAt: timestamp('last_login_at').optional(),
  createdAt: timestamp('created_at', { mode: 'default' }).defaultNow(),
  updatedAt: timestamp('updated_at', { mode: 'default' }).defaultNow()
});

// Enhanced products table
export const products = pgTable('products', {
  id: serial('id').primaryKey(),
  name: varchar('name', { length: 255 }).notNull(),
  slug: varchar('slug', { length: 255 }).unique().notNull(),
  description: text('description').notNull(),
  shortDescription: varchar('short_description', { length: 500 }).optional(),
  price: real('price').notNull(),
  compareAtPrice: real('compare_at_price').optional(),
  sku: varchar('sku', { length: 100 }).unique().notNull(),
  barcode: varchar('barcode', { length: 50 }).unique().optional(),
  trackInventory: boolean('track_inventory').default(true),
  stockQuantity: integer('stock_quantity').default(0),
  minStockLevel: integer('min_stock_level').default(0),
  weight: real('weight').optional(),
  dimensions: json('dimensions').optional(), // { length, width, height }
  images: json('images').default([]), // Array of image URLs
  isDigital: boolean('is_digital').default(false),
  isActive: boolean('is_active').default(true),
  featured: boolean('featured').default(false),
  rating: real('rating').default(0),
  reviewCount: integer('review_count').default(0),
  createdAt: timestamp('created_at', { mode: 'default' }).defaultNow(),
  updatedAt: timestamp('updated_at', { mode: 'default' }).defaultNow()
});

// Orders table
export const orders = pgTable('orders', {
  id: serial('id').primaryKey(),
  orderNumber: varchar('order_number', { length: 50 }).unique().notNull(),
  customerId: integer('customer_id').references(() => users.id).notNull(),
  status: paymentStatusEnum('status').default('pending'),
  subtotal: real('subtotal').notNull(),
  tax: real('tax').notNull(),
  shipping: real('shipping').notNull(),
  total: real('total').notNull(),
  currency: varchar('currency', { length: 3 }).default('USD'),
  shippingAddress: json('shipping_address').notNull(),
  billingAddress: json('billing_address').notNull(),
  notes: text('notes').optional(),
  shippedAt: timestamp('shipped_at').optional(),
  deliveredAt: timestamp('delivered_at').optional(),
  createdAt: timestamp('created_at', { mode: 'default' }).defaultNow(),
  updatedAt: timestamp('updated_at', { mode: 'default' }).defaultNow()
});

// Order items table
export const orderItems = pgTable('order_items', {
  id: serial('id').primaryKey(),
  orderId: integer('order_id').references(() => orders.id).notNull(),
  productId: integer('product_id').references(() => products.id).notNull(),
  quantity: integer('quantity').positive().notNull(),
  unitPrice: real('unit_price').notNull(),
  totalPrice: real('total_price').notNull(),
  productName: varchar('product_name', { length: 255 }).notNull(), // Store product name at time of order
  productSku: varchar('product_sku', { length: 100 }).notNull(),
  createdAt: timestamp('created_at', { mode: 'default' }).defaultNow()
});

// Reviews table
export const reviews = pgTable('reviews', {
  id: serial('id').primaryKey(),
  productId: integer('product_id').references(() => products.id).notNull(),
  customerId: integer('customer_id').references(() => users.id).notNull(),
  rating: integer('rating').min(1).max(5).notNull(),
  title: varchar('title', { length: 255 }).optional(),
  content: text('content').notNull(),
  isVerified: boolean('is_verified').default(false),
  isApproved: boolean('is_approved').default(true),
  helpfulCount: integer('helpful_count').default(0),
  createdAt: timestamp('created_at', { mode: 'default' }).defaultNow(),
  updatedAt: timestamp('updated_at', { mode: 'default' }).defaultNow()
});

// Notifications table
export const notifications = pgTable('notifications', {
  id: serial('id').primaryKey(),
  userId: integer('user_id').references(() => users.id).notNull(),
  type: notificationTypeEnum('type').notNull(),
  title: varchar('title', { length: 255 }).notNull(),
  message: text('message').notNull(),
  data: json('data').optional(),
  isRead: boolean('is_read').default(false),
  createdAt: timestamp('created_at', { mode: 'default' }).defaultNow(),
  readAt: timestamp('read_at').optional()
});

// 2. Enhanced Relations

export const usersRelations = relations(users, ({ many }) => ({
  orders: many(orders, { relationName: 'customer' }),
  reviews: many(reviews, { relationName: 'customer' }),
  notifications: many(notifications, { relationName: 'user' })
}));

export const productsRelations = relations(products, ({ many }) => ({
  orderItems: many(orderItems, { relationName: 'product' }),
  reviews: many(reviews, { relationName: 'product' })
}));

export const ordersRelations = relations(orders, ({ one, many }) => ({
  customer: one(users, {
    fields: [orders.customerId],
    references: [users.id],
    relationName: 'orders'
  }),
  items: many(orderItems, { relationName: 'order' })
}));

export const orderItemsRelations = relations(orderItems, ({ one }) => ({
  order: one(orders, {
    fields: [orderItems.orderId],
    references: [orders.id],
    relationName: 'items'
  }),
  product: one(products, {
    fields: [orderItems.productId],
    references: [products.id],
    relationName: 'items'
  })
}));

export const reviewsRelations = relations(reviews, ({ one }) => ({
  product: one(products, {
    fields: [reviews.productId],
    references: [products.id],
    relationName: 'reviews'
  }),
  customer: one(users, {
    fields: [reviews.customerId],
    references: [users.id],
    relationName: 'reviews'
  })
}));

export const notificationsRelations = relations(notifications, ({ one }) => ({
  user: one(users, {
    fields: [notifications.userId],
    references: [users.id],
    relationName: 'notifications'
  })
}));

// 3. Migration Functions

// Migration configuration
export const migrationConfig = {
  migrationsFolder: './migrations',
  database: DATABASE_URL
};

// Create initial migration
export async function createInitialMigration() {
  const migrationId = Date.now().toString();
  const migrationSql = `
    -- Create users table
    CREATE TABLE IF NOT EXISTS users (
      id SERIAL PRIMARY KEY,
      username VARCHAR(50) UNIQUE NOT NULL,
      email VARCHAR(255) UNIQUE NOT NULL,
      password_hash VARCHAR(255) NOT NULL,
      first_name VARCHAR(100) NOT NULL,
      last_name VARCHAR(100) NOT NULL,
      avatar_url VARCHAR(500),
      bio TEXT,
      website VARCHAR(255),
      location JSONB,
      preferences JSONB DEFAULT '{}',
      is_active BOOLEAN DEFAULT TRUE,
      is_verified BOOLEAN DEFAULT FALSE,
      last_login_at TIMESTAMP,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );

    -- Create indexes
    CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
    CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
    CREATE INDEX IF NOT EXISTS idx_users_active ON users(is_active);

    -- Create products table
    CREATE TABLE IF NOT EXISTS products (
      id SERIAL PRIMARY KEY,
      name VARCHAR(255) NOT NULL,
      slug VARCHAR(255) UNIQUE NOT NULL,
      description TEXT NOT NULL,
      short_description VARCHAR(500),
      price REAL NOT NULL,
      compare_at_price REAL,
      sku VARCHAR(100) UNIQUE NOT NULL,
      barcode VARCHAR(50) UNIQUE,
      track_inventory BOOLEAN DEFAULT TRUE,
      stock_quantity INTEGER DEFAULT 0,
      min_stock_level INTEGER DEFAULT 0,
      weight REAL,
      dimensions JSONB,
      images JSONB DEFAULT '[]',
      is_digital BOOLEAN DEFAULT FALSE,
      is_active BOOLEAN DEFAULT TRUE,
      featured BOOLEAN DEFAULT FALSE,
      rating REAL DEFAULT 0,
      review_count INTEGER DEFAULT 0,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );

    -- Create products indexes
    CREATE INDEX IF NOT EXISTS idx_products_sku ON products(sku);
    CREATE INDEX IF NOT EXISTS idx_products_active ON products(is_active);
    CREATE INDEX IF NOT EXISTS idx_products_featured ON products(featured);
    CREATE INDEX IF NOT EXISTS idx_products_rating ON products(rating);

    -- Create orders table
    CREATE TABLE IF NOT EXISTS orders (
      id SERIAL PRIMARY KEY,
      order_number VARCHAR(50) UNIQUE NOT NULL,
      customer_id INTEGER REFERENCES users(id) NOT NULL,
      status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'completed', 'failed', 'refunded')),
      subtotal REAL NOT NULL,
      tax REAL NOT NULL,
      shipping REAL NOT NULL,
      total REAL NOT NULL,
      currency VARCHAR(3) DEFAULT 'USD',
      shipping_address JSONB NOT NULL,
      billing_address JSONB NOT NULL,
      notes TEXT,
      shipped_at TIMESTAMP,
      delivered_at TIMESTAMP,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
      updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    );

    -- Create orders indexes
    CREATE INDEX IF NOT EXISTS idx_orders_customer ON orders(customer_id);
    CREATE INDEX IF NOT EXISTS idx_orders_status ON orders(status);
    CREATE INDEX IF NOT EXISTS idx_orders_created_at ON orders(created_at);

    -- Add foreign key constraint
    ALTER TABLE orders ADD CONSTRAINT fk_orders_customer
      FOREIGN KEY (customer_id) REFERENCES users(id) ON DELETE CASCADE;

    -- Insert migration record
    INSERT INTO drizzle_migrations (id, name, created_at)
    VALUES ('${migrationId}', 'initial_schema', CURRENT_TIMESTAMP);
  `;

  return migrationSql;
}

// 4. Advanced Query Examples with Relations

// Get user with all their orders and reviews
export async function getUserWithOrdersAndReviews(userId: number) {
  return db.query.users
    .findFirst({
      where: eq(users.id, userId),
      with: {
        orders: {
          orderBy: desc(orders.createdAt),
          with: {
            items: {
              with: {
                product: true
              }
            }
          }
        },
        reviews: {
          where: eq(reviews.isApproved, true),
          orderBy: [desc(reviews.createdAt), desc(reviews.id)],
          limit: 10
        }
      }
    });
}

// Get product with reviews and order statistics
export async function getProductWithStats(productId: number) {
  const product = await db.query.products
    .findFirst({
      where: eq(products.id, productId),
      with: {
        reviews: {
          where: eq(reviews.isApproved, true),
          orderBy: desc(reviews.createdAt),
          limit: 10
        },
        orderItems: {
          with: {
            order: {
              columns: {
                id: true,
                orderNumber: true,
                customerId: true,
                status: true
              }
            }
          }
        }
      }
    });

  if (!product) return null;

  // Calculate order statistics
  const [orderStats] = await db
    .select({
      totalOrders: count(orderItems.id),
      totalQuantity: sum(orderItems.quantity),
      totalRevenue: sum(orderItems.totalPrice)
    })
    .from(orderItems)
    .where(eq(orderItems.productId, productId));

  return {
    ...product,
    orderStats: orderStats || {
      totalOrders: 0,
      totalQuantity: 0,
      totalRevenue: 0
    }
  };
}

// Get orders with customer and product information
export async function getOrdersWithDetails() {
  return db.query.orders
    .findMany({
      with: {
        customer: true,
        items: {
          with: {
            product: {
              columns: {
                id: true,
                name: true,
                sku: true,
                price: true
              }
            }
          }
        }
      },
      orderBy: desc(orders.createdAt)
    });
}

// Get customer order history with statistics
export async function getCustomerOrderHistory(customerId: number) {
  const orders = await db.query.orders
    .findMany({
      where: eq(orders.customerId, customerId),
      with: {
        items: {
          with: {
            product: true
          }
        }
      },
      orderBy: desc(orders.createdAt)
    });

  // Calculate statistics
  const [stats] = await db
    .select({
      totalOrders: count(orders.id),
      totalSpent: sum(orders.total),
      orderCount: count(orders.id)
    })
    .from(orders)
    .where(eq(orders.customerId, customerId));

  return {
    orders,
    stats: stats || { totalOrders: 0, totalSpent: 0, orderCount: 0 }
  };
}

// 5. Complex Business Logic Operations

// Complete order processing
export async function processOrder(orderData: {
  customerId: number;
  items: Array<{ productId: number; quantity: number }>;
  shippingAddress: any;
  billingAddress: any;
}) {
  return db.transaction(async (tx) => {
    // Calculate totals
    let subtotal = 0;
    let items = [];

    for (const item of orderData.items) {
      // Check product availability
      const [product] = await tx
        .select()
        .from(products)
        .where(eq(products.id, item.productId))
        .limit(1);

      if (!product) {
        throw new Error(`Product ${item.productId} not found`);
      }

      if (product.trackInventory && product.stockQuantity < item.quantity) {
        throw new Error(`Insufficient stock for product ${product.name}. Available: ${product.stockQuantity}, Requested: ${item.quantity}`);
      }

      const unitPrice = product.price;
      const totalPrice = unitPrice * item.quantity;
      subtotal += totalPrice;

      // Store item details
      items.push({
        productId: product.id,
        productName: product.name,
        productSku: product.sku,
        quantity: item.quantity,
        unitPrice,
        totalPrice
      });

      // Update inventory if tracking
      if (product.trackInventory) {
        await tx
          .update(products)
          .set({
            stockQuantity: sql`${products.stockQuantity} - ${item.quantity}`,
            updatedAt: new Date()
          })
          .where(eq(products.id, item.productId));
      }
    }

    // Calculate tax and shipping (simplified)
    const tax = subtotal * 0.1; // 10% tax
    const shipping = items.length > 0 ? 5.99 : 0; // Flat shipping rate
    const total = subtotal + tax + shipping;

    // Generate order number
    const orderNumber = `ORD-${Date.now()}`;

    // Create order
    const [order] = await tx
      .insert(orders)
      .values({
        orderNumber,
        customerId: orderData.customerId,
        status: 'pending',
        subtotal,
        tax,
        shipping,
        total,
        shippingAddress: orderData.shippingAddress,
        billingAddress: orderData.billingAddress,
        currency: 'USD'
      })
      .returning();

    // Create order items
    await tx.insert(orderItems).values(
      items.map(item => ({
        orderId: order.id,
        ...item
      }))
    );

    // Create notification for customer
    await tx.insert(notifications).values({
      userId: orderData.customerId,
      type: 'success',
      title: 'Order Placed Successfully',
      message: `Your order ${orderNumber} has been placed successfully.`,
      data: {
        orderId: order.id,
        orderNumber,
        total
      }
    });

    return {
      order,
      items
    };
  });
}

// Bulk operations
export async function bulkUpdateProductPrices(updates: Array<{ productId: number; newPrice: number }>) {
  const result = await db.transaction(async (tx) => {
    const updatedProducts = await Promise.all(
      updates.map(async ({ productId, newPrice }) => {
        const [product] = await tx
          .update(products)
          .set({ price: newPrice, updatedAt: new Date() })
          .where(eq(products.id, productId))
          .returning();

        return product;
      })
    );

    return updatedProducts;
  });

  return result;
}

// 6. Data Analysis and Reporting

// Sales report by date range
export async function getSalesReport(startDate: Date, endDate: Date) {
  const sales = await db
    .select({
      date: sql`DATE(orders.created_at)`.as('date'),
      orderCount: count(orders.id),
      totalRevenue: sum(orders.total),
      averageOrderValue: sql`AVG(orders.total)`
    })
    .from(orders)
    .where(
      and(
        gte(orders.createdAt, startDate),
        lte(orders.createdAt, endDate)
      )
    )
    .groupBy(sql`DATE(orders.created_at)`)
    .orderBy(sql`DATE(orders.created_at)`);

  return sales;
}

// Top performing products
export async function getTopPerformingProducts(limit: number = 10) {
  return db
    .select({
      id: products.id,
      name: products.name,
      sku: products.sku,
      price: products.price,
      totalSold: sum(orderItems.quantity),
      totalRevenue: sum(orderItems.totalPrice),
      orderCount: count(sql`DISTINCT ${orderItems.orderId}`),
      averageRating: sql`COALESCE(
        (
          SELECT AVG(rating)
          FROM reviews
          WHERE reviews.productId = products.id
          AND reviews.isApproved = true
        ), 0
      )`,
      reviewCount: count(
        sql`${reviews.id}`
      )
    })
    .from(products)
    .leftJoin(orderItems, eq(orderItems.productId, products.id))
    .leftJoin(reviews, eq(reviews.productId, products.id))
    .groupBy(products.id)
    .orderBy(desc(sql`sum(${orderItems.totalPrice})`))
    .limit(limit);
}

// Customer analytics
export async function getCustomerAnalytics() {
  const analytics = await db
    .select({
      totalCustomers: count(users.id),
      activeCustomers: count(sql`CASE WHEN ${users.lastLoginAt} > CURRENT_DATE - INTERVAL '30 days' THEN 1 END`),
      verifiedCustomers: count(sql`${users.isVerified}`),
      registrationTrend: sql`COUNT(CASE WHEN created_at >= CURRENT_DATE - INTERVAL '30 days' THEN 1 END)`,
      topCountries: sql`json_agg(DISTINCT CASE
        WHEN ${users.location} IS NOT NULL
        THEN (${users.location}->>'country')
        ELSE 'Unknown'
      END) ORDER BY count(*) DESC LIMIT 5)`
    })
    .from(users);

  // Recent registrations
  const recentRegistrations = await db
    .select()
    .from(users)
    .orderBy(desc(users.createdAt))
    .limit(10);

  return {
    analytics: analytics[0],
    recentRegistrations
  };
}

// 7. Backup and Data Export

// Export user data for GDPR compliance
export async function exportUserData(userId: number) {
  const userData = await db.query.users
    .findFirst({
      where: eq(users.id, userId),
      with: {
        orders: {
          columns: {
            id: true,
            orderNumber: true,
            status: true,
            total: true,
            createdAt: true
          },
          with: {
            items: {
              columns: {
                productName: true,
                quantity: true,
                unitPrice: true,
                totalPrice: true
              }
            }
          }
        },
        reviews: {
          columns: {
            id: true,
            rating: true,
            title: true,
            content: true,
            createdAt: true
          }
        },
        notifications: {
          columns: {
            id: true,
            type: true,
            title: true,
            message: true,
            isRead: true,
            createdAt: true
          }
        }
      }
    });

  return userData;
}

// Inventory report
export async function getInventoryReport() {
  return db
    .select({
      id: products.id,
      name: products.name,
      sku: products.sku,
      price: products.price,
      stockQuantity: products.stockQuantity,
      minStockLevel: products.minStockLevel,
      trackInventory: products.trackInventory,
      reorderPoint: sql`${products.minStockLevel}`,
      stockStatus: sql`
        CASE
          WHEN ${products.trackInventory} = false THEN 'Not tracked'
          WHEN ${products.stockQuantity} <= ${products.minStockLevel} THEN 'Low stock'
          WHEN ${products.stockQuantity} = 0 THEN 'Out of stock'
          ELSE 'In stock'
        END
      `,
      lastUpdated: products.updatedAt,
      totalValue: sql`${products.price} * ${products.stockQuantity}`
    })
    .from(products)
    .orderBy(
      sql`CASE
        WHEN ${products.stockQuantity} <= ${products.minStockLevel} THEN 1
        ELSE 2
      END`,
      asc(sql`${products.name}`)
    .where(eq(products.isActive, true));
}

// 8. Search and Filtering

// Advanced product search with filters
export function searchProducts(filters: {
  query?: string;
  minPrice?: number;
  maxPrice?: number;
  category?: string[];
  minRating?: number;
  inStock?: boolean;
  sortBy?: 'name' | 'price' | 'rating' | 'created_at';
  sortOrder?: 'asc' | 'desc';
  limit?: number;
  page?: number;
}) {
  let query = db.select().from(products).where(eq(products.isActive, true));

  // Add text search
  if (filters.query) {
    query = query.where(
      or(
        ilike(products.name, `%${filters.query}%`),
        ilike(products.description, `%${filters.query}%`),
        ilike(products.shortDescription, `%${filters.query}%`)
      )
    );
  }

  // Add price range filter
  if (filters.minPrice !== undefined) {
    query = query.where(gte(products.price, filters.minPrice));
  }
  if (filters.maxPrice !== undefined) {
    query = query.where(lte(products.price, filters.maxPrice));
  }

  // Add stock filter
  if (filters.inStock !== undefined) {
    if (filters.inStock) {
      query = query.where(gt(products.stockQuantity, 0));
    } else {
      query = query.where(eq(products.stockQuantity, 0));
    }
  }

  // Add rating filter
  if (filters.minRating !== undefined) {
    query = query.where(gte(products.rating, filters.minRating));
  }

  // Add sorting
  const sortBy = filters.sortBy || 'created_at';
  const sortOrder = filters.sortOrder || 'desc';
  query = query.orderBy(
    sortOrder === 'asc' ? asc(sql`${sortBy}`) : desc(sql`${sortBy}`)
  );

  // Add pagination
  if (filters.limit) {
    query = query.limit(filters.limit);
    if (filters.page && filters.page > 1) {
      query = query.offset((filters.page - 1) * filters.limit);
    }
  }

  return query;
}

// 9. Type Definitions
import type { PgTable } from 'drizzle-orm/pg-core';
import { InferSelectModel } from 'drizzle-orm';

export type User = InferSelectModel<typeof users>;
export type Product = InferSelectModel<typeof products>;
export type Order = InferSelectModel<typeof orders>;
export type OrderItem = InferSelectModel<typeof orderItems>;
export <type T> New = Insertable<PgTable<T>>;

export {
  users,
  products,
  orders,
  orderItems,
  reviews,
  notifications,
  db,
  migrationConfig,
  getUserWithOrdersAndReviews,
  getProductWithStats,
  getOrdersWithDetails,
  getCustomerOrderHistory,
  processOrder,
  bulkUpdateProductPrices,
  getSalesReport,
  getTopPerformingProducts,
  getCustomerAnalytics,
  exportUserData,
  getInventoryReport,
  searchProducts
};

工具

经常与该案例搭配使用的工具

相关内容

相关案例集合

相关内容

相关专题

专题

药代动力学教学模型与结果核查

用于教学或 QA 的药代公式、模型假设和结果一致性核查,不提供用药剂量、诊断或临床建议。

专题

经典与现代密码工具

在安全的教学或 CTF 场景中比较可逆的经典变换与现代加密,并明确玩具密码不等于安全防护。

专题

文本前后缀与包裹符号批处理,整行整词一次到位

给文本行或词批量添加前后缀,为 SQL、配置和 Markdown 给每个值加引号或反引号包裹,并剥掉粘贴列表带来的编号、符号和装饰。

专题

Markdown 写作、整理与发布工作流

整理 Markdown 结构、检查链接、合并文件、预览效果、翻译文章,并导出可发布的 HTML、PDF、幻灯片、纯文本或 JSON 交付物。

首页
工具3212
案例593
可视化471
文件查看器103
专题239
标签333
图像、音频与视频657

图像、音频、视频处理、转换与优化工具

数学、日期与金融647

计算器、数值计算、日期逻辑、统计与金融工具

设计与颜色298

颜色、版式、图形、视觉样式与设计辅助工具

文本与写作209

写作、清洗、格式化、提取与文本分析工具

开发与 Web193

开发者工具、网络处理、Web 调试与自动化辅助工具

转换与编码175

格式、文件、压缩包、单位与编码转换工具

潜客测验发布与联系人交接
讣告、悼词与纪念卡的家属复核及交付
本地习惯记录的备份、恢复演练与分享前检查
长文改编 X 串文与 LinkedIn 帖子的事实复核与交付
Markdown 互动课件审校、离线演示与交付

© 2026 Elysia Tools. 保留所有权利。

文档价格关于我们服务条款隐私政策Cookie 政策联系我们

Format Conversion

CSV 转多方言 SQL INSERT 生成器

解析 CSV/Excel-CSV/TSV(自动方言检测,RFC 4180),生成 MySQL/PostgreSQL/SQLite/SQL Server 的批量 INSERT,带类型推断和 ON CONFLICT/IGNORE 变体

Development

数据库备份生成器

为数据库表生成SQL备份脚本

Development

数据库迁移生成器

为数据库架构更改生成SQL迁移脚本

Development

数据库 Schema 对比

比较两个数据库 Schema 并生成迁移草案

Development

SQL 方言互译桥

在 MySQL / PostgreSQL / SQLite / BigQuery / Snowflake / Redshift 之间互译 DDL、DML、函数与类型,并标注不支持的写法

Development

SQL 执行计划可视化器

粘贴 EXPLAIN / EXPLAIN ANALYZE 输出(PostgreSQL/MySQL/SQLite),渲染为成本树,标注估算行数与实际行数的偏差热点,并给出具体的索引建议

Development

SQL 查询格式化器 & 压缩器

美化或压缩 SQL,完全可控 SQL 方言(PostgreSQL、MySQL、SQLite、SQL Server/T-SQL、BigQuery、Snowflake 等)、关键字/标识符/函数大小写、缩进风格(标准、左对齐表格、右对齐表格)以及语句间空行。输出带语法高亮、可直接复制的结果,并显示大小与语句数统计。

Development

表结构生成器

生成SQL表结构定义

数据库

MySQL 数据库示例

各种复杂度级别的MySQL数据库脚本示例,从简单表到企业ERP系统

Objective-C

macOS Objective-C 数据库示例

macOS Objective-C 数据库示例,包括SQLite CRUD操作、查询、事务和连接管理

数据库

Prisma 示例

Prisma现代数据库工具包和ORM,包含模式定义、迁移、关系和类型安全的数据库访问

数据库

Sequelize 示例

Sequelize Node.js ORM,包含模型定义、关联、迁移、查询和数据库操作

数据格式

SQL 脚本示例

各种场景和用例的 SQL 查询示例和数据库脚本

数据库

SQLite 数据库示例

SQLite数据库示例,包括基本操作、高级查询、迁移和数据库管理