Schema Versions

Store and retrieve database schema versions with metadata tracking.

Overview

Schema Versions feature stores snapshots of your database schema with:

Full schema content (SQL or Ruby format)
MD5 content hash for deduplication
PostgreSQL version
Format type (sql/rb)
Output mode (single_file/multi_file)
Creation timestamp
Automatic cleanup based on retention limit
ZIP archive storage for multi-file schemas

Setup

Enable in Configuration

# config/initializers/better_structure_sql.rb
BetterStructureSql.configure do |config|
  config.enable_schema_versions = true
  config.schema_versions_limit = 10  # Keep last 10, 0 = unlimited
end

Install Migration

rails generate better_structure_sql:install
rails db:migrate

Creates table: better_structure_sql_schema_versions

Database Schema

CREATE TABLE better_structure_sql_schema_versions (
  id bigserial PRIMARY KEY,
  content text NOT NULL,
  content_hash varchar(32) NOT NULL,
  pg_version varchar NOT NULL,
  format_type varchar NOT NULL,
  output_mode varchar NOT NULL DEFAULT 'single_file',
  zip_archive bytea,
  file_count integer,
  created_at timestamp(6) NOT NULL
);

CREATE INDEX index_schema_versions_on_created_at
  ON better_structure_sql_schema_versions (created_at DESC);

CREATE INDEX index_schema_versions_on_content_hash
  ON better_structure_sql_schema_versions (content_hash);

Usage

Store Schema Version

# Store after migration
rails db:migrate
rails db:schema:store

List Versions

rails db:schema:versions

Output:

Schema Versions (5 total):
  #5 - 2024-01-20 14:30:22 UTC - PostgreSQL 14.5 (sql) - 15.2 KB
  #4 - 2024-01-19 10:15:45 UTC - PostgreSQL 14.5 (sql) - 14.8 KB
  #3 - 2024-01-18 09:22:33 UTC - PostgreSQL 14.5 (sql) - 14.5 KB

Programmatic Access

# Get latest version
latest = BetterStructureSql::SchemaVersions.latest
puts latest.content
puts latest.pg_version  # "14.5"
puts latest.format_type # "sql"
puts latest.created_at  # 2024-01-20 14:30:22 UTC

# Get all versions
versions = BetterStructureSql::SchemaVersions.all_versions

# Find specific version
version = BetterStructureSql::SchemaVersions.find(3)

# Count versions
count = BetterStructureSql::SchemaVersions.count

Automatic Deduplication

Schema versions are automatically deduplicated using MD5 content hashing:

# First store - schema saved
rails db:schema:store
# => Stored schema version #1 (hash: a3b2c1d4...)

# Second store - no changes, skipped
rails db:schema:store
# => Skipped storage - schema unchanged (matches version #1)

# After migration - schema changed, stored
rails db:migrate
rails db:schema:store
# => Stored schema version #2 (hash: e5f6g7h8...)

Benefits:

Storage efficiency: Only actual schema changes create versions
Clear audit trail: Version history shows meaningful evolution
Production friendly: Safe to run db:schema:store on every deploy

Hash Calculation

Content hashes are calculated from:

Single-file mode: MD5 of structure.sql or schema.rb
Multi-file mode: MD5 of combined content from all .sql files in order

Manifest JSON is excluded (metadata only, not schema content).

# Check if current schema matches stored version
latest_hash = BetterStructureSql::SchemaVersions.latest_hash
current_hash = BetterStructureSql::SchemaVersions.calculate_hash_from_file('db/structure.sql')

if latest_hash == current_hash
  puts "Schema unchanged"
else
  puts "Schema has changed"
end

Automatic Cleanup

When schema_versions_limit is set, old versions are automatically deleted.

# Keep last 10 versions
config.schema_versions_limit = 10

# Keep all versions
config.schema_versions_limit = 0

# Manual cleanup
BetterStructureSql::SchemaVersions.cleanup!

Cleanup happens automatically after db:schema:store (only when storage occurs, not when skipped).

Model Reference

class BetterStructureSql::SchemaVersion < ActiveRecord::Base
  # Attributes
  # - id: integer
  # - content: text (schema SQL/Ruby content)
  # - content_hash: string (32-character MD5 hexdigest)
  # - pg_version: string (PostgreSQL version)
  # - format_type: string ('sql' or 'rb')
  # - output_mode: string ('single_file' or 'multi_file')
  # - zip_archive: binary (ZIP archive for multi-file schemas)
  # - file_count: integer (number of SQL files in multi-file mode)
  # - created_at: datetime

  # Class Methods
  def self.store_current
    # Store current schema as new version
    # Automatically skips if content_hash matches latest version
  end

  def self.latest
    # Get most recent version
  end

  def self.latest_hash
    # Get content_hash of most recent version
  end

  def self.all_versions
    # Get all versions ordered by created_at DESC
  end

  def self.find_by_hash(hash)
    # Find version by content_hash
  end

  def self.hash_exists?(hash)
    # Check if content_hash exists
  end

  def self.cleanup!
    # Remove old versions per retention limit
  end

  # Instance Methods
  def hash_matches?(other_hash)
    # Check if content_hash matches given hash
  end

  def size
    # Content size in bytes
  end

  def formatted_size
    # Human-readable size (e.g., "15.2 KB")
  end
end

API Endpoint Example

Expose schema versions to developers via authenticated endpoint.

Controller Example

# app/controllers/api/v1/schema_versions_controller.rb
module Api
  module V1
    class SchemaVersionsController < ApplicationController
      before_action :authenticate_developer!

      # GET /api/v1/schema_versions
      def index
        versions = BetterStructureSql::SchemaVersions.all_versions
        render json: {
          versions: versions.map do |v|
            {
              id: v.id,
              pg_version: v.pg_version,
              format_type: v.format_type,
              created_at: v.created_at,
              size: v.formatted_size
            }
          end
        }
      end

      # GET /api/v1/schema_versions/latest
      def latest
        version = BetterStructureSql::SchemaVersions.latest
        render json: {
          id: version.id,
          content: version.content,
          pg_version: version.pg_version,
          format_type: version.format_type,
          created_at: version.created_at
        }
      end

      # GET /api/v1/schema_versions/:id
      def show
        version = BetterStructureSql::SchemaVersions.find(params[:id])
        render json: {
          id: version.id,
          content: version.content,
          pg_version: version.pg_version,
          format_type: version.format_type,
          created_at: version.created_at
        }
      end

      private

      def authenticate_developer!
        # Implement authentication
        # Example: token, OAuth, session, etc.
        authenticate_or_request_with_http_token do |token, options|
          ActiveSupport::SecurityUtils.secure_compare(
            token,
            ENV['SCHEMA_API_TOKEN']
          )
        end
      end
    end
  end
end

Routes

# config/routes.rb
namespace :api do
  namespace :v1 do
    resources :schema_versions, only: [:index, :show] do
      collection do
        get :latest
      end
    end
  end
end

Client Usage

# Get latest schema
curl -H "Authorization: Bearer YOUR_TOKEN" \
  https://api.yourapp.com/api/v1/schema_versions/latest

# List all versions
curl -H "Authorization: Bearer YOUR_TOKEN" \
  https://api.yourapp.com/api/v1/schema_versions

# Get specific version
curl -H "Authorization: Bearer YOUR_TOKEN" \
  https://api.yourapp.com/api/v1/schema_versions/5

Download Schema Script

#!/bin/bash
# scripts/download_schema.sh

TOKEN="your_api_token"
API_URL="https://api.yourapp.com/api/v1/schema_versions/latest"

curl -H "Authorization: Bearer $TOKEN" "$API_URL" | \
  jq -r '.content' > db/structure.sql

echo "Schema downloaded to db/structure.sql"

Use Cases

Developer Onboarding

New developers download latest schema instead of running migrations:

# Download latest schema
./scripts/download_schema.sh

# Load schema
rails db:schema:load

# Start working
rails db:seed

Schema Comparison

# Compare two versions
v1 = BetterStructureSql::SchemaVersions.find(10)
v2 = BetterStructureSql::SchemaVersions.find(15)

File.write('/tmp/v1.sql', v1.content)
File.write('/tmp/v2.sql', v2.content)

system('diff -u /tmp/v1.sql /tmp/v2.sql')

Rollback Schema

# Restore previous version
old_version = BetterStructureSql::SchemaVersions.find(5)

File.write('db/structure.sql', old_version.content)
system('rails db:schema:load')

CI/CD Schema Validation

# spec/schema_spec.rb
RSpec.describe 'Database Schema' do
  it 'matches latest stored version' do
    BetterStructureSql::Dumper.dump_to_string

    latest = BetterStructureSql::SchemaVersions.latest
    current = File.read('db/structure.sql')

    expect(current).to eq(latest.content)
  end
end

Works with schema.rb

Schema versions work with both structure.sql and schema.rb:

# Store schema.rb version
content = File.read('db/schema.rb')

# Get database version (adapter-aware)
db_version = case ActiveRecord::Base.connection.adapter_name
             when 'PostgreSQL'
               ActiveRecord::Base.connection.select_value('SHOW server_version')
             when 'Mysql2', 'Trilogy'
               ActiveRecord::Base.connection.select_value('SELECT VERSION()')
             when 'SQLite'
               ActiveRecord::Base.connection.select_value('SELECT sqlite_version()')
             else
               'unknown'
             end

BetterStructureSql::SchemaVersions.store(
  content: content,
  format_type: 'rb',
  pg_version: db_version  # Note: column name is pg_version but stores any DB version
)

Performance Considerations

Large Schemas

For very large schemas (>1MB):

Consider compression
Use pagination in API
Implement caching

Storage

Typical schema sizes:

Small app (10 tables): ~5-10 KB per version
Medium app (100 tables): ~50-100 KB per version
Large app (500 tables): ~200-500 KB per version

With 10 version limit:

Small: ~100 KB total
Medium: ~1 MB total
Large: ~5 MB total

Cleanup Strategy

# Aggressive cleanup (keep last 5)
config.schema_versions_limit = 5

# Conservative (keep last 50)
config.schema_versions_limit = 50

# Archive old versions before cleanup
BetterStructureSql::SchemaVersions.where('created_at < ?', 1.year.ago).each do |v|
  File.write("archive/schema_#{v.id}.sql", v.content)
end

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Schema Versions

Overview

Setup

Enable in Configuration

Install Migration

Database Schema

Usage

Store Schema Version

List Versions

Programmatic Access

Automatic Deduplication

Hash Calculation

Automatic Cleanup

Model Reference

API Endpoint Example

Controller Example

Routes

Client Usage

Download Schema Script

Use Cases

Developer Onboarding

Schema Comparison

Rollback Schema

CI/CD Schema Validation

Works with schema.rb

Performance Considerations

Large Schemas

Storage

Cleanup Strategy

Next Steps

FilesExpand file tree

schema_versions.md

Latest commit

History

schema_versions.md

File metadata and controls

Schema Versions

Overview

Setup

Enable in Configuration

Install Migration

Database Schema

Usage

Store Schema Version

List Versions

Programmatic Access

Automatic Deduplication

Hash Calculation

Automatic Cleanup

Model Reference

API Endpoint Example

Controller Example

Routes

Client Usage

Download Schema Script

Use Cases

Developer Onboarding

Schema Comparison

Rollback Schema

CI/CD Schema Validation

Works with schema.rb

Performance Considerations

Large Schemas

Storage

Cleanup Strategy

Next Steps