How to Manage Schema in Automattic/harper: Drizzle ORM Workflow

Harper manages its MySQL database schema using Drizzle ORM, defining tables in TypeScript at packages/web/src/lib/db/schema.ts and versioning migrations in packages/web/drizzle/ via Drizzle-Kit CLI commands.

Managing the database schema in Automattic/harper follows a code-first, type-safe approach. All relational data—including uninstall feedback and problematic lint tracking—is defined using Drizzle ORM and synchronized to MySQL through auto-generated migrations. This keeps schema changes under version control, reviewable as SQL diffs, and reversible without hand-editing production databases.

Schema Definition in TypeScript

Harper centralizes its database structure in packages/web/src/lib/db/schema.ts. Instead of raw SQL, tables are declared using Drizzle’s mysqlTable helper and column types from drizzle-orm/mysql-core.

The repository currently defines tables such as uninstall_feedback and problematic_lint with strictly typed columns:

// packages/web/src/lib/db/schema.ts
import { boolean, int, mysqlTable, text, timestamp } from 'drizzle-orm/mysql-core';

export const uninstallFeedbackTable = mysqlTable('uninstall_feedback', {
  id: int().autoincrement().primaryKey(),
  feedback: text().notNull(),
  timestamp: timestamp().notNull().defaultNow(),
});

export const problematicLintTable = mysqlTable('problematic_lint', {
  id: int().autoincrement().primaryKey(),
  is_false_positive: boolean().notNull(),
  example: text().notNull(),
  feedback: text().notNull(),
  rule_id: text(),
  timestamp: timestamp().notNull().defaultNow(),
});

Adding New Tables

To extend the schema, import the appropriate column helpers and export a new table definition:

export const userPreferencesTable = mysqlTable('user_preferences', {
  user_id: int().primaryKey(),
  theme: text().notNull().default('system'),
  notifications: boolean().notNull().default(true),
});

Drizzle Configuration

The packages/web/drizzle.config.ts file configures Drizzle-Kit with three critical settings:

• Schema location: Points to src/lib/db/schema.ts
• Dialect: Set to mysql for MySQL-compatible databases
• Connection: Reads credentials from process.env.DATABASE_URL

This configuration decouples the migration tooling from hardcoded credentials, relying on environment variables instead.

Migration Workflow

Harper leverages Drizzle-Kit to diff the TypeScript definitions against the live database and produce portable SQL migrations.

Generate Migrations

After modifying schema.ts, generate a timestamped migration file:

npx drizzle-kit generate \
  --config ./packages/web/drizzle.config.ts \
  --out ./packages/web/drizzle

Alternatively, use the project’s Just task runner:

just drizzle-generate

This creates a SQL file (e.g., 20240912_1023_create_user_preferences.sql) in packages/web/drizzle/ containing the exact CREATE TABLE or ALTER statements needed to sync the database.

Apply Migrations

Execute pending migrations against the target database:

npx drizzle-kit migrate \
  --config ./packages/web/drizzle.config.ts

This command runs all unapplied SQL files in chronological order, ensuring schema consistency across development, staging, and production environments.

Database Connection Setup

The application expects a DATABASE_URL environment variable formatted as a standard MySQL connection string. Reference packages/web/.env.example for the expected syntax:

DATABASE_URL=mysql://user:password@localhost:3306/harper_db

Summary

• Schema definitions live in packages/web/src/lib/db/schema.ts using Drizzle’s mysqlTable API
• Configuration is centralized in packages/web/drizzle.config.ts, reading from DATABASE_URL
• Migrations are generated via drizzle-kit generate and applied via drizzle-kit migrate
• Migration files are stored in packages/web/drizzle/ and committed to version control
• Type safety is enforced throughout—TypeScript definitions mirror the actual database schema

Frequently Asked Questions

What ORM does Harper use for schema management?

Harper uses Drizzle ORM with MySQL. Tables are defined in TypeScript using the mysqlTable helper from drizzle-orm/mysql-core, and migrations are handled by Drizzle-Kit.

Where are database migrations stored in Harper?

Generated migrations are stored in packages/web/drizzle/. These files follow a timestamped naming convention (e.g., 20240912_1023_create_table.sql) and should be committed to Git alongside code changes.

How do I add a new table to the Harper database?

Add a new mysqlTable definition to packages/web/src/lib/db/schema.ts, then run npx drizzle-kit generate to create the migration SQL. Review the generated file before applying it with npx drizzle-kit migrate.

Is the schema definition in Harper type-safe?

Yes. Drizzle ORM provides full TypeScript support, ensuring that column types, nullability, and defaults are enforced at compile time and match the actual MySQL schema after migrations run.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}