RFC: Auto-schema push in development mode (like PayloadCMS)

[AliiiBenn]

Summary

This issue documents how PayloadCMS handles automatic schema creation in development mode and proposes implementing a similar feature for @deessejs/collections.

---

How PayloadCMS Handles Schema

1. Automatic Schema Push (Development Mode)

In PayloadCMS, when you start the server in development mode, the database tables are automatically created/updated. This happens in packages/db-postgres/src/connect.ts:

// Only push schema if not in production
if (
  process.env.NODE_ENV !== 'production' &&
  process.env.PAYLOAD_MIGRATING !== 'true' &&
  this.push !== false
) {
  await pushDevSchema(this as unknown as DrizzleAdapter)
}

The pushDevSchema function:

1. Compares current schema with previous schema
2. If no changes, skips the push
3. Uses drizzle-kit's pushSchema API to push changes
4. Shows warnings if there's risk of data loss
5. Prompts for confirmation before applying

2. Generated Types File (payload-types.ts)

PayloadCMS generates a payload-types.ts file containing TypeScript interfaces for all collections and globals.

Why is it needed?

• TypeScript cannot infer types from runtime config
• The file is generated via: payload generate:types
• It provides full type inference and IntelliSense

Example output:

export interface Config {
  collections: {
    posts: Post;
    users: User;
  };
  globals: {
    global: Global;
  };
}

export interface Post {
  id: number;
  title?: string | null;
  content?: ...;
}

---

Current State of @deessejs/collections

Current Behavior

• Schema must be pushed manually: npx collections db:push
• Types are inferred directly from TypeScript config
• No automatic schema push on server start

Is a generated types file needed?

No, because:

• We use TypeScript with direct type inference from config
• Types are already available through the API
• The user defines collections with proper typing

---

Proposal: Auto-Schema Push in Development

We could implement automatic schema push similar to PayloadCMS by:

1. Option A: Auto-push on server start (Next.js dev server)

   • Modify the Next.js plugin (withCollections) to push schema on startup
   • Check if tables exist before pushing

2. Option B: Keep manual CLI (current approach)

   • User runs npx collections db:push after config changes
   • Simpler, more explicit

3. Option C: Add a flag to enable auto-push

   • withCollections({ autoSchemaPush: true })
   • Only pushes in development mode

---

Discussion

What's the preferred approach? Should we implement automatic schema push or keep the manual CLI approach?

[AliiiBenn]

Deep Analysis: PayloadCMS Architecture Flow

This document details how PayloadCMS handles the flow from collections definition → config → Drizzle schema → database operations.

---

1. Collection Definition (User Level)

File: examples/auth/src/collections/Users.ts

export const Users: CollectionConfig = {
  slug: 'users',
  auth: { ... },
  admin: { useAsTitle: 'email' },
  access: {
    read: adminsAndUser,
    create: anyone,
    update: adminsAndUser,
    delete: admins,
  },
  fields: [
    { name: 'email', type: 'email', required: true, unique: true },
    { name: 'password', type: 'password', required: true },
    // ... more fields
  ],
}

Each collection is defined as a typed object with:

• slug: unique identifier
• fields: array of field definitions (text, email, relationship, etc.)
• access: access control rules
• hooks: lifecycle hooks
• admin: admin UI config

---

2. Config Building & Sanitization

File: packages/payload/src/config/build.ts

export async function buildConfig(config: Config): Promise<SanitizedConfig> {
  if (Array.isArray(config.plugins)) {
    let configAfterPlugins = config
    for (const plugin of config.plugins) {
      configAfterPlugins = await plugin(configAfterPlugins)
    }
    return await sanitizeConfig(configAfterPlugins)
  }
  return await sanitizeConfig(config)
}

Key process:

1. Plugins are applied first (transform config)
2. Config is sanitized (validates, defaults, flattening)
3. Collections get flattenedFields property (all nested fields flattened)

---

3. Drizzle Schema Building

File: packages/drizzle/src/schema/buildRawSchema.ts

This is where collections → Drizzle tables conversion happens:

adapter.payload.config.collections.forEach((collection) => {
  const tableName = adapter.tableNameMap.get(toSnakeCase(collection.slug))

  buildTable({
    adapter,
    fields: collection.flattenedFields,  // ← flattened fields
    tableName,
    timestamps: collection.timestamps,
    // ...
  })
})

Key functions:

1. buildRawSchema() - iterates collections and builds tables
2. buildTable() - creates table structure
3. traverseFields() - converts each field type to DB columns

Field type mapping example:

text field → varchar column
email field → varchar column with unique index
number field → integer column
relationship field → foreign key + relation table

---

4. Database Initialization

File: packages/drizzle/src/postgres/init.ts

export const init: Init = async function init(this: BasePostgresAdapter) {
  this.rawRelations = {}
  this.rawTables = {}

  // Step 1: Build raw schema (collections → tables)
  buildRawSchema({ adapter: this, setColumnID })

  // Step 2: Execute schema hooks (before)
  await executeSchemaHooks({ type: 'beforeSchemaInit', adapter: this })

  // Step 3: Build Drizzle tables from raw schema
  for (const tableName in this.rawTables) {
    buildDrizzleTable({ adapter: this, rawTable: this.rawTables[tableName] })
  }

  // Step 4: Build relations
  buildDrizzleRelations({ adapter: this })

  // Step 5: Execute schema hooks (after)
  await executeSchemaHooks({ type: 'afterSchemaInit', adapter: this })

  // Step 6: Finalize schema
  this.schema = {
    pgSchema: this.pgSchema,
    ...this.tables,
    ...this.relations,
    ...this.enums,
  }
}

---

5. Schema Push (Development Mode)

File: packages/db-postgres/src/connect.ts

// Only push schema if not in production
if (
  process.env.NODE_ENV !== 'production' &&
  process.env.PAYLOAD_MIGRATING !== 'true' &&
  this.push !== false
) {
  await pushDevSchema(this as unknown as DrizzleAdapter)
}

File: packages/drizzle/src/utilities/pushDevSchema.ts

export const pushDevSchema = async (adapter: DrizzleAdapter) => {
  // 1. Compare current schema with previous
  const equal = dequal(previousSchema, {
    localeCodes,
    rawTables: adapter.rawTables,
  })

  if (equal) {
    // No changes, skip
    return
  }

  // 2. Use drizzle-kit's pushSchema
  const { pushSchema } = adapter.requireDrizzleKit()
  const { apply, hasDataLoss, warnings } = await pushSchema(
    adapter.schema,
    adapter.drizzle,
    // ...
  )

  // 3. Show warnings if needed
  // 4. Apply changes
  await apply()
}

---

6. Operations (CRUD)

File: packages/drizzle/src/create.ts

export const create: Create = async function create(
  this: DrizzleAdapter,
  { collection: collectionSlug, data, req, returning, select },
) {
  // 1. Get collection config
  const collection = this.payload.collections[collectionSlug].config

  // 2. Get table name
  const tableName = this.tableNameMap.get(toSnakeCase(collection.slug))

  // 3. Get transaction
  const db = await getTransaction(this, req)

  // 4. Execute upsert (handles all field transformations)
  const result = await upsertRow({
    adapter: this,
    collectionSlug,
    data,
    db,
    fields: collection.flattenedFields,
    operation: 'create',
    tableName,
  })

  return result
}

Operations flow:

1. Operation receives collection slug and data
2. Retrieves collection config from payload
3. Transforms data using field hooks (beforeChange)
4. Executes Drizzle query
5. Transforms result using field hooks (afterChange)
6. Returns result

---

7. Type Generation

File: packages/payload/src/bin/generateTypes.ts

export async function generateTypes(config: SanitizedConfig): Promise<void> {
  // 1. Convert config to JSON Schema
  const jsonSchema = configToJSONSchema(config, config.db.defaultIDType, i18n)

  // 2. Compile to TypeScript
  let compiled = await compile(jsonSchema, 'Config', { ... })

  // 3. Add module declaration
  compiled += `\n\ndeclare module 'payload' {
  export interface GeneratedTypes extends Config {}
}`

  // 4. Write to file
  await fs.writeFile(outputFile, compiled)
}

---

Architecture Summary

┌─────────────────────────────────────────────────────────────────┐
│  1. USER DEFINES COLLECTIONS                                    │
│     └── payload.config.ts                                        │
│         └── collections: [Users, Posts, ...]                    │
└─────────────────────────────────────────────────────────────────┘
                            ↓
┌─────────────────────────────────────────────────────────────────┐
│  2. CONFIG BUILD & SANITIZATION                                 │
│     └── buildConfig() → sanitizeConfig()                        │
│         └── flattenFields, validate, defaults                   │
└─────────────────────────────────────────────────────────────────┘
                            ↓
┌─────────────────────────────────────────────────────────────────┐
│  3. DRIZZLE SCHEMA BUILDING                                     │
│     └── init() → buildRawSchema() → buildTable()               │
│         └── Field types → DB columns/tables                     │
└─────────────────────────────────────────────────────────────────┘
                            ↓
┌─────────────────────────────────────────────────────────────────┐
│  4. DATABASE CONNECT & PUSH (Dev mode)                           │
│     └── connect() → pushDevSchema()                            │
│         └── drizzle-kit pushSchema()                           │
└─────────────────────────────────────────────────────────────────┘
                            ↓
┌─────────────────────────────────────────────────────────────────┐
│  5. OPERATIONS (CRUD)                                           │
│     └── createLocal() → create() → upsertRow()                 │
│         └── Transform → Drizzle Query → Transform              │
└─────────────────────────────────────────────────────────────────┘
                            ↓
┌─────────────────────────────────────────────────────────────────┐
│  6. TYPE GENERATION (Optional)                                  │
│     └── payload generate:types                                  │
│         └── JSON Schema → TypeScript interfaces                 │
└─────────────────────────────────────────────────────────────────┘

---

Comparison with @deessejs/collections

Current @deessejs/collections approach:

1. Collections → Schema: We already have buildSchema() in packages/core/src/schema.ts
2. Schema → DB: We use drizzle-kit CLI (push, generate, migrate)
3. Operations: We expose raw Drizzle queries, not wrapped operations
4. Types: Direct TypeScript inference (no generation needed)

Key difference:

• PayloadCMS wraps ALL operations with hooks, access control, validation
• @deessejs/collections provides the schema building and leaves operations to the user
• PayloadCMS has a complex plugin system; we have a simpler approach

---

References

• Collection Config: packages/payload/src/collections/config/types.ts
• Config Building: packages/payload/src/config/build.ts
• Drizzle Init: packages/drizzle/src/postgres/init.ts
• Schema Building: packages/drizzle/src/schema/buildRawSchema.ts
• Field Traversal: packages/drizzle/src/schema/traverseFields.ts
• Push Dev Schema: packages/drizzle/src/utilities/pushDevSchema.ts
• Create Operation: packages/drizzle/src/create.ts
• Type Generation: packages/payload/src/bin/generateTypes.ts

[AliiiBenn]

Update: No Generated Types File Needed

After analysis, we don't need a collection-types.ts file like PayloadCMS because of our API design:

PayloadCMS (needs generated types)

// Config is runtime, types are "lost"
export default buildConfig({
  collections: [Users, Posts]
})

// Must generate separate file for types
// payload-types.ts
export interface User { id: number; email: string; }

Our approach (no generation needed)

// Config is TypeScript, types are preserved via inference
export const { collections, db } = defineConfig({
  database: adapter,
  collections: [Users, Posts]
})

// db.users.findMany() automatically has correct types
// collections.users.slug is typed as 'users'

Why it works:

1. defineConfig uses TypeScript generics
2. Collections are defined in TypeScript code
3. Types are inferred directly by TypeScript
4. No runtime config that "loses" types

The only case where a separate file might help:

• Sharing types between projects without importing full config
• But even then, we can simply do: export type { User } from './collections/config'

This makes our design simpler and more modern than PayloadCMS.

[AliiiBenn]

CLI Simplification

With our approach where collections are defined in TypeScript and the schema is built at runtime, the CLI can be simplified:

Current CLI (not needed)

• db:generate - No migration files to generate, schema is implicit
• db:migrate - Same, no explicit migration files

Simplified CLI (sufficient)

collections db:push           # Sync schema to database (development)
collections db:push --dry-run # Preview changes without applying

Why this works

collections/config.ts (TypeScript)
        ↓ buildSchema()
Drizzle Schema (implicit)
        ↓ db:push
Database (tables created/updated)

The schema is:

1. Built dynamically from collection definitions
2. Synced automatically with db:push
3. No explicit migration files needed

This is similar to how Prisma works in development mode - you just run prisma db push to sync the schema.

[AliiiBenn]

Default Config Path

By default, the system should look for the collections config in a standard location:

project/
├── lib/
│   └── collections.ts    ← default config path
├── app/
│   └── page.tsx

// lib/collections.ts (default)
export const { collections, db } = defineConfig({
  database: pgAdapter({ url: process.env.DATABASE_URL! }),
  collections: [Users, Posts]
})

Override with withCollections

Users can override the config path if needed:

// next.config.ts
import { withCollections } from '@deessejs/collections/next'

export default withCollections({
  // Override config path
  collectionsPath: './config/my-collections.ts'
}, {
  // Next.js config
  reactCompiler: true
})

This makes the DX simpler - users don't need to specify the path unless they want a non-default location.

[AliiiBenn]

Auto Schema Push in Development

The withCollections plugin should include a worker that automatically watches for config changes and pushes the schema:

Development Mode (Automatic)

// withCollections starts a background worker that:
// 1. Watches lib/collections.ts for changes
// 2. Detects when collections are modified
// 3. Runs db:push automatically in the background
// 4. Logs status to console

How it works

User edits lib/collections.ts
        ↓
Worker detects file change
        ↓
Rebuild schema from collections
        ↓
db:push (if schema changed)
        ↓
Console log: Schema synced!

Production Mode

• No worker (disabled)
• User runs db:push manually once to deploy schema
• Or CI/CD runs db:push during deployment

Implementation

// withCollections.ts
export const withCollections = (nextConfig, options) => {
  return {
    ...nextConfig,
    // Development only: start schema watcher
    ...(process.env.NODE_ENV !== 'production' && {
      onDevServerReady: async (devServer) => {
        await startSchemaWatcher({
          collectionsPath: options.collectionsPath || './lib/collections',
          onChange: async () => {
            const { buildSchema, push } = await import('@deessejs/collections')
            const { pgAdapter } = await import('@deessejs/collections')
            // Build and push schema
          }
        })
      }
    })
  }
}

This gives a great DX - users don't even need to run db:push manually during development!