🗄️ BetterStructureSql

⚠️ Beta Notice: Version 0.2.1 is feature-complete and production-ready for PostgreSQL. Multi-database support (MySQL, SQLite) is implemented but considered experimental. APIs are stable but may see minor refinements before v1.0. We welcome feedback and contributions!

Rails' database dump tools (pg_dump, mysqldump, etc.) create noisy structure.sql files with version-specific comments, inconsistent formatting, and metadata that pollutes git diffs.

BetterStructureSql uses pure Ruby introspection to generate clean schema files:

• PostgreSQL: Installation → | Rails Guide →
• MySQL: Installation → | Rails Guide →
• SQLite: Installation → | Rails Guide →

📖 See Feature Compatibility Matrix for detailed comparison.

• Pure Ruby implementation - No external tool dependencies (pg_dump, mysqldump, sqlite3 CLI)
• Multi-database adapter pattern - Auto-detects database type from ActiveRecord connection
• Clean structure.sql - Only essential schema information
• Complete database support:
  • Tables with all column types and defaults
  • Primary keys, foreign keys, and constraints
  • Indexes (including partial, unique, and expression indexes)
  • Views (and materialized views for PostgreSQL)
  • Functions/stored procedures and triggers (database-dependent)
  • Extensions (PostgreSQL)
  • Sequences (PostgreSQL)
  • Custom types and enums (PostgreSQL, MySQL SET/ENUM)

• Massive schema support - Designed to handle tens of thousands of database objects
• Directory-based output - Split schema across organized, numbered directories
• Smart chunking - 500 LOC per file (configurable) with intelligent overflow handling
• Better git diffs - See only changed files, not entire schema
• ZIP downloads - Download complete directory structure as archive
• Easy navigation - Find tables quickly in 05_tables/, triggers in 09_triggers/, etc.

• Store schema versions in database with metadata
• Hash-based deduplication - Automatically skip storing when schema unchanged
• Track database type and version, format type (SQL/Ruby), creation timestamp
• ZIP archive storage for multi-file schemas
• Configurable retention policy (keep last N versions)
• Browse and download versions via web UI (mountable Rails engine)
• Works with both structure.sql and schema.rb
• Works across all database types (PostgreSQL, MySQL, SQLite)
• Restore from any stored version

• Mountable Rails Engine - Browse schema versions in any Rails app
• Bootstrap 5 interface - No asset compilation required (CDN-based)
• View schema versions - List, view formatted schema, download raw text
• Configurable authentication - Integrate with Devise, Pundit, or custom auth
• Developer onboarding - Easy access to latest schema for new team members

• Drop-in replacement: rake db:schema:dump → uses BetterStructureSql (when enabled)
• Configuration via config/initializers/better_structure_sql.rb
• Rake Tasks:
  • db:schema:dump_better - Explicitly dump schema using BetterStructureSql
  • db:schema:load_better - Load schema (supports both file and directory mode)
  • db:schema:store - Store current schema as a version in database
  • db:schema:versions - List all stored schema versions
  • db:schema:cleanup - Remove old versions based on retention limit
  • db:schema:restore[VERSION_ID] - Restore database from specific version

• Single command setup - docker compose up for full environment
• PostgreSQL included - No local database installation needed
• Live code reloading - Changes reflect immediately
• Integration app - Test and demo environment included

# Gemfile
gem 'better_structure_sql'
gem 'pg'  # For PostgreSQL (or 'mysql2' for MySQL, or 'sqlite3' for SQLite)

Database adapter is auto-detected from your ActiveRecord::Base.connection.adapter_name. No manual configuration needed!

bundle install
rails generate better_structure_sql:install
rails db:schema:dump_better

🎉 Your db/structure.sql is now clean and maintainable!

BetterStructureSql automatically tracks schema evolution by storing versions in your database. Hash-based deduplication ensures only meaningful schema changes are recorded.

When you run rails db:schema:store, the gem:

1. Reads your current schema files (single or multi-file)
2. Calculates MD5 hash of the complete schema content
3. Compares with the most recent stored version's hash
4. Skips storage if hash matches (no changes detected) ✨
5. Creates new version if hash differs (schema changed)

# After migrations, dump and store schema
rails db:migrate
rails db:schema:dump_better
rails db:schema:store

# First run (no previous version)
# =>
# Stored schema version #1
#   Format: sql
#   Mode: single_file
#   PostgreSQL: 15.4
#   Size: 45.2 KB
#   Hash: a3f5c9d2e8b1f4a6c7e9d3f1b5a8c2e4
#   Total versions: 1

# Second run (no schema changes)
# =>
# No schema changes detected
#   Current schema matches version #1
#   Hash: a3f5c9d2e8b1f4a6c7e9d3f1b5a8c2e4
#   No new version stored
#   Total versions: 1

# After adding a table
rails db:migrate  # Adds new table
rails db:schema:dump_better
rails db:schema:store

# =>
# Stored schema version #2
#   Format: sql
#   Mode: single_file
#   PostgreSQL: 15.4
#   Size: 48.7 KB
#   Hash: b7e2d1c4f9a6c3e5d8b2f1a4c9e7d3b6
#   Total versions: 2

Perfect for deployment automation:

# config/deploy.rb or GitHub Actions
namespace :deploy do
  task :update_schema do
    # Run migrations (may be zero)
    # Rails automatically dumps schema after migrations
    execute :rake, 'db:migrate'

    # Store schema version only if changed (automatic deduplication)
    execute :rake, 'db:schema:store'
  end
end

Benefits in Production:

• ✅ Deploys without migrations don't create duplicate versions
• ✅ Developers see clean schema evolution timeline
• ✅ Storage efficient (no duplicate content)
• ✅ Clear audit trail of actual schema changes

# List all versions with hashes
rails db:schema:versions

Schema Versions (3 total)

ID   | Format | Mode        | Files | PostgreSQL | Hash     | Created             | Size
-----|--------|-------------|-------|------------|----------|---------------------|-------
3    | sql    | multi_file  | 47    | 15.4       | a3f5c9d2 | 2025-01-20 14:30:15 | 125 KB
2    | sql    | single_file | -     | 15.4       | b7e2d1c4 | 2025-01-19 10:15:42 | 98 KB
1    | sql    | single_file | -     | 15.3       | c9f8a3b2 | 2025-01-18 08:45:30 | 85 KB

# config/initializers/better_structure_sql.rb
BetterStructureSql.configure do |config|
  # Enable schema versioning
  config.enable_schema_versions = true

  # Retain 10 most recent unique versions (0 = unlimited)
  config.schema_versions_limit = 10
end

Developers can view stored schema versions via the web UI without database access:

# config/routes.rb
authenticate :user, ->(user) { user.admin? } do
  mount BetterStructureSql::Engine, at: '/schema_versions'
end

Navigate to /schema_versions to browse versions, view formatted schema, and download raw SQL files.

📖 See Schema Versioning Documentation for complete details.

Get started with a fully configured development environment in seconds:

# Start PostgreSQL + Rails integration app
docker compose up

# Visit http://localhost:3000

See DOCKER.md for complete Docker documentation.

Visit the full documentation site →

Interactive documentation with tutorials, database-specific guides, and real-world examples showing how to use SQL databases to their fullest with BetterStructureSql. Features include:

• Step-by-step tutorials for PostgreSQL, MySQL, and SQLite
• Real-world examples using advanced database features (triggers, views, functions)
• Production deployment guides with automatic schema versioning
• API reference and configuration examples
• AI-friendly multi-file schema benefits

• Installation - Setup and configuration
• Configuration - All configuration options
• Usage - Rake tasks and examples
• Schema Versions - Version storage feature
• Troubleshooting - Common issues and solutions
• Multi-File Schema Output - Handle massive schemas
• Web UI Engine - Browse versions via web interface
• Docker Development - Complete Docker environment guide
• Testing - RSpec testing guide
• MVP Phases - Implementation roadmap

• Multi-Database Architecture - Overview and feature matrix
• Database Adapters Architecture - Technical deep dive
• Implementation Phases - Phased rollout plan

--
-- PostgreSQL database dump
--

-- Dumped from database version 14.5
-- Dumped by pg_dump version 14.5

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;
-- ... 50+ more lines ...

SET client_encoding = 'UTF8';

-- Extensions
CREATE EXTENSION IF NOT EXISTS plpgsql
  WITH SCHEMA pg_catalog;
CREATE EXTENSION IF NOT EXISTS pgcrypto
  WITH SCHEMA public;

-- Tables
CREATE TABLE users (
  id bigserial PRIMARY KEY,
  email varchar NOT NULL,
  created_at timestamp(6) NOT NULL,
  updated_at timestamp(6) NOT NULL
);

CREATE INDEX index_users_on_email
  ON users (email);

# config/initializers/better_structure_sql.rb
BetterStructureSql.configure do |config|
  # Single file output (default)
  config.output_path = 'db/structure.sql'

  # Replace default rake db:schema:dump (opt-in, default: false)
  # When false, use explicit tasks: rails db:schema:dump_better
  config.replace_default_dump = false
  config.replace_default_load = false

  # Schema version storage (optional)
  config.enable_schema_versions = true
  config.schema_versions_limit = 10  # Keep last 10 versions (0 = unlimited)

  # Customize output (feature toggles)
  config.include_extensions = true
  config.include_functions = true
  config.include_triggers = true
  config.include_views = true
  config.include_materialized_views = true  # PostgreSQL only
  config.include_domains = true             # PostgreSQL only
  config.include_sequences = true           # PostgreSQL only
  config.include_custom_types = true        # PostgreSQL ENUM, MySQL ENUM/SET
  # config.include_rules = false            # Not yet implemented
  # config.include_comments = false         # Not yet implemented

  # Search path and schema filtering
  config.search_path = '"$user", public'
  config.schemas = ['public']               # Which schemas to dump
end

💡 Recommended: Use db/schema directory mode for projects with 100+ tables for better git diffs, easier navigation, and AI-friendly organization.

# config/initializers/better_structure_sql.rb
BetterStructureSql.configure do |config|
  # Multi-file output - splits schema across directories
  config.output_path = 'db/schema'

  # Chunking configuration
  config.max_lines_per_file = 500        # Soft limit per file (default: 500)
  config.overflow_threshold = 1.1        # 10% overflow allowed (default: 1.1)
  config.generate_manifest = true        # Create _manifest.json (default: true)

  # Schema version storage with ZIP archives
  config.enable_schema_versions = true
  config.schema_versions_limit = 10

  # Feature toggles (same as single-file mode)
  config.include_extensions = true
  config.include_functions = true
  config.include_triggers = true
  config.include_views = true
  config.include_materialized_views = true
  config.include_domains = true
  config.include_sequences = true
  config.include_custom_types = true

  # Formatting options
  config.indent_size = 2                    # SQL indentation (default: 2)
  config.add_section_spacing = true         # Add blank lines between sections
  config.sort_tables = true                 # Sort tables alphabetically
end

When using config.output_path = 'db/schema', your schema is organized by type with numbered directories indicating load order:

db/schema/
├── _header.sql              # SET statements and search path
├── _manifest.json           # Metadata and load order
├── 01_extensions/
│   └── 000001.sql
├── 02_types/
│   └── 000001.sql
├── 03_functions/
│   └── 000001.sql
├── 04_sequences/
│   └── 000001.sql
├── 05_tables/
│   ├── 000001.sql          # ~500 lines per file
│   ├── 000002.sql
│   └── 000003.sql
├── 06_indexes/
│   └── 000001.sql
├── 07_foreign_keys/
│   └── 000001.sql
├── 08_views/
│   └── 000001.sql
├── 09_triggers/
│   └── 000001.sql
├── 10_comments/
│   └── 000001.sql
└── 20_migrations/
    └── 000001.sql

Benefits for Large Schemas:

• ✅ Memory efficient - incremental file writing
• ✅ Git friendly - only changed files in diffs
• ✅ Easy navigation - find specific tables in 05_tables/, triggers in 09_triggers/, etc.
• ✅ ZIP downloads - complete directory as single archive
• ✅ Scalable - handles 50,000+ database objects
• ✅ AI-friendly - 500-line chunks work better with LLM context windows

Manifest File (_manifest.json):

The manifest tracks metadata and provides load order information:

{
  "version": "1.0",
  "total_files": 11,
  "total_lines": 2345,
  "max_lines_per_file": 500,
  "directories": {
    "01_extensions": { "files": 1, "lines": 3 },
    "02_types": { "files": 1, "lines": 13 },
    "03_functions": { "files": 1, "lines": 332 },
    "04_sequences": { "files": 1, "lines": 289 },
    "05_tables": { "files": 2, "lines": 979 },
    "06_indexes": { "files": 1, "lines": 397 },
    "07_foreign_keys": { "files": 1, "lines": 67 },
    "08_views": { "files": 1, "lines": 217 },
    "09_triggers": { "files": 1, "lines": 35 },
    "10_comments": { "files": 1, "lines": 9 },
    "20_migrations": { "files": 1, "lines": 13 }
  }
}

This example shows a real schema with 2,345 lines split across 11 files. The 05_tables directory has 2 files because the tables exceed the 500-line limit.

# Dump schema using BetterStructureSql (explicit)
rails db:schema:dump_better

# Load schema from file or directory
rails db:schema:load_better

Store Current Schema

# Store the current schema as a version in the database
rails db:schema:store

This command:

• Reads your current db/structure.sql or db/schema directory
• Stores it in the better_structure_sql_schema_versions table
• Includes metadata: format type, output mode, database version, file count
• For multi-file schemas, creates a ZIP archive of all files
• Automatically manages retention (keeps last N versions based on config)

List Stored Versions

# View all stored schema versions
rails db:schema:versions

Output example:

Total versions: 3

ID     Format  Mode          Files   PostgreSQL      Created              Size
-----------------------------------------------------------------------------------------------
3      sql     multi_file    12      15.3            2025-01-15 10:30:22  56.42 KB
2      sql     single_file   1       15.3            2025-01-14 15:20:10  45.21 KB
1      sql     single_file   1       15.2            2025-01-13 09:45:33  44.03 KB

The multi-file mode example shows 12 files across 10 directories (extensions, types, functions, sequences, tables, indexes, foreign_keys, views, triggers, migrations) stored as a ZIP archive.

Restore from Version

# Restore database from a specific version
rails db:schema:restore[5]

# Or using environment variable
VERSION_ID=5 rails db:schema:restore

Cleanup Old Versions

# Remove old versions based on retention limit
rails db:schema:cleanup

Mount the web interface to browse schema versions:

# config/routes.rb
Rails.application.routes.draw do
  # With authentication (recommended for production)
  authenticate :user, ->(user) { user.admin? } do
    mount BetterStructureSql::Engine, at: '/schema_versions'
  end

  # Or without authentication (development only)
  mount BetterStructureSql::Engine, at: '/schema_versions' if Rails.env.development?
end

Access at http://localhost:3000/schema_versions to:

• View list of up to 100 most recent schema versions (pagination-ready)
• Browse formatted schema content with syntax highlighting (for files <200KB)
• Download raw SQL/Ruby schema files as text
• Download ZIP archives for multi-file schemas
• View manifest metadata for multi-file schemas
• Stream large files efficiently (>2MB) without memory issues
• Compare database versions and formats

Authentication Examples:

# Devise with admin check
authenticate :user, ->(user) { user.admin? } do
  mount BetterStructureSql::Engine, at: '/admin/schema'
end

# Custom constraint class
class AdminConstraint
  def matches?(request)
    user = request.env['warden']&.user
    user&.admin?
  end
end

constraints AdminConstraint.new do
  mount BetterStructureSql::Engine, at: '/schema_versions'
end

# Environment-based
if Rails.env.production?
  # Add your production auth here
else
  mount BetterStructureSql::Engine, at: '/schema_versions'
end

Option 1: After Each Migration (Recommended)

# Run migration and store schema version
rails db:migrate && rails db:schema:store

Option 2: Git Hooks

# .git/hooks/post-merge
#!/bin/bash
if git diff HEAD@{1} --name-only | grep -q "db/migrate"; then
  echo "Migrations detected, storing schema version..."
  rails db:schema:store
fi

Option 3: CI/CD Pipeline

# .github/workflows/deploy.yml
- name: Run migrations and store schema
  run: |
    rails db:migrate
    rails db:schema:store

Note: The gem currently requires the pg gem as a dependency. Multi-database support (MySQL, SQLite) is implemented but requires manual gem installation. Future versions may make database adapters optional.

If you're currently using Rails' schema.rb (Ruby format) and want to switch to structure.sql (SQL format) with BetterStructureSql, we have a comprehensive guide:

📖 Migration Guide: From schema.rb to structure.sql

This guide covers:

• Why migrate from schema.rb to structure.sql
• Step-by-step migration process
• Configuration for both formats
• Switching between formats dynamically
• Comparing SQL vs Ruby schema versions
• Rollback procedures
• Best practices and troubleshooting

BetterStructureSql supports both schema.rb and structure.sql formats, allowing you to:

• Store versions of either format
• Switch between formats using SCHEMA_FORMAT environment variable
• Compare different formats in the web UI
• Migrate gradually from Ruby to SQL format

Codebase Metrics (as of v0.1.0):

• 47 Ruby files in lib/ (~5,296 total lines)
• 25 test files in spec/ (~3,022 lines)
• 8 adapter files (PostgreSQL, MySQL, SQLite, Registry, Configs)
• 13 SQL generators (Tables, Indexes, Functions, Triggers, Views, etc.)
• 9 introspection modules (Extensions, Types, Tables, Indexes, Foreign Keys, etc.)
• 3 integration apps (PostgreSQL, MySQL, SQLite) with Docker support
• React documentation site deployed to GitHub Pages

Test Coverage: Comprehensive RSpec test suite with unit and integration tests across all major components.

Real-World Example: The integration app generates a multi-file schema with:

• 11 SQL files across 10 directories
• 2,345 total lines of SQL
• Complete PostgreSQL feature coverage (extensions, types, functions, triggers, materialized views)

Production Status:

• ✅ PostgreSQL: Fully implemented and tested (primary focus)
• ✅ Multi-file output: Complete with ZIP storage and streaming
• ✅ Schema versioning: Full CRUD with web UI
• ✅ Rails integration: Drop-in replacement for default tasks
• 🧪 MySQL: Adapter implemented, integration app available (experimental)
• 🧪 SQLite: Adapter implemented, basic testing (experimental)

We welcome contributions! Bug reports and pull requests are welcome on GitHub.

1. Fork the repository
2. Create your feature branch (git checkout -b feature/amazing-feature)
3. Run the tests (bundle exec rspec)
4. Commit your changes (git commit -m 'Add amazing feature')
5. Push to the branch (git push origin feature/amazing-feature)
6. Open a Pull Request

This gem is available as open source under the terms of the MIT License.

Made with ❤️ by sebyx07 and contributors

⭐ Star this repo if you find it useful! ⭐