Database Setup

KnowledgeCheckr uses MySQL as its database with Drizzle ORM for schema management and migrations. The database is automatically initialized when using Docker, or can be set up manually for custom deployments.

Database Requirements

MySQL 8.0 or later
Minimum 512MB RAM allocated to database
UTF-8 character set support
InnoDB storage engine

Automatic Setup (Docker)

When using Docker Compose, the database is automatically initialized with all required tables and schema.

Database image initialization

The database Docker image (~/workspace/source/database/Dockerfile:1-11) uses a two-stage build:

Setup stage: Concatenates all Drizzle migrations into a single init.sql file
MySQL stage: Copies the init script to /docker-entrypoint-initdb.d/

On first run, MySQL automatically executes all scripts in this directory.

Migration consolidation

The compose-init-script.sh (~/workspace/source/database/compose-init-script.sh:1-52) script:

Reads all .sql files from drizzle/migrations/ directory
Sorts them in lexicographic order (0000_…, 0001_…, etc.)
Removes statement-breakpoint comments
Concatenates them into a single initialization file

This ensures migrations are applied in the correct order.

Verify initialization

Check that the database is ready:

docker-compose exec db mysql -u root -p123 -e "USE KnowledgeCheckr; SHOW TABLES;"

You should see the complete schema with all tables created.

Manual Database Setup

For non-Docker deployments or custom database configurations:

Create the database

Connect to your MySQL server and create the database:

CREATE DATABASE KnowledgeCheckr CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Create database user (optional)

For production, create a dedicated user:

CREATE USER 'knowledgecheckr'@'%' IDENTIFIED BY 'secure_password';
GRANT ALL PRIVILEGES ON KnowledgeCheckr.* TO 'knowledgecheckr'@'%';
FLUSH PRIVILEGES;

Update your environment variables accordingly:

DATABASE_USER=knowledgecheckr
DATABASE_PASSWORD=secure_password

Run migrations manually

Apply all migrations in order from the database/drizzle/migrations/ directory:

# List all migration files in order
ls -1 database/drizzle/migrations/*.sql

# Apply each migration
mysql -h <host> -u <user> -p<password> KnowledgeCheckr < database/drizzle/migrations/0000_initial.sql
mysql -h <host> -u <user> -p<password> KnowledgeCheckr < database/drizzle/migrations/0001_*.sql
# ... and so on

Verify schema

Confirm all tables are created:

mysql -h <host> -u <user> -p<password> KnowledgeCheckr -e "SHOW TABLES;"

Drizzle ORM Configuration

KnowledgeCheckr uses Drizzle Kit for managing database schema and migrations. Configuration is defined in drizzle.config.ts (~/workspace/source/drizzle.config.ts:1-17):

import 'dotenv/config'
import { defineConfig } from 'drizzle-kit'
import env from '@/src/lib/Shared/Env'

export default defineConfig({
  out: './database/drizzle/migrations',
  schema: './database/drizzle/schema.ts',
  dialect: 'mysql',
  dbCredentials: {
    host: env.DATABASE_HOST,
    port: env.DATABASE_PORT,
    user: env.DATABASE_USER,
    password: env.DATABASE_PASSWORD,
    database: env.DATABASE_NAME,
  },
})

Key Configuration Points

Migrations directory: ./database/drizzle/migrations - All generated SQL migrations
Schema file: ./database/drizzle/schema.ts - TypeScript schema definitions
Dialect: MySQL-specific SQL generation
Credentials: Loaded from validated environment variables

Working with Migrations

Generating New Migrations

When you modify the schema in database/drizzle/schema.ts, generate a new migration:

npm run drizzle-kit generate

This creates a new timestamped migration file in database/drizzle/migrations/.

Applying Migrations

For development environments, apply migrations directly:

npm run drizzle-kit migrate

Migration File Structure

Migration files follow this naming pattern:

0000_initial_setup.sql
0001_add_users_table.sql
0002_add_courses_table.sql

Each file contains:

SQL statements to modify the schema
Statement breakpoint comments (removed during Docker initialization)

Example migration file:

CREATE TABLE `users` (
  `id` varchar(255) PRIMARY KEY,
  `email` varchar(255) NOT NULL,
  `name` varchar(255),
  `created_at` timestamp DEFAULT CURRENT_TIMESTAMP
);
--> statement-breakpoint

CREATE INDEX `email_idx` ON `users` (`email`);

Database Schema Overview

The complete schema is defined in database/drizzle/schema.ts and includes:

User management: User accounts, authentication, profiles
Course structure: Courses, modules, lessons
Content: Questions, answers, learning materials
Progress tracking: User progress, quiz results
Relations: Foreign keys and table relationships

Table relationships are defined in database/drizzle/relations.ts.

Connection Configuration

Docker Compose Setup

When using Docker Compose (~/workspace/source/docker-compose.yml:4-16):

db:
  image: ghcr.io/master-thesis-188199/knowledgecheckr-database:1.0.0
  restart: on-failure
  environment:
    MYSQL_ROOT_PASSWORD: "123"
    MYSQL_ROOT_HOST: "%"
    MYSQL_DATABASE: KnowledgeCheckr
    MYSQL_USER: test
    MYSQL_PASSWORD: pass
  volumes:
    - ./mysql_data:/var/lib/mysql
  ports:
    - "3305:3306"

Note: The database is accessible:

Internally (from containers): db:3306
Externally (from host): localhost:3305

Application Connection

The application connects using environment variables:

DATABASE_HOST=db  # Docker service name
DATABASE_PORT=3306  # Internal port
DATABASE_USER=root
DATABASE_PASSWORD=123
DATABASE_NAME=KnowledgeCheckr

Data Persistence

Volume Management

Database data is persisted in the mysql_data directory:

volumes:
  - ./mysql_data:/var/lib/mysql

Important:

This directory is created automatically on first run
Data persists across container restarts
Deleting this directory will erase all data

Backup Procedures

Create a backup

# Using Docker
docker-compose exec db mysqldump -u root -p123 KnowledgeCheckr > backup_$(date +%Y%m%d).sql

# Direct MySQL access
mysqldump -h localhost -P 3305 -u root -p123 KnowledgeCheckr > backup.sql

Restore from backup

# Using Docker
docker-compose exec -T db mysql -u root -p123 KnowledgeCheckr < backup.sql

# Direct MySQL access
mysql -h localhost -P 3305 -u root -p123 KnowledgeCheckr < backup.sql

Verify restoration

docker-compose exec db mysql -u root -p123 KnowledgeCheckr -e "SELECT COUNT(*) FROM users;"

Testing Database Connection

Verify the application can connect to the database:

Check database accessibility

# From within Docker Compose
docker-compose exec knowledgeCheckr npm run validateEnvironment

Test MySQL connection directly

docker-compose exec db mysql -u root -p123 -e "SELECT VERSION();"

Verify tables exist

docker-compose exec db mysql -u root -p123 KnowledgeCheckr -e "SHOW TABLES;"

Migration Script Details

The compose-init-script.sh performs the following:

#!/usr/bin/env sh
# Accumulate Drizzle migration SQL files into a single init.sql

MIGRATIONS_DIR="drizzle/migrations"
OUTPUT_FILE="schema/init.sql"

# Create output directory
mkdir -p schema

# Concatenate all .sql files in order
for f in "$MIGRATIONS_DIR"/*.sql; do
  # Remove statement-breakpoint comments
  tr -d '\r' < "$f" | sed -E 's/[[:space:]]*-->[[:space:]]*statement-breakpoint[[:space:]]*//g' >> "$OUTPUT_FILE"
done

Why remove statement-breakpoints?

Statement breakpoints are Drizzle-specific markers
MySQL doesn’t recognize them
Removing them ensures clean initialization

Troubleshooting

”Can’t connect to MySQL server”

Check:

Database container is running: docker-compose ps
Port is not already in use: lsof -i :3305
DATABASE_HOST matches the service name
Firewall allows connections on port 3306/3305

”Access denied for user”

Solutions:

Verify credentials in .env match docker-compose.yml
Check MYSQL_ROOT_HOST allows connections from application host
Ensure user has necessary privileges:
```
SHOW GRANTS FOR 'root'@'%';
```

“Table doesn’t exist”

Causes:

Migrations not applied
Database initialized with wrong schema
Using wrong database name

Solution:

# Recreate database with fresh migrations
docker-compose down -v
docker-compose up -d

“Too many connections”

Increase MySQL max connections:

db:
  command: --max-connections=500

Migration conflicts

If you have manual schema changes conflicting with migrations:

Export current schema:

mysqldump -u root -p123 --no-data KnowledgeCheckr > current_schema.sql

Compare with migration output:

diff current_schema.sql database/drizzle/migrations/latest.sql

Resolve conflicts by updating schema files or creating new migrations

Production Considerations

Default Docker Compose credentials are for development only. Always change these in production.

Security

Change default passwords: Use strong, unique passwords
Restrict root access: Create application-specific users with limited privileges
Disable external port: Remove port mapping if external access not needed
Enable SSL: Configure MySQL to use SSL/TLS connections

Performance

Configure buffer pool: Allocate 70-80% of available RAM to InnoDB buffer pool
```
db:
  command: --innodb-buffer-pool-size=2G
```
Enable query cache: For read-heavy workloads
Index optimization: Review and optimize indexes based on query patterns
Connection pooling: Configure application connection pool settings

Monitoring

Enable slow query log:

db:
  command: --slow-query-log=1 --long-query-time=2

Monitor disk usage: Ensure sufficient space for data growth
Track connection usage: Alert on connection limit approaching
Query performance: Use EXPLAIN to analyze slow queries

Next Steps

Configure environment variables
Complete Docker deployment
Review database schema in database/drizzle/schema.ts

Architecture

API Reference

Deployment

Database Requirements

Automatic Setup (Docker)

Manual Database Setup

Drizzle ORM Configuration

Key Configuration Points

Working with Migrations

Generating New Migrations

Applying Migrations

Migration File Structure

Database Schema Overview

Connection Configuration

Docker Compose Setup

Application Connection

Data Persistence

Volume Management

Backup Procedures

Testing Database Connection

Migration Script Details

Troubleshooting

”Can’t connect to MySQL server”

”Access denied for user”

“Table doesn’t exist”

“Too many connections”

Migration conflicts

Production Considerations

Security

Performance

Monitoring

Next Steps

Build docs developers (and LLMs) love

Architecture

API Reference

Deployment

​Database Requirements

​Automatic Setup (Docker)

​Manual Database Setup

​Drizzle ORM Configuration

​Key Configuration Points

​Working with Migrations

​Generating New Migrations

​Applying Migrations

​Migration File Structure

​Database Schema Overview

​Connection Configuration

​Docker Compose Setup

​Application Connection

​Data Persistence

​Volume Management

​Backup Procedures

​Testing Database Connection

​Migration Script Details

​Troubleshooting

​”Can’t connect to MySQL server”

​”Access denied for user”

​“Table doesn’t exist”

​“Too many connections”

​Migration conflicts

​Production Considerations

​Security

​Performance

​Monitoring

​Next Steps

Build docs developers (and LLMs) love

Database Requirements

Automatic Setup (Docker)

Manual Database Setup

Drizzle ORM Configuration

Key Configuration Points

Working with Migrations

Generating New Migrations

Applying Migrations

Migration File Structure

Database Schema Overview

Connection Configuration

Docker Compose Setup

Application Connection

Data Persistence

Volume Management

Backup Procedures

Testing Database Connection

Migration Script Details

Troubleshooting

”Can’t connect to MySQL server”

”Access denied for user”

“Table doesn’t exist”

“Too many connections”

Migration conflicts

Production Considerations

Security

Performance

Monitoring

Next Steps