or run

npx @tessl/cli init
Log in

Version

Tile

Overview

Evals

Files

docs

examples

edge-cases.mdreal-world-scenarios.md

guides

quick-start.md

reference

advanced-features.mddrivers.mdmysql.mdpostgresql.mdquery-building.mdrelations.mdschema-definition.mdsqlite.md
index.md
tile.json

real-world-scenarios.mddocs/examples/

Real-World Scenarios

Comprehensive examples demonstrating practical usage patterns for Drizzle ORM.

Blog Application

Complete blog application with users, posts, comments, and tags.

Schema

import { pgTable, serial, text, integer, boolean, timestamp, relations, primaryKey } from 'drizzle-orm/pg-core';

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

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

export const comments = pgTable('comments', {
  id: serial('id').primaryKey(),
  content: text('content').notNull(),
  postId: integer('post_id').notNull(),
  authorId: integer('author_id').notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

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

export const postsToTags = pgTable('posts_to_tags', {
  postId: integer('post_id').notNull().references(() => posts.id),
  tagId: integer('tag_id').notNull().references(() => tags.id),
}, (t) => ({
  pk: primaryKey({ columns: [t.postId, t.tagId] }),
}));

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

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

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

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

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

Queries

import { eq, and, desc, sql } from 'drizzle-orm';

// Get blog post with all related data
async function getBlogPost(slug: string) {
  return db.query.posts.findFirst({
    where: and(
      eq(posts.slug, slug),
      eq(posts.published, true)
    ),
    with: {
      author: {
        columns: {
          id: true,
          name: true,
          bio: true,
        },
      },
      comments: {
        orderBy: desc(comments.createdAt),
        with: {
          author: {
            columns: {
              id: true,
              name: true,
            },
          },
        },
      },
      postsToTags: {
        with: {
          tag: true,
        },
      },
    },
    extras: {
      readingTime: sql<number>`CEIL(LENGTH(${posts.content}) / 200.0)`.as('reading_time'),
    },
  });
}

// Get author's published posts
async function getAuthorPosts(authorId: number) {
  return db.query.users.findFirst({
    where: eq(users.id, authorId),
    with: {
      posts: {
        where: eq(posts.published, true),
        orderBy: desc(posts.createdAt),
        columns: {
          id: true,
          title: true,
          slug: true,
          createdAt: true,
        },
        extras: {
          commentCount: sql<number>`(
            SELECT COUNT(*)
            FROM ${comments}
            WHERE ${comments.postId} = ${posts.id}
          )`.as('comment_count'),
        },
      },
    },
  });
}

// Get recent posts with pagination
async function getRecentPosts(page: number, pageSize: number) {
  return db.query.posts.findMany({
    where: eq(posts.published, true),
    orderBy: desc(posts.createdAt),
    limit: pageSize,
    offset: (page - 1) * pageSize,
    with: {
      author: {
        columns: {
          id: true,
          name: true,
        },
      },
      postsToTags: {
        with: {
          tag: true,
        },
      },
    },
    extras: {
      excerpt: sql<string>`SUBSTRING(${posts.content}, 1, 200)`.as('excerpt'),
    },
  });
}

// Get posts by tag
async function getPostsByTag(tagName: string) {
  const tag = await db.query.tags.findFirst({
    where: eq(tags.name, tagName),
    with: {
      postsToTags: {
        with: {
          post: {
            where: eq(posts.published, true),
            with: {
              author: {
                columns: {
                  id: true,
                  name: true,
                },
              },
            },
          },
        },
      },
    },
  });

  return tag?.postsToTags.map(ptt => ptt.post) ?? [];
}

E-Commerce Application

Complete e-commerce system with customers, orders, and products.

Schema

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

export const orders = pgTable('orders', {
  id: serial('id').primaryKey(),
  customerId: integer('customer_id').notNull(),
  status: text('status').notNull(),
  total: numeric('total', { precision: 10, scale: 2 }).notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

export const orderItems = pgTable('order_items', {
  id: serial('id').primaryKey(),
  orderId: integer('order_id').notNull(),
  productId: integer('product_id').notNull(),
  quantity: integer('quantity').notNull(),
  price: numeric('price', { precision: 10, scale: 2 }).notNull(),
});

export const products = pgTable('products', {
  id: serial('id').primaryKey(),
  name: text('name').notNull(),
  description: text('description'),
  price: numeric('price', { precision: 10, scale: 2 }).notNull(),
  stock: integer('stock').notNull(),
});

Queries

// Get order with all details
async function getOrderDetails(orderId: number) {
  return db.query.orders.findFirst({
    where: eq(orders.id, orderId),
    with: {
      customer: {
        columns: {
          id: true,
          name: true,
          email: true,
        },
      },
      items: {
        with: {
          product: true,
        },
      },
    },
  });
}

// Get customer order history
async function getCustomerOrders(customerId: number) {
  return db.query.customers.findFirst({
    where: eq(customers.id, customerId),
    with: {
      orders: {
        orderBy: desc(orders.createdAt),
        with: {
          items: {
            with: {
              product: {
                columns: {
                  id: true,
                  name: true,
                },
              },
            },
          },
        },
      },
    },
  });
}

// Get popular products
async function getPopularProducts(limit: number) {
  return db.query.products.findMany({
    limit,
    extras: {
      totalSold: sql<number>`(
        SELECT COALESCE(SUM(${orderItems.quantity}), 0)
        FROM ${orderItems}
        WHERE ${orderItems.productId} = ${products.id}
      )`.as('total_sold'),
    },
    orderBy: (fields) => desc(fields.totalSold),
  });
}

Authentication System

User authentication with sessions and roles.

export const users = pgTable('users', {
  id: serial('id').primaryKey(),
  email: text('email').notNull().unique(),
  passwordHash: text('password_hash').notNull(),
  emailVerified: boolean('email_verified').default(false).notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

export const sessions = pgTable('sessions', {
  id: text('id').primaryKey(),
  userId: integer('user_id').notNull().references(() => users.id, { onDelete: 'cascade' }),
  expiresAt: timestamp('expires_at').notNull(),
  createdAt: timestamp('created_at').defaultNow().notNull(),
});

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

export const userRoles = pgTable('user_roles', {
  userId: integer('user_id').notNull().references(() => users.id),
  roleId: integer('role_id').notNull().references(() => roles.id),
  assignedAt: timestamp('assigned_at').defaultNow().notNull(),
}, (t) => ({
  pk: primaryKey({ columns: [t.userId, t.roleId] }),
}));

Analytics Dashboard

Aggregating data for analytics.

import { count, sum, avg, max, min, sql } from 'drizzle-orm';

// User statistics
async function getUserStats() {
  return db.select({
    totalUsers: count(),
    verifiedUsers: count(users.emailVerified),
    newUsersToday: sql<number>`COUNT(*) FILTER (WHERE ${users.createdAt} > CURRENT_DATE)`,
  }).from(users);
}

// Post analytics
async function getPostAnalytics() {
  return db.select({
    authorId: posts.authorId,
    totalPosts: count(posts.id),
    avgLength: avg(sql`LENGTH(${posts.content})`),
    latestPost: max(posts.createdAt),
  })
  .from(posts)
  .groupBy(posts.authorId)
  .having(gt(count(posts.id), 5));
}