Database Guide

Overview

This guide covers database development practices for Tenki Cloud, including schema management, migrations, and query patterns.

Database Stack

• PostgreSQL: Primary database
• sqlc: Type-safe SQL query generation
• golang-migrate: Database migration management

Schema Management

Migrations

All database schema changes must be made through migrations:

# Create a new migration
make migration name=add_user_settings

# Run migrations
make migrate-up

# Rollback last migration
make migrate-down

Best Practices

1. Always include both up and down migrations
2. Keep migrations small and focused
3. Test rollbacks before merging
4. Never modify existing migrations

Query Development

We use sqlc for type-safe database queries:

Writing Queries

1. Add queries to pkg/db/queries/*.sql
2. Use named parameters: @param_name
3. Follow naming conventions:
   • GetUserByID for single row
   • ListUsersByOrg for multiple rows
   • CreateUser for inserts
   • UpdateUser for updates
   • DeleteUser for deletes

Generating Code

# Generate Go code from SQL
make sqlc

Performance

Indexing

• Add indexes for frequently queried columns
• Use composite indexes for multi-column queries
• Monitor slow query logs

Query Optimization

• Use EXPLAIN ANALYZE for query planning
• Avoid N+1 queries
• Batch operations when possible
• Use database views for complex queries

Testing

Unit Tests

• Mock database interfaces
• Test query logic separately from business logic

Integration Tests

• Use test database containers
• Clean up test data after each test
• Test migration up/down paths