Database Patterns

Vinoflare templates use Cloudflare D1 (SQLite) with Drizzle ORM for type-safe database operations. This guide covers common patterns and best practices.

Database Setup

Local Development

D1 databases are automatically created for local development:

# Initialize local database
npm run db:push:local
 
# This runs:
wrangler d1 execute DB --local --file=./drizzle/0001_init.sql

Production Database

Create a production D1 database:

# Create database
wrangler d1 create my-app-db
 
# Add to wrangler.toml
[[d1_databases]]
binding = "DB"
database_name = "my-app-db"
database_id = "your-database-id"
 
# Apply migrations
npm run db:push:prod

Schema Design

Basic Table Structure

// src/server/db/schema.ts
import { sqliteTable, text, integer, real } from "drizzle-orm/sqlite-core";
import { sql } from "drizzle-orm";
 
export const posts = sqliteTable("posts", {
  // Primary key
  id: text("id").primaryKey().$defaultFn(() => crypto.randomUUID()),
  
  // Basic fields
  title: text("title").notNull(),
  content: text("content").notNull(),
  slug: text("slug").unique().notNull(),
  
  // Relationships
  authorId: text("author_id").notNull(),
  categoryId: text("category_id"),
  
  // Metadata
  published: integer("published", { mode: "boolean" }).default(false),
  viewCount: integer("view_count").default(0),
  
  // Timestamps
  createdAt: integer("created_at", { mode: "timestamp" })
    .$defaultFn(() => new Date()),
  updatedAt: integer("updated_at", { mode: "timestamp" })
    .$defaultFn(() => new Date())
    .$onUpdateFn(() => new Date()),
});
 
// Type inference
export type Post = typeof posts.$inferSelect;
export type NewPost = typeof posts.$inferInsert;

Indexes

import { index } from "drizzle-orm/sqlite-core";
 
export const posts = sqliteTable("posts", {
  // ... columns
}, (table) => ({
  // Single column index
  authorIdx: index("author_idx").on(table.authorId),
  
  // Composite index
  publishedIdx: index("published_idx").on(table.published, table.createdAt),
  
  // Unique index
  slugIdx: index("slug_idx").on(table.slug).unique(),
}));

Relationships

// One-to-Many
export const users = sqliteTable("users", {
  id: text("id").primaryKey(),
  name: text("name").notNull(),
});
 
export const posts = sqliteTable("posts", {
  id: text("id").primaryKey(),
  title: text("title").notNull(),
  authorId: text("author_id").notNull()
    .references(() => users.id, { onDelete: "cascade" }),
});
 
// Many-to-Many
export const tags = sqliteTable("tags", {
  id: text("id").primaryKey(),
  name: text("name").notNull().unique(),
});
 
export const postTags = sqliteTable("post_tags", {
  postId: text("post_id").notNull()
    .references(() => posts.id, { onDelete: "cascade" }),
  tagId: text("tag_id").notNull()
    .references(() => tags.id, { onDelete: "cascade" }),
}, (table) => ({
  pk: primaryKey({ columns: [table.postId, table.tagId] }),
}));

Query Patterns

Basic CRUD Operations

// src/server/modules/posts/posts.handlers.ts
import { db } from "@/server/db";
import { posts } from "@/server/db/schema";
import { eq, desc, and, like } from "drizzle-orm";
 
// Create
export async function createPost(data: NewPost) {
  const [post] = await db.insert(posts).values(data).returning();
  return post;
}
 
// Read single
export async function getPost(id: string) {
  const post = await db.select().from(posts).where(eq(posts.id, id)).get();
  return post;
}
 
// Read many with filters
export async function getPosts(filters: {
  published?: boolean;
  authorId?: string;
  search?: string;
}) {
  const conditions = [];
  
  if (filters.published !== undefined) {
    conditions.push(eq(posts.published, filters.published));
  }
  
  if (filters.authorId) {
    conditions.push(eq(posts.authorId, filters.authorId));
  }
  
  if (filters.search) {
    conditions.push(like(posts.title, `%${filters.search}%`));
  }
  
  return db
    .select()
    .from(posts)
    .where(conditions.length > 0 ? and(...conditions) : undefined)
    .orderBy(desc(posts.createdAt));
}
 
// Update
export async function updatePost(id: string, data: Partial<NewPost>) {
  const [updated] = await db
    .update(posts)
    .set(data)
    .where(eq(posts.id, id))
    .returning();
  return updated;
}
 
// Delete
export async function deletePost(id: string) {
  await db.delete(posts).where(eq(posts.id, id));
}

Joins and Relations

// Simple join
export async function getPostsWithAuthors() {
  return db
    .select({
      post: posts,
      author: users,
    })
    .from(posts)
    .leftJoin(users, eq(posts.authorId, users.id));
}
 
// Multiple joins
export async function getPostDetails(postId: string) {
  const result = await db
    .select({
      post: posts,
      author: users,
      tags: sql<string[]>`
        GROUP_CONCAT(${tags.name}) as tags
      `,
    })
    .from(posts)
    .leftJoin(users, eq(posts.authorId, users.id))
    .leftJoin(postTags, eq(posts.id, postTags.postId))
    .leftJoin(tags, eq(postTags.tagId, tags.id))
    .where(eq(posts.id, postId))
    .groupBy(posts.id)
    .get();
    
  return result ? {
    ...result.post,
    author: result.author,
    tags: result.tags?.split(",") || [],
  } : null;
}

Pagination

export async function getPaginatedPosts(options: {
  page: number;
  limit: number;
  orderBy?: "newest" | "popular";
}) {
  const { page, limit, orderBy = "newest" } = options;
  const offset = (page - 1) * limit;
  
  // Get total count
  const [{ count }] = await db
    .select({ count: sql<number>`COUNT(*)` })
    .from(posts)
    .where(eq(posts.published, true));
  
  // Get paginated data
  const items = await db
    .select()
    .from(posts)
    .where(eq(posts.published, true))
    .orderBy(
      orderBy === "popular" 
        ? desc(posts.viewCount)
        : desc(posts.createdAt)
    )
    .limit(limit)
    .offset(offset);
  
  return {
    items,
    pagination: {
      page,
      limit,
      total: count,
      totalPages: Math.ceil(count / limit),
    },
  };
}

Aggregations

// Count by category
export async function getPostCountByCategory() {
  return db
    .select({
      categoryId: posts.categoryId,
      count: sql<number>`COUNT(*)`,
    })
    .from(posts)
    .where(eq(posts.published, true))
    .groupBy(posts.categoryId);
}
 
// Statistics
export async function getUserStats(userId: string) {
  const stats = await db
    .select({
      totalPosts: sql<number>`COUNT(*)`,
      publishedPosts: sql<number>`
        SUM(CASE WHEN ${posts.published} = 1 THEN 1 ELSE 0 END)
      `,
      totalViews: sql<number>`COALESCE(SUM(${posts.viewCount}), 0)`,
      avgViews: sql<number>`COALESCE(AVG(${posts.viewCount}), 0)`,
    })
    .from(posts)
    .where(eq(posts.authorId, userId))
    .get();
    
  return stats || {
    totalPosts: 0,
    publishedPosts: 0,
    totalViews: 0,
    avgViews: 0,
  };
}

Transactions

export async function publishPostWithNotification(postId: string) {
  return db.transaction(async (tx) => {
    // Update post
    const [post] = await tx
      .update(posts)
      .set({ 
        published: true,
        publishedAt: new Date(),
      })
      .where(eq(posts.id, postId))
      .returning();
    
    if (!post) {
      tx.rollback();
      throw new Error("Post not found");
    }
    
    // Create notification
    await tx.insert(notifications).values({
      userId: post.authorId,
      type: "post_published",
      message: `Your post "${post.title}" has been published!`,
      metadata: { postId },
    });
    
    // Update user stats
    await tx
      .update(userStats)
      .set({ 
        publishedPosts: sql`${userStats.publishedPosts} + 1`,
      })
      .where(eq(userStats.userId, post.authorId));
    
    return post;
  });
}

Migrations

Create Migration

# After schema changes
npm run db:generate
 
# This creates: drizzle/0002_add_column.sql

Migration Files

-- drizzle/0002_add_tags.sql
CREATE TABLE tags (
  id TEXT PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  slug TEXT NOT NULL UNIQUE,
  created_at INTEGER DEFAULT (unixepoch()) NOT NULL
);
 
CREATE TABLE post_tags (
  post_id TEXT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  tag_id TEXT NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);
 
CREATE INDEX idx_post_tags_post ON post_tags(post_id);
CREATE INDEX idx_post_tags_tag ON post_tags(tag_id);

Apply Migrations

# Local
npm run db:push:local
 
# Production
npm run db:push:prod

Performance Optimization

1. Use Indexes Wisely

// Good: Index frequently queried columns
export const posts = sqliteTable("posts", {
  // columns...
}, (table) => ({
  // Index for common queries
  publishedAuthorIdx: index("published_author_idx")
    .on(table.published, table.authorId),
  
  // Index for sorting
  createdAtIdx: index("created_at_idx").on(table.createdAt),
}));

2. Select Only Needed Columns

// Bad: Select everything
const posts = await db.select().from(posts);
 
// Good: Select specific columns
const postSummaries = await db
  .select({
    id: posts.id,
    title: posts.title,
    excerpt: sql<string>`SUBSTR(${posts.content}, 1, 200)`,
    authorName: users.name,
  })
  .from(posts)
  .leftJoin(users, eq(posts.authorId, users.id));

3. Batch Operations

// Bad: Multiple individual inserts
for (const tag of tags) {
  await db.insert(tags).values(tag);
}
 
// Good: Batch insert
await db.insert(tags).values(tags);
 
// Batch update with CASE
await db.run(sql`
  UPDATE posts
  SET view_count = CASE id
    ${sql.join(
      updates.map(u => sql`WHEN ${u.id} THEN ${u.views}`),
      sql` `
    )}
  END
  WHERE id IN ${sql`(${sql.join(updates.map(u => u.id), sql`, `)})`}
`);

4. Use Prepared Statements

// Prepare statement for reuse
const getPostBySlug = db
  .select()
  .from(posts)
  .where(eq(posts.slug, sql.placeholder("slug")))
  .prepare();
 
// Use multiple times
const post1 = await getPostBySlug.execute({ slug: "first-post" });
const post2 = await getPostBySlug.execute({ slug: "second-post" });

Database Utilities

Soft Deletes

// Schema with soft delete
export const posts = sqliteTable("posts", {
  // ... other columns
  deletedAt: integer("deleted_at", { mode: "timestamp" }),
});
 
// Soft delete function
export async function softDeletePost(id: string) {
  await db
    .update(posts)
    .set({ deletedAt: new Date() })
    .where(eq(posts.id, id));
}
 
// Query only non-deleted
export async function getActivePosts() {
  return db
    .select()
    .from(posts)
    .where(isNull(posts.deletedAt));
}

Audit Trail

// Audit log table
export const auditLogs = sqliteTable("audit_logs", {
  id: text("id").primaryKey(),
  tableName: text("table_name").notNull(),
  recordId: text("record_id").notNull(),
  action: text("action", { enum: ["create", "update", "delete"] }).notNull(),
  userId: text("user_id").notNull(),
  changes: text("changes", { mode: "json" }),
  createdAt: integer("created_at", { mode: "timestamp" }),
});
 
// Log changes
export async function updatePostWithAudit(
  id: string, 
  data: Partial<NewPost>,
  userId: string
) {
  const oldPost = await getPost(id);
  const newPost = await updatePost(id, data);
  
  await db.insert(auditLogs).values({
    tableName: "posts",
    recordId: id,
    action: "update",
    userId,
    changes: {
      before: oldPost,
      after: newPost,
    },
  });
  
  return newPost;
}

Testing Database Code

// src/server/tests/db-test-utils.ts
import { db } from "@/server/db";
import { sql } from "drizzle-orm";
 
export async function resetDatabase() {
  // Get all tables
  const tables = await db
    .select({ name: sql<string>`name` })
    .from(sql`sqlite_master`)
    .where(sql`type = 'table' AND name NOT LIKE 'sqlite_%'`);
  
  // Delete all data
  for (const { name } of tables) {
    await db.run(sql`DELETE FROM ${sql.identifier(name)}`);
  }
}
 
export async function seedDatabase() {
  // Create test users
  await db.insert(users).values([
    { id: "user1", name: "Test User 1", email: "test1@example.com" },
    { id: "user2", name: "Test User 2", email: "test2@example.com" },
  ]);
  
  // Create test posts
  await db.insert(posts).values([
    { 
      id: "post1",
      title: "Test Post 1",
      content: "Content 1",
      authorId: "user1",
    },
  ]);
}