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

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 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.

// 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 or test utilities where an in-memory SQLite database replaces the MySQL connection.

// 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 file defines the entity that executes tasks in the OpenWork system:

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

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

// 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 for centralized imports:

// 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.

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

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:

// 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	ORM client factory with mysql2 driver
src/client.ts	Environment-aware connection abstraction (includes SQLite fallback)
src/schema/*.ts	Table definitions using mysqlTable
src/schema/index.ts	Schema module re-exports
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
• 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 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. The development server runs drizzle-kit push automatically via scripts/dev-local.mjs, applying schema changes without manual intervention.

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