microck/database implementation
Database schema design, migrations, query optimization with SQL, Exposed ORM, Flyway. Use for database, migration, schema, sql, flyway tags. Provides migration patterns, validation commands, rollback strategies.
npx skills add https://github.com/Microck/ordinary-claude-skills --skill Database ImplementationDomain-specific guidance for database schema design, migrations, and data modeling.
Load this Skill when task has tags:
database, migration, schema, sql, flywayexposed, orm, query, index, constraint# Gradle + Flyway
./gradlew flywayMigrate
# Test migration on clean database
./gradlew flywayClean flywayMigrate
# Check migration status
./gradlew flywayInfo
# Validate migrations
./gradlew flywayValidate
# Migration tests
./gradlew test --tests "*migration*"
# Database integration tests
./gradlew test --tests "*Repository*"
# All tests
./gradlew test
✅ Migration runs without errors on clean database
✅ Schema matches design specifications
✅ Indexes created correctly
✅ Constraints validate as expected
✅ Rollback works (if applicable)
✅ Tests pass with new schema
-- V001__create_users_table.sql
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email VARCHAR(255) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
name VARCHAR(255) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- Indexes
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created_at ON users(created_at);
-- V002__add_users_phone.sql
ALTER TABLE users
ADD COLUMN phone VARCHAR(20);
-- Add with default value
ALTER TABLE users
ADD COLUMN is_active BOOLEAN NOT NULL DEFAULT true;
-- V003__create_tasks_table.sql
CREATE TABLE tasks (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
title VARCHAR(500) NOT NULL,
user_id UUID NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
-- Foreign key with cascade
CONSTRAINT fk_tasks_user
FOREIGN KEY (user_id)
REFERENCES users(id)
ON DELETE CASCADE
);
CREATE INDEX idx_tasks_user_id ON tasks(user_id);
-- V004__create_user_roles.sql
CREATE TABLE user_roles (
user_id UUID NOT NULL,
role_id UUID NOT NULL,
assigned_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (user_id, role_id),
CONSTRAINT fk_user_roles_user
FOREIGN KEY (user_id)
REFERENCES users(id)
ON DELETE CASCADE,
CONSTRAINT fk_user_roles_role
FOREIGN KEY (role_id)
REFERENCES roles(id)
ON DELETE CASCADE
);
CREATE INDEX idx_user_roles_user_id ON user_roles(user_id);
CREATE INDEX idx_user_roles_role_id ON user_roles(role_id);
@Test
fun `migration V004 creates user_roles table`() {
// Arrange - Clean database
flyway.clean()
// Act - Run migrations
flyway.migrate()
// Assert - Check table exists
val tableExists = database.useConnection { connection ->
val meta = connection.metaData
val rs = meta.getTables(null, null, "user_roles", null)
rs.next()
}
assertTrue(tableExists, "user_roles table should exist after migration")
}
@Test
fun `user_roles enforces foreign key constraint`() {
// Arrange
val invalidUserId = UUID.randomUUID()
val role = createTestRole()
// Act & Assert
assertThrows<SQLException> {
database.transaction {
UserRoles.insert {
it[userId] = invalidUserId // Invalid - user doesn't exist
it[roleId] = role.id
}
}
}
}
Issue: Adding NOT NULL column to table with existing rows
ERROR: column "status" contains null values
What to try:
Example fix:
-- Step 1: Add nullable
ALTER TABLE tasks ADD COLUMN status VARCHAR(20);
-- Step 2: Update existing rows
UPDATE tasks SET status = 'pending' WHERE status IS NULL;
-- Step 3: Make NOT NULL
ALTER TABLE tasks ALTER COLUMN status SET NOT NULL;
Issue: Table A references B, B references A - which to create first?
What to try:
Example:
-- V001: Create tables without FKs
CREATE TABLE users (...);
CREATE TABLE profiles (...);
-- V002: Add foreign keys
ALTER TABLE users ADD CONSTRAINT fk_users_profile ...;
ALTER TABLE profiles ADD CONSTRAINT fk_profiles_user ...;
Issue: Creating index on large table times out
What to try:
Issue: ORM expects UUID but database has VARCHAR
What to try:
ALTER TABLE tasks ALTER COLUMN id TYPE UUID USING id::uuid;
Issue: Foreign key references table that doesn't exist yet
What to try:
If blocked: Report to orchestrator - migration order issue or missing prerequisite
⚠️ BLOCKED - Requires Senior Engineer
Issue: [Specific problem - migration fails, constraint violation, etc.]
Attempted Fixes:
- [What you tried #1]
- [What you tried #2]
- [Why attempts didn't work]
Root Cause (if known): [Your analysis]
Partial Progress: [What work you DID complete]
Context for Senior Engineer:
- Migration SQL: [Paste migration]
- Error output: [Database error]
- Related migrations: [Dependencies]
Requires: [What needs to happen]
object Users : UUIDTable("users") {
val email = varchar("email", 255).uniqueIndex()
val passwordHash = varchar("password_hash", 255)
val name = varchar("name", 255)
val createdAt = timestamp("created_at").defaultExpression(CurrentTimestamp())
val updatedAt = timestamp("updated_at").defaultExpression(CurrentTimestamp())
}
object Tasks : UUIDTable("tasks") {
val title = varchar("title", 500)
val userId = reference("user_id", Users)
val createdAt = timestamp("created_at").defaultExpression(CurrentTimestamp())
}
fun findTasksWithUser(userId: UUID): List<TaskWithUser> {
return (Tasks innerJoin Users)
.select { Tasks.userId eq userId }
.map { row ->
TaskWithUser(
task = rowToTask(row),
user = rowToUser(row)
)
}
}
Good (can rollback):
Difficult to rollback:
For complex migrations, document rollback steps:
-- Migration: V005__add_user_status.sql
ALTER TABLE users ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'active';
-- Rollback (document in comments):
-- ALTER TABLE users DROP COLUMN status;
✅ DO create indexes on:
❌ DON'T create indexes on:
-- ❌ BAD - Missing index, full table scan
SELECT * FROM users WHERE email = '[email protected]';
-- ✅ GOOD - Index on email column
CREATE INDEX idx_users_email ON users(email);
-- ❌ BAD - N+1 query problem
SELECT * FROM users; -- 1 query
SELECT * FROM tasks WHERE user_id = ?; -- N queries (one per user)
-- ✅ GOOD - Single query with JOIN
SELECT u.*, t.*
FROM users u
LEFT JOIN tasks t ON t.user_id = u.id;
❌ Don't modify existing migrations (create new one)
❌ Don't drop columns without data backup
❌ Don't forget indexes on foreign keys
❌ Don't use SELECT * in production queries
❌ Don't skip testing migrations on clean database
❌ Don't forget CASCADE behavior on foreign keys
❌ Don't create migrations that depend on data state
When reading task sections, prioritize:
requirements - What schema changes neededtechnical-approach - Migration strategydata-model - Entity relationshipsmigration - Specific SQL requirementsFor deeper patterns and examples, see:
Take microck/database implementation from the repository into ~/.claude/skills for personal
use, or into .claude/skills inside a project.
The agent identifies a skill by the name field in its header. Two skills with the
same name cannot sit side by side — one of them will be ignored.