Database Schema Management

Overview

Unkey uses multiple MySQL databases that are automatically created and initialized during development:

• unkey: Main application database containing APIs, keys, workspaces, and related data
• hydra: Workflow orchestration engine database for managing deployment workflows
• partition_00X: Dataplane partition database

Schema Files

Schema definitions are maintained in separate files:

• go/pkg/db/schema.sql - Main Unkey application schema
• go/pkg/hydra/store/schema.sql - Hydra workflow engine schema
• go/pkg/partition/schema.sq. - Dataplane schema

Docker Development Setup

During local development, schemas are automatically applied via Docker:

File Structure

deployment/
├── Dockerfile.mysql          # MySQL container definition
├── init-databases.sql        # Database creation and user permissions
└── docker-compose.yaml       # Service orchestration

go/pkg/
├── db/schema.sql             # Main application schema  
└── hydra/store/schema.sql    # Hydra workflow schema

Initialization Order

The MySQL container applies files from /docker-entrypoint-initdb.d/ in alphabetical order:

1. 00-init-databases.sql - Creates databases and grants permissions
2. 01-main-schema.sql - Creates all main application tables in unkey database
3. **02-partition_schema.sql - Creates the partition tables in the partition_001 database
4. 03-hydra-schema.sql - Creates workflow tables in hydra database

Database-Qualified Table Names

All schema files use fully-qualified table names to ensure tables are created in the correct database:

-- Main schema (go/pkg/db/schema.sql)
CREATE TABLE `unkey`.`apis` (
  `id` varchar(256) NOT NULL,
  -- ...
);
 
-- Hydra schema (go/pkg/hydra/store/schema.sql)
CREATE TABLE `hydra`.`workflow_executions` (
  id VARCHAR(255) PRIMARY KEY,
  -- ...
);

Schema Management Strategy

Current State (Drizzle-First)

Currently, the primary source of truth for the unkey database is the Drizzle schema in internal/db/. This is used to:

• Generate and run migrations against PlanetScale in production
• Define the actual database structure

The go/pkg/db/schema.sql file is manually maintained and must be kept in sync with the Drizzle schema. Engineers are responsible for ensuring this file matches the current database structure.

Future State (SQL-First)

In the future, we plan to reverse this workflow:

• go/pkg/db/schema.sql will become the primary source of truth
• Drizzle schema will be generated from the SQL schema
• This will enable better tooling and consistency

Hydra Database

The hydra database uses go/pkg/hydra/store/schema.sql as its source of truth since it's Go-native and doesn't use Drizzle.

SQLC Integration

The project uses SQLC to generate type-safe Go code from SQL queries:

• Query definitions: go/pkg/db/queries/*.sql
• Generated code: go/pkg/db/queries.sql.go
• Configuration: go/pkg/db/sqlc.yaml

Regenerating SQLC Code

After schema changes, regenerate the SQLC code:

cd go
go generate ./...

Do not use sqlc generate directly as we have some custom logic during the generation step.

Making Schema Changes

For Unkey Database (Current Workflow)

1. Update Drizzle schema in internal/db/ (primary source of truth)
2. Run migrations against PlanetScale using Drizzle
3. Manually update go/pkg/db/schema.sql to match the Drizzle changes
4. Update SQLC queries if needed in go/pkg/db/queries/
5. Regenerate SQLC code with go generate ./...
6. Rebuild Docker containers for local development:

   docker-compose down
   docker-compose up --build

For Hydra Database

1. Update the schema file go/pkg/hydra/store/schema.sql
2. Rebuild Docker containers to apply changes:

   docker stop $(docker ps -aq); docker system prune -f; docker volume prune --all -f
   docker compose up --build