Feature Request: Multi-Database Support

[ameykusurkar]

Feature Request: Multi-Database Support

Summary

Add support for multiple databases to Nandi, allowing users to write safe migrations for different database connections in Rails applications that use ActiveRecord's multiple database feature.

Background

Rails 6.0+ introduced official support for multiple databases, allowing applications to connect to different databases for different purposes (e.g., primary database, analytics database, read replicas). Nandi currently assumes a single database connection, but many applications would benefit from safe migration support across multiple databases.

Proposed API Approaches

Preferences are (most to least): 3, 2, 1

Option 1: Database-Specific Migration Classes

Specify the database through class inheritance:

class AddWidgets < Nandi::Migration[:primary]
  def up
    create_table :widgets do |t|
      t.text :name
    end
  end
end

class AddAnalyticsEvents < Nandi::Migration[:analytics]  
  def up
    create_table :events do |t|
      t.jsonb :data
    end
  end
end

Generators:

rails generate nandi:migration add_widgets --db=primary
rails generate nandi:foreign_key users posts --db=analytics

Pros:

• Database is explicit in the class definition
• Type-safe approach that could catch database mismatches at compile time
• Familiar pattern similar to versioned migrations

Cons:

• Unusual Ruby syntax that may be confusing
• Would require significant changes to the base Migration class
• Less flexible for dynamic database selection

Option 2: Database Declaration Method

Declare the database within the migration class:

class AddWidgets < Nandi::Migration
  database :primary
  
  def up
    create_table :widgets do |t|
      t.text :name  
    end
  end
end

class AddAnalyticsEvents < Nandi::Migration
  database :analytics
  
  def up
    create_table :events do |t|
      t.jsonb :data
    end
  end
end

Generators:

rails generate nandi:migration add_widgets --db=primary

Pros:

• Clean, readable syntax
• Similar to existing class methods like set_lock_timeout
• Easy to implement with existing architecture
• Allows for database-specific configuration per migration

Cons:

• Database declaration could be missed/forgotten
• Runtime rather than compile-time database binding

Option 3: Directory-Based Organization

Organize migrations by database using directory structure:

db/safe_migrations/
├── primary/
│   └── 20240101000000_add_widgets.rb
├── analytics/ 
│   └── 20240101000001_add_events.rb
└── shared/
    └── 20240101000002_add_audit_log.rb

Alternatively, have primary (or default database) in db/safe_migrations, with sub folders only for other databases

Migrations use standard Nandi::Migration with database inferred from directory:

# db/safe_migrations/primary/20240101000000_add_widgets.rb
class AddWidgets < Nandi::Migration
  def up
    create_table :widgets do |t|
      t.text :name
    end
  end
end

Generators:

rails generate nandi:migration add_widgets --db=primary
# Creates file in db/safe_migrations/primary/

Pros:

• Clear visual organization of migrations by database
• No changes needed to migration syntax
• Easy to see which migrations affect which database
• Supports shared migrations that could run on multiple databases

Cons:

• Requires restructuring existing projects
• Database selection is implicit rather than explicit
• More complex file management

Configuration

All approaches would support database-specific configuration:

Nandi.configure do |config|
  config.databases = {
    primary: {
      migration_directory: "db/safe_migrations/primary",
      output_directory: "db/migrate/primary", 
      lock_timeout: 5_000,
      statement_timeout: 1_500
    },
    analytics: {
      migration_directory: "db/safe_migrations/analytics",
      output_directory: "db/migrate/analytics",
      lock_timeout: 10_000,  # Longer timeout for analytics
      statement_timeout: 30_000
    }
  }
end

However, could directories be inferred from database name?

Compilation Commands

# Compile specific database
rails generate nandi:compile --db=primary
rails generate nandi:compile --db=analytics

# Compile all databases  
rails generate nandi:compile

Generated Output

Compiled migrations would follow ActiveRecord's multi-database patterns:

# db/migrate/primary/20240101000000_add_widgets.rb
class AddWidgets < ActiveRecord::Migration[8.0]
  def connection
    ApplicationRecord.connection
  end
  
  set_lock_timeout(5000)
  set_statement_timeout(1500)
  
  def up
    create_table(:widgets) do |t|
      t.column(:name, :text)
    end
  end
end

Implementation Considerations

1. Backwards Compatibility: How should existing single-database projects migrate?
2. Lockfiles: How should .nandilock.yml handle multiple databases?