Source: data_layer/docs/README_DRIZZLE_FIRST.md

Drizzle-First Schema System

🎯 Quick Summary

What: A complete code generation pipeline where Drizzle TypeScript schemas are the single source of truth.

Why: Edit schemas once in TypeScript, automatically generate everything else (JSON Schema, Pydantic, SQLAlchemy, SQL, GraphQL, Neo4j Cypher).

How: Run python scripts/generate_from_drizzle.py after editing Drizzle schemas.

🚀 Quick Start (30 seconds)

# 1. Look at the example
cat schemas/domain/drizzle/v1/examples/league_example.schema.ts
 
# 2. Generate everything
python scripts/generate_from_drizzle.py
 
# 3. Check the output
ls -R schemas/generated/from_drizzle/

📚 Documentation

Document	Purpose
DRIZZLE_FIRST_ARCHITECTURE.md	Complete architecture guide with best practices
MIGRATION_TO_DRIZZLE_FIRST.md	Step-by-step migration guide from JSON Schema
Example Schema	Comprehensive example showing all features

🏗️ Architecture

📝 Write Here (Drizzle TypeScript)
    ↓
┌───┴──────────────────────────────────────┐
│  python scripts/generate_from_drizzle.py │
└───┬──────────────────────────────────────┘
    ↓
    ├──> schemas/generated/from_drizzle/json/           (JSON Schema)
    ├──> schemas/generated/from_drizzle/pydantic/       (Pydantic Models)
    ├──> schemas/generated/from_drizzle/sqlalchemy/     (SQLAlchemy Models)
    ├──> schemas/generated/from_drizzle/sql/            (SQL DDL)
    ├──> schemas/generated/from_drizzle/graphql/        (GraphQL SDL)
    └──> schemas/generated/from_drizzle/neo4j/          (Neo4j Cypher)

💡 Key Benefits

✅ Single Source of Truth

Edit once in TypeScript
Generate everything automatically
No more manual synchronization

✅ Type Safety

TypeScript gives immediate feedback
IDE autocomplete for schema definitions
Compile-time error checking

✅ Comprehensive Generation

JSON Schema - API documentation, validation
Pydantic - Python FastAPI validation
SQLAlchemy - Python ORM queries
SQL - Database migrations
GraphQL - GraphQL API schemas
Neo4j - Graph database constraints

✅ Best DX (Developer Experience)

Beautiful TypeScript syntax
Clear error messages
Fast iteration
Example-driven

📋 What Gets Generated

1. JSON Schema

Use for: API documentation, OpenAPI specs, validation

{
  "$schema": "https://json-schema.org/draft/2020-12/schema",
  "title": "users",
  "properties": {
    "id": { "type": "string", "format": "uuid" },
    "email": { "type": "string", "maxLength": 255 }
  },
  "required": ["email"]
}

2. Pydantic Models

Use for: FastAPI, Python validation, type hints

class Users(BaseModel):
    id: Optional[UUID] = None
    email: str
    created_at: datetime
 
class UsersCreate(BaseModel):
    """For POST requests (excludes auto-generated fields)"""
    email: str
 
class UsersUpdate(BaseModel):
    """For PUT/PATCH requests (all optional)"""
    email: Optional[str] = None

3. SQLAlchemy Models

Use for: Python ORM queries, complex relationships

class Users(Base):
    __tablename__ = "users"
    id = Column(UUID, primary_key=True)
    email = Column(String(255), nullable=False)
    created_at = Column(DateTime, default=datetime.utcnow)

4. SQL DDL

Use for: Database migrations, schema initialization

CREATE TABLE IF NOT EXISTS users (
  id UUID PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE NOT NULL
);
 
CREATE INDEX users_email_idx ON users(email);

5. GraphQL SDL

Use for: GraphQL APIs, frontend type generation

type Users {
  id: UUID!
  email: String!
  createdAt: DateTime!
}
 
type Query {
  users(id: ID!): Users
  usersList(limit: Int): [Users!]!
}

6. Neo4j Cypher

Use for: Graph database constraints, indexes

CREATE CONSTRAINT users_id_unique IF NOT EXISTS
FOR (n:Users) REQUIRE n.id IS UNIQUE;
 
CREATE INDEX users_email_index IF NOT EXISTS
FOR (n:Users) ON (n.email);

🛠️ Usage

Define Your Schema

Create schemas/domain/drizzle/v1/my_feature/users.schema.ts:

import { pgTable, uuid, varchar, timestamp } from "drizzle-orm/pg-core";
 
/**
 * Users in the system
 */
export const users = pgTable("users", {
  id: uuid("id").primaryKey().defaultRandom(),
  email: varchar("email", { length: 255 }).notNull().unique(),
  name: varchar("name", { length: 100 }).notNull(),
  created_at: timestamp("created_at").defaultNow().notNull(),
  updated_at: timestamp("updated_at").defaultNow().notNull(),
});

Generate Everything

# Generate all targets
python scripts/generate_from_drizzle.py
 
# Generate specific target
python scripts/generate_from_drizzle.py --target pydantic
 
# Process specific schema
python scripts/generate_from_drizzle.py --schema schemas/domain/drizzle/v1/my_feature/users.schema.ts

Use Generated Code

Python (FastAPI):

from fastapi import FastAPI
from schemas.generated.from_drizzle.pydantic.users_model import Users, UsersCreate
 
app = FastAPI()
 
@app.post("/users", response_model=Users)
async def create_user(user: UsersCreate):
    # Automatic validation!
    return await db.insert_user(user)

SQL (Migrations):

psql -d mydatabase -f schemas/generated/from_drizzle/sql/schema.sql

GraphQL (API):

query GetUser {
  users(id: "123") {
    email
    name
  }
}

🎓 Best Practices

Always Include These Fields

export const my_table = pgTable("my_table", {
  // ✅ UUID primary key with auto-generation
  id: uuid("id").primaryKey().defaultRandom(),
  
  // ... your fields ...
  
  // ✅ Timestamps for auditing
  created_at: timestamp("created_at").defaultNow().notNull(),
  updated_at: timestamp("updated_at").defaultNow().notNull(),
});

Define Foreign Keys Properly

export const documents = pgTable("documents", {
  id: uuid("id").primaryKey().defaultRandom(),
  
  // ✅ Foreign key with cascade delete
  league_id: uuid("league_id")
    .notNull()
    .references(() => leagues.id, { onDelete: 'cascade' }),
}, (table) => ({
  // ✅ Index on foreign key
  leagueIdx: index("documents_league_idx").on(table.league_id),
}));

Use JSONB for Flexible Data

export const leagues = pgTable("leagues", {
  // ... other fields ...
  
  // ✅ JSONB with default value
  metadata: jsonb("metadata").default({}),
  tags: jsonb("tags").default([]),
});

Add JSDoc Comments

/**
 * Leagues represent sports organizations and competitions
 */
export const leagues = pgTable("leagues", {
  // ... columns ...
});

📊 Comparison

Approach	Pros	Cons
JSON Schema First	Language-agnostic, widely supported	Manual sync, verbose, no type safety
Prisma First	Great DX, migrations	Prisma-specific, limited to Prisma
Drizzle First (✅)	Type-safe, generates everything, best DX	Requires TypeScript knowledge

🔧 Advanced

Custom Generators

Add your own generator in scripts/generate_from_drizzle.py:

class MyCustomGenerator:
    def generate(self, tables: List[Table], output_dir: Path) -> None:
        for table in tables:
            # Your custom logic
            pass

Selective Generation

# Only SQL
python scripts/generate_from_drizzle.py --target sql
 
# Only specific schema
python scripts/generate_from_drizzle.py --schema schemas/domain/drizzle/v1/combat/**.ts

🐛 Troubleshooting

Problem	Solution
Columns not parsing	Check Drizzle syntax: `varchar("name", { length: 100 })`
Foreign keys failing	Use arrow function: `references(() => table.id)`
Types are wrong	Check column type: `timestamp()` not `date()`
SQL migration fails	Check for circular dependencies

📚 Resources

📖 Full Architecture Guide - Comprehensive documentation
🚀 Migration Guide - Step-by-step migration from JSON Schema
📝 Example Schema - Working example with all features
🔧 Generation Script - The actual generator
🌐 Drizzle ORM Docs (opens in a new tab) - Official Drizzle documentation

🎯 Next Steps

Learn: Read DRIZZLE_FIRST_ARCHITECTURE.md
Explore: Check out the example
Try: Generate from the example
Create: Make your first Drizzle schema
Migrate: Follow MIGRATION_TO_DRIZZLE_FIRST.md

✅ Checklist

Questions? Check the Architecture Guide or Migration Guide.

Ready to start? Run: python scripts/generate_from_drizzle.py

Drizzle-First Code Generation Architecture DUPLICATION AUDIT REPORT