# OpenWork Server Data Model Architecture: How Drizzle ORM and better-sqlite3 Work Together

> Discover OpenWork server's data model architecture. Learn how Drizzle ORM provides type-safe access with SQLite for development and MySQL for production.

- Repository: [Different AI/openwork](https://github.com/different-ai/openwork)
- Tags: architecture
- Published: 2026-08-15

---

**The OpenWork server uses Drizzle ORM for type-safe database access, with MySQL (via mysql2) as the primary production datastore and better-sqlite3 as a lightweight fallback for local development and testing.**

This architecture is implemented in the `ee/packages/den-db` package of the different-ai/openwork repository. The design separates schema definitions, connection logic, and migrations into distinct layers while maintaining full type safety across both database engines.

---

## Drizzle ORM Setup and Database Drivers

The connection layer in [`src/drizzle.ts`](https://github.com/different-ai/openwork/blob/main/src/drizzle.ts) instantiates Drizzle with the appropriate driver based on the runtime environment. This dual-driver approach allows the same schema code to run against different storage backends without modification.

### Production: MySQL with mysql2

The primary driver uses `mysql2/promise` for asynchronous connection handling. The `drizzle()` function from `drizzle-orm/mysql2` wraps the connection and exposes the typed query API.

```typescript
// src/drizzle.ts
import mysql from 'mysql2/promise';
import { drizzle } from 'drizzle-orm/mysql2';

const connection = await mysql.createConnection({
  host: process.env.MYSQL_HOST,
  user: process.env.MYSQL_USER,
  password: process.env.MYSQL_PASSWORD,
  database: process.env.MYSQL_DB,
});

export const db = drizzle(connection);

```

### Development/Testing: better-sqlite3 Fallback

For fast, deterministic tests and local development, the server can swap in `better-sqlite3`. This is typically handled in [`src/client.ts`](https://github.com/different-ai/openwork/blob/main/src/client.ts) or test utilities where an in-memory SQLite database replaces the MySQL connection.

```typescript
// Test helper pattern for in-memory SQLite
import Database from 'better-sqlite3';
import { drizzle } from 'drizzle-orm/sqlite';

const sqliteDb = new Database(':memory:');
export const db = drizzle(sqliteDb);

```

The `:memory:` mode creates a transient database that exists only for the process lifetime. This eliminates external dependencies and ensures test isolation.

---

## Schema Definitions with mysqlTable

All tables are declared using Drizzle's **mysqlTable** helper from `drizzle-orm/mysql-core`. These definitions reside in `src/schema/` and export typed table objects used throughout the application.

### Workers Table

The [`workers.ts`](https://github.com/different-ai/openwork/blob/main/workers.ts) file defines the entity that executes tasks in the OpenWork system:

```typescript
// src/schema/workers.ts
import { mysqlTable, varchar, timestamp } from 'drizzle-orm/mysql-core';

export const workers = mysqlTable('workers', {
  id: varchar('id', { length: 36 }).primaryKey(),
  name: varchar('name', { length: 255 }).notNull(),
  orgId: varchar('org_id', {长度: 36 }).notNull(),
  createdAt: timestamp('created_at').defaultNow(),
});

```

### Teams Table

Organizations are partitioned into teams, defined in [`src/schema/teams.ts`](https://github.com/different-ai/openwork/blob/main/src/schema/teams.ts):

```typescript
// src/schema/teams.ts
import { mysqlTable, varchar } from 'drizzle-orm/mysql-core';

export const teams = mysqlTable('teams', {
  id: varchar('id', { length: 36 }).primaryKey(),
  orgId: varchar('org_id', { length: 36 }).notNull(),
  name: varchar('name', { length: 255 }).notNull(),
});

```

### Auth Table with JSON Columns

The authentication schema demonstrates advanced column types including **JSON** for flexible provider data:

```typescript
// src/schema/auth.ts
import { mysqlTable, varchar, json } from 'drizzle-orm/mysql-core';

export const auth = mysqlTable('auth', {
  userId: varchar('user_id', { length: 36 }).primaryKey(),
  passwordHash: varchar('password_hash', { length: 255 }).notNull(),
  providerData: json('provider_data').notNull(),
});

```

Schema modules are aggregated in [`src/schema/index.ts`](https://github.com/different-ai/openwork/blob/main/src/schema/index.ts) for centralized imports:

```typescript
// src/schema/index.ts
export * from './workers';
export * from './teams';
export * from './auth';
// ... additional schema exports

```

---

## Typed Query API Usage

With Drizzle's type inference, queries against these tables are fully typed without code generation. The `db` client exposes methods like `select()`, `insert()`, and `update()` that understand column types and relationships.

```typescript
// Querying with type safety
import { db } from './drizzle';
import { workers, teams } from './schema';
import { eq, and } from 'drizzle-orm/expressions';

const activeWorkers = await db
  .select()
  .from(workers)
  .where(eq(workers.orgId, orgId));

// Join example
const teamWorkers = await db
  .select({
    workerName: workers.name,
    teamName: teams.name,
  })
  .from(workers)
  .innerJoin(teams, eq(workers.orgId, teams.orgId))
  .where(and(
    eq(teams.id, teamId),
    eq(workers.orgId, orgId)
  ));

```

TypeScript validates that:
- Column names exist on the table
- Comparison operators match column types
- Selected fields are accessible in the result type

---

## Database Migrations with Drizzle-Kit

Schema changes are managed through **Drizzle-Kit**, configured in [`drizzle.config.ts`](https://github.com/different-ai/openwork/blob/main/drizzle.config.ts) at the package root. This file specifies the schema path, output directory, and database connection for migration generation.

The development workflow executes migrations via the `scripts/dev-local.mjs` utility:

```bash
pnpm --filter @openwork-ee/den-db exec node --import tsx \
  ./node_modules/drizzle-kit/bin.cjs push \
  --config drizzle.config.ts \
  --force

```

The `--force` flag applies pending migrations without interactive confirmation, suitable for automated startup scripts.

---

## Testing Strategy with better-sqlite3

The better-sqlite3 driver enables **in-memory test databases** that provide:

- **Speed**: No network latency or disk I/O for test data
- **Isolation**: Each test run starts with a clean state
- **Determinism**: Reproducible results across environments

The pattern appears in test files like [`migration-schema-parity.test.ts`](https://github.com/different-ai/openwork/blob/main/migration-schema-parity.test.ts):

```typescript
// Test setup with better-sqlite3
import Database from 'better-sqlite3';
import { drizzle } from 'drizzle-orm/sqlite';
import * as schema from '../src/schema';

const sqlite = new Database(':memory:');
const testDb = drizzle(sqlite, { schema });

// Execute migrations against SQLite
await applyMigrations(testDb);

// Verify schema parity between MySQL and SQLite representations
const result = await testDb.select().from(schema.workers).all();

```

This approach validates that schema definitions are compatible with both database engines before deployment.

---

## Package Structure Overview

| File Path | Responsibility |
|-----------|---------------|
| [`src/drizzle.ts`](https://github.com/different-ai/openwork/blob/main/src/drizzle.ts) | ORM client factory with mysql2 driver |
| [`src/client.ts`](https://github.com/different-ai/openwork/blob/main/src/client.ts) | Environment-aware connection abstraction (includes SQLite fallback) |
| `src/schema/*.ts` | Table definitions using `mysqlTable` |
| [`src/schema/index.ts`](https://github.com/different-ai/openwork/blob/main/src/schema/index.ts) | Schema module re-exports |
| [`drizzle.config.ts`](https://github.com/different-ai/openwork/blob/main/drizzle.config.ts) | Drizzle-Kit configuration for migrations |
| `scripts/dev-local.mjs` | Development server with migration auto-run |
| `test/*.test.ts` | Test suite demonstrating better-sqlite3 usage |

---

## Summary

- **Drizzle ORM** provides type-safe database access across MySQL and SQLite backends in the OpenWork server
- **Production deployments** use `mysql2` driver with MySQL as the persistent datastore
- **Local development and testing** leverage `better-sqlite3` with in-memory databases for speed and isolation
- **Schema definitions** in `src/schema/` use `mysqlTable` and are engine-agnostic
- **Migrations** are managed by Drizzle-Kit with configuration in [`drizzle.config.ts`](https://github.com/different-ai/openwork/blob/main/drizzle.config.ts)
- The same query API works identically against both database engines, ensuring code portability

---

## Frequently Asked Questions

### How does OpenWork handle database connections in different environments?

The connection logic in [`src/client.ts`](https://github.com/different-ai/openwork/blob/main/src/client.ts) detects the environment and instantiates either a MySQL connection via `mysql2/promise` or an in-memory SQLite database via `better-sqlite3`. Production code paths always use MySQL, while tests and local development can opt into SQLite for faster iteration.

### Can I use the same Drizzle schema with both MySQL and SQLite?

Yes. The `mysqlTable` definitions generate SQL compatible with both engines for core column types. However, some MySQL-specific features (like `timestamp` with `defaultNow()`) may require adapter layers or migration adjustments when running against SQLite.

### Where are database migrations defined and executed?

Migrations live in the `drizzle/` folder and are configured through [`drizzle.config.ts`](https://github.com/different-ai/openwork/blob/main/drizzle.config.ts). The development server runs `drizzle-kit push` automatically via `scripts/dev-local.mjs`, applying schema changes without manual intervention.