Database Migrations

Cognova uses Drizzle Kit to manage PostgreSQL schema changes. The schema is defined in TypeScript and migrations are generated as SQL files.

Configuration

Drizzle Kit is configured in drizzle.config.ts:

export default defineConfig({
  schema: './server/db/schema.ts',
  out: './server/drizzle/migrations',
  dialect: 'postgresql',
  dbCredentials: {
    url: process.env.DATABASE_URL!
  }
})

• Schema source: server/db/schema.ts (single file)
• Migration output: server/drizzle/migrations/
• Dialect: PostgreSQL

Development Workflow

During development, use db:push to sync schema changes directly without generating migration files:

# 1. Edit server/db/schema.ts
# 2. Push changes to the local database
pnpm db:push

This is faster for iterating because it applies the diff immediately. No migration files are created.

Production Workflow

For production deployments, always use generated migrations:

1. Edit the schema

Add or modify tables in server/db/schema.ts. For example, adding a new table:

export const tags = pgTable('tags', {
  id: uuid('id').primaryKey().defaultRandom(),
  name: text('name').notNull().unique(),
  color: text('color').notNull(),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull()
})

2. Generate the migration

pnpm db:generate

This creates a new SQL file in server/drizzle/migrations/ with the DDL statements needed to bring the database in sync with the schema. The filename includes a timestamp for ordering.

3. Review the migration

Always read the generated SQL before applying it. Check for:

• Unexpected DROP statements
• Missing NOT NULL constraints
• Correct default values
• Foreign key cascade behavior

4. Apply the migration

pnpm db:migrate

In production, migrations run automatically on server startup via the 02.database plugin. The plugin:

1. Acquires a PostgreSQL advisory lock to prevent concurrent migration runs
2. Runs all pending migrations from server/drizzle/migrations/
3. Releases the lock in a finally block

5. Verify

Open Drizzle Studio to inspect the database:

pnpm db:studio

Commands Reference

Adding Relations

When adding a new table, define its relations for the Drizzle query builder:

export const tagsRelations = relations(tags, ({ one }) => ({
  creator: one(user, { fields: [tags.createdBy], references: [user.id] })
}))

Relations are not stored in the database -- they exist only in the TypeScript layer for type-safe joins via db.query.

Migration Safety

Advisory locks

The migration runner uses pg_try_advisory_lock() with a fixed lock ID to prevent two server instances from running migrations simultaneously. If the lock is already held, the instance skips migrations.

Non-blocking in production

If a migration fails in production, the server logs the error and continues with database features enabled (the existing schema still works). This prevents a bad migration from taking down the entire application.

Never edit migration files

Generated files in server/drizzle/migrations/ should not be modified by hand. If a migration is wrong, generate a new corrective migration instead.

Shared Types

After changing the schema, update the corresponding TypeScript types in shared/types/ so the frontend stays in sync. Drizzle provides inference helpers:

import type { InferSelectModel, InferInsertModel } from 'drizzle-orm'
import type { tags } from '~~/server/db/schema'

export type Tag = InferSelectModel<typeof tags>
export type NewTag = InferInsertModel<typeof tags>

See the Database Schema reference for the full table definitions.