Anyone thinking about porting Active Record Doctor to Ecto?

Great question! Active Record Doctor is indeed fantastic for Rails apps. Here’s what exists in the Phoenix/Elixir and PostgreSQL worlds:

1. Phoenix/Elixir World

Unfortunately, there’s no direct equivalent to Active Record Doctor for Phoenix/Ecto yet. However, there are some tools that provide partial coverage:

Ecto-specific Tools:

• Ecto.Linter - Basic linting for Ecto schemas and queries (though less active)
• Boundary - Architectural analysis (not database-focused)
• Credo - Code analysis that includes some database-related checks

What’s Missing:

The Phoenix ecosystem could really use a tool that checks for:

• Missing foreign key constraints
• Missing indexes on associations
• Schema/validation mismatches
• Unused indexes
• Missing presence validations for NOT NULL columns

This would be a great open-source opportunity for someone to build an “Ecto Doctor” equivalent!

2. PostgreSQL Level

There are several excellent PostgreSQL-native tools:

Schema Analysis:

• pg_insights - Queries to detect schema issues
• pgbadger - Log analyzer (performance-focused)
• pg_stat_statements - Built-in query performance tracking

Database Health:

• pg_activity - Real-time activity monitoring
• pg_top - Activity monitoring
• PostgREST - Can expose schema information via API

Custom Queries:

You can write PostgreSQL queries to detect many issues Active Record Doctor finds:

-- Find tables without primary keys
SELECT schemaname, tablename 
FROM pg_tables 
WHERE schemaname = 'public' 
AND tablename NOT IN (
  SELECT tablename 
  FROM pg_indexes 
  WHERE indexname LIKE '%_pkey'
);

-- Find foreign keys without indexes
SELECT conname, conrelid::regclass, confrelid::regclass
FROM pg_constraint 
WHERE contype = 'f' 
AND NOT EXISTS (
  SELECT 1 FROM pg_index 
  WHERE indrelid = conrelid 
  AND indkey[0] = conkey[1]
);

Recommendation

For your Phoenix app, I’d suggest:

1. Create custom Mix tasks that run PostgreSQL queries to detect common issues
2. Build a simple “Ecto Doctor” - this could be a valuable contribution to the community
3. Use PostgreSQL-native tools for performance and schema analysis

Given your appreciation for C.J. Date’s rigorous approach to database design, you might actually enjoy building such a tool - it would enforce good relational practices in the Elixir ecosystem!