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

> Learn how to manage MySQL database schema in Automattic/harper using Drizzle ORM. Explore TypeScript table definitions and Drizzle-Kit CLI for versioned migrations.

- Repository: [Automattic/harper](https://github.com/Automattic/harper)
- Tags: how-to-guide
- Published: 2026-07-27

---

**Harper manages its MySQL database schema using Drizzle ORM, defining tables in TypeScript at [`packages/web/src/lib/db/schema.ts`](https://github.com/Automattic/harper/blob/main/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`](https://github.com/Automattic/harper/blob/main/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:

```typescript
// 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:

```typescript
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`](https://github.com/Automattic/harper/blob/main/packages/web/drizzle.config.ts) file configures Drizzle-Kit with three critical settings:

- **Schema location**: Points to [`src/lib/db/schema.ts`](https://github.com/Automattic/harper/blob/main/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`](https://github.com/Automattic/harper/blob/main/schema.ts), generate a timestamped migration file:

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

```

Alternatively, use the project’s Just task runner:

```bash
just drizzle-generate

```

This creates a SQL file (e.g., [`20240912_1023_create_user_preferences.sql`](https://github.com/Automattic/harper/blob/main/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:

```bash
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:

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

```

## Summary

- **Schema definitions** live in [`packages/web/src/lib/db/schema.ts`](https://github.com/Automattic/harper/blob/main/packages/web/src/lib/db/schema.ts) using Drizzle’s `mysqlTable` API
- **Configuration** is centralized in [`packages/web/drizzle.config.ts`](https://github.com/Automattic/harper/blob/main/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`](https://github.com/Automattic/harper/blob/main/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`](https://github.com/Automattic/harper/blob/main/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.