Database Schema Management

Overview

Unkey uses a single MySQL database that is automatically created and initialized during development:

• unkey: Main application database containing APIs, keys, workspaces, deployments, gateways, and all related data

Schema Files

Schema definitions are maintained in:

• go/pkg/db/schema.sql - Complete Unkey application schema (includes all tables)

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             # Complete application schema

Initialization Order

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

1. 00-init-databases.sql - Creates the unkey database and grants permissions
2. 01-main-schema.sql - Creates all application tables in the unkey database

Database-Qualified Table Names

The schema file uses 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,
  -- ...
);
 
-- All tables including deployments, gateways, vms, and certificates are in the same database
CREATE TABLE `unkey`.`gateways` (
  `id` varchar(256) NOT NULL,
  -- ...
);

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

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