Database governance built for humans and agents — controlling changes and access across every major database.
Remove IsCaseSensitive from Database Store Lookups
Date: 2025-12-20 Status: Proposed
Problem Statement
The IsCaseSensitive field in FindDatabaseMessage adds unnecessary complexity to database store lookups:
Current behavior:
- When
IsCaseSensitive=true: Usesdb.name = ?(exact match) - When
IsCaseSensitive=false: UsesLOWER(db.name) = LOWER(?)(case-insensitive)
Why it exists:
- Originally intended to handle database engines where object names are case-insensitive (MySQL with
lower_case_table_names, TiDB, MSSQL) - Assumption: User might provide "mydb" when database is stored as "MyDB"
Why it's unnecessary:
Database names are synced exactly - The sync pipeline stores database names exactly as returned by the engine with no normalization (
mysql/sync.go:102,tidb/sync.go:94,pg/pg.go:305)Clients use exact names - API clients get resource names from our responses (e.g.,
GET /v1/instances/prod/databasesreturns{name: "instances/prod/databases/MyDB"}) and reuse them verbatim in subsequent requestsInternal references are exact - Tasks, rollouts, and other internal components store the exact database name from database records
Performance cost -
LOWER(db.name) = LOWER(?)prevents efficient index usage on theUNIQUE(instance, name)constraint
Proposed Solution
Remove the IsCaseSensitive field entirely and always use exact matching in store layer queries.
Code change:
// Before (database.go:147-151)
if find.IsCaseSensitive {
where.And("db.name = ?", *v)
} else {
where.And("LOWER(db.name) = LOWER(?)", *v)
}
// After
where.And("db.name = ?", *v)
Scope of changes:
Store layer (remove case-insensitive logic):
backend/store/database.go- RemoveIsCaseSensitivefield fromFindDatabaseMessage(line 71)- Simplify query logic (lines 147-151) to always use exact match
- Remove ~15 call sites that set
IsCaseSensitive: store.IsObjectCaseSensitive(instance):backend/api/v1/database_service.gobackend/api/v1/database_service_changelog.gobackend/api/v1/common.gobackend/api/v1/rollout_service.gobackend/component/export/resources.gobackend/component/sampleinstance/manager.gobackend/runner/approval/runner.gobackend/runner/taskrun/database_migrate_executor.go
Query/LSP layer (keep existing logic):
- No changes to
IsObjectCaseSensitive()function inbackend/store/instance.go:348 - This function is still needed for Query/LSP identifier matching
- When users write SQL like
SELECT * FROM mydb.users, the Query/LSP layer needs to match "mydb" against stored metadata using case-sensitivity rules - Continues using
IsObjectCaseSensitive(instance)for auto-completion and span analysis
- No changes to
Parser contexts (keep existing):
backend/plugin/parser/base/- KeepIsCaseSensitivefield in parser contexts- These are unrelated to store lookups, used for SQL parsing rules
Migration Strategy
- No data migration needed - Only changing query behavior
- No API changes - This is internal to the store layer
- Backward compatible - Exact matching is stricter but represents correct behavior
Edge Case Handling
If a database name mismatch occurs (e.g., from manual DB manipulation or API bug):
- Before: Case-insensitive match might succeed incorrectly
- After: Returns "not found" - correct fail-fast behavior
Validation
Before implementing the change:
Verify no case duplicates exist:
SELECT instance, name FROM db GROUP BY instance, LOWER(name) HAVING COUNT(*) > 1;Expected: Zero rows (enforced by
UNIQUE(instance, name)constraint)Grep for manual database name construction:
grep -r "DatabaseName.*=" backend/ | grep -v "database.DatabaseName"Verify callers get names from database records, not constructing them
Testing Strategy
Existing tests should pass - If tests break, they were relying on incorrect fuzzy matching
Add explicit test for exact matching:
func TestGetDatabase_ExactMatchRequired(t *testing.T) { // Create database "MyDatabase" // Query with "mydatabase" should return nil (not found) // Query with "MyDatabase" should succeed }Integration test: Sync a real MySQL/TiDB instance, verify databases are found with their exact synced names
Benefits
- Performance - Exact match enables index usage on
UNIQUE(instance, name)constraint - Simplicity - Removes conditional query logic and boolean flag
- Correctness - Fail fast on mismatches rather than fuzzy matching
- Clarity - Separates concerns: store lookups use exact match, Query/LSP uses case rules
Implementation Order
- Run validation queries to verify no case-mismatch scenarios exist
- Remove
IsCaseSensitivefield fromFindDatabaseMessagestruct - Simplify query logic to always use exact match
- Remove
IsCaseSensitiveassignments from all ~15 call sites - Run full test suite
- Manual testing with MySQL (case-insensitive) and PostgreSQL (case-sensitive) instances
Rollout Safety
- Low risk - Makes queries stricter, not looser
- Observable - Issues manifest as "database not found" errors (easy to spot in logs)
- Reversible - Can restore
IsCaseSensitivelogic if needed (though it shouldn't be)