Drizzle Schema Design

Every Chef’s database schema demonstrates several patterns for building maintainable, type-safe schemas with Drizzle.

Enum Pattern for SQLite

SQLite doesn’t have native enums, so we use a const array pattern:

export const messageRoles = ["user", "assistant"] as const;
export type MessageRole = (typeof messageRoles)[number];

// Use in table definition
export const messages = sqliteTable("messages", {
  id: text("id").primaryKey(),
  role: text("role", { enum: messageRoles }).notNull(),
  // ...
});

This gives you:

• Type safety in TypeScript
• Runtime validation from Drizzle
• A union type you can use elsewhere

Normalized Data with Specialized Tables

Rather than storing everything in JSON columns, Every Chef normalizes data into separate tables. For example, messages can have different types of content (text, images, tool calls):

// Base parts table
export const messageParts = sqliteTable("message_parts", {
  id: text("id").primaryKey(),
  messageId: text("message_id").references(() => messages.id, {
    onDelete: "cascade",
  }),
  type: text("type", { enum: messagePartTypes }).notNull(),
  order: text("order").notNull(),
});

// Specialized tables for each type
export const textMessageParts = sqliteTable("text_message_parts", {
  partId: text("part_id")
    .primaryKey()
    .references(() => messageParts.id, { onDelete: "cascade" }),
  text: text("text").notNull(),
});

export const toolInvocationMessageParts = sqliteTable(
  "tool_invocation_message_parts",
  {
    partId: text("part_id")
      .primaryKey()
      .references(() => messageParts.id, { onDelete: "cascade" }),
    toolCallId: text("tool_call_id").notNull(),
    toolName: text("tool_name").notNull(),
    args: text("args").notNull(), // JSON stringified
    result: text("result"), // JSON stringified, nullable
  },
);

This approach:

• Avoids nullable columns that only apply to certain types
• Makes migrations cleaner
• Enforces data integrity at the database level

Referential Integrity

Use cascading deletes to keep data consistent:

// When a message is deleted, delete all its parts
messageId: text("message_id")
  .references(() => messages.id, { onDelete: "cascade" }),

// When a chat is deleted, set recipe's chatId to null
chatId: text("chat_id")
  .references(() => chats.id, { onDelete: "set null" }),

Junction Tables with Unique Constraints

For many-to-many relationships, use junction tables with unique indexes:

export const chatActiveRecipes = sqliteTable(
  "chat_active_recipes",
  {
    id: text("id").primaryKey(),
    chatId: text("chat_id")
      .notNull()
      .references(() => chats.id, { onDelete: "cascade" }),
    recipeId: text("recipe_id")
      .notNull()
      .references(() => recipes.id, { onDelete: "cascade" }),
    addedAt: text("added_at").notNull(),
  },
  (table) => [
    uniqueIndex("chat_active_recipes_unique_idx").on(
      table.chatId,
      table.recipeId,
    ),
  ],
);

The unique index prevents duplicate associations.

Drizzle Relations

Define relations for type-safe queries with nested data:

export const messagePartsRelations = relations(messageParts, ({ one }) => ({
  message: one(messages, {
    fields: [messageParts.messageId],
    references: [messages.id],
  }),
  textPart: one(textMessageParts, {
    fields: [messageParts.id],
    references: [textMessageParts.partId],
  }),
  toolInvocationPart: one(toolInvocationMessageParts, {
    fields: [messageParts.id],
    references: [toolInvocationMessageParts.partId],
  }),
}));

Type Inference

Export inferred types for use throughout the app:

// Basic types
export type Recipe = typeof recipes.$inferSelect;
export type NewRecipe = typeof recipes.$inferInsert;

// Composite types for complex queries
export type ChatWithActiveRecipes = Chat & {
  activeRecipes: (ChatActiveRecipe & { recipe: Recipe })[];
};

These types are automatically kept in sync with your schema.