fix(core): use drizzle-kit programmatic API instead of CLI for migrations

[AliiiBenn]

Summary

The current CLI implementation uses drizzle-kit CLI which requires:

1. A drizzle.config.json file pointing to our collections config
2. Drizzle-kit trying to parse our collections config format (which fails)

This needs to be replaced with the programmatic API from drizzle-kit/api.

Current Problem

What happens now

CLI → drizzle.config.json → collections/config.ts → drizzle-kit CLI
                                              ↓
                                    "No schema files found"

When users run collections db:generate or collections db:push:

1. CLI creates a temporary drizzle.config.json pointing to ./collections/config.ts
2. Drizzle-kit tries to read this file as a Drizzle schema
3. Our config uses defineConfig() which drizzle-kit doesn't understand
4. Error: "No schema files found"

Solution: Use Drizzle-kit Programmatic API

Drizzle-kit provides a full programmatic API in drizzle-kit/api:

Available Functions

Function	Purpose
generateDrizzleJson(imports)	Convert schema to snapshot
pushSchema(imports, dbInstance)	Push schema to DB
generateMigration(prev, cur)	Generate SQL migration

How pushSchema Works

import { pushSchema } from 'drizzle-kit/api'
import { drizzle } from 'drizzle-orm/node-postgres'
import { buildSchema } from '@deessejs/collections'

// 1. Build schema from our collections (already implemented!)
const schema = buildSchema(collections)

// 2. Create drizzle instance
const db = drizzle(pool, { schema })

// 3. Push to database
const result = await pushSchema(schema, db)

// 4. Apply changes
await result.apply()

What pushSchema does internally:

1. Generates snapshot from schema (generateDrizzleJson)
2. Introspects current DB state
3. Calculates diff (ADD TABLE, ADD COLUMN, etc.)
4. Returns statementsToExecute and apply()

Implementation Plan

1. Update migrations.ts

Replace CLI-based approach with programmatic API:

import { pushSchema, generateMigration, generateDrizzleJson } from 'drizzle-kit/api'
import { drizzle } from 'drizzle-orm/node-postgres'
import { buildSchema } from './schema'
import { PgAdapter } from './adapter'

export const push = async (adapter: PgAdapter, collections: Collection[], options: MigrationOptions = {}) => {
  const { verbose = false } = options

  // Build schema from collections (already exists!)
  const schema = buildSchema(collections)

  // Create drizzle instance with our schema
  const pool = await adapter.getPool()
  const db = drizzle(pool, { schema })

  // Use pushSchema directly
  const result = await pushSchema(schema, db)

  if (verbose) {
    console.log('Warnings:', result.warnings)
    console.log('Statements to execute:', result.statementsToExecute.length)
  }

  // Apply changes
  await result.apply()
}

2. Implement generate (db:generate)

export const generate = async (adapter: PgAdapter, collections: Collection[], options: MigrationOptions = {}) => {
  const schema = buildSchema(collections)
  const pool = await adapter.getPool()
  const db = drizzle(pool, { schema })

  // Get current DB state
  const currentJson = /* introspect from DB */ 

  // Generate migration SQL
  const newJson = generateDrizzleJson(schema)
  const sqlStatements = await generateMigration(currentJson, newJson)

  // Write to file
  writeFileSync('./migrations/new_migration.sql', sqlStatements.join('\n'))
}

Files to Modify

File	Changes
src/migrations.ts	Replace CLI calls with programmatic API
src/cli.ts	Remove config file creation

Benefits

1. No config files - Everything is internal, no drizzle.config.json
2. Works with our format - Our defineConfig() is compatible
3. Faster - No subprocess spawning
4. More control - Access to warnings, statements before apply
5. Better errors - Direct error messages from API

References

• Drizzle-kit API: drizzle-kit/api.d.ts
• Key functions: pushSchema, generateMigration, generateDrizzleJson