RFC: High-level database API + Auto Schema Sync

[AliiiBenn]

Summary

This issue proposes a new architecture for @deessejs/collections that provides a higher-level API similar to PayloadCMS but simpler, with automatic schema synchronization in development.

---

Current State

Currently, @deessejs/collections:

• Uses defineConfig() to create collections
• Exposes raw Drizzle instance: db.select().from(posts).where(...)
• Requires manual CLI commands: db:push, db:generate, db:migrate

---

Proposed Architecture

1. High-Level API

const { collections, db } = defineConfig({
  database: adapter,
  collections: [Users, Posts]
})

// Instead of: db.select().from(posts).where(...)
// We get:
await db.posts.find({ where: { status: 'published' } })
await db.posts.findById(1)
await db.posts.create({ data: { title: 'Hello' } })
await db.posts.update({ where: { id: 1 }, data: { title: 'Updated' } })
await db.posts.delete({ where: { id: 1 } })
await db.posts.count({ where: { status: 'draft' } })

2. No Generated Types File Needed

Unlike PayloadCMS which needs payload-types.ts because config is runtime, our approach preserves TypeScript types through inference:

// Types are automatically inferred
db.posts.find()  // TypeScript knows the return type
collections.users.slug  // Typed as 'users'

3. Simplified CLI

Only db:push is needed since schema is implicit (no migration files):

collections db:push           # Sync schema to database
collections db:push --dry-run # Preview changes

4. Default Config Path

Default location for collections config:

lib/collections.ts

Can be overridden:

withCollections({
  collectionsPath: './config/my-collections.ts'
}, nextConfig)

5. Auto Schema Push in Development

withCollections includes a worker that automatically watches for config changes and pushes the schema:

// Development mode (automatic)
// - Worker watches lib/collections.ts
// - Detects changes to collections
// - Auto db:push in background

// Production mode
// - No worker
// - User runs db:push once manually

---

API Design (Detailed)

Query Methods

db.posts.find({
  where: { status: 'published', authorId: 1 },
  select: { id: true, title: true },
  orderBy: { createdAt: 'desc' },
  limit: 10,
  offset: 0
})

db.posts.findById(id, { select: { id: true, title: true } })

db.posts.count({ where: { status: 'draft' } })

Mutation Methods

db.posts.create({
  data: { title: 'Hello', content: '...' },
  returning: true  // default
})

db.posts.update({
  where: { id: 1 },
  data: { title: 'Updated' },
  returning: true
})

db.posts.delete({
  where: { id: 1 },
  returning: false
})

Hooks Support

const posts = collection({
  slug: 'posts',
  fields: { ... },
  hooks: {
    beforeCreate: [validateData],
    afterCreate: [logCreation]
  }
})

Hooks are automatically executed by the high-level API.

---

Open Questions

1. Where syntax: Simple equality vs operators (e.g., { title: 'hello' } vs { title: { $contains: 'hello' } })
2. Pagination: Cursor-based vs offset-based
3. Transactions: How to handle multi-collection transactions
4. Return format: Just data vs data + metadata (total, page, etc.)

---

Implementation Plan

1. Create high-level DbWrapper with collection methods
2. Update defineConfig to return db with collection operations
3. Implement CRUD operations with hooks
4. Simplify CLI (keep only db:push)
5. Add default config path detection
6. Implement schema watcher for auto-push in dev

---

References

• PayloadCMS architecture analysis: RFC: Auto-schema push in development mode (like PayloadCMS) #57
• Drizzle ORM: https://drizzle-orm.com

[AliiiBenn]

Plugin System

We need a robust plugin system that allows extending collections and adding new functionality.

Plugin Definition

Plugins are defined as higher-order functions:

// Plugin creator (takes config, returns plugin function)
const timestampsPlugin = () => {
  return {
    name: 'timestamps',
    // Default fields to add to all collections
    defaultFields: {
      createdAt: field({ fieldType: f.timestamp(), defaultValue: 'now' }),
      updatedAt: field({ fieldType: f.timestamp(), defaultValue: 'now' })
    },
    // Hooks to add
    hooks: {
      beforeChange: [(data) => {
        data.updatedAt = new Date()
        return data
      }]
    }
  }
}

// Usage in defineConfig
export const { collections, db } = defineConfig({
  database: adapter,
  collections: [Users, Posts],
  plugins: [timestampsPlugin()]
})

Plugin Interface

type Plugin = (pluginConfig?: any) => {
  name: string
  version?: string

  // Add default fields to collections
  defaultFields?: Record<string, Field>

  // Hooks to inject
  hooks?: Partial<CollectionHooks>

  // Create new collections
  collections?: Collection[]

  // Extend existing collections
  extendCollections?: (collections: Collection[]) => Collection[]
}

What Plugins Can Do

1. Add default fields to collections

   const timestampsPlugin = () => ({
     name: 'timestamps',
     defaultFields: {
       createdAt: field({ fieldType: f.timestamp() }),
       updatedAt: field({ fieldType: f.timestamp() })
     }
   })

2. Create new collections

   const softDeletePlugin = () => ({
     name: 'soft-delete',
     collections: [
       // Automatically added to db.posts.deleted, etc.
       deletedPosts = collection({
         slug: 'deleted-posts',
         fields: { ... }
       })
     ]
   })

3. Add hook behaviors

   const auditPlugin = ({ userId }) => ({
     name: 'audit',
     hooks: {
       afterChange: [(args) => {
         // Log changes
         await logAudit(args, userId)
       }]
     }
   })

Plugin Execution Order

1. First, user collections are defined
2. Plugins are applied in order
3. Each plugin can:
   • Add default fields to all collections
   • Add new collections
   • Modify hooks
4. Final schema is built from all collections

Key Questions

1. Field conflict resolution: What happens if plugin adds field that already exists?
2. Plugin ordering: Should plugins be order-sensitive?
3. Plugin configuration: How to pass config to plugins?
4. Extending vs overriding: Can plugins override user-defined fields?

[AliiiBenn]

Plugin System (Revised Syntax)

Following the discussion, plugins should use a cleaner syntax:

// Define plugin with plugin()
const timestampPlugin = plugin({
  name: 'timestamps',
  
  // Plugin arguments (optional)
  args: {
    defaultValue: 'now',
    autoUpdate: true
  },
  
  // Default fields to add to all collections
  fields: {
    createdAt: field({ fieldType: f.timestamp(), defaultValue: 'now' }),
    updatedAt: field({ fieldType: f.timestamp(), defaultValue: 'now' })
  },
  
  // Hooks to inject
  hooks: {
    beforeChange: [(data) => {
      data.updatedAt = new Date()
      return data
    }]
  },
  
  // Create new collections
  collections: [
    // ...
  ]
})

// Usage - call with args if needed
export const { collections, db } = defineConfig({
  database: adapter,
  collections: [Users, Posts],
  plugins: [
    timestampPlugin({ defaultValue: () => new Date() })
  ]
})

Why this syntax?

1. plugin({ ... }) - Creates a plugin with schema definition
2. timestampPlugin({ args }) - Instantiates the plugin with config
3. More TypeScript-friendly with proper inference

Plugin Function Signature

function plugin<TArgs = {}>(
  config: {
    name: string
    args?: TArgs
    fields?: Record<string, Field>
    hooks?: Partial<CollectionHooks>
    collections?: Collection[]
  }
): <TArgs>(args: TArgs) => PluginInstance

This is clearer and more consistent with how collections are defined.

[AliiiBenn]

Plugin System (Revised Syntax with Zod)

Following the discussion, plugins should use Zod for args validation:

// Define plugin with plugin() and Zod schema
const timestampPlugin = plugin({
  name: 'timestamps',
  
  // Zod schema for args validation + type inference
  args: zod.object({
    defaultValue: zod.union([zod.string(), zod.function()]).default('now'),
    autoUpdate: zod.boolean().default(true)
  }),
  
  // Default fields to add to all collections
  fields: {
    createdAt: field({ fieldType: f.timestamp(), defaultValue: 'now' }),
    updatedAt: field({ fieldType: f.timestamp(), defaultValue: 'now' })
  },
  
  // Hooks to inject
  hooks: {
    beforeChange: [(data) => {
      data.updatedAt = new Date()
      return data
    }]
  },
  
  // Create new collections
  collections: [
    // ...
  ]
})

// Usage - args are validated and typed
export const { collections, db } = defineConfig({
  database: adapter,
  collections: [Users, Posts],
  plugins: [
    timestampPlugin({
      defaultValue: () => new Date(),  // typed as function
      autoUpdate: true                   // typed as boolean
    })
  ]
})

Why Zod for args?

1. Validation - Args are automatically validated
2. Type inference - TypeScript infers types from Zod schema
3. Defaults - Zod handles defaults automatically
4. Documentation - Zod schemas serve as documentation

Plugin Function Signature

function plugin<TArgs extends zod.ZodType>(
  config: {
    name: string
    args?: TArgs
    fields?: Record<string, Field>
    hooks?: Partial<CollectionHooks>
    collections?: Collection[]
  }
): (args: zod.infer<TArgs>) => PluginInstance

[AliiiBenn]

Custom Collection Methods

Collections can define custom methods that have access to the typed db instance:

const posts = collection({
  slug: 'posts',
  fields: { ... },
  
  // Custom methods with access to typed db
  methods: {
    // this.db is available and fully typed
    findDrafts() {
      return this.db.posts.find({ where: { _status: 'draft' } })
    },
    
    findPublished() {
      return this.db.posts.find({ where: { _status: 'published' } })
    },
    
    getWithAuthor(authorId: number) {
      return this.db.posts.find({ 
        where: { authorId },
        select: { id: true, title: true, author: true }
      })
    },
    
    publish(id: number) {
      return this.db.posts.update({ 
        where: { id }, 
        data: { _status: 'published' } 
      })
    }
  }
})

// Usage - call methods on collection instance
const drafts = await posts.findDrafts()
const published = await posts.findPublished()
const withAuthor = await posts.getWithAuthor(123)
await posts.publish(1)

Why on collection (posts) not db.posts?

// More intuitive
posts.findDrafts()

// vs redundant
db.posts.findDrafts()  // \"posts\" is already in the path

Method Context

• this.db - the high-level db API (db.posts.find, etc.)
• this.collections - access to other collections
• this.collection - current collection metadata
• All fully typed

Plugin Integration

Plugins can also add methods:

const timestampPlugin = plugin({
  name: 'timestamps',
  methods: {
    touch(id: number) {
      return this.db[this.collection.slug].update({
        where: { id },
        data: { updatedAt: new Date() }
      })
    }
  }
})

[AliiiBenn]

Query/Mutation API (Alternative to Collection Methods)

Instead of collection methods, we could use a query/mutation system similar to tRPC:

import { success, cause } from '@deessejs/core'

// Define queries and mutations
const userQueries = t.query({
  getById: {
    args: z.object({
      id: z.number()
    }),
    handler: async (ctx, args) => {
      const user = await ctx.db.users.findById(args.id)
      if (!user) {
        return cause({
          name: 'NOT_FOUND',
          message: 'User not found',
          data: { id: args.id }
        })
      }
      return success(user)
    }
  },

  listPublished: {
    args: z.object({
      limit: z.number().default(10)
    }),
    handler: async (ctx, args) => {
      return success(await ctx.db.users.find({ 
        where: { status: 'published' },
        limit: args.limit 
      }))
    }
  }
})

const userMutations = t.mutation({
  create: {
    args: z.object({
      name: z.string().min(2),
      email: z.string().email()
    }),
    handler: async (ctx, args) => {
      const existing = await ctx.db.users.find({ 
        where: { email: args.email } 
      })
      if (existing.data.length > 0) {
        return cause({
          name: 'DUPLICATE',
          message: 'Email already exists',
          data: { field: 'email' }
        })
      }
      return success(await ctx.db.users.create({ data: args }))
    }
  }
})

// Usage
const result = await userQueries.getById({ id: 1 })

if (result.ok) {
  console.log(result.data) // typed as User
} else {
  console.log(result.error.name) // 'NOT_FOUND'
}

Why this approach?

1. ctx.db instead of this.db - More explicit, consistent with tRPC pattern
2. Query/Mutation separation - Clear distinction between reads and writes
3. Result type - success(data) or cause(error) pattern
4. Error typing - Each query/mutation can define its own error types

Result Types

// Success case
{ ok: true, data: T }

// Error case  
{ ok: false, error: Cause<T> }

type Cause<T> = {
  name: string       // 'NOT_FOUND', 'DUPLICATE', etc.
  message: string   // Human readable
  data?: T           // Additional error data
}

Integration with defineConfig

const { collections, db, queries, mutations } = defineConfig({
  database: adapter,
  collections: [Users, Posts],
  queries: userQueries,
  mutations: userMutations
})

Key Differences from Collection Methods

Aspect	Collection Methods	Query/Mutation
Access	posts.findDrafts()	queries.getPost({ id: 1 })
db access	this.db	ctx.db
throw	Errors exceptions	success() / cause()
Organization	Per collection	Grouped by feature
Typing	Method return types	Union types

This is more aligned with modern patterns like tRPC, RSC, and server actions.

[AliiiBenn]

Custom Collection Methods (Revised)

Methods are defined in the collection but accessed via db (not on the collection object itself):

import { query, mutation } from '@deessejs/core'

const posts = collection({
  slug: 'posts',
  fields: { ... },
  
  // Custom methods - defined in collection
  methods: {
    // query() for read operations - returns AsyncOutcome
    findDrafts: query({
      handler: async (ctx) => {
        return ctx.db.posts.find({ where: { _status: 'draft' } })
      }
    }),
    
    // with args
    findByAuthor: query({
      args: z.object({ authorId: z.number() }),
      handler: async (ctx, args) => {
        return ctx.db.posts.find({ 
          where: { authorId: args.authorId },
          select: { id: true, title: true, author: true }
        })
      }
    }),
    
    // mutation() for write operations
    publish: mutation({
      args: z.object({ id: z.number() }),
      handler: async (ctx, args) => {
        return ctx.db.posts.update({ 
          where: { id: args.id }, 
          data: { _status: 'published' } 
        })
      }
    }),
    
    // Can also be plain functions (not query/mutation)
    findPublished() {
      return this.db.posts.find({ where: { _status: 'published' } })
    }
  }
})

// Usage - call via db (not collection)
const drafts = await db.posts.findDrafts()
const byAuthor = await db.posts.findByAuthor({ authorId: 123 })
await db.posts.publish({ id: 1 })
const published = await db.posts.findPublished()

Key Points

1. Methods on collection definition - Defined inside collection({ methods: {...} })
2. Accessed via db - Called as db.posts.findDrafts(), not posts.findDrafts()
3. ctx.db - Inside handlers, use ctx.db to access other collections
4. query() - For read operations, returns AsyncOutcome
5. mutation() - For write operations, returns AsyncOutcome
6. Plain functions - Can also use regular functions with this.db

Why db.posts and not posts?

// More intuitive - db is the interface for operations
db.posts.findDrafts()
db.posts.findByAuthor({ authorId: 1 })
db.posts.publish({ id: 1 })

// vs separating collection (metadata) from operations
posts.findDrafts()  // less clear

Query/Mutation Return Types

// query/mutation return AsyncOutcome
{ ok: true, data: T } | { ok: false, error: Cause }

// Regular functions return directly

Integration

const { collections, db } = defineConfig({
  database: adapter,
  collections: [posts, users]
})

// db.posts has:
// - Built-in: find(), findById(), create(), update(), delete(), count()
// - Custom: findDrafts(), findByAuthor(), publish()

[AliiiBenn]

Custom Collection Methods (Final)

ALL methods must use query() or mutation():

import { query, mutation } from '@deessejs/core'

const posts = collection({
  slug: 'posts',
  fields: { ... },
  
  methods: {
    // query() for read operations
    findDrafts: query({
      handler: async (ctx) => {
        return ctx.db.posts.find({ where: { _status: 'draft' } })
      }
    }),
    
    findPublished: query({
      handler: async (ctx) => {
        return ctx.db.posts.find({ where: { _status: 'published' } })
      }
    }),
    
    // with args
    findByAuthor: query({
      args: z.object({ authorId: z.number() }),
      handler: async (ctx, args) => {
        return ctx.db.posts.find({ 
          where: { authorId: args.authorId },
          select: { id: true, title: true, author: true }
        })
      }
    }),
    
    // mutation() for write operations
    publish: mutation({
      args: z.object({ id: z.number() }),
      handler: async (ctx, args) => {
        return ctx.db.posts.update({ 
          where: { id: args.id }, 
          data: { _status: 'published' } 
        })
      }
    })
  }
})

// Usage
const drafts = await db.posts.findDrafts()
const published = await db.posts.findPublished()
const byAuthor = await db.posts.findByAuthor({ authorId: 123 })
await db.posts.publish({ id: 1 })

Why mandatory query/mutation?

1. Consistency - All methods have consistent return types (AsyncOutcome)
2. Error handling - All use success/cause pattern
3. Caching - query() can be cached, mutation() cannot
4. Type safety - Args are validated with Zod

No Plain Functions

Plain functions like findPublished() { return ... } are NOT allowed. All must use:

• query() for reads
• mutation() for writes

[AliiiBenn]

Plugins Can Add Methods to Collections

Plugins can also add custom methods to collections:

// Plugin that adds soft-delete methods to ALL collections
const softDeletePlugin = plugin({
  name: 'soft-delete',
  
  // Add methods to all collections
  methods: {
    // This will be available on ALL collections: db.users.softDelete(), db.posts.softDelete(), etc.
    softDelete: mutation({
      args: z.object({ id: z.number() }),
      handler: async (ctx, args) => {
        const tableName = ctx.collection.slug
        return ctx.db[tableName].update({ 
          where: { id: args.id },
          data: { deletedAt: new Date() }
        })
      }
    }),
    
    restore: mutation({
      args: z.object({ id: z.number() }),
      handler: async (ctx, args) => {
        const tableName = ctx.collection.slug
        return ctx.db[tableName].update({ 
          where: { id: args.id },
          data: { deletedAt: null }
        })
      }
    }),
    
    isDeleted: query({
      args: z.object({ id: z.number() }),
      handler: async (ctx, args) => {
        const tableName = ctx.collection.slug
        return ctx.db[tableName].findById(args.id, {
          select: { deletedAt: true }
        })
      }
    })
  }
})

// Usage
await db.users.softDelete({ id: 1 })
await db.posts.restore({ id: 1 })
const deleted = await db.posts.isDeleted({ id: 1 })

Plugin Methods Context

In plugin methods:

• ctx.collection - gives access to the current collection being operated on
• ctx.db - access to all collections
• Methods are added to ALL collections automatically

Filter by Collection

Plugins can filter which collections get the methods:

const timestampsPlugin = plugin({
  name: 'timestamps',
  
  // Only add to collections with timestamps enabled
  applyTo: (collection) => collection.options?.timestamps !== false,
  
  methods: {
    touch: mutation({ ... })
  }
})